Упражнение по проектированию индексов: стратегия полнотекстового поиска

Команда выкатывает поисковую строку. Пользователи вводят в неё текст. Запрос: WHERE title ILIKE '%invoice%'. На staging с 10k задач всё работает. В продакшне с 50 млн задач — 12 секунд. ILIKE с leading-wildcard не может использовать B-tree. Решение — не «добавить индекс на title». Решение — выбрать правильный тип индекса для конкретного вопроса. Этот выбор зависит от того, нужен пользователям точный поиск по слову, нечёткий поиск или семантический — и этот урок разбирает все три варианта.

Почему ILIKE не масштабируется

WHERE title ILIKE '%term%' имеет leading-wildcard. B-tree индексы требуют известного префикса; они не могут ответить на вопрос «содержит ли эта строка данный фрагмент где-либо». Необходимо вычислять каждую строку — это всегда O(n).

Три масштабируемые альтернативы:

1. GIN на tsvector — инвертированный индекс на уровне слов; отвечает на вопрос «какие документы содержат это слово или фразу»; понимает стемминг и языковые правила.
2. pg_trgm GIN — декомпозиция на триграммы; отвечает на вопрос «какие строки содержат эту подстроку или похожи на неё»; обрабатывает опечатки и частичный ввод.
3. pgvector HNSW — граф-based приближённый поиск ближайших соседей по embedding-векторам; отвечает на вопрос «какие документы семантически похожи на этот запрос»; требует pipeline для инференса модели.

GIN на tsvector: нативный выбор Postgres

-- Добавить generated tsvector-столбец (Postgres 12+)
ALTER TABLE tasks
  ADD COLUMN tsv_search TSVECTOR
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

-- GIN-индекс на generated-столбце
CREATE INDEX CONCURRENTLY idx_tasks_search
  ON tasks USING GIN (tsv_search);

-- Запрос
SELECT id, title, ts_rank(tsv_search, query) AS rank
FROM tasks, to_tsquery('english', 'invoice & payment') AS query
WHERE tsv_search @@ query
ORDER BY rank DESC
LIMIT 20;

Трейдофф: GIN-индексы в 2–5 раз больше данных столбца. Каждый INSERT обновляет GIN posting-листы для каждой лексемы документа. Для документов из 200 слов каждый INSERT затрагивает ~200 GIN-записей. fastupdate=on (по умолчанию) откладывает эти обновления — запись быстрая, но буфер отложенных обновлений рано или поздно сбрасывается, вызывая периодические всплески задержки. Используйте fastupdate=off для write-heavy таблиц, где важна постоянная задержка, а не пиковая пропускная способность.

Не обрабатывает: опечатки (invoce), поиск по подстрокам внутри слов (inv), семантическое сходство.

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

Расширение pg_trgm разбивает строки на триграммы (окна из 3 символов) и строит GIN или GiST индекс на них. Это позволяет:

• LIKE '%term%' и ILIKE '%term%' с поддержкой индекса
• Поиск по сходству (оператор %) для толерантности к опечаткам
• Сортировка по расстоянию сходства <->

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_tasks_title_trgm
  ON tasks USING GIN (title gin_trgm_ops);

-- Теперь используют индекс:
SELECT * FROM tasks WHERE title ILIKE '%invoice%';
SELECT * FROM tasks WHERE title % 'invoce';  -- сходство, обрабатывает опечатку

Трейдофф: триграммные индексы большие (сопоставимо с GIN tsvector или больше для коротких строк), и поиск по сходству медленнее, чем точный поиск по GIN. Лучший вариант для коротких строк (логины, SKU, заголовки), где нечёткий поиск — основной use case.

pgvector HNSW: семантический поиск

Embedding-векторы представляют смысл численно. Два семантически близких документа имеют embedding-векторы, близкие в векторном пространстве. HNSW (Hierarchical Navigable Small World) — граф-based индекс для приближённого поиска ближайших соседей по высокоразмерным векторам.

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE tasks ADD COLUMN embedding VECTOR(1536);  -- например, OpenAI text-embedding-3-small

CREATE INDEX CONCURRENTLY idx_tasks_embedding_hnsw
  ON tasks USING HNSW (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Семантический поиск: найти задачи, семантически похожие на query-embedding
SELECT id, title, 1 - (embedding <=> $1::vector) AS similarity
FROM tasks
ORDER BY embedding <=> $1::vector
LIMIT 20;

Трейдофф: HNSW-индексы большие (размерность вектора × количество строк; embedding размером 1536 измерений на 10 млн строк = ~60 ГБ). Запись медленная — балансировка графа при каждом INSERT. Индекс приближённый: recall@10 обычно 95–99%. Требует pipeline для embedding (инференс модели для каждого документа и каждого запроса).

В продакшн AI/ML системах 2026 года pgvector + HNSW — канонический выбор для семантического поиска при Postgres как основном хранилище. При требованиях, выходящих за возможности pgvector (сотни миллионов векторов, real-time фильтрация), альтернативы — специализированные векторные базы данных (Pinecone, Weaviate, Milvus).

Гибридный подход: объединение стратегий

Для большинства продуктовых требований к поиску гибридный подход совмещает точное и семантическое соответствие:

-- GIN tsvector для точного keyword-ранжирования
-- HNSW для семантического ранжирования
-- Слой приложения: объединение и re-ranking результатов
SELECT id, title, ts_rank(tsv_search, query) AS kw_rank, NULL AS sem_rank
FROM tasks, to_tsquery('english', $1) AS query
WHERE tsv_search @@ query
UNION ALL
SELECT id, title, NULL AS kw_rank, 1 - (embedding <=> $2::vector) AS sem_rank
FROM tasks
ORDER BY embedding <=> $2::vector
LIMIT 50;
-- Объединить по id, суммировать ранги, пересортировать, взять top 20

Это сложнее в реализации, но даёт и точный recall, и семантическую релевантность.