Полнотекстовый поиск

База знаний Brew разрослась: статьи о приготовлении, гайды, заметки бариста. Поиск «в лоб» — body LIKE '%brew%' — почти бесполезен: он не найдёт brewing по запросу brew, не отличит заголовок от тела, не отсортирует по релевантности и на большой таблице всегда читает всё подряд. Postgres умеет настоящий полнотекстовый поиск прямо в БД — без отдельного движка вроде Elasticsearch, пока объёмы умеренные.

Цель юнита — собрать рабочий FTS на встроенных типах: текст → tsvector (нормализованные лексемы), запрос → tsquery, оператор @@ для совпадения и ts_rank для ранжирования. Плюс два приёма для прода: генерируемый tsvector-столбец (БД сама держит его в синхроне) и setweight (заголовок важнее тела).

tsvector и tsquery: что видит поиск

FTS не ищет по сырому тексту — он работает с tsvector: текст разобран на лексемы (нормализованные слова) с позициями. Разбор делает конфигурация языка: 'english' приводит слова к основе (стемминг: brewing и brew → одна лексема brew, hours → hour) и выбрасывает стоп-слова (is, about, for — шум, по которому не ищут). Запрос тоже нормализуется — в tsquery — той же конфигурацией, поэтому brewing в запросе найдёт brew в тексте. Оператор @@ проверяет, удовлетворяет ли tsvector запросу. Это и есть разница с LIKE: поиск идёт по смыслу слов, а не по подстроке.

Генерируемый столбец и веса

Гонять to_tsvector на каждый запрос — дорого; правильнее посчитать его один раз при записи и проиндексировать. Делаем tsvector генерируемым столбцом (GENERATED ALWAYS AS (...) STORED): БД сама пересчитывает его при любом INSERT/UPDATE — никаких триггеров. Под него вешаем GIN-индекс, и @@ летит индексом, а не сканом (06-05). Дальше — setweight: помечаем лексемы из заголовка весом A, из тела — B, и склеиваем (||). При ранжировании ts_rank совпадение в заголовке весит больше, чем в теле, — релевантность из коробки.

ts_rank: сортировка по релевантности

@@ отвечает «да/нет», но пользователю нужен порядок. ts_rank(tsv, query) даёт число тем больше, чем чаще и «весомее» (по setweight) встретились лексемы запроса. Сортируем по нему DESC — и сверху самые релевантные. В демо запрос brew находит две статьи, но у «Cold brew guide» слово есть и в заголовке (вес A), и в теле (B), поэтому её ранг заметно выше.

Конвейер поиска

И текст, и запрос проходят одну нормализацию и встречаются на операторе @@:

plaintext

  тело статьи ──to_tsvector('english',…)──▶ tsvector   'brew':2,8 'cold':1 'hour':11 …
                 стемминг + стоп-слова            │
                                                  @@   совпадает?
                                                  │
  запрос 'brewing' ─plainto_tsquery('english')─▶ tsquery   'brew'
                 та же нормализация
 
  прошедшие @@ ──ts_rank(tsvector, tsquery)──▶ число релевантности → ORDER BY DESC

Вот почему brewing находит brew: обе стороны нормализованы одной конфигурацией, поэтому и в индексе, и в запросе оказывается одна лексема brew. LIKE так не умеет — он сравнивает подстроки сырого текста.

Что показывает наш код

Лабораторный стол kb_articles (английская база знаний) с генерируемым tsvector + GIN. Четыре запроса:

sql

SELECT to_tsvector('english', body) FROM kb_articles WHERE id = 2;             -- ShowTsvector (лексемы)
 
SELECT id, title, ts_rank(tsv, plainto_tsquery('english','brew')) AS rank      -- SearchRanked
FROM kb_articles WHERE tsv @@ plainto_tsquery('english','brew') ORDER BY rank DESC;
 
SELECT id, title FROM kb_articles WHERE tsv @@ to_tsquery('english','milk & cappuccino');  -- SearchAnd
SELECT id, title FROM kb_articles WHERE tsv @@ plainto_tsquery('english','brewing');       -- StemmingMatch

Ранг (float) округляем round(...::numeric, 4)::text — стабильное печатаемое число. Контент английский: конфигурация 'english' встроена и делает стемминг детерминированно, без зависимости от локали машины. Юнит добавляет свой стол → make db-reset накатывает его через brew.Apply.

Запуск

docker compose up -d
make lecture L=07-jsonb-arrays-and-search/07-05-full-text-search T=db-reset
make lecture L=07-jsonb-arrays-and-search/07-05-full-text-search

Вывод:

plaintext

1) tsvector тела статьи 2 (стемминг brewing→brew, hours→hour; стоп-слова выброшены):
   'brew':2,8 'cold':1 'hour':11 'sixteen':10 'temperatur':7 'time':5
 
2) поиск 'brew', ранжирование ts_rank (вес A заголовка > B тела):
ID  ЗАГОЛОВОК        РАНГ
2   Cold brew guide  0.6957
1   Espresso basics  0.2432
 
3) to_tsquery('milk & cappuccino') — нужны обе лексемы:
   1  Espresso basics
   3  Milk steaming
 
4) запрос 'brewing' (стем → brew) — морфология, чего не дал бы LIKE:
   1  Espresso basics
   2  Cold brew guide

В первом блоке видно, что хранит tsvector: brewing и brew слились в 'brew':2,8, temperature → 'temperatur', hours → 'hour', а is/about/not/for выброшены как стоп-слова. Поиск brew поставил «Cold brew guide» выше (0.6957 против 0.2432) — сработал вес заголовка. to_tsquery с & потребовал обе лексемы, а запрос brewing благодаря стеммингу нашёл то же, что и brew.

Заборчик

FTS в Postgres — отличный дефолт, пока объёмы умеренные и язык один-два. Его границы:

ts_rank — простое частотное ранжирование: без обучения, синонимов «из коробки», опечаток (за нечёткость отвечает pg_trgm, см. 07-06) и распределённого индекса;
конфигурацию языка выбирают осознанно: 'english' стеммит по-английски, для русского нужна 'russian', а 'simple' не стеммит вовсе;
без GIN по tsv каждый @@ — это Seq Scan с пересчётом to_tsvector на лету (06-05);
в проде держат словари синонимов и тезаурусы и думают про релевантность как продукт.

За этими границами берут внешний движок — та самая эстафета в Elasticsearch соседнего kafka-cookbook.

Что забрать с собой

FTS: текст → tsvector (лексемы со стеммингом и без стоп-слов), запрос → tsquery, совпадение через @@.
Стемминг даёт морфологию бесплатно (brewing находит brew) — то, чего LIKE '%...%' не умеет.
Генерируемый tsvector-столбец (GENERATED ALWAYS AS ... STORED) + GIN — считаем один раз, ищем индексом; setweight поднимает заголовок над телом.
ts_rank сортирует по релевантности; границы FTS (опечатки, синонимы, масштаб) — повод для pg_trgm или внешнего движка.

Дальше — 07-06 «Нечёткий поиск через pg_trgm»: FTS не прощает опечаток («капучино» vs «капучіно») — добавим триграммную схожесть (similarity, оператор %), ускоренный LIKE и матрицу выбора: когда FTS, когда trgm, а когда пора во внешний движок.

Этот урок ещё впереди