Полнотекстовый поиск в реляционных БД

Введение

Статья 418 кратко упомянула полнотекстовый поиск как применение GIN-индексов PostgreSQL — в этой статье разберём эту тему подробнее, как практичную альтернативу специализированным поисковым системам (Elasticsearch и аналогичным) для проектов с умеренными по объёму требованиями к текстовому поиску, не оправдывающими развёртывание и поддержку отдельной, специализированной поисковой инфраструктуры.

Концепция

Обычный LIKE '%слово%' (статья 394) не использует индекс эффективно и не учитывает морфологию языка (поиск «программирование» не найдёт документ, содержащий «программировать») — полнотекстовый поиск решает обе проблемы: эффективное использование специализированного индекса (GIN, статья 418) и лингвистическую обработку (стемминг/лемматизация, приводящая разные словоформы к общей, сопоставимой основе), учитывающую особенности конкретного языка (через указание языкового словаря, как 'russian' в примерах статьи 418).

Пример кода: Полнотекстовый поиск в реляционных БД

-- Полнотекстовый поиск с весами для разных частей документа — заголовок важнее основного текста
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    setweight(to_tsvector('russian', title), 'A') ||      -- заголовок — наивысший вес
    setweight(to_tsvector('russian', body), 'B');           -- основной текст — более низкий вес

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Поиск с ранжированием по релевантности — наиболее подходящие результаты первыми
SELECT id, title,
       ts_rank(search_vector, to_tsquery('russian', 'qt & программирование')) AS relevance
FROM articles
WHERE search_vector @@ to_tsquery('russian', 'qt & программирование')
ORDER BY relevance DESC
LIMIT 20;

-- Автоматическое поддержание search_vector через триггер (статья 410) при изменении данных
CREATE TRIGGER trg_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.russian', title, body);
-- встроенная в PostgreSQL функция-помощник, автоматически поддерживающая search_vector
-- в актуальном состоянии без необходимости явного UPDATE при каждом изменении title/body

Пояснения к коду

setweight(..., 'A')/setweight(..., 'B') показывают взвешенное объединение нескольких текстовых полей в единый поисковый вектор — совпадение в заголовке ('A', наивысший вес) при последующем ранжировании через ts_rank() будет учитываться как более значимое, чем такое же по сути совпадение где-то в основном тексте статьи ('B'), что соответствует интуитивному ожиданию пользователя — документ, содержащий искомые слова в заголовке, скорее всего более релевантен запросу, чем документ, где те же слова встречаются лишь где-то в глубине основного текста. to_tsquery('russian', 'qt & программирование') показывает синтаксис поискового запроса с явным логическим оператором (& — оба слова должны присутствовать), демонстрирующий, что полнотекстовый поиск PostgreSQL поддерживает не просто проверку наличия отдельного слова, а полноценный язык запросов с логическими операторами. Триггер tsvector_update_trigger показывает практичную автоматизацию — без него разработчик должен бы был не забывать вручную пересчитывать search_vector при каждом изменении title/body (что легко упустить, аналогично общим подводным камням денормализации, статья 403), тогда как встроенная функция-помощник PostgreSQL автоматизирует именно эту конкретную, часто нужную задачу.

Подводные камни

Использование LIKE '%слово%' для текстового поиска вместо нативного полнотекстового поиска, особенно для таблиц значительного объёма, где такой запрос неизбежно приводит к полному просмотру (статья 413, индекс по обычному B-tree не помогает для шаблона, начинающегося с %) — полнотекстовый поиск (с правильно настроенным GIN-индексом) значительно эффективнее для таблиц с большим объёмом текстовых данных и даёт дополнительные, не доступные простому LIKE возможности (учёт словоформ, ранжирование по релевантности, логические операторы запроса).
Игнорирование необходимости выбора правильного языкового словаря ('russian' в примерах) для конкретного языка реального контента — использование словаря, не соответствующего реальному языку текста (например, английского словаря для русскоязычного контента), даёт существенно худшее качество стемминга/лемматизации, фактически сводя полнотекстовый поиск к менее эффективному, чем мог бы быть, базовому поиску без полноценного учёта морфологии конкретного языка.
Восприятие встроенного полнотекстового поиска PostgreSQL как полноценной замены специализированных поисковых систем (Elasticsearch и аналогичных) для действительно масштабных, сложных требований к поиску (фасетный поиск, продвинутая релевантность с учётом множества разнородных факторов, поиск с опечатками через триграммы статьи 423 в сочетании с морфологией, масштаб данных, существенно превышающий разумные пределы единственного экземпляра реляционной БД) — для проектов с умеренными требованиями к поиску встроенный полнотекстовый поиск PostgreSQL — практичный, не требующий дополнительной инфраструктуры выбор, но для действительно продвинутых, масштабных поисковых систем специализированные решения (Elasticsearch и аналогичные) обычно дают значительно более широкий набор возможностей и лучшую масштабируемость именно для поисковых задач.
Забытое обновление search_vector для существующих, уже накопленных строк при первом внедрении полнотекстового поиска в уже работающий проект — добавление столбца search_vector и триггера (tsvector_update_trigger) автоматически поддерживает его актуальность для НОВЫХ и ИЗМЕНЯЕМЫХ строк начиная с момента создания триггера, но не заполняет это поле автоматически для уже существующих, ранее созданных строк, для которых требуется отдельный, явный одноразовый UPDATE всей таблицы с пересчётом search_vector для каждой существующей строки при первоначальном внедрении этой функциональности.