Полнотекстовый поиск PostgreSQL: tsvector, tsquery, GIN

PostgreSQL умеет искать по тексту без Elasticsearch и без любой другой отдельной системы. Каждый документ превращается в tsvector (отсортированный список нормализованных слов с позициями), введённая в строку поиска фраза превращается в tsquery, они сравниваются оператором @@, tsvector индексируется через GIN, чтобы сравнение не читало каждую строку, а найденное сортируется по ts_rank. Для блога, справочного центра, каталога товаров или внутренней вики на несколько миллионов строк этого достаточно, причём поиск живёт в тех же транзакциях, тех же backup и за той же строкой подключения, что и остальные данные. В этом руководстве мы шаг за шагом собираем его на реальной таблице и честно говорим, где он перестаёт быть подходящим инструментом.

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

Что умеет полнотекстовый поиск и чего не умеет LIKE#

Первое, что пробуют, - WHERE body ILIKE '%server%'. На ста строках это работает, а потом ломается тремя способами:

• Обычный индекс не используется. Шаблон с подстановочным знаком в начале заставляет просмотреть каждую строку и прочитать каждый байт каждого текста. Время растёт вместе с таблицей.
• Совпадают символы, а не слова. Запрос run находит prune и brunch, но не находит running и ran.
• Нет понятия релевантности. Строка, где слово упомянуто один раз в сноске, оценивается так же, как строка целиком о нём.

Полнотекстовый поиск решает все три проблемы, потому что работает с лексемами, а не с символами. Документ разбирается на токены, каждый токен проходит через словарь, который отбрасывает стоп-слова (the, and, of) и приводит остальные к корню (running, runs и ran не превращаются в run одинаково - стеммер работает по правилам, и неправильные формы вроде ran обычно остаются в проигрыше, - но running и runs превращаются), и сохраняется результат. Запрос проходит тот же путь, так что обе стороны одинаково понимают, что такое слово.

Преобразование можно увидеть напрямую:

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');

 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

Слова отсортированы, приведены к основе (foxes стало fox, lazy стало lazi - это не английское слово, и ему не обязательно им быть), и у каждого указана позиция. the и over исчезли, но позиции для них всё равно были посчитаны - именно так поиск фразы потом узнаёт, что два слова шли подряд.

Два типа: tsvector и tsquery#

tsvector - это сторона документа. tsquery - сторона вопроса. Оператор сравнения - @@, он возвращает boolean:

SELECT to_tsvector('english', 'PostgreSQL stores documents as lexemes')       @@ to_tsquery('english', 'document & lexeme');

Результат - t. Обе стороны прошли стемминг с одной и той же конфигурацией, поэтому documents совпало с document. Если смешать конфигурации - tsvector собран с english, а запрос с simple, - получатся молчаливые промахи. Это самая частая причина, по которой поиск «работает у меня на машине» и не работает в продакшене. Раздел про языковые конфигурации ниже показывает, как этого избежать.

В PostgreSQL есть четыре способа построить tsquery, и разница в основном в том, что происходит, когда пользователь вводит в строку поиска что-то странное:

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

websearch_to_tsquery (PostgreSQL 11 и новее) - то, что стоит ставить за строку поиска. Она никогда не падает с синтаксической ошибкой на пользовательском вводе и понимает привычки, знакомые людям по обычным поисковикам:

SELECT websearch_to_tsquery('english', '"dedicated server" -windows or linux');

 'dedic' <-> 'server' & !'window' | 'linux'

Фраза в кавычках становится фразой (оператор <-> значит «сразу за»), -word становится отрицанием, а or - оператором |. Слова без кавычек соединяются через AND.

Поиск по префиксу - возможность to_tsquery: to_tsquery('english', 'postgr:*') находит любую лексему, начинающуюся с postgr. На этом строится поиск по мере набора, но с оговоркой: префикс не проходит стемминг так, как целое слово, поэтому проверьте нужные вам случаи.

Рабочий пример: небольшая таблица статей#

Создайте таблицу и добавьте несколько строк. Она заменяет то, по чему вы хотите искать:

CREATE TABLE articles (    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    title      text NOT NULL,    body       text NOT NULL,    created_at timestamptz NOT NULL DEFAULT now());INSERT INTO articles (title, body) VALUES  ('Backups that restore', 'A backup you have never restored is a hope, not a backup.'),  ('Connection pooling', 'Each connection costs memory. A pooler keeps the count low.'),  ('Choosing an index', 'An index speeds up reads and slows down writes on the table.');

Наивный вариант поиска вычисляет tsvector на лету:

SELECT id, titleFROM articlesWHERE to_tsvector('english', title || ' ' || body)      @@ websearch_to_tsquery('english', 'restore backup');

Он корректен и медленен, потому что для каждого запроса PostgreSQL заново разбирает и стеммит текст каждой строки. Чтобы перестать это делать, есть два стандартных способа, и оба заканчиваются индексом GIN.

Вариант 1: хранимая генерируемая колонка

Начиная с PostgreSQL 12 колонку можно вычислять из других колонок и хранить на диске, а синхронизация происходит автоматически:

ALTER TABLE articlesADD COLUMN search tsvectorGENERATED ALWAYS AS (    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

coalesce здесь важен: склейка NULL с чем угодно даёт NULL, и строка с пустым заголовком полностью пропала бы из поиска. Выражение должно быть неизменяемым (immutable), поэтому имя конфигурации записано явно. Этот вариант стоит выбирать по умолчанию. Цена - tsvector занимает место на диске рядом с исходным текстом, а добавление колонки один раз перезаписывает таблицу.

На PostgreSQL 11 и старше генерируемых колонок нет: используйте колонку tsvector, которую обновляет триггер. В PostgreSQL для этого есть помощник tsvector_update_trigger, он описан в главе руководства о текстовом поиске.

Вариант 2: индекс по выражению, без лишней колонки

Если не хочется хранить вектор, проиндексируйте само выражение:

CREATE INDEX articles_fts_exprON articles USING gin (to_tsvector('english', title || ' ' || body));

Тогда запрос должен повторять точно такое же выражение, чтобы планировщик его узнал:

WHERE to_tsvector('english', title || ' ' || body) @@ query

Так экономится хранимая колонка, но легко получить расхождение: измените выражение в одном месте и не в другом - и индекс молча перестанет использоваться. Заметьте и то, что индекс принимает только двухаргументную форму to_tsvector с явно указанной конфигурацией. Одноаргументная форма зависит от параметра сессии default_text_search_config, поэтому не является immutable, и PostgreSQL отказывается её индексировать.

Индекс GIN#

GIN расшифровывается как Generalized Inverted Index. У tsvector много лексем на строку, поэтому полезная структура обратна таблице: для каждой лексемы - список строк, которые её содержат. Поиск backup тогда превращается в одно обращение, а не в полный просмотр.

CREATE INDEX articles_search_idx ON articles USING gin (search);

Проверьте, что индекс используется, через EXPLAIN:

EXPLAINSELECT id, title FROM articlesWHERE search @@ websearch_to_tsquery('english', 'backup');

На таблице из трёх строк планировщик выберет последовательное сканирование, что бы вы ни делали, потому что прочитать три строки дешевле, чем обращаться к индексу. Это правильное поведение, а не отсутствующий индекс. Чтобы увидеть план с индексом на игрушечной таблице, выполните SET enable_seqscan = off; в сессии и повторите explain; ищите Bitmap Index Scan on articles_search_idx. Не оставляйте этот параметр включённым. На таблице с десятками тысяч строк индекс выбирается без всяких подсказок.

GIN - правильный выбор по умолчанию для текстового поиска. Альтернатива, GiST, строит индекс поменьше и обновляемый быстрее, но он медленнее при поиске и с потерями (может вернуть строки, которые потом перепроверяются). Совет самой документации PostgreSQL: GIN для статичных или в основном читаемых данных, а это большинство случаев. Общую картину смотрите в индексах PostgreSQL: типах, порядке и цене.

Цены, о которых нужно знать:

• Размер. GIN-индекс по тексту может занимать значительную долю размера самого текста, иногда больше. Проверьте так: SELECT pg_size_pretty(pg_relation_size('articles_search_idx'));.
• Скорость записи. Вставка одного документа затрагивает много записей индекса, по одной на каждую различную лексему. Чтобы смягчить это, GIN накапливает новые записи в списке ожидания и вливает их пачками (параметр хранения fastupdate, по умолчанию включён). Большую таблицу гораздо быстрее загружать сначала, а индекс создавать потом.
• Раздувание и обслуживание. Список ожидания очищает autovacuum, поэтому таблице с интенсивной записью нужно, чтобы autovacuum справлялся. Как это проверить, объясняет Postgres VACUUM и bloat.

Ранжирование результатов через ts_rank#

Булево совпадение говорит, какие строки подходят, но не какие показывать первыми. ts_rank возвращает для каждого совпадения число с плавающей точкой по тому, как часто и как плотно в тексте встречаются слова запроса:

SELECT id, title,       ts_rank(search, q) AS rankFROM articles,     websearch_to_tsquery('english', 'backup restore') AS qWHERE search @@ qORDER BY rank DESCLIMIT 10;

Если поставить запрос в FROM как q, он вычисляется один раз, а не для каждой строки. Есть родственная функция ts_rank_cd, которая дополнительно поощряет близко стоящие слова («плотность покрытия»). Она дороже, и её стоит пробовать, когда пользователям важны совпадения фраз.

Два ограничения полезно знать заранее:

• Ранжирование не использует индекс. GIN-индекс находит подходящие строки; ранг приходится считать для каждой из них, а потом сортировать. Если частое слово совпало с 400 000 строк, вы ранжируете 400 000 строк. Обычное лекарство - сузить запрос (больше обязательных слов, фильтр по категории или дате) или ранжировать только набор кандидатов.
• Ранжирование не учитывает частоту документов. Встроенное ранжирование PostgreSQL не знает, что одно слово редкое во всём корпусе, а другое частое, как это делает BM25 в специализированных поисковых движках. Для большинства приложений этого достаточно, и это самая частая причина перейти на что-то другое.

Веса: заголовок должен весить больше, чем текст

Части документа можно пометить одним из четырёх весов, от A до D, через setweight. По умолчанию веса умножают совпадение на 1.0 для A, 0.4 для B, 0.2 для C и 0.1 для D. Пересоздайте генерируемую колонку так, чтобы заголовки получили A, а тексты - B:

ALTER TABLE articles DROP COLUMN search;ALTER TABLE articlesADD COLUMN search tsvectorGENERATED ALWAYS AS (    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||    setweight(to_tsvector('english', coalesce(body, '')),  'B')) STORED;CREATE INDEX articles_search_idx ON articles USING gin (search);

Удаление колонки удаляет и старый индекс, поэтому он создаётся заново. Страница, в заголовке которой есть искомое слово, теперь обгонит ту, где оно упомянуто мимоходом. Свои веса можно передать первым аргументом ts_rank массивом в порядке {D, C, B, A}:

SELECT id, ts_rank('{0.1, 0.2, 0.4, 1.0}', search, q) AS rankFROM articles, websearch_to_tsquery('english', 'backup') AS qWHERE search @@ q;

Последним аргументом ts_rank принимает и параметр нормализации - битовую маску. Без него длинные документы получают более высокий балл просто потому, что слов в них больше. Опция 1 делит ранг на 1 + log(длина документа), а 32 приводит ранг к диапазону от 0 до 1. Их можно объединять сложением: ts_rank(search, q, 1 | 32).

Показываем совпадение через ts_headline#

Результаты поиска выглядят гораздо лучше, если показать подходящий фрагмент с подсвеченными словами. Это делает ts_headline:

SELECT id,       ts_headline('english', body, q,                   'StartSel=<mark>, StopSel=</mark>, MaxWords=25, MinWords=10') AS snippetFROM articles,     websearch_to_tsquery('english', 'backup') AS qWHERE search @@ qORDER BY ts_rank(search, q) DESCLIMIT 10;

Две предосторожности. ts_headline работает с исходным текстом, а не с tsvector, поэтому заново разбирает документ; вызывайте её только для строк, которые действительно будете показывать (десять после LIMIT), и никогда для всего результата до сортировки. И она возвращает текст с выбранными вами маркерами, так что при построении веб-страницы экранируйте текст документа сами до подсветки: HTML-экранирование функция не делает.

Языковые конфигурации#

Конфигурация текстового поиска - это рецепт, который решает, как текст разбивается на токены и какие словари приводят токены к основе. english - одна из них. Список того, что есть на вашем сервере:

$ psql -c '\dF'

или из SQL:

SELECT cfgname FROM pg_ts_config ORDER BY cfgname;SHOW default_text_search_config;

Стандартная установка содержит конфигурации для многих европейских языков (french, german, spanish, russian, italian и другие) и конфигурацию simple. Нужно знать о двух видах пробелов:

• Языки без встроенной конфигурации. Чешский и грузинский, например, в стандартный список не входят. Без конфигурации используйте simple: она переводит слова в нижний регистр и оставляет их целыми, без стемминга и стоп-слов. Это даёт только точное совпадение слов, так что server не найдёт servers. Более полная поддержка означает добавление словаря (для чешского, например, Ispell или Hunspell) и построение на нём собственной конфигурации; для этого нужны файлы на диске сервера базы данных, и на управляемом хостинге это обычно невозможно.
• Диакритика. café и cafe по умолчанию - разные лексемы. Расширение unaccent это исправляет, если вы можете его создать.

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

Документы на нескольких языках

Если строки на разных языках, храните конфигурацию для каждой строки. Тип - regconfig:

ALTER TABLE articles ADD COLUMN lang regconfig NOT NULL DEFAULT 'english';-- a generated column that uses the row's own languageALTER TABLE articlesADD COLUMN search tsvector GENERATED ALWAYS AS (    to_tsvector(lang, coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

Это допустимо, потому что аргумент regconfig неизменяем. Во время запроса слова поиска стеммятся на языке, на котором ищет пользователь, а он обычно известен из интерфейса, а не из содержимого. Стемминг запроса не на том языке даёт те же молчаливые промахи, что и смешение конфигураций.

Игнорируем диакритику через собственную конфигурацию

Создайте расширение, скопируйте встроенную конфигурацию и поставьте unaccent перед стеммером:

CREATE EXTENSION IF NOT EXISTS unaccent;CREATE TEXT SEARCH CONFIGURATION english_unaccent (COPY = english);ALTER TEXT SEARCH CONFIGURATION english_unaccent    ALTER MAPPING FOR hword, hword_part, word    WITH unaccent, english_stem;

Теперь to_tsvector('english_unaccent', 'Café') и запрос cafe дают одну и ту же лексему. Если CREATE EXTENSION отклонён с ошибкой прав, роль, под которой вы подключены, не может его устанавливать; на хостинге баз данных проверьте, какие расширения разрешает ваш тариф, прежде чем строить на одном из них. Кто и что может делать, объясняют роли и права в PostgreSQL.

Почему поиск ничего не находит#

Встроенные функции отладки отвечают на большинство вопросов «почему нет совпадения»:

SELECT * FROM ts_debug('english', 'The quick brown foxes');SELECT to_tsvector('english', 'Backups') @@ to_tsquery('english', 'backup');SELECT ts_lexize('english_stem', 'foxes');SELECT * FROM ts_stat('SELECT search FROM articles') ORDER BY ndoc DESC LIMIT 20;

Каждая из них отвечает на свой вопрос:

• ts_debug показывает каждый токен, какое правило парсера его создало, какой словарь его обработал и какая лексема получилась.
• ts_lexize пропускает одно слово через один словарь. Пустой результат значит, что слово в этом словаре - стоп-слово.
• ts_stat перечисляет лексемы в колонке и сколько в них документов и вхождений. Она полезна, чтобы найти слова, настолько частые, что их стоит считать стоп-словами, и заметить мусорные токены вроде длинных hex-строк или фрагментов URL, раздувающие индекс.

Частые причины пустого результата, примерно по убыванию частоты:

1. tsvector собран с одной конфигурацией, а запрос - с другой.
2. Слово поиска - стоп-слово в этом языке, и запрос сводится к ничему. to_tsquery('english', 'the') даёт пустой запрос с уведомлением, а пустой запрос не находит строк.
3. Поиск неполного слова без :*. postgr - это не postgres.
4. Колонка tsvector равна NULL, потому что исходные колонки были NULL, а coalesce не использовался.
5. Колонку изменили, но tsvector не пересобрали (колонка, которую ведут вручную, без своего триггера).

Поиск на небольшом сервере#

Полнотекстовый поиск требователен к памяти особым образом: обращения к GIN быстры, когда индекс в кэше, и заметно медленнее, когда он читается с диска. На небольшом экземпляре важно число, показывающее размер индекса по сравнению с оперативной памятью, которая остаётся после остального рабочего набора. База на тарифе с 2 GB и поисковый индекс в 600 MB - комфортно; индекс в 3 GB на том же тарифе будет в основном читаться с диска. Проверьте размер до и после добавления поисковой колонки:

SELECT pg_size_pretty(pg_total_relation_size('articles')) AS table_and_indexes,       pg_size_pretty(pg_relation_size('articles_search_idx')) AS gin_index;

Настройка shared_buffers и work_mem разобрана в настройке PostgreSQL для небольших серверов. Для поиска быстрее всего помогает следующее: индексируйте только те колонки, по которым действительно ищут, убирайте веса и колонки, которыми не пользуетесь, и делайте запросы избирательнее, чтобы ранжировать меньше строк.

На RE:NODE тарифы базы PostgreSQL начинаются от $6 в месяц на странице хостинга баз данных, а хост, порт, пользователь и пароль создаются для вас вместе с сервером. Вы подключаетесь из psql или из приложения так же, как описано в подключении к PostgreSQL: с чего начать, и выполняете все команды этого руководства как есть. Поисковая колонка и её GIN-индекс - обычные данные таблицы, поэтому они входят в слоты backup вашего тарифа и в дамп. Прежде чем строить что-то на расширении вроде unaccent, выполните CREATE EXTENSION на своей базе и узнайте, разрешено ли оно; не полагайтесь на это руководство. Индекс пересобирается автоматически при восстановлении дампа, и это ещё одна причина держать проверенную процедуру восстановления - смотрите backup и восстановление баз данных.

Когда поиска PostgreSQL недостаточно#

Встроенный поиск - сильный вариант по умолчанию, и честное руководство обязано сказать, где он заканчивается:

• Опечатки и нечёткое совпадение. Полнотекстовый поиск сравнивает лексемы точно. Для поведения «возможно, вы имели в виду» и устойчивости к ошибкам добавьте расширение pg_trgm, которое индексирует фрагменты из трёх символов (тоже через GIN) и поддерживает ранжирование по сходству и быстрый ILIKE '%fragment%'. Некоторые приложения используют оба способа: триграммы для коротких полей вроде имён, полнотекстовый поиск для длинных текстов.
• Настройка релевантности. Без учёта частоты документов и без буста по полям сверх четырёх весов точно подстроить ранжирование, как это позволяет специализированный движок, нельзя.
• Фасетные счётчики и очень большие корпуса. Подсчёт совпадений по категориям на многих миллионах строк медленен, потому что нужно обойти каждое совпадение.
• Тяжёлый поисковый трафик, конкурирующий с приложением. Поиск и ваши транзакционные запросы делят одни и те же CPU и память. Если нагрузка от поиска начинает вредить остальному приложению, пора их разделять.

Если вопрос в том, подходит ли PostgreSQL как база для старта вообще, сравнение вариантов даёт какую базу данных выбрать и MySQL или PostgreSQL. Если данные в основном полуструктурированные документы, PostgreSQL JSONB можно сочетать с полнотекстовым поиском: стройте tsvector из payload ->> 'title' и подобных выражений.

Чек-лист для новой функции поиска#

1. Выберите конфигурацию (english или другую, подходящую вашим данным) и запишите её один раз.
2. Добавьте хранимую генерируемую колонку tsvector с setweight для важных полей и coalesce для каждого поля, допускающего NULL.
3. Создайте индекс GIN по этой колонке.
4. Запрашивайте через websearch_to_tsquery с тем же именем конфигурации.
5. Сортируйте по ts_rank и ограничивайте LIMIT; ts_headline вызывайте только для итоговых строк.
6. Проверьте план через EXPLAIN на реалистичном объёме данных, а не на трёх строках.
7. Проверьте размер индекса и следите за autovacuum на таблицах с интенсивной записью.

FAQ#

Достаточно ли полнотекстового поиска PostgreSQL для продакшена?

Да, для поиска по каталогу, документации, справочному центру и блогу на объёме до нескольких миллионов строк. Он не рассчитан на устойчивость к опечаткам и тонкую настройку релевантности. Многие продакшен-системы годами работают на нём и добавляют отдельный движок, только упёршись в один из описанных выше пределов.

В чём разница между tsvector и tsquery?

tsvector - сохранённая, обработанная форма документа: отсортированные лексемы с позициями. tsquery - обработанная форма поиска: лексемы, соединённые операторами вроде AND, OR, NOT и «следует за». Оператор @@ проверяет, удовлетворяет ли вектор запросу.

Почему мой полнотекстовый запрос не использует индекс GIN?

Обычные причины: таблица слишком мала, чтобы индекс имел смысл; выражение в запросе отличается от выражения в индексе; tsvector собран с другой конфигурацией, чем запрос; или применена одноаргументная форма to_tsvector. Выполните EXPLAIN на таблице с реалистичным числом строк и сравните выражения посимвольно.

Что выбрать: генерируемую колонку или триггер?

На PostgreSQL 12 и новее берите хранимую генерируемую колонку: она короче, её нельзя обойти забытым путём в коде, и она пересобирается сама при восстановлении таблицы. Триггер - запасной вариант для старых версий или для случая, когда вектор нужно строить из других таблиц.

Работает ли полнотекстовый поиск для чешского, грузинского или русского?

Для русского есть встроенная конфигурация. Для чешского и грузинского в стандартной установке её нет, поэтому можно использовать simple для точного совпадения слов или установить словарь и построить собственную конфигурацию там, где это разрешает хостинг. Проверяйте на реальных словах из ваших данных, потому что качество стемминга зависит от языка.

Как сделать так, чтобы поиск игнорировал диакритику и регистр?

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