16 апреля 2026 г.

Full text search в PostgreSQL: tsvector и tsquery

Q: Как обновлять search_vector при UPDATE?

Либо триггером, либо через `GENERATED ALWAYS AS` columns в новых Postgres версиях.

Зачем нужен FTS в БД

Иногда нужно искать по тексту в таблицах — по описаниям товаров, комментариям пользователей, тикетам поддержки. Простой LIKE '%word%' работает для маленьких объёмов, но:

LIKE делает последовательный скан и медленный на больших таблицах.

LIKE ищет буквально, без учёта языковой морфологии. «Работы» и «работа» — разные строки.

LIKE не ранжирует результаты по релевантности.

Full-text search в PostgreSQL решает все три проблемы. Он учитывает морфологию (разные формы слов), ранжирует результаты и работает быстро через специальные индексы.

Для сайтов с большим объёмом контента часто ставят Elasticsearch или Meilisearch. Но для средних проектов FTS в Postgres хватает — и одной БД меньше в стеке.

Основные типы

tsvector — предобработанный текст в виде списка лексем (нормализованных слов).

Пример: «Работы аналитика данных» превращается в 'анализ':2 'данн':3 'работ':1. Слова приведены к base form (lemma), сохраняются позиции.

tsquery — запрос, который сравнивается с tsvector.

'работа & аналитик'::tsquery — ищет документы с обеими лексемами.

Оператор совпадения — @@:

SELECT 'Работы аналитика данных'::tsvector @@ 'работа'::tsquery;
-- true, потому что 'работы' и 'работа' нормализуются к одной лексеме

Русский язык

Для русского нужно указать конфигурацию с русским dictionary:

-- Правильно для русского
SELECT to_tsvector('russian', 'Работы аналитика данных');
-- 'анализ':2 'данн':3 'работ':1

-- Неправильно (default english)
SELECT to_tsvector('Работы аналитика данных');
-- 'аналитика':2 'данных':3 'работы':1 — без нормализации

Русская конфигурация понимает падежи, множественные формы, суффиксы. Без неё поиск будет только по точному совпадению слов.

Для английского аналогично: to_tsvector('english', 'Running the analysis').

Базовый поиск

Типичный запрос:

SELECT title, content
FROM articles
WHERE to_tsvector('russian', title || ' ' || content)
  @@ to_tsquery('russian', 'аналитика');

Находит статьи, где есть «аналитика» или её формы (аналитик, аналитики и т.д.).

Проблема — для каждой строки tsvector считается заново. Seq scan на миллион статей — секунды. Нужен индекс.

GIN-индекс для FTS

Чтобы ускорить, добавляем GIN-индекс:

CREATE INDEX idx_articles_fts
ON articles
USING GIN (to_tsvector('russian', title || ' ' || content));

Теперь тот же запрос использует индекс и возвращается за миллисекунды.

Альтернатива — хранить tsvector в отдельной колонке:

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles
SET search_vector = to_tsvector('russian', title || ' ' || content);

CREATE INDEX idx_articles_sv ON articles USING GIN (search_vector);

Тогда индекс создаётся один раз, при UPDATE-ах нужно обновлять колонку через триггер.

Операторы tsquery

& — AND: оба слова должны быть.

to_tsquery('russian', 'аналитика & данных')

| — OR: хотя бы одно.

to_tsquery('russian', 'SQL | Python')

! — NOT: исключение.

to_tsquery('russian', 'аналитика & !вакансия')

Скобки для сложных выражений:

to_tsquery('russian', '(SQL | Python) & аналитик')

Phrase search

Поиск точной последовательности слов — оператор <->:

to_tsquery('russian', 'продуктовый <-> аналитик')

Находит «продуктовый аналитик» рядом. <2> — через одно слово, <N> — через N.

Для произвольного порядка просто используйте &.

Ранжирование

Одна из главных фич — ранжировать результаты. ts_rank вычисляет релевантность:

SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM articles,
    to_tsquery('russian', 'аналитика & продуктов') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Чем больше rank, тем релевантнее документ. Учитывается частота, близость слов, плотность совпадений.

Более сложный вариант — ts_rank_cd, учитывает, что слова рядом важнее, чем далеко.

Разбираться с такими продвинутыми темами — часть senior-подготовки. В тренажёре Карьерник есть задачи на SQL-эксперименты с индексами и необычными запросами.

Выделение совпадений

Для подсветки найденных слов — ts_headline:

SELECT
    title,
    ts_headline('russian', content, query,
                'StartSel=<mark>, StopSel=</mark>, MaxWords=30')
FROM articles,
    to_tsquery('russian', 'аналитика') AS query
WHERE to_tsvector('russian', content) @@ query;

Возвращает фрагмент текста с выделенными тегами <mark>слово</mark>. Удобно для выдачи в поиске.

Настройка stop words и словаря

По умолчанию русская конфигурация исключает stop words (предлоги, союзы) и нормализует через snowball stemmer.

Можно создать свою конфигурацию с кастомным словарём синонимов, своими stop words и т.д. Для большинства проектов default хватает.

Weights для важности

Можно присвоить вес разным полям:

UPDATE articles
SET search_vector =
    setweight(to_tsvector('russian', title), 'A') ||
    setweight(to_tsvector('russian', content), 'C');

Заголовок — вес A (самый высокий), контент — C. При ranking title будет влиять больше.

Это полезно, когда поиск должен приоритизировать совпадения в важных полях.

Alternative — pg_trgm для fuzzy search

Иногда нужен не точный поиск, а «похожий». Для этого есть расширение pg_trgm:

CREATE EXTENSION pg_trgm;

-- Находит похожие строки
SELECT title FROM articles
WHERE title % 'анналетика'  -- опечатка
ORDER BY similarity(title, 'анналетика') DESC;

Работает на триграммах — разбивает слова на сочетания из 3 символов и сравнивает. Хорошо для поиска с опечатками.

Не заменяет FTS: pg_trgm про похожесть строк, FTS про семантический поиск по лексемам.

Когда Postgres FTS не подходит

Если ваш основной продукт — поиск, и нужны продвинутые возможности (facets, ML-ranking, typo tolerance, autocomplete) — лучше специализированное решение:

Elasticsearch — индустриальный стандарт для search.
Meilisearch — более современный и простой, typo tolerance из коробки.
Typesense — лёгкий, быстрый, с хорошим typo handling.

Postgres FTS — для случаев, когда поиск нужен, но не критичен до уровня «собственный Elastic».

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

На миллион документов Postgres FTS с GIN-индексом даёт ответ за миллисекунды. На 10-50 миллионов — ещё ок.

На сотнях миллионов и выше — производительность просаживается. Нужно либо sharding, либо переход на специализированный search engine.

FAQ

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

Есть через FULLTEXT индексы. По возможностям похож, но чуть проще и менее гибкий, чем в Postgres.

ClickHouse full text search?

С версии 22.4 есть встроенный FTS на основе инвертированного индекса. В стандартных кейсах работает, но менее развит, чем в Postgres.

Elasticsearch или Postgres FTS?

До 1-10 миллионов документов — Postgres. Больше — Elastic. Плюс Elastic даёт faceted search, ML, более гибкий ranking.

Как обновлять search_vector при UPDATE?

Либо триггером, либо через GENERATED ALWAYS AS columns в новых Postgres версиях.

Тренироваться в Telegram