Full-text search: tsvector, tsquery и pg_trgm

Full-text search: tsvector, tsquery и pg_trgm

Главная идея

Как это выглядит на практике

1. Интернет-магазин: поиск товаров через WHERE name LIKE '%смартфон%'. При 200K товаров — 800ms на каждый запрос. Пользователи жалуются на медленный поиск.
2. Первая попытка: добавляешь индекс на name. PostgreSQL не использует индекс для LIKE '%pattern%' (leading wildcard) — только для LIKE 'pattern%' (trailing wildcard). Скорость не улучшается.
3. Узнаёшь про полнотекстовый поиск. Добавляешь столбец search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('russian', name || ' ' || description)) STORED. Создаёшь GIN индекс. Поиск теперь 8ms.
4. Пользователи пишут 'смартфоны samsung' — запрос с опечаткой 'самсунг' не работает. Добавляешь pg_trgm: GIN индекс с gin_trgm_ops на name. Нечёткий поиск через % оператор находит похожие строки.
5. Улучшение релевантности: title должен весить больше description. Добавляешь setweight: to_tsvector... для title получает вес 'A', для description — 'B'. Теперь поиск 'iPhone 15' находит релевантные товары выше.
6. ts_headline для сниппетов: показываешь пользователю контекст вокруг найденных слов с выделением. Добавляешь ts_headline() в SELECT — каждый результат показывает фрагмент с подсвеченными совпадениями.
7. Автодополнение в поисковой строке: LIKE 'текст%' с GIN pg_trgm индексом — возвращает 5 подсказок за 3ms. Пользователи видят предложения по мере ввода.
8. Мультиязычный контент: сайт на русском и английском. Добавляешь два столбца: search_vector (russian конфигурация) и search_vector_en (english). Поиск по обоим через OR — находит контент независимо от языка запроса.
9. Синонимы и словари: пользователи ищут 'ноутбук' но товары называются 'лэптоп'. Добавляешь пользовательский словарь синонимов в PostgreSQL tsearch_data — поиск начинает работать для обоих вариантов.
10. Нагрузочный тест: 500 одновременных поисковых запросов. GIN индекс с tsvector справляется — запросы за 10-15ms. Сравниваешь с LIKE — таймаут на большинстве запросов.
11. Аналитика поиска: логируешь поисковые запросы без результатов. Находишь 'самсунг галакси' — часто ищут, нет результатов. Добавляешь синоним samsung → самсунг. Проблема решена на уровне поисковой конфигурации, не кода.
12. Принятие решения о Elasticsearch: задача — поиск по 50M документам с фасетной фильтрацией, ML-ранжированием по поведению пользователей. PostgreSQL FTS не справится. Мигрируешь поиск в ES, оставляя PostgreSQL как источник данных.

Примеры кода

Настройка FTS: GENERATED column и GIN индекс

-- Таблица статей с FTS
CREATE TABLE articles (
  id           BIGSERIAL PRIMARY KEY,
  title        TEXT NOT NULL,
  body         TEXT,
  author       TEXT,
  tags         TEXT[],
  published_at TIMESTAMPTZ,

  -- GENERATED столбец: автоматически обновляется при INSERT/UPDATE
  -- Конкатенируем title (вес A — важнее) и body (вес B)
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(body,  '')), 'B') ||
    setweight(to_tsvector('russian', coalesce(author,'')), 'C')
  ) STORED
);

-- GIN индекс на search_vector — поиск за миллисекунды
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

-- Поиск с ранжированием
SELECT
  id,
  title,
  author,
  ts_rank(search_vector, query)      AS rank,
  ts_headline('russian', body, query,
    'MaxWords=30, MinWords=15, StartSel=<b>, StopSel=</b>'
  )                                  AS snippet
FROM
  articles,
  websearch_to_tsquery('russian', 'PostgreSQL индексы производительность') AS query
WHERE search_vector @@ query
  AND published_at >= NOW() - INTERVAL '1 year'
ORDER BY rank DESC
LIMIT 20;

-- websearch_to_tsquery парсит запросы в стиле Google:
-- 'postgresql indexes'  → postgresql & indexes
-- '"full text"'         → full <-> text (phrase)
-- 'postgresql -mysql'   → postgresql & !mysql
-- 'postgresql OR mysql' → postgresql | mysql

Расширенный поиск: фразы, операторы, многоязычность

-- Операторы tsquery
-- AND: оба слова должны присутствовать
SELECT * FROM articles WHERE search_vector @@ to_tsquery('russian', 'postgresql & индекс');

-- Phrase: слова рядом (в указанном порядке)
SELECT * FROM articles WHERE search_vector @@ phraseto_tsquery('russian', 'полнотекстовый поиск');
-- Или явный оператор <->: следующий за предыдущим
SELECT * FROM articles WHERE search_vector @@ to_tsquery('russian', 'полнотекстов <-> поиск');
-- <2> означает расстояние 2 слова:
SELECT * FROM articles WHERE search_vector @@ to_tsquery('russian', 'полнотекстов <2> postgresql');

-- Поиск с фильтрацией и сортировкой по релевантности + дате
WITH search AS (
  SELECT
    a.*,
    ts_rank_cd(
      search_vector,
      websearch_to_tsquery('russian', $1),
      32  -- cover density ranking
    ) AS rank
  FROM articles a
  WHERE search_vector @@ websearch_to_tsquery('russian', $1)
    AND published_at IS NOT NULL
)
SELECT *
FROM search
ORDER BY
  rank DESC,
  published_at DESC
LIMIT 10 OFFSET $2;

-- Многоязычный поиск (английский + русский)
ALTER TABLE articles ADD COLUMN search_vector_en TSVECTOR
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX ON articles USING GIN (search_vector_en);

-- Поиск по обоим языкам
SELECT * FROM articles
WHERE search_vector    @@ websearch_to_tsquery('russian', $1)
   OR search_vector_en @@ websearch_to_tsquery('english', $1);

pg_trgm: нечёткий поиск и LIKE с индексом

-- pg_trgm: поиск с опечатками, LIKE с индексом, похожие строки
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN или GiST индекс для триграммного поиска
CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- LIKE с индексом (без pg_trgm — full scan!)
SELECT * FROM products WHERE name ILIKE '%смартф%';
-- С pg_trgm GIN индексом — использует индекс!

-- Поиск похожих строк (с опечатками)
SELECT
  name,
  similarity(name, 'смартфон samsung') AS sim_score
FROM products
WHERE name % 'смартфон samsung'  -- оператор похожести
  AND is_active = true
ORDER BY sim_score DESC
LIMIT 10;

-- similarity() возвращает 0.0-1.0, пороговое значение через pg_trgm.similarity_threshold
SET pg_trgm.similarity_threshold = 0.3;  -- 30% совпадение триграмм

-- Автодополнение: строки начинающиеся с...
SELECT name, similarity(name, 'posta') AS sim
FROM products
WHERE name ILIKE 'posta%'  -- GIN индекс работает для prefix LIKE
ORDER BY sim DESC, name
LIMIT 5;

-- Комбинация FTS + pg_trgm для лучшего UX
SELECT
  id,
  name,
  description,
  GREATEST(
    similarity(name, $1),
    ts_rank(search_vector, websearch_to_tsquery('russian', $1))
  ) AS combined_score
FROM products
WHERE
  name % $1  -- триграммный поиск (с опечатками)
  OR search_vector @@ websearch_to_tsquery('russian', $1)  -- FTS
ORDER BY combined_score DESC
LIMIT 20;

Что происходит под капотом

• Парсер текста (text parser): разбивает текст на токены по типам (слово, цифра, URL, email, имя файла). Каждый тип токена обрабатывается соответствующим словарём.
• Словари (dictionaries): применяются к токенам в заданном порядке. Russian словарь: стоп-слова (и, в, на, для, с → NULL), стемминг snowball алгоритм (разработчик → разработк). Simple словарь: lowercase без стемминга. Unaccent: убирает диакритические знаки.
• tsvector format: 'разработк':1,5,8 'postgresql':3,9 'приложен':2. Слово:позиции. Позиции используются для phrase search (<->) и вычисления ts_rank.
• GIN индекс (Generalized Inverted Index): инвертированный индекс лексема→список (docid, positions). При поиске по tsquery PostgreSQL находит документы для каждой лексемы, пересекает множества (AND), объединяет (OR). Сложность: O(log N + K) где K — число результатов.
• GiST vs GIN для tsvector: GIN быстрее при поиске но медленнее при обновлении. GiST быстрее при обновлении но медленнее при поиске и занимает меньше места. Для большинства случаев — GIN.
• ts_rank() алгоритм: считает частоту появления лексем запроса в документе, нормализует по длине документа (опционально). Веса A/B/C/D влияют на score: совпадение в заголовке (A) даёт больше очков чем в теле (D). ts_rank_cd использует cover density — учитывает расстояние между лексемами запроса в документе.
• pg_trgm триграммы: строка разбивается на все возможные триплеты символов с padding. 'cat' → {' c', ' ca', 'cat', 'at '}. GIN индекс хранит триграммы→список строк. LIKE '%patt%' — извлекает триграммы из паттерна и ищет строки их содержащие. similarity(a, b) = |trigramSet(a) ∩ trigramSet(b)| / |trigramSet(a) ∪ trigramSet(b)|.
• GENERATED STORED vs триггер: GENERATED ALWAYS AS STORED — декларативно, атомарно с INSERT/UPDATE, нельзя записать вручную. Триггер: гибче (можно вычислять из нескольких таблиц), но сложнее в поддержке. Для простых случаев — GENERATED STORED.

Термины урока

Мини-разбор реальной ситуации

Платформа документации: 300K статей, поиск через LIKE '%query%' — 6 секунд, пользователи уходят. Решение: GENERATED tsvector с весами (заголовок A, тело B, теги C) + GIN индекс + websearch_to_tsquery. Добавили pg_trgm на title для autocomplete. Результат: поиск — 12ms (500x быстрее), релевантность улучшилась (title-hits выше body-hits), autocomplete работает с опечатками. Elasticsearch не понадобился — вся функциональность в PostgreSQL, нет синхронизации данных, нет дополнительной инфраструктуры.