Оглавление

• Когда имеет смысл строить поиск в PostgreSQL
• Как это работает: tsvector, tsquery, GIN и триграммы
• Быстрый старт: проектируем поиск по каталогу товаров
• Релевантность и подсветка
• Опечатки и подсказки: триграммы
• Фильтры, сортировка, пагинация
• Миграция без простоя на большой таблице
• Эксплуатация: размер индексов, вакуум, оповещения
• Ограничения и когда нужен отдельный движок
• Чек-лист внедрения

Когда имеет смысл строить поиск в PostgreSQL

Если у вас есть:

• каталог (товары, документы, вакансии),
• база знаний или справочник,
• блог/медиа с фильтрами по категориям и датам,

и при этом нет экстремальных требований к объёму (сотни миллионов документов) и сложному ранжированию — PostgreSQL сэкономит месяцы внедрения и десятки тысяч на инфраструктуре. Вы получаете:

• единый контур данных без отдельного кластера для поиска;
• транзакционную актуальность (без лагов синхронизации);
• зрелые инструменты индексации и ранжирования «из коробки».

Когда Postgres не подойдёт: сложные опечатки на разных языках, специфичное морфологическое ранжирование, поиск по вложенным JSON-полям с агрегациями на терабайтах, heavy-аналитика по логам — там лучше специализированные движки. Но для 80% продуктовых задач Postgres закрывает потребность отлично.

Как это работает: tsvector, tsquery, GIN и триграммы

Ключевые сущности:

• tsvector — «словарь» документа: нормализованные слова (лексемы) с позициями и весами.
• tsquery — запрос: набор лексем с логикой И/ИЛИ/НЕ, поддержка фраз.
• Индекс GIN на tsvector — быстрый поиск по совпадениям и ранжирование.
• Триграммы (pg_trgm) — индексы для поиска по опечаткам и подсказкам.

Для русско- и англоязычного контента мы можем одновременно использовать конфигурации russian и english, плюс расширение unaccent для работы без диакритики.

Быстрый старт: проектируем поиск по каталогу товаров

Предположим, есть таблица products. Поднимем расширения и создадим всё для полноценного поиска.

-- 1) Включаем нужные расширения (однократно на базу)
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2) Пример упрощённой схемы
CREATE TABLE IF NOT EXISTS products (
  id                bigserial PRIMARY KEY,
  title             text NOT NULL,
  short_description text,
  description       text,
  category_id       bigint NOT NULL,
  price_cents       integer NOT NULL,
  is_active         boolean NOT NULL DEFAULT true,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_products_category ON products (category_id);
CREATE INDEX IF NOT EXISTS idx_products_active ON products (is_active);

-- 3) Функция формирования поискового вектора: взвешиваем заголовок сильнее
-- Важно: unaccent — STABLE, поэтому и функция будет STABLE (не IMMUTABLE)
CREATE OR REPLACE FUNCTION make_product_tsvector(
  p_title text,
  p_short text,
  p_desc  text
) RETURNS tsvector
LANGUAGE sql
STABLE
AS $$
  SELECT 
    setweight(to_tsvector('russian', unaccent(coalesce(p_title, ''))), 'A') ||
    setweight(to_tsvector('russian', unaccent(coalesce(p_short, ''))), 'B') ||
    setweight(to_tsvector('russian', unaccent(coalesce(p_desc,  ''))), 'C') ||
    setweight(to_tsvector('english', unaccent(coalesce(p_title, ''))), 'A') ||
    setweight(to_tsvector('english', unaccent(coalesce(p_short, ''))), 'B') ||
    setweight(to_tsvector('english', unaccent(coalesce(p_desc,  ''))), 'C');
$$;

-- 4) Добавляем колонку для поискового вектора
ALTER TABLE products
  ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- 5) Поддерживающий триггер, чтобы вектор обновлялся при изменениях
CREATE OR REPLACE FUNCTION products_searchvector_sync() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.search_vector := make_product_tsvector(NEW.title, NEW.short_description, NEW.description);
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_products_searchvector_sync ON products;
CREATE TRIGGER trg_products_searchvector_sync
BEFORE INSERT OR UPDATE OF title, short_description, description ON products
FOR EACH ROW EXECUTE FUNCTION products_searchvector_sync();

-- 6) Индекс GIN для быстрого поиска (создавайте CONCURRENTLY в отдельной транзакции)
-- Внимание: CREATE INDEX CONCURRENTLY нельзя выполнять внутри транзакции
-- Выполните эту команду отдельно, без BEGIN/COMMIT
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_search_gin
  ON products USING GIN (search_vector);

Теперь запрос с ранжированием и пагинацией:

-- Параметр :q — то, что ввёл пользователь (например, 'iphone 128 -чехол')
-- Используем websearch_to_tsquery для привычной логики поиска (AND, OR, -, кавычки)
WITH q AS (
  SELECT (
    websearch_to_tsquery('russian', unaccent($1)) ||
    websearch_to_tsquery('english', unaccent($1))
  ) AS query
)
SELECT p.id,
       p.title,
       ts_rank_cd(p.search_vector, q.query, 1) AS rank,
       p.price_cents
FROM products p, q
WHERE p.is_active = true
  AND p.search_vector @@ q.query
ORDER BY rank DESC, p.id DESC
LIMIT 20 OFFSET 0; -- подойдёт на старте, а в проде лучше курсорная пагинация по (rank, id)

Почему websearch_to_tsquery? Пользовательские запросы вроде "чехол -силиконовый "iphone 13"" будут интерпретированы ожидаемо: фразы, исключения и т.д.

Релевантность и подсветка

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

WITH q AS (
  SELECT (
    websearch_to_tsquery('russian', unaccent($1)) ||
    websearch_to_tsquery('english', unaccent($1))
  ) AS query
)
SELECT p.id,
       p.title,
       ts_rank_cd(p.search_vector, q.query, 1) AS rank,
       ts_headline(
         'russian',
         coalesce(p.description, ''),
         q.query,
         'StartSel=<mark>,StopSel=</mark>,ShortWord=2,MinWords=5,MaxWords=15'
       ) AS snippet
FROM products p, q
WHERE p.is_active = true
  AND p.search_vector @@ q.query
ORDER BY rank DESC, p.id DESC
LIMIT 10;

ts_headline вернёт фрагмент с подсветкой. Если у вас несколько языков, выбирайте конфигурацию по языку документа (например, храните language_code в таблице и выбирайте соответствующую конфигурацию).

Тонкая настройка ранжирования:

• ts_rank_cd — более «строгое» ранжирование, хорошо режет шум.
• Можно добавить бонусы за точное совпадение бренда/модели.
• Исключите слишком короткие слова (стоп-слова) — это уменьшит шум и нагрузку.

Опечатки и подсказки: триграммы

Полнотекст идеально находит формы слов и фразы, но не всегда «прощает» опечатки. Для подсказок и «похоже на…» используем триграммы.

-- Индекс для подсказок по названию (без диакритики и регистра)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_title_trgm
  ON products USING GIN ((unaccent(lower(title))) gin_trgm_ops);

-- Быстрые подсказки по префиксу/опечаткам
-- :q — пользовательский ввод
SELECT id,
       title,
       similarity(unaccent(lower(title)), unaccent(lower($1))) AS sim
FROM products
WHERE unaccent(lower(title)) % unaccent(lower($1))  -- оператор «похоже»
  AND is_active = true
ORDER BY sim DESC, id DESC
LIMIT 10;

Подсказки удобно запускать «на лету» после 2–3 символов. Для автодополнения по префиксу можно использовать ILIKE с индексацией по триграммам (она тоже сработает):

SELECT id, title
FROM products
WHERE unaccent(lower(title)) ILIKE unaccent(lower($1)) || '%'
  AND is_active = true
ORDER BY id DESC
LIMIT 10;

Фильтры, сортировка, пагинация

Типичный коммерческий поиск включает фильтры: категория, цена, склад. Важно, чтобы они не ломали план и не съедали индекс.

WITH q AS (
  SELECT (
    websearch_to_tsquery('russian', unaccent($1)) ||
    websearch_to_tsquery('english', unaccent($1))
  ) AS query
)
SELECT p.id,
       p.title,
       ts_rank_cd(p.search_vector, q.query, 1) AS rank,
       p.price_cents
FROM products p, q
WHERE p.is_active = true
  AND p.search_vector @@ q.query
  AND p.category_id = ANY($2) -- массив категорий
  AND p.price_cents BETWEEN $3 AND $4
ORDER BY rank DESC, p.id DESC
LIMIT 20;

Советы:

• Держите отдельные индексы по активным фильтрам (category_id, price_cents, is_active). Оптимизатор умеет совмещать их с GIN.
• На проде лучше курсорная пагинация по (rank, id) — это устойчивее Offset на больших выборках.

Миграция без простоя на большой таблице

Добавление STORED-генератора для tsvector перезапишет таблицу — дорого и с риском простоя. Безопасный путь:

1. Добавляем колонку tsvector и триггер (уже сделали выше).
2. Заполняем колонку батчами.
3. Строим индекс CONCURRENTLY.
4. Включаем запросы на новый поиск.

Пример батчевого бэкфилла:

-- Обновляем по первичному ключу порциями по 50k строк
DO $$
DECLARE
  v_last_id bigint := 0;
  v_batch   integer := 50000;
  v_updated integer := 0;
BEGIN
  LOOP
    WITH cte AS (
      SELECT id
      FROM products
      WHERE id > v_last_id
      ORDER BY id
      LIMIT v_batch
    )
    UPDATE products p
       SET search_vector = make_product_tsvector(p.title, p.short_description, p.description)
      FROM cte
     WHERE p.id = cte.id
    RETURNING p.id INTO v_last_id;

    GET DIAGNOSTICS v_updated = ROW_COUNT;
    EXIT WHEN v_updated = 0;
    PERFORM pg_sleep(0.05); -- чуть разгрузим
  END LOOP;
END $$;

-- После бэкфилла стоит пересобрать статистики
ANALYZE products;

Индекс создаём отдельно (вне транзакции):

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_search_gin
  ON products USING GIN (search_vector);

После переключения трафика можно оставить триггер навсегда — он недорогой. Если очень нужна максимальная простота, позже можно пересоздать таблицу с STORED-колонкой и перекинуть данные через атомарный свитч таблиц, но это отдельная операция.

Эксплуатация: размер индексов, вакуум, оповещения

На что смотреть в проде:

• Размер индекса GIN и его рост:

SELECT relname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_products_search_gin';

• Доля «битых» страниц после массовых удалений — периодический VACUUM ANALYZE по таблице, автоваакуум должен быть включён. Если были большие волны апдейтов — подумайте о REINDEX CONCURRENTLY (редко нужно).
• Ограничьте длину пользовательского запроса на уровне API (например, до 200 символов) и отбрасывайте полностью «пустые» запросы после удаления стоп-слов.
• Настройки, которые иногда помогают:
  • gin_pending_list_limit — баланс скорости записи/поиска (по умолчанию нормально).
  • maintenance_work_mem — ускорит построение индекса, если ресурсов хватает.

Для наблюдаемости добавьте:

• метрику времени ответа по поисковым эндпоинтам,
• долю пустых результатов,
• распределение рангов в выдаче (поймёте, когда ухудшается качество),
• алерт на рост ошибки 500 и таймаутов при поиске.

План запроса проверяйте точечно:

EXPLAIN (ANALYZE, BUFFERS)
WITH q AS (
  SELECT (
    websearch_to_tsquery('russian', unaccent('iphone 13')) ||
    websearch_to_tsquery('english', unaccent('iphone 13'))
  ) AS query
)
SELECT id FROM products p, q
WHERE p.is_active = true AND p.search_vector @@ q.query
ORDER BY ts_rank_cd(p.search_vector, q.query, 1) DESC
LIMIT 20;

Ограничения и когда нужен отдельный движок

PostgreSQL даёт быстрый и доступный полнотекст, но у него есть пределы:

• Нелегко строить сложное ранжирование «как у поисковиков» из 100 факторов.
• Нет нативных синонимов «из коробки» (можно допилить словари/тезаурус из contrib, но это ручная работа).
• Огромные объёмы с тяжёлыми фильтрами и агрегациями могут упереться в планировщик и размер индексов.

Когда масштаб вырастет — внедряйте специализированный движок поэтапно: оставьте Postgres для простых кейсов, а тяжёлые — постепенно перенесите. Важно, что старт с Postgres даёт быстрый time-to-value без затрат на отдельный кластер и конвейер индексации.

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

• Включить расширения: unaccent, pg_trgm.
• Добавить search_vector, триггер обновления и GIN-индекс CONCURRENTLY.
• Использовать websearch_to_tsquery + объединение конфигураций языков.
• Настроить ранжирование: веса полей (A/B/C), ts_rank_cd.
• Добавить подсветку через ts_headline.
• Сделать подсказки/опечатки с триграммами.
• Покрыть основные фильтры отдельными индексами.
• Провести бэкфилл батчами + ANALYZE.
• Ограничить длину пользовательского запроса и минимальную длину слова.
• Добавить метрики и оповещения по поисковому эндпоинту.

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