Настройка полнотекстового поиска по кириллице в PostgreSQL

В современных веб‑приложениях часто возникает необходимость сочетать, казалось бы, несвязанные области: мистические практики (например, привороты) и высокопроизводительные базы данных. В данной статье мы покажем, как настроить PostgreSQL для корректного полнотекстового поиска по кириллице, чтобы ваши запросы к таблице с описаниями ритуалов, заклинаний и ингредиентов возвращали точные результаты без лишнего «шума».

Почему кириллица может вызывать проблемы в PostgreSQL?

По умолчанию PostgreSQL использует локаль операционной системы для сортировки и сравнения строк. Если локаль не поддерживает правильную обработку русских букв (например, установлена C или POSIX), то:

• Сортировка происходит по кодовым точкам, а не по алфавиту;
• Поиск по шаблону LIKE или регулярным выражениям может пропускать буквы с диакритическими знаками;
• Полнотекстовый поиск (tsvector/tsquery) не находит слова, если словарь не настроен на русский язык.

Эти проблемы проявляются особенно заметно, когда в базе хранятся описания ритуалов, содержащие архаичные слова, старославянские формы или специальные символы (например, «ѣ», «ъ», «Ё»).

Шаг 1: Выбор правильной локали и collation

Первое, что нужно сделать – создать базу данных с локалью, поддерживающей русский язык.

CREATE DATABASE rituals_db
 WITH ENCODING='UTF8'
 LC_COLLATE='ru_RU.UTF-8'
 LC_CTYPE='ru_RU.UTF-8'
 TEMPLATE=template0;

Если база уже существует, можно изменить collation только для новых колонок, используя COLLATE "ru_RU.UTF-8" при их определении.

Шаг 2: Включение расширения pg_trgm для поиска по подстрокам

Для быстрого поиска по подстрокам (аналогично оператору ILIKE '%слово%') полезно использовать триграммное индексирование.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_rituals_name_trgm
 ON rituals USING GIN (name gin_trgm_ops);

Теперь запрос SELECT * FROM rituals WHERE name ILIKE '%приворот%'; будет использовать индекс и работать быстро даже на миллионах записей.

Шаг 3: Настройка полнотекстового поиска на русском языке

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

1. Создать конфигурацию текстового поиска на основе simple или pg_catalog.russian;
2. Определить колонку типа tsvector и триггер для её автоматического обновления;
3. Выполнять поиск через оператор @@ и tsquery.

-- 1. Создаём свою конфигурацию, основанную на русской
CREATE TEXT SEARCH CONFIGURATION russian_ext ( COPY = pg_catalog.russian );

-- 2. Добавляем собственный словарь синонимов (по желанию)
-- Например, чтобы «приворот» и «заговор» считались эквивалентными:
ALTER TEXT SEARCH CONFIGURATION russian_ext
 ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part
 WITH russian_stem, synonym;

-- 3. Добавляем колонку tsvector
ALTER TABLE rituals ADD COLUMN tsv tsvector;

-- 4. Заполняем её текущими данными
UPDATE rituals SET tsv = to_tsvector('russian_ext', name || ' ' || description);
-- 5. Создаём триггер для автоматического обновления
CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
 ON rituals FOR EACH ROW EXECUTE FUNCTION
 tsvector_update_trigger(tsv, 'russian_ext', name, description);

-- 6. Индексируем tsvector
CREATE INDEX idx_rituals_tsv ON rituals USING GIN (tsv);

Пример поиска:

SELECT name, description
FROM rituals
WHERE tsv @@ to_tsquery('russian_ext', 'приворот & любовь');

Этот запрос найдёт записи, где в названии или описании присутствуют оба слова «приворот» и «любовь» в любой форме (с учётом stemming).

Шаг 4: Избегаем типичных ошибок

• Неправильная кодировка. Убедитесь, что база, клиент и приложение используют UTF‑8. Любая другая кодировка приведёт к «кракозябрам» при выводе кириллицы.
• Отсутствие индекса. Без GIN‑индекса на tsvector или pg_trgm полнотекстовый поиск будет выполнятся последовательным сканированием, что неприемлемо на больших объёмах данных.
• Использование оператора LIKE без учёта локали. LIKE '%приворот%' зависит от collation; если она установлена в C, поиск будет чувствителен к регистру и может пропускать буквы «Ё». Лучше использовать ILIKE вместе с правильной collation или перейти на полнотекстовый поиск.
• Не обновляемый tsvector. Если вы изменяете name или description вручную, не забывайте обновлять колонку tsv (или полагайтесь на триггер).

Практический пример: таблица ритуалов

Представим, что у нас есть таблица rituals со следующими полями:

• id – serial primary key;
• name – текстовое название ритуала;
• description – подробное описание;
• components – массив используемых ингредиентов;
• tsv – вектор для полнотекстового поиска.

После выполнения шагов 1‑3 вы сможете выполнять запросы вида:

-- Найти все ритуалы, связанные с «приворотом» и «свечой»
SELECT id, name, description
FROM rituals
WHERE tsv @@ to_tsquery('russian_ext', 'приворот & свеча');

-- Поиск по подстроке в названии (с использованием pg_trgm)
SELECT id, name
FROM rituals
WHERE name % 'приворот';

Оба запроса будут использовать соответствующие индексы и возвращать результаты за миллисекунды.

Настройка PostgreSQL для корректной работы с кириллицей – это не только техническая необходимость, но и основа для создания удобных и точных сервисов, будь то каталог магических ритуалов, encyclopedia of spells или любой другой контент на русском языке. Следуя представленным шагам (выбор локали, использование pg_trgm, настройка полнотекстового поиска с русским словарем и правильная индексация), вы избавитесь от типичных ошибок поиска и обеспечите быстрый, релевантный ответ на запросы пользователей.

Пусть ваши данные будут находиться так же точно, как правильно выполненный приворот находит свою цель!