PostgreSQL: полнотекстовый поиск (FTS)

PostgreSQL умеет полнотекстовый поиск из коробки: токенизация, словари (включая русский), стемминг, ранжирование. Для большинства проектов «поиск по статьям/товарам/контенту» PG FTS закрывает 90% задач без отдельного Elasticsearch.

Эта статья — что хватает, когда нужен ES, как настроить под русский язык. Правила пронумерованы кодами PG-FTS-NNN.

1. Когда PG FTS хватает

PG-FTS-001 — PG FTS подходит для:

• Корпоративный/админский поиск (≤ 10M документов, ≤ 100 запросов/сек).
• Поиск товаров в e-commerce среднего масштаба.
• Поиск по комментариям, тикетам, документации.
• Когда не нужны: scoring алгоритмы (BM25), агрегации, фасетный поиск, geosearch с релевантностью.

PG-FTS-002 — PG FTS не подходит:

• ≫ 10M документов с ranking-нагрузкой → Elasticsearch / Meilisearch.
• Сложный fuzzy-поиск с typo tolerance → Elasticsearch с edge-ngram.
• Multi-language с автоопределением → Elasticsearch.
• Aggregations + facets + analytics → специализированный движок.

2. Базовые типы и функции

PG-FTS-010 — tsvector — токенизированный документ. tsquery — запрос

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

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

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

PG-FTS-011 — @@ — оператор поиска

tsvector @@ tsquery возвращает boolean.

3. Хранение tsvector

Два подхода:

PG-FTS-020 — Generated column (рекомендуется, PG12+):

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);

PG сам пересчитывает search_doc при каждом INSERT/UPDATE. setweight('A') для важных полей (title), 'B'/'C'/'D' для менее.

PG-FTS-021 — Триггер (для PG <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);

PG-FTS-022 — Хранить tsvector отдельно от текста, не вычислять на лету

Без stored column каждый запрос будет рассчитывать to_tsvector(...) @@ ... — без индекса, медленно.

4. Запросы

PG-FTS-030 — Базовый поиск:

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;

PG-FTS-031 — plainto_tsquery для пользовательского ввода — безопаснее:

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

PG-FTS-032 — websearch_to_tsquery — синтаксис Google-style

(PG11+):

SELECT * FROM article WHERE search_doc @@ websearch_to_tsquery('russian', $1);
-- 'покупатель -ребёнок "новый каталог"'
-- → minus, фразы в кавычках

PG-FTS-033 — Ranking — ts_rank или ts_rank_cd

(cover density). Для большинства проектов хватает ts_rank с весами через setweight.

5. Подсветка результатов

PG-FTS-040 — 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;

6. Конфигурации (словари)

PG-FTS-050 — Встроенные конфигурации в PG:

• simple — без стемминга, только нижний регистр.
• english, russian, german, ... — стемминг по языку.

SELECT cfgname FROM pg_ts_config;

PG-FTS-051 — Если нужно уточнить стемминг (бренды, термины) — кастомная конфигурация:

CREATE TEXT SEARCH DICTIONARY my_synonyms (
    template = synonym,
    synonyms = 'my_synonyms'   -- файл $SHAREDIR/tsearch_data/my_synonyms.syn
);

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

В файле my_synonyms.syn:

postgresql postgres
postgres postgres pg

PG-FTS-052 — Stop words настраиваются через словарь

Дефолтный russian имеет встроенный список (предлоги, союзы).

7. Индексы — GIN vs GiST

PG-FTS-060 — Для read-heavy — GIN. Для write-heavy — GiST

	GIN	GiST
Размер	больше	меньше
Скорость build	медленнее	быстрее
Скорость search	быстрее	медленнее
Скорость update	медленнее	быстрее
Lossy (false positive)	нет	да

В 95% случаев — GIN.

PG-FTS-061 — fastupdate для GIN

— буфер pending-list ускоряет вставки. Default on. Для read-heavy с редкими вставками можно off:

ALTER INDEX ix_article_search_doc SET (fastupdate = off);

8. pg_trgm — триграммы для коротких фраз и опечаток

PG-FTS-070 — pg_trgm дополняет FTS, когда нужны:

• Поиск с опечатками (similarity() через триграммы).
• LIKE '%substring%' с индексом.
• Поиск по очень коротким значениям (имена, бренды), где FTS оверкилл.

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;

PG-FTS-071 — Комбинируй FTS + триграммы:

FTS для длинного контента (тело статьи), pg_trgm для коротких полей с опечатками (имя автора, бренд).

9. Производительность и пагинация

PG-FTS-080 — OFFSET + LIMIT для глубокой пагинации страдает

PG ranks ВСЕ совпадения, потом дропает offset. Для глубоких страниц — keyset pagination по 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;

PG-FTS-081 — Использовать ts_rank в WHERE неэффективно

Считается на каждом совпадении. Лучше — limit'ить только в ORDER BY.

10. Антипаттерны

PG-FTS-090 Хранить to_tsvector(...) как stored function в WHERE — без индекса, медленно.

PG-FTS-091 Использовать simple конфигурацию для русского текста — без стемминга «покупатель» и «покупателя» — разные токены.

PG-FTS-092 Игнорировать веса setweight — все совпадения равны, ranking неточный.

PG-FTS-093 Использовать FTS для ≫10M документов с высокой нагрузкой ранжирования — придёт время Elasticsearch.

PG-FTS-094 Триграммы pg_trgm на полях > 100 символов — индекс распухает, медленно.

---

Чек-лист настройки

• [ ] tsvector хранится как GENERATED ... STORED или через триггер, не вычисляется на лету.
• [ ] GIN-индекс на tsvector колонке.
• [ ] Используется русская конфигурация to_tsvector('russian', ...).
• [ ] Веса setweight для разных полей (title=A, body=B, tags=C).
• [ ] Пользовательский ввод парсится через plainto_tsquery или websearch_to_tsquery, не сырой to_tsquery.
• [ ] Подсветка через ts_headline.
• [ ] Глубокая пагинация — keyset, не OFFSET.
• [ ] Триграммы pg_trgm для поиска по коротким полям с опечатками.

---

Связанные

• Типы индексов — GIN-индекс под FTS.
• Строки — text для содержимого, citext для case-insensitive коротких полей.