Полнотекстовый поиск в PostgreSQL — tsvector, GIN, russian

← назад к разделу

Когда нужен поиск по тексту, первый порыв — поставить Elasticsearch. Но PostgreSQL умеет искать по тексту из коробки, без отдельного сервиса. Разберём, как это работает и когда этого достаточно.

Почему простой LIKE не работает

Самый очевидный способ — WHERE body LIKE '%покупатель%'. У него два недостатка.

Первый — скорость. При поиске с % в начале PostgreSQL не может использовать обычный индекс и вынужден перебирать все строки. На тысяче записей это незаметно, на миллионе — катастрофа.

Второй — грамматика. Запрос LIKE '%покупатель%' не найдёт строку «покупателя выбирают товары», потому что там другое окончание. Пользователи пишут слова в разных формах, а LIKE это не учитывает.

Полнотекстовый поиск решает обе проблемы: он работает по индексу и понимает стемминг — сведение слов к корню.

tsvector и tsquery — два ключевых типа

PostgreSQL хранит обработанный текст в специальном типе tsvector. Это не просто текст, а список лексем — слов, приведённых к корневой форме с указанием позиций.

SELECT to_tsvector('russian', 'Покупатели выбирают товары в каталоге');
-- 'выбира':2 'каталог':5 'покупател':1 'товар':3

«Покупатели» превратились в «покупател» — это корень, под который подходят любые формы слова. Предлог «в» выброшен как незначимый.

Поисковый запрос хранится в типе tsquery:

SELECT to_tsquery('russian', 'покупатель & каталог');
-- 'покупател' & 'каталог'

Проверка совпадения — оператор @@:

SELECT to_tsvector('russian', 'Покупатели выбирают товары в каталоге')
    @@ to_tsquery('russian', 'покупатель & каталог');
-- true

Как хранить tsvector в таблице

Вычислять to_tsvector() при каждом запросе — медленно и без индекса. Правильный путь — хранить вектор отдельно и индексировать его.

Вычисляемая колонка (PostgreSQL 12+)

CREATE TABLE article (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       text NOT NULL,
    body        text NOT NULL,
    search_doc  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('russian', coalesce(body, '')), 'B')
    ) STORED
);

CREATE INDEX ix_article_search_doc ON article USING gin (search_doc);

GENERATED ALWAYS AS ... STORED означает, что PostgreSQL сам пересчитывает колонку при каждом INSERT и UPDATE. Делать это руками не нужно.

setweight('A') помечает токены из заголовка как более важные — это влияет на ранжирование. Доступны веса A, B, C, D от большего к меньшему.

Триггер (PostgreSQL до 12)

CREATE TRIGGER article_search_update
BEFORE INSERT OR UPDATE ON article
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_doc, 'pg_catalog.russian', title, body);

Как искать

Базовый запрос с ранжированием:

SELECT id, title, ts_rank(search_doc, q) AS rank
FROM article, to_tsquery('russian', 'покупатель & товар') q
WHERE search_doc @@ q
ORDER BY rank DESC
LIMIT 20;

ts_rank возвращает число — чем выше, тем лучше совпадение. При одинаковом количестве совпадений выше поднимаются документы, где слова встретились в полях с высоким весом (A > B > C > D).

plainto_tsquery — для пользовательского ввода

to_tsquery требует правильного синтаксиса с операторами &, |, !. Если туда попадёт произвольный ввод пользователя, запрос может упасть с ошибкой.

Для пользовательского ввода используйте plainto_tsquery — он интерпретирует слова через AND без специального синтаксиса:

WHERE search_doc @@ plainto_tsquery('russian', $1);
-- 'купить новый товар' → 'купи' & 'новый' & 'товар'

websearch_to_tsquery — Google-стиль

Доступен с PostgreSQL 11. Понимает минус для исключения слов и кавычки для фразового поиска:

WHERE search_doc @@ websearch_to_tsquery('russian', $1);
-- 'покупатель -ребёнок "новый каталог"'

Конфигурация стемминга

PostgreSQL поставляется с несколькими конфигурациями полнотекстового поиска:

SELECT cfgname FROM pg_ts_config;
-- simple, english, russian, german, ...

• simple — только перевод в нижний регистр, стемминг не применяется. Полезен для кодов, идентификаторов, артикулов.
• russian — стемминг по русскому языку. «Покупатель», «покупателя», «покупателям» — один токен, поиск находит все формы.

Для русскоязычного контента почти всегда нужна конфигурация russian.

Синонимы

Если нужно, чтобы поиск по «postgres» находил и «postgresql», и «pg», можно создать словарь синонимов:

CREATE TEXT SEARCH DICTIONARY my_synonyms (
    template = synonym,
    synonyms = 'my_synonyms'
);

CREATE TEXT SEARCH CONFIGURATION ru_extended (COPY = russian);
ALTER TEXT SEARCH CONFIGURATION ru_extended
    ALTER MAPPING FOR word, asciiword
    WITH my_synonyms, russian_stem;

В файле $SHAREDIR/tsearch_data/my_synonyms.syn:

postgresql postgres
postgres postgres pg

GIN против GiST

Для полнотекстового поиска подходят два типа индексов:

	GIN	GiST
Размер на диске	больше	меньше
Скорость поиска	быстрее	медленнее
Скорость обновления	медленнее	быстрее
Ложные срабатывания	нет	есть (проверяет повторно)

В подавляющем большинстве случаев выбирают GIN — он быстрее при поиске, а это обычно важнее.

GIN накапливает изменения в буфере и сбрасывает их пачкой (fastupdate = on по умолчанию). Для систем с редкими вставками и частым чтением буфер можно отключить:

ALTER INDEX ix_article_search_doc SET (fastupdate = off);

Подсветка совпадений

ts_headline выделяет найденные слова прямо в тексте:

SELECT
    id,
    title,
    ts_headline('russian', body, q,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MaxWords=20')
        AS snippet
FROM article, websearch_to_tsquery('russian', $1) q
WHERE search_doc @@ q
ORDER BY ts_rank(search_doc, q) DESC
LIMIT 20;

MaxFragments=2 — показывать не более двух фрагментов, MaxWords=20 — длина каждого фрагмента в словах.

pg_trgm — нечёткий поиск и подстроки

Полнотекстовый поиск не поможет, если нужно:

• найти товар по части артикула (LIKE '%ABC%'),
• или исправить опечатку в имени (например, «Иванв» вместо «Иванов»).

Для этого есть расширение pg_trgm. Оно разбивает строку на трёхбуквенные группы (триграммы) и строит по ним индекс:

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_customer_name_trgm
    ON customer USING gin (full_name gin_trgm_ops);

-- поиск по подстроке с индексом
SELECT * FROM customer WHERE full_name ILIKE '%иван%';

-- поиск с допуском на опечатки
SELECT * FROM customer
WHERE similarity(full_name, 'иванв') > 0.4
ORDER BY similarity(full_name, 'иванв') DESC
LIMIT 10;

Хорошее сочетание: FTS для длинного текста (тело статьи, описание), pg_trgm для коротких полей с опечатками (имена, бренды, коды). На полях длиннее 100 символов pg_trgm теряет смысл — там лучше FTS.

Пагинация через keyset

OFFSET + LIMIT работает медленно на глубоких страницах: PostgreSQL всё равно вычисляет и сортирует все совпадения, затем отбрасывает нужное количество. На 10-й странице из 50 результатов это пустая работа.

Вместо OFFSET используйте keyset-пагинацию по паре (rank, id):

SELECT id, title, ts_rank(search_doc, q) AS rank
FROM article, websearch_to_tsquery('russian', $1) q
WHERE search_doc @@ q
  AND (ts_rank(search_doc, q), id) < ($prev_rank, $prev_id)
ORDER BY rank DESC, id DESC
LIMIT 20;

$prev_rank и $prev_id — значения последней записи с предыдущей страницы.

Когда PostgreSQL FTS достаточно, а когда нужен Elasticsearch

PostgreSQL FTS хорошо справляется с задачами поиска по статьям, товарам, комментариям, тикетам — при объёме до 10 миллионов документов и нагрузке до 100 запросов в секунду.

Elasticsearch стоит рассматривать, если нужны:

• объёмы значительно больше 10 миллионов документов с сложным ранжированием,
• автоматическое определение языка в многоязычном контенте,
• фасеты, агрегации, аналитика по результатам поиска,
• сложная толерантность к опечаткам.

Частые ошибки

Вычисление на лету. Писать WHERE to_tsvector('russian', body) @@ ... без сохранённой колонки — запрос не использует индекс и сканирует всю таблицу.

Конфигурация simple для русского текста. Без стемминга «покупатель» и «покупателей» — разные слова, поиск не находит нужное.

Нет весов. Если не использовать setweight, заголовок и тело имеют одинаковый приоритет — документ, где слово есть только в теле, встанет выше документа с ним в заголовке.

to_tsquery для пользовательского ввода. Если пользователь введёт спецсимвол, запрос упадёт с ошибкой. Используйте plainto_tsquery или websearch_to_tsquery.

OFFSET на глубоких страницах. Медленно и неэффективно — используйте keyset-пагинацию.

Коротко

• PostgreSQL FTS работает по индексу и понимает грамматику — в отличие от LIKE.
• tsvector — индексируемое представление текста, tsquery — поисковый запрос, @@ — оператор совпадения.
• Конфигурация russian даёт стемминг: разные формы одного слова считаются одним токеном.
• Хранить tsvector в вычисляемой колонке (GENERATED ALWAYS AS ... STORED) и индексировать через GIN.
• setweight расставляет приоритеты полей — заголовок важнее тела, это учитывается при ранжировании.
• Для пользовательского ввода — plainto_tsquery или websearch_to_tsquery, не to_tsquery.
• ts_headline подсвечивает совпадения в тексте.
• pg_trgm дополняет FTS: нечёткий поиск и поиск по подстрокам на коротких полях.
• Для пагинации — keyset по (rank, id), не OFFSET.
• FTS достаточно до ~10M документов и ~100 RPS; при больших объёмах — Elasticsearch.

Что почитать дальше

• Поиск: PostgreSQL FTS или Elasticsearch — детальное сравнение, по каким критериям выбирать.
• Типы индексов в PostgreSQL — GIN, GiST и другие в деталях.
• JSONB в PostgreSQL — GIN-индекс работает и на jsonb.