• Оглавление
• Когда PostgreSQL-поиск уместен для бизнеса
• Архитектура решения
• Схема данных, генерация tsvector и индексы
• Поисковые запросы: точные, человеко-понятные и с ранжированием
• Подсветка фрагментов и сниппеты
• Опечатки и неточный поиск с pg_trgm
• Производительность и размер индексов
• Миграция без простоя: пошагово
• Пограничные случаи и качество результатов
• Мониторинг и эксплуатация
• Чек‑лист внедрения

Когда PostgreSQL-поиск уместен для бизнеса

Отдельный поисковый кластер — это новые серверы, обучение команды, обновления, бэкапы и своя модель отказоустойчивости. Во многих продуктах этого не нужно. Если у вас:

• до десятков миллионов документов/строк (каталоги, статьи, тикеты, карточки клиентов),
• нужны простые операторы (И/ИЛИ/исключение), морфология и релевантность,
• важны предсказуемые затраты и меньше сервисов в эксплуатации,

то встроенный полнотекстовый поиск PostgreSQL закрывает задачу с отличной скоростью и качеством. Он не заменит специализированные движки для сложной аналитики, скоринга по кликам и синонимических графов на сотни миллионов документов, но для 80% прикладных сценариев — это «ещё как да».

Архитектура решения

• Данные живут в вашей основной базе.
• Для полей, по которым ищем (название, описание, атрибуты), формируем поисковый вектор tsvector на языке «russian» (или другом).
• Ставим GIN‑индекс по этому вектору — это быстрый поиск по лексемам.
• Для человеко-понятного ввода используем websearch_to_tsquery: поддержка кавычек, минусов, пробелов.
• Для опечаток и неточных совпадений добавляем расширение pg_trgm и отдельный индекс — как «вторую передачу».
• Релевантность настраиваем весами полей (заголовок важнее, чем описание) и добавляем буст по свежести.
• Сниппеты делаем через ts_headline — подсветка фраз прямо из базы.

Итого: один кластер, один бэкап, один мониторинг.

Схема данных, генерация tsvector и индексы

Начнём с простой модели каталога товаров. Сразу подключим морфологию для русского языка и модуль для опечаток.

-- Подключаем расширения (делается один раз в базе)
CREATE EXTENSION IF NOT EXISTS unaccent;      -- нормализация текста (удаление диакритики)
CREATE EXTENSION IF NOT EXISTS pg_trgm;       -- триграммы для неточного поиска

-- Таблица с данными
CREATE TABLE IF NOT EXISTS product (
  id            bigserial PRIMARY KEY,
  title         text NOT NULL,
  description   text NOT NULL,
  category      text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

-- Для быстрого LIKE/ILIKE и триграмм на title
CREATE INDEX IF NOT EXISTS idx_product_title_trgm
  ON product USING gin (lower(title) gin_trgm_ops);

-- Вариант А: сгенерированная колонка (PostgreSQL 12+), без триггеров
-- Примечание: to_tsvector('russian', ...) детерминирован для одних и тех же входных данных.
ALTER TABLE product ADD COLUMN IF NOT EXISTS search_vector tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('russian', unaccent(coalesce(title, ''))), 'A') ||
  setweight(to_tsvector('russian', unaccent(coalesce(category, ''))), 'B') ||
  setweight(to_tsvector('russian', unaccent(coalesce(description, ''))), 'C')
) STORED;

-- GIN-индекс для полнотекстового поиска
CREATE INDEX IF NOT EXISTS idx_product_search_vector
  ON product USING gin (search_vector);

-- Триггер на обновление updated_at (не обязателен, но полезен)
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS trg_set_updated_at ON product;
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON product FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Если по политике вашей команды генераторы столбцов использовать нельзя — примените триггер для поддержки tsvector:

-- Вариант Б: триггер для tsvector вместо GENERATED столбца
ALTER TABLE product ADD COLUMN IF NOT EXISTS search_vector tsvector;

CREATE OR REPLACE FUNCTION product_tsvector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('russian', unaccent(coalesce(NEW.title, ''))), 'A') ||
    setweight(to_tsvector('russian', unaccent(coalesce(NEW.category, ''))), 'B') ||
    setweight(to_tsvector('russian', unaccent(coalesce(NEW.description, ''))), 'C');
  RETURN NEW;
END
$$ LANGUAGE plpgsql IMMUTABLE; -- тело функции детерминированно для одинаковых входных данных

DROP TRIGGER IF EXISTS trg_product_tsvector ON product;
CREATE TRIGGER trg_product_tsvector
BEFORE INSERT OR UPDATE OF title, category, description ON product
FOR EACH ROW EXECUTE FUNCTION product_tsvector_update();

-- Не забудьте про индекс (если не создавали выше)
CREATE INDEX IF NOT EXISTS idx_product_search_vector
  ON product USING gin (search_vector);

Почему веса A/B/C

Мы говорим поиску, что совпадение в заголовке важнее (A) совпадения в описании (C). Это влияет на итоговый балл релевантности при ранжировании.

Поисковые запросы: точные, человеко-понятные и с ранжированием

Есть три базовых способа превратить строку пользователя в запрос:

• to_tsquery — строгий язык операторов (&, |, !, префиксы :*).
• plainto_tsquery — разбивает строку на лексемы, соединяет через AND.
• websearch_to_tsquery — человеко-понятный синтаксис похож на «гугл»: минус-слова, кавычки, OR.

Рекомендуемый вариант для интерфейсов — websearch_to_tsquery.

-- Пример запроса с websearch_to_tsquery и ранжированием
WITH q AS (
  SELECT websearch_to_tsquery('russian', unaccent($1)) AS query
)
SELECT p.id, p.title,
       ts_rank_cd(p.search_vector, q.query, 1) AS rank,
       p.category, p.created_at
FROM product p, q
WHERE p.search_vector @@ q.query
ORDER BY rank DESC, p.created_at DESC
LIMIT $2 OFFSET $3;

Пояснения:

• ts_rank_cd — ранжирование с «понижением» за слишком частые слова.
• Последний ORDER BY добавляет свежесть как второй критерий.

Если нужен префиксный поиск (по мере ввода), используйте оператор :*:

-- Поддержка автодополнения: ищем слова с указанным префиксом
SELECT id, title
FROM product
WHERE to_tsvector('russian', unaccent(title)) @@ to_tsquery('russian', unaccent($1) || ':*')
ORDER BY id DESC
LIMIT 10;

Подсветка фрагментов и сниппеты

Показать пользователю, почему документ попал в выдачу, — важно для доверия и кликабельности.

WITH q AS (
  SELECT websearch_to_tsquery('russian', unaccent($1)) AS query
)
SELECT p.id,
       p.title,
       ts_headline('russian', p.description, q.query,
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=3, MaxWords=12') AS snippet,
       ts_rank_cd(p.search_vector, q.query) AS rank
FROM product p, q
WHERE p.search_vector @@ q.query
ORDER BY rank DESC
LIMIT 20;

Результат даст два фрагмента описания с выделением совпадений через … — удобно отрендерить как HTML.

Опечатки и неточный поиск с pg_trgm

Пользователь ошибся на одну-две буквы? Добавим триграммный индекс и мягкий поиск. Это не заменяет полнотекстовый, а дополняет его.

-- Уже создали раньше индекс для title. Можно расширить покрытие:
CREATE INDEX IF NOT EXISTS idx_product_desc_trgm
  ON product USING gin (lower(description) gin_trgm_ops);

-- Поиск по близости (similarity). Порог можно настроить, например 0.3–0.5
SELECT id, title
FROM product
WHERE similarity(lower(title), lower($1)) > 0.35
ORDER BY similarity(lower(title), lower($1)) DESC
LIMIT 20;

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

-- $1 = строка поиска, $2 = сколько результатов хотим
WITH params AS (
  SELECT unaccent($1)::text AS q, $2::int AS k
), fts AS (
  SELECT p.id, p.title,
         ts_rank_cd(p.search_vector, websearch_to_tsquery('russian', unaccent(params.q))) AS rank
  FROM product p, params
  WHERE p.search_vector @@ websearch_to_tsquery('russian', unaccent(params.q))
  ORDER BY rank DESC
  LIMIT params.k
), need_more AS (
  SELECT (SELECT count(*) FROM fts) < (SELECT k FROM params) AS few
), trgm AS (
  SELECT p.id, p.title,
         similarity(lower(p.title), lower(params.q)) AS sim
  FROM product p, params, need_more
  WHERE need_more.few
    AND similarity(lower(p.title), lower(params.q)) > 0.35
    AND p.id NOT IN (SELECT id FROM fts)
  ORDER BY sim DESC
  LIMIT GREATEST(0, (SELECT k FROM params) - (SELECT count(*) FROM fts))
)
SELECT * FROM fts
UNION ALL
SELECT * FROM trgm;

Производительность и размер индексов

• GIN‑индекс по tsvector — компактный и быстрый на чтение. На запись дороже, чем B‑tree, но обычно приемлемо, если нет огромного потока апдейтов.
• Для очень горячих таблиц рассмотрите раздельные индексы: по заголовку и по описанию. Это может ускорить вставки.
• Следите за bloat: периодически делайте VACUUM (автоматический) и, при необходимости, REINDEX CONCURRENTLY.
• Обновляете поля — обновляется и tsvector. При большом потоке апдейтов используйте пакетные заливки и отключайте лишние поля из вектора.

Проверяйте планы:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM product
WHERE search_vector @@ websearch_to_tsquery('russian', 'красные кеды');

Ожидаем Bitmap Index Scan по idx_product_search_vector.

Миграция без простоя: пошагово

1. Добавьте столбец и индекс CONCURRENTLY (чтобы не блокировать записи).

-- Если используете GENERATED — добавьте столбец сразу. Иначе сначала пустой столбец.
ALTER TABLE product ADD COLUMN IF NOT EXISTS search_vector tsvector;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_product_search_vector
  ON product USING gin (search_vector);

2. Бекфил вектором партиями, чтобы не забить диск и WAL. Например, по id‑диапазонам.

-- Пример пакетного обновления на 10_000 строк за проход
WITH cte AS (
  SELECT id
  FROM product
  WHERE search_vector IS NULL
  ORDER BY id
  LIMIT 10000
)
UPDATE product p
SET search_vector =
  setweight(to_tsvector('russian', unaccent(coalesce(p.title, ''))), 'A') ||
  setweight(to_tsvector('russian', unaccent(coalesce(p.category, ''))), 'B') ||
  setweight(to_tsvector('russian', unaccent(coalesce(p.description, ''))), 'C')
FROM cte
WHERE p.id = cte.id;

Повторяйте до опустошения.

3. Включите поддержание вектора: либо переключитесь на GENERATED STORED, либо повесьте триггер на INSERT/UPDATE.

4. Переключите приложение на FTS‑запросы. Держите старый способ поиска как фолбэк на время отсечки метрик.

5. Очистите старые индексы LIKE, если они больше не нужны.

Пограничные случаи и качество результатов

• Язык. Настройте конфигурацию под ваш язык: russian, english, simple. Для смешанного контента можно хранить язык в строке и выбирать конфигурацию динамически.
• Стоп‑слова. Очень частые слова обнуляют ранжирование — это нормально. При необходимости настройте словари.
• Синонимы. В PostgreSQL можно подключить тезаурус (thesaurus) или ispell‑словарь. Это отдельная инженерная задача, но заметно улучшает качество.
• Е/Ё и диакритика. unaccent помогает делать единообразный поиск.
• Длинные документы. Держите в search_vector только важные поля. В индексе не обязательно весь текст; остальное используйте лишь для сниппетов.
• Релевантность и бизнес‑логика. Добавляйте факторы: свежесть, популярность (клики/покупки), в наличии ли товар. Это можно учесть вторым/третьим сортировочным ключом.

Пример с бустом по «свежести» и «наличию»:

WITH q AS (
  SELECT websearch_to_tsquery('russian', unaccent($1)) AS query
)
SELECT p.id, p.title,
       ts_rank_cd(p.search_vector, q.query, 1) *
       (1.0 + LEAST(0.5, EXTRACT(EPOCH FROM (now() - p.created_at)) / (60*60*24*30) * -0.02)) -- мягкий буст к новым
       + CASE WHEN stock > 0 THEN 0.1 ELSE 0 END AS score
FROM (
  SELECT id, title, search_vector, created_at,  (random()*10)::int AS stock  -- пример наличия
  FROM product
) p, q
WHERE p.search_vector @@ q.query
ORDER BY score DESC
LIMIT 20;

(В реальном запросе замените генерацию stock на реальное поле.)

Мониторинг и эксплуатация

• pg_stat_statements — смотрите топ тяжёлых запросов.
• Логи медленных запросов — ищите FTS‑запросы без использования индекса (плохие планы при слишком частых словах).
• Размер индексов — отслеживайте рост, при необходимости архивируйте «устаревшие» записи или переносите тяжёлые поля в отдельную таблицу.
• Ваккуум — убедитесь, что autovacuum не отстаёт на таблицах с частыми апдейтами.

Полезные команды:

-- Топ запросов по времени
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- Размеры индексов по таблице
SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_all_indexes
WHERE schemaname = 'public' AND relname = 'product'
ORDER BY pg_relation_size(indexrelid) DESC;

Чеклист внедрения

• Определите поля, по которым ищете, и веса A/B/C исходя из важности.
• Подключите unaccent и, при необходимости, pg_trgm.
• Решите: GENERATED STORED или триггер для tsvector.
• Создайте GIN‑индекс, бекфиллите данные партиями.
• Используйте websearch_to_tsquery для пользовательского ввода.
• Добавьте подсветку фрагментов через ts_headline.
• Настройте фолбэк по триграммам для опечаток.
• Отследите метрики (время ответа, точность, CTR) и подкорректируйте веса и пороги.
• Удалите лишние индексы и ненужные пути поиска.

Итог для бизнеса: поиск работает быстро, стабильно и без отдельного поискового кластера. Меньше сервисов — ниже расходы и риски, а качество результатов — на уровне, достаточном для большинства случаев. Если вырастете до сценариев с миллиардом документов и сложным скорингом — тогда уже будет из чего мигрировать, имея чистую архитектуру и аккуратные данные в одной базе.