Зачем сразу тянуть в проект тяжёлую артиллерию типа Elasticsearch, если нужно просто поискать по тексту статей? Ведь у PostgreSQL есть свой, встроенный полнотекстовый поиск. Многие о нём забывают.

Зачем вообще использовать FTS в PostgreSQL?

Есть база с контентом (статьи, товары, комментарии, что угодно текстовое), и нужно сделать поиск. Первая мысль у многих подключить ElasticSearch, Solr или аналогичный специализированный поисковый движок. Но что если объём данных средний, и нам не нужен весь оверхед отдельного сервиса? PostgreSQL умеет искать по тексту сам.

Конечно, есть и ситуации, когда встроенного FTS может не хватить, например, если нужен фаззи‑поиск (нечёткий, по опечаткам), сложное агрегирование результатов, или кластеризация поиска на масштаб миллиарды документов. Также ElasticSearch может предложить больше возможностей по аналитике поисковых запросов, автодополнение и прочие плюшки. Но в большинстве небольших и средних проектов таких требований нет. Так что имеет смысл хотя бы попробовать встроенный инструмент, прежде чем тянуть в стек что‑то громоздкое.

Настраиваем полнотекстовый поиск в PostgreSQL

Есть таблица статей, скажем:

CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT,
    content   TEXT
);

Задача научиться искать по content (и, возможно, по title) ключевые слова. Начнём с основ: PostgreSQL использует два спецтипа для FTS, TSVECTOR и TSQUERY.

• TSVECTOR особый вектор, хранящий лексемы документа (нормализованные слова) и, опционально, позиции этих слов. Проще говоря, это обработанный текст, пригодный для быстрого поиска.

• TSQUERY тип данных для поискового запроса. Он представляет собой одно или несколько искомых слов, возможно с логическими операторами AND/OR/NOT и так далее

В базе запрос на полнотекстовый поиск выглядит как сравнение tsvector против tsquery с помощью оператора @@ (двойное собачка). Пример:

SELECT * 
FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgres & search');

Этот запрос найдёт все статьи, где в тексте встречаются слова «postgres» и «search» (потому что мы использовали оператор & в tsquery). Но такой подход, вычислять to_tsvector налету в WHERE не совсем хороший, ведь придётся каждый раз парсить весь текст. Гораздо лучше сохранить результат to_tsvector в таблице и проиндексировать его.

Добавляем колонку TSVECTOR

Нам нужна колонка, хранящая tsvector для каждого документа. Можно поступить по‑старому, добавить колонку tsv_content tsvector и повесить триггер, который при INSERT/UPDATE будет обновлять это поле вызовом to_tsvector(...). Но проще и современнее использовать сгенерированную колонку, появившуюся в PostgreSQL 12+. Она вычисляется автоматически на основе других полей.

Добавим такую колонку к нашей таблице:

ALTER TABLE articles 
ADD COLUMN tsv_content tsvector 
    GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

'english' — это конфигурация полнотекстового поиска (о ней чуть позже), content — поле, из которого строим tsvector. STORED означает, что значение сохраняется в таблице и обновляется автоматически при изменении content. То есть ручного триггера не нужно — удобненько.

Теперь в каждой записи articles поле tsv_content содержит лексемы текста. Например, если content = 'PostgreSQL provides a powerful search engine', то в tsv_content хранится примерно "provid":2 "power":4 "search":5 "engine":6 "postgr":1 (лексемы со своими позициями в тексте).

Можно проверить на простом примере:

SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
-- вернет: 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

В этом результате: слова «a», «on», «it» отсутствуют;

слово «rats» преобразовалось в «rat» (это работает стеммер — приводящий множественное число к основе);

знаки препинания игнорируются. Именно такие преобразования и выполняет to_tsvector согласно правилам выбранной языковой конфигурации.

Индекс GIN на tsvector

Настало время ускорить поиск. Для этого создадим индекс по новой колонке tsv_content. Чаще всего для полнотекстового поиска используется индекс типа GIN (Generalized Inverted Index), который подходит для множества ключевых слов. Создаём:

CREATE INDEX idx_articles_fts 
ON articles 
USING GIN(tsv_content);

Теперь наш обработанный текст проиндексирован. Оператор @@ сможет использовать этот индекс, что важно, если строк много. Кстати, индекс хранит лексемы, а не оригинальный текст, поэтому он экономичнее, чем индекс по самому полю content, да и толку индексировать сырые тексты, если мы ищем по словам, а не по буквальным совпадениям фразы.

После создания индекса, любые изменения content будут автоматически реиндексировать tsv_content и обновлять GIN‑индекс. Всё происходит за вас, так что данные в поисковом индексе всегда консистентны с основным текстом.

Поиск запросом TSQUERY (пример использования)

Итак, данные подготовлены, индекс есть. Попробуем что‑нибудь поискать. Представим, у нас наполнена таблица articles с разными текстами. Выполним запрос:

SELECT id, title
FROM articles
WHERE tsv_content @@ plainto_tsquery('english', 'powerful search engine');

Функция plainto_tsquery('english', 'powerful search engine') возьмёт строку «powerful search engine», разобьёт на слова, отфильтрует стоп‑слова и приведёт слова к нормальной форме. Получится некий TSQUERY, в котором слова объединены оператором AND, то есть все должны присутствовать. Конкретно запрос станет аналогом to_tsquery('english', 'power & search & engine'). Таким образом найдём статьи, где есть и «engine», и «search», и «power» (что покроет «powerful» тоже, так как стеммер приводит к «power»).

plainto_tsquery хорош для пользовательского ввода, когда мы не ожидаем, что пользователь будет знать синтаксис логических операторов. Если же мы хотим задать точный логический запрос, можно использовать to_tsquery, где уже сами прописываем & (AND), | (OR), ! (NOT) и даже <-> (FOLLOWED BY), оператор расстояния между словами. Например:

-- Ищем статьи с фразой "search engine"
SELECT id FROM articles 
WHERE tsv_content @@ to_tsquery('english', 'search <-> engine');

Выражение 'search <-> engine' означает, что слова должны идти друг за другом в тексте (через <→ без указания числа — то есть непосредственно подряд). PG сам там подставит <1> между лексемами. Если написать 'search <2> engine', это бы означало, что слово «engine» должно быть ровно через одно слово после «search».

Есть также функция phraseto_tsquery('english', 'search engine'), которая сама составит запрос с нужными <-> между словами (то есть фактически фразовый поиск). Кроме того, начиная с PostgreSQL 11 появился прикольный вариант websearch_to_tsquery, он позволяет писать запрос почти как в гугле, например, кавычки означают фразу, пробелы как AND, можно ставить минус перед словом для NOT и так далее

Пример с websearch_to_tsquery:

SELECT id FROM articles 
WHERE tsv_content @@ websearch_to_tsquery('english', 'Postgres "full text" -Elastic');

Этот запрос найдёт статьи, где встречается «Postgres» и фраза «full text», при этом без слова «Elastic». Удобно, правда?

Ранжирование результатов поиска

Одно дело — найти подходящие статьи, но обычно хочется отсортировать их по релевантности (насколько хорошо они соответствуют запросу). Ведь если результатов много, показать первыми самые подходящие хороший тон.

PostgreSQL имеет функции ts_rank и ts_rank_cd (cd = cover density) для вычисления числового рейтинга соответствия документа запросу. Эти функции берут tsvector и tsquery, и возвращают float, чем выше, тем лучше совпадение. Пример использования:

SELECT id, title, ts_rank(tsv_content, plainto_tsquery('english', 'powerful search engine')) AS rank
FROM articles
WHERE tsv_content @@ plainto_tsquery('english', 'powerful search engine')
ORDER BY rank DESC;

Мы добавили ts_rank(... AS rank) и отсортировали по нему по убыванию. Теперь первая строчка результата самая релевантная. Функция ts_rank по дефолту учитывает частоту и позиции слов. Например, документ, где все искомые слова встречаются ближе к началу текста или чаще, получит больший вес.

Отдельно, если нужно, можно вручную назначить веса (A, B, C, D) разным частям документа. Допустим, мы хотим искать и по заголовку, и по контенту, но совпадения в заголовке считать более значимыми. Решение: склеить tsvector из двух частей с разными весами. Например:

ALTER TABLE articles 
ADD COLUMN tsv_all tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A')    || 
    setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;

Создаём tsvector, где лексемы из title помечены весом A (максимальным), а из content весом B (поменьше). Оператор || объединяет два tsvector. Функция coalesce вставлена, чтобы не было проблем с NULL. Затем индексируем tsv_all аналогично. При поиске по tsv_all и сортировке через ts_rank_cd(tsv_all, query) или ts_rank, движок будет знать, что совпадения с весом A важнее. В итоге статьи, где ключевое слово нашлось в заголовке, окажутся выше, чем те, где оно только глубоко в тексте.

Замечу, что ts_rank_cd чуть иначе считает вес (метод cover density, учитывает плотность распределения терминов). В простых случаях можно использовать обычный ts_rank. Попробуйте оба и посмотрите, в каком порядке они выдают, разница не всегда заметна на малых данных.

Хотите понять, почему документ получил такой рейтинг? Просто выводите ts_rank как колонку и смотрите числа. Можно даже умножать веса на коэффициенты, если нужно тонко настроить порядок. Однако обычно хватает стандартных весов A‑D.

Языковые настройки: стемминг и стоп-слова

При использовании FTS важно правильно выбрать конфигурацию языка. В примерах выше я использовал 'english', значит, PostgreSQL будет применять английские словари для токенизации, стемминга и стоп‑слов. Если тексты на русском, имеет смысл использовать 'russian'. Посмотрим, что это меняет.

Для русского языка у PostgreSQL есть встроенный словарь, который умеет приводить слова к базовой форме. Например, слова «машина», «машины», «машину» все будут индексироваться как «машин» (условно говоря, усечённая форма). А стоп‑словами по‑умолчанию являются самые частотные слова типа «и», «в», «на» и так далее, которые мало что значат для поиска.

Пример для русского:

SELECT to_tsvector('russian', 'машины едут по дороге');
-- вернёт: 'дорог':4 'машин':1

Видно, что «едут», «по» были выброшены/преобразованы. «едут» имеет базовую форму «ехать», но, похоже, может быть признано малоинформативным (или, в зависимости от словаря, может преобразоваться, тут надо смотреть). А слово «машины» привелось к «машин». Таким образом, запрос по слову «машина» найдет этот документ.

Если вы работаете с многоязычными данными, можно указывать конфигурацию прямо в функции to_tsvector или to_tsquery для каждого случая. Даже можно внутри tsvector объединять результаты разных языков (но это экзотика, чаще проще либо разделять индекс по языкам, либо использовать универсальный simple конфиг без стемминга).

Отдельно отмечу: стоп‑слова — это слова, которые исключаются из индекса. Их списки заданы в конфигурации (файлы стоп‑слов для каждого языка). Иногда их нужно подправить, если, например, в вашей предметной области какое‑то частое слово не должно считаться бесполезным. PostgreSQL позволяет создавать свои конфигурации и словари, но это уже тема для отдельной статьи.

Пишем запрос поиска

Сведём всё вместе. Допустим, есть таблица articles с колонкой tsv_content (сгенерированной и проиндексированной, как выше) и, например, 10 000 записей. Мы хотим, чтобы пользователь мог вбить несколько слов и получить список подходящих статей по релевантности. Какой SQL‑запрос мы будем выполнять на сервере?

1. Берём пользовательский ввод, например строку user_query := 'PostgreSQL search engine'. Возможно, стоит эту строку немного очистить (удалить лишние символы, привести к нужному регистру, хотя функции FTS сами приведут к нижнему).

2. Подставляем в plainto_tsquery('english', user_query) — это преобразует строку в tsquery, понятный PostgreSQL. Если нужно, можно определить язык на основе настроек пользователя или содержимого (например, если статьи только на русском, ставим 'russian').

3. Выполняем запрос:

   SELECT id, title, ts_rank(tsv_content, query) AS rank
   FROM articles, to_tsquery('english', :user_query) AS query
   WHERE tsv_content @@ query
   ORDER BY rank DESC
   LIMIT 20;

   to_tsquery('english', :user_query) AS queryсоздаст единовременно значение tsquery и позволит использовать его в SELECT и WHERE, чтобы не вызывать функцию дважды. Можно то же самое сделать через CTE или подзапрос. Обратите внимание, можно использовать plainto_tsquery или websearch_to_tsquery аналогично, зависит от того, хотите ли вы поддерживать операторы в пользовательском запросе.

4. Этот запрос вернёт топ-20 самых релевантных статей. Можно также вывести краткий фрагмент статьи с подсветкой найденных слов, для этого есть функция ts_headline. Она берёт текст и tsquery, и обрамляет найденные слова тегами (по умолчанию <b>...</b>). Пример:

   SELECT ts_headline('english', content, query, 'StartSel=<mark>, StopSel=</mark>') 
   FROM articles, to_tsquery('english', 'postgres search') AS query 
   WHERE tsv_content @@ query;

   Настроили выделение через <mark>...</mark> для HTML. Функция ts_headline достаточно умна, она старается выбрать вокруг совпадений небольшой контекст. Так пользователь увидит отрывок текста с подсветкой искомых слов.

Заключение: всегда ли хватит PostgreSQL?

Конечно, PostgreSQL не претендует полностью заменить спецпоисковики. У ElasticSearch есть фичи: масштабирование на кластере, сложная агрегация аналитики запросов, уютный JSON API и Kibana для анализа, поддержка опечаток (fuzzy search) и так далее Но за всё это платится сложностью и ресурсами. Если вы не индексируете гигантский поток разнородных документов, а просто хотите поискать по полям в своей базе, попробуйте для начала PostgreSQL FTS. Как минимум, вы лучше поймёте свои потребности и сможете оценить, нужен ли внешне поисковый движок.

Когда задачам FTS становится тесно в пределах одного узла, приходится разбираться в самом Postgres глубже — от кластеров до многооблачных сред. Для этого пригодится системное понимание инфраструктуры и производительности. Продвинутый курс от OTUS как раз про такие вещи: Patroni, MPP-кластеры, Kubernetes, облака и реалистичная практика работы с PostgreSQL в сложных окружениях.

Пройдите вступительный тест, чтобы узнать, подойдет ли вам программа курса.

А чтобы узнать больше о формате обучения и познакомиться с преподавателями, приходите на бесплатные демо-уроки:

• 2 декабря: AI-Ops для PostgreSQL: нейросети против узких мест и деградации производительности. Записаться

• 8 декабря: PostgreSQL и DevOps — управляем базой данных через CI/CD и Kubernetes. Записаться

• 23 декабря: Как восстановить базу PostgreSQL на конкретный момент времени. Записаться