Бесшовная интеграция PostgreSQL и Sphinx Search

Интеграция PostgreSQL и Sphinx Search

Полнотекстовый поиск - одна из самых распространённых задач, с которой придётся столкнуться при разработке сайта. Такие популярные РСУБД как MySQL и PostgreSQL уже содержат встроенные поисковые механизмы. Но, к сожалению, их реализация оставляет желать лучшего. Простая разбивка на слова не подходит для языков с флексиями, таким как русский.

На выручку приходят специализированные инструменты, хорошо разбирающиеся в разных языках. Зарекомендовал себя поисковый сервер Sphinx, поддерживающий большое количество языков. Есть в Sphinx даже такая интересная возможность как фонетический поиск. Это значит, что можно искать такие слова как: «моск», «афтар», «превед» и находить «мозг», «автор», «привет».

К сожалению, за возможность искать (и находить) произвольный текст приходится расплачиваться гибкостью.

Традиционная схема работы с поисковым сервером такая:

  1. программа-индексатор обращается напрямую к базе данных, сканнирует нужные таблицы и строит индексные файлы;
  2. запускается поисковая служба (демон);
  3. приложение обращается к службе с поисковым запросом и получает результат.

Если же данные обновляются (а скорее всего так и есть), то индексы нуждаются в обновлении. И тут схема далека от идеала:

  1. программа-индексатор строит новый индекс в промежуточных файлах;
  2. посылается сигнал поисковой службе, и она переключается на новые индексы;
  3. старые индексы удаляются.
Индексатор нужно запускать самостоятельно (cron) и это приводит к тому, что данные попадают в поисковые индексы не сразу. Во время обновления индекс требует в два раза большего места на диске. Есть возможность делать инкрементальные обновления, но они требуют дополнительной процедуры слияния индексов. Также придётся как-то определять, какие данные уже проиндексированы, а какие ещё нет. Тут можно придумать много всяких способов: запоминать последний добавленный id, булево поле и т. п. Так или иначе, это потребует написания какого-то кода. Если ещё вспомнить, что данные могут не только добавляться, но и обновляться и даже удаляться, то рисуется безрадостная картина.

К счастью, выход есть. Sphinx предлагает такую замечательную возможность, как real-time-индексы. Основное отличие RT-индексов в том, что они не требуют индексатора и позволяют добавлять, обновлять и удалять данные обращаясь непосредственно к поисковой службе.

В традиционной схеме индексатор самостоятельно обращается к базе данных. Здесь же приложение вынуждено обновлять данные. Нельзя ли совместить эти две схемы? Нельзя ли переложить эту работу на базу данных?

Оказывается, можно. Современные РСУБД позволяют отслеживать изменения в данных и реагировать на них соответственно (так называемые триггеры). То есть заботу об обновлении поискового индекса можно переложить на триггеры.

К несчастью триггеры не могут устанавливать сетевых соединений и всю чёрную работу придётся делать в пользовательских функциях (UDF — user-defined function). В случае PostgreSQL это потребует написания разделяемой библиотеки. Можно было бы написать например на Python, но мы же хотим максимальной производительности!

От слов к делу

Расширение pg-sphinx для PostgreSQL позволяет обновлять индексы непосредственно из триггеров, хранимых процедур и любых других мест, где возможен вызов функции. Например, нижеприведённый SQL-код обновляет (или вставляет, если её ещё не было) запись №3 в индексе blog_posts.

    SELECT sphinx_replace('blog_posts', 3, ARRAY[
      'title', 'Отчёт',
      'content', 'Вот фоточки с последней поездки...'
      ]);
  

Удаление ещё проще:

    SELECT sphinx_delete('blog_posts', 3);
  

Раз уж у нас есть расширение, почему бы не пойти дальше? Можно кроме обновления данных делать и поисковые запросы прямо из базы данных.

    SELECT * FROM sphinx_search(
                    'blog_posts',       /* индекс */
                    'рецепты майонез',  /* запрос */
                    'author_id = 361',  /* дополнительное условие */
                    '@relevance DESC',  /* порядок сортировки */
                    0,                  /* смещение */
                    3,                  /* лимит */
                    null);              /* опции */
  

Подобный запрос выдаст что-то в духе:

id weight
144 1661
135 1644
130 1640

Пока ничего особенного. Такой же результат мы могли получить в приложении обратившись непосредственно к поисковой службе. Однако такая функция в SQL-сервере открывает широкие возможности, ведь её можно использовать и в более сложных запросах. Первое и самое простое, что приходит на ум — использовать её в как источник для INNER JOIN.

    SELECT posts.*, ss.weight
    FROM posts
    INNER JOIN sphinx_search(
                        'blog_posts',       /* индекс */
                        'рецепты майонез',  /* запрос */
                        'author_id = 361',  /* дополнительное условие */
                        '@relevance DESC',  /* порядок сортировки */
                        0,                  /* смещение */
                        3,                  /* лимит */
                        null)               /* опции */
        AS ss ON ss.id = posts.id;
  

Такой запрос не просто ищет идентификаторы, но и выбирает сами записи.

id title content weight
144 Бессмысленно и беспощадно Для чего фаршировать котлеты макаронами... 1661
135 В копилку идей Надеюсь подборочка рецептиков освежит вашу фантазию... 1644
130 Когда душа требует праздника Хочется карнавала, бразильского... 1640

Его уже можно использовать в непосредственно в приложении. Результат такого запроса можно свободно передать в ORM.

Итого

Что получилось?

  1. Избавились от запуска индексатора, нет пиков нагрузки на базу данных.
  2. Данные в поисковом индексе всегда актуальны.
  3. Приложение не заботится об обновлении индексов, этим занимается сама РСУБД.
  4. Поиск выполняется на стороне сервера баз данных и приложению не нужно поддерживать соединение с поисковым сервером.
  5. Поисковые запросы можно произвольным образом смешивать между собой и с запросами к самим данным.

Чего не хватает? Что не реализовано?

  1. Сделано неявное предположение, что все данные хранятся в кодировке UTF-8 и поддержка других кодировок не сделана намеренно.
  2. Подсветка найденных слов не реализована.
  3. Перенастройка подключения к поисковому серверу требует перекомпиляции.
  4. Не реализованы такие функции как транзакции (сейчас AUTOCOMMIT по-умолчанию) и пользовательские функции (нужно разворачивать выражения явно).
  5. ...
Однако, это расширение можно использовать уже сейчас в большинстве простых приложений, несмотря на недостатки.

Ссылки

  1. Sphinx
  2. PostgreSQL
  3. pg-sphinx
Связаться