PostgreSQL
Полнотекстовый поиск: tsvector и tsquery
Пользователь вводит `databse indexs` с опечаткой - он всё равно должен найти нужное. LIKE делает Seq Scan, Elasticsearch требует отдельного кластера. PostgreSQL FTS с правильными словарями и GIN-индексом даёт умный поиск прямо в базе - без синхронизации, без eventual consistency, без отдельного сервиса.
- **Stack Overflow:** PostgreSQL FTS на 50+ млн вопросов - ответ за 20-50 мс без Elasticsearch; GIN-индекс на generated tsvector-колонку покрывает title и body с разными весами
- **Basecamp:** полнотекстовый поиск по проектным документам, задачам и сообщениям - всё в PostgreSQL; отказались от внешнего поискового движка ради простоты и консистентности
- **Discourse (форум):** поиск по миллионам постов через PostgreSQL FTS; `websearch_to_tsquery` обрабатывает пользовательский ввод, ts_headline генерирует сниппеты прямо в SQL-запросе
tsvector и tsquery: основа FTS
Полнотекстовый поиск в PostgreSQL строится на двух типах: `tsvector` - нормализованный вектор лексем (слов без окончаний, стоп-слов, дублей), и `tsquery` - поисковый запрос с булевой логикой. Оператор `@@` проверяет совпадение между ними.
Всегда предпочитай `websearch_to_tsquery` для пользовательского ввода - поддерживает кавычки для фраз, минус для исключения, OR. `to_tsquery` требует явных операторов `&`, `|`, `!` и падает с ошибкой на обычный текст.
Какая функция корректно обработает пользовательский поисковый запрос `"machine learning" -deep` ?
Конфигурации поиска и языки
Text search configuration (TSConfig) определяет: какие словари использовать для стемминга, какие слова считать стоп-словами, как обрабатывать числа и аббревиатуры. PostgreSQL поставляется с готовыми конфигурациями для 20+ языков.
Для русскоязычного поиска используй конфигурацию `russian`, но учти: стандартный стеммер Snowball для русского работает хуже, чем Ispell-словари. Для production-поиска на русском рассмотри расширение `hunspell_ru_ru` или `pg_trgm` как дополнение.
Как проверить, какие лексемы PostgreSQL извлечёт из текста при заданной конфигурации поиска?
Ранжирование результатов поиска
Найти документы - это полдела. Отсортировать их по релевантности - вот где FTS становится настоящим. PostgreSQL предоставляет `ts_rank` и `ts_rank_cd` для расчёта релевантности на основе частоты и позиции вхождений.
Параметр normalization в `ts_rank`: `0` - не нормировать; `1` - делить на 1 + log(length); `2` - делить на length; `32` - делить на rank + 1. Без нормализации длинные документы всегда выигрывают у коротких даже при одинаковой плотности термина.
Что делает `setweight(to_tsvector('english', title), 'A')` перед вызовом `ts_rank`?
Словари и подсветка результатов
Словари (dictionaries) в FTS выполняют нормализацию: стеммер Snowball убирает окончания, Ispell проверяет орфографию, synonym заменяет слова на канонические формы, thesaurus группирует синонимы. Цепочка словарей применяется последовательно.
`ts_headline` работает с исходным текстом, не с tsvector, поэтому его нельзя использовать с GIN-индексом. Для production выноси tsvector в отдельную колонку: `ALTER TABLE articles ADD COLUMN fts tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;` - тогда поиск использует индекс, а `ts_headline` применяется к сырому тексту только у найденных строк.
Почему `ts_headline` не ускоряется от GIN-индекса по tsvector?
GIN-индекс для полнотекстового поиска
Без GIN-индекса FTS выполняет Seq Scan с вызовом `to_tsvector` для каждой строки - на таблице с 1 млн записей это секунды. GIN-индекс на сохранённый tsvector делает поиск за миллисекунды.
Stack Overflow использует PostgreSQL FTS для поиска по вопросам. При 50+ млн вопросов GIN-индекс на tsvector-колонку обеспечивает ответ за 20-50 мс. Альтернатива - Elasticsearch - требует отдельного кластера, синхронизации данных и дополнительной инфраструктуры.
PostgreSQL FTS - слабая альтернатива Elasticsearch; нужен Elasticsearch для любого серьёзного поиска.
PostgreSQL FTS покрывает 80% продуктовых потребностей: булевы запросы, ранжирование, подсветка, многоязычность, фразовый поиск. Elasticsearch оправдан при сложной аналитике поискового поведения, миллиардах документов или распределённом кластере.
Stack Overflow, Basecamp, GitHub (частично) используют PostgreSQL FTS в production. Главное преимущество - данные уже в PostgreSQL, нет проблем с eventual consistency между основной БД и поисковым движком. Elasticsearch добавляет операционную сложность: синхронизация, дополнительный кластер, двойное хранение данных.
Какой подход создаёт наиболее эффективный FTS на таблице `articles(title, body)`?
Итоги
- **tsvector + tsquery + @@:** основа FTS; `to_tsvector` нормализует текст, `websearch_to_tsquery` обрабатывает пользовательский ввод безопасно
- **GIN + STORED column:** добавь колонку `fts tsvector GENERATED ALWAYS AS (...) STORED`, создай `GIN`-индекс - поиск будет быстрым и автоматически актуальным
- **Ранжирование:** `ts_rank_cd` + `setweight` (A для заголовка, B для тела) + нормализация по длине документа дают качественный порядок результатов
Связанные темы
Полнотекстовый поиск взаимодействует с несколькими механизмами PostgreSQL:
- JSONB и GIN-индексы — FTS и JSONB-поиск используют одинаковый механизм GIN-индексов; понимание одного помогает разобраться в другом
- Расширения: pg_trgm — pg_trgm дополняет FTS для fuzzy-поиска с опечатками и similarity-matching; часто используются вместе
- Индексы PostgreSQL — GIN - это тип индекса; выбор между GIN и GiST для FTS требует понимания trade-offs обоих типов
Вопросы для размышления
- Сайт с документацией хранит статьи на русском и английском. Как настроить FTS так, чтобы поиск работал правильно для обоих языков одновременно?
- Пользователь жалуется, что поиск `PostgreSQL` не находит статьи где написано `Postgres`. Как это исправить без изменения существующих данных?
- Таблица с 10 млн записей; добавили GIN-индекс на tsvector, но EXPLAIN всё равно показывает Seq Scan. Что могло пойти не так?