Что такое полнотекстовый поиск в PostgreSQL?
Полнотекстовый поиск в PostgreSQL
Полнотекстовый поиск (Full-Text Search, FTS) — это механизм поиска по текстовым данным, который понимает структуру языка: учитывает морфологию (леммы и корни слов), стоп-слова, синонимы и позволяет ранжировать результаты по релевантности. В отличие от LIKE/ILIKE, FTS работает через предварительно обработанные индексируемые структуры.
Ключевые типы данных
tsvector — нормализованное представление документа. Хранит список лексем (нормализованных слов) с позициями и весами. Получается через функцию to_tsvector(config, text).
tsquery — поисковый запрос: набор лексем с логическими операторами (& — И, | — ИЛИ, ! — НЕ, <-> — следование). Получается через to_tsquery, plainto_tsquery, websearch_to_tsquery.
-- Преобразование текста в tsvector
SELECT to_tsvector('russian', 'Программирование на PostgreSQL — мощный инструмент');
-- 'инструмент':5 'мощн':4 'postgresql':3 'программирован':1
-- Преобразование запроса в tsquery
SELECT to_tsquery('russian', 'программирование & postgresql');
-- 'программирован' & 'postgresql'
Оператор совпадения
Для проверки совпадения используется оператор @@:
SELECT * FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'postgresql & поиск');
Индексы для FTS
Для производительности необходимо создать GIN или GiST индекс. GIN — быстрее для поиска, GiST — быстрее для обновления.
-- Рекомендуемый подход: отдельная колонка tsvector
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles
SET search_vector = to_tsvector('russian', coalesce(title, '') || ' ' || coalesce(body, ''));
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);
-- Автоматическое обновление через триггер
CREATE TRIGGER update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.russian', title, body);
Ранжирование результатов
PostgreSQL предоставляет функции ts_rank и ts_rank_cd для сортировки по релевантности:
SELECT
title,
ts_rank(search_vector, query) AS rank
FROM articles,
to_tsquery('russian', 'postgresql & поиск') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
Подсветка результатов
Функция ts_headline позволяет выделить найденные фрагменты:
SELECT ts_headline(
'russian',
body,
to_tsquery('russian', 'postgresql'),
'MaxWords=15, MinWords=5, StartSel=<b>, StopSel=</b>'
) FROM articles
WHERE search_vector @@ to_tsquery('russian', 'postgresql');
Конфигурации поиска
PostgreSQL поддерживает языковые конфигурации (pg_catalog.russian, english, и др.), которые определяют словари стоп-слов и алгоритмы стемминга. Можно создавать кастомные конфигурации.
Когда использовать FTS vs Elasticsearch
FTS PostgreSQL подходит для: умеренных объёмов данных, когда данные уже в PostgreSQL, и не требуется распределённый поиск. Elasticsearch/OpenSearch — для больших объёмов, сложной аналитики и горизонтального масштабирования.
Что хочет услышать интервьюер
Понимание разницы между типами tsvector и tsquery и как они взаимодействуют через оператор @@
Знание важности индексов GIN/GiST для производительности FTS и когда использовать каждый из них
Понимание концепции лексем, нормализации текста и языковых конфигураций (словари, стоп-слова)
Знание функций ранжирования ts_rank и ts_headline для релевантных результатов
Осознание ограничений встроенного FTS и понимание, когда лучше использовать специализированные решения вроде Elasticsearch
Пример: Полнотекстовый поиск через TypeORM
import { DataSource } from 'typeorm';
const dataSource = new DataSource({
type: 'postgres',
// ...конфигурация
});
// Полнотекстовый поиск через TypeORM query builder
async function searchArticles(searchTerm: string) {
return dataSource
.getRepository('Article')
.createQueryBuilder('article')
.where(
// Используем оператор @@ и функцию websearch_to_tsquery
// для безопасного парсинга пользовательского ввода
`article.search_vector @@ websearch_to_tsquery('russian', :term)`,
{ term: searchTerm }
)
.addSelect(
// Вычисляем ранг для сортировки по релевантности
`ts_rank(article.search_vector, websearch_to_tsquery('russian', :term))`,
'rank'
)
.orderBy('rank', 'DESC')
.limit(20)
.getRawAndEntities();
}
Пример: TypeORM миграция с GIN-индексом и триггером
// Миграция: добавляем tsvector-колонку и GIN-индекс
import { MigrationInterface, QueryRunner } from 'typeorm';
export class AddFullTextSearch1700000000000 implements MigrationInterface {
async up(queryRunner: QueryRunner): Promise<void> {
// Добавляем колонку для хранения поискового вектора
await queryRunner.query(`
ALTER TABLE articles
ADD COLUMN search_vector tsvector
`);
// Заполняем для существующих записей
await queryRunner.query(`
UPDATE articles
SET search_vector = to_tsvector(
'russian',
coalesce(title, '') || ' ' || coalesce(body, '')
)
`);
// Создаём GIN-индекс для быстрого поиска
await queryRunner.query(`
CREATE INDEX articles_fts_idx ON articles USING GIN(search_vector)
`);
// Триггер для автоматического обновления вектора
await queryRunner.query(`
CREATE TRIGGER articles_search_vector_update
BEFORE INSERT OR UPDATE OF title, body ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.russian', title, body)
`);
}
async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TRIGGER IF EXISTS articles_search_vector_update ON articles`);
await queryRunner.query(`DROP INDEX IF EXISTS articles_fts_idx`);
await queryRunner.query(`ALTER TABLE articles DROP COLUMN IF EXISTS search_vector`);
}
}
Типичные ошибки
Использование LIKE/ILIKE вместо FTS и непонимание разницы в производительности и возможностях
Отсутствие GIN-индекса на tsvector-колонке — вызывает seq scan на больших таблицах
Вызов to_tsvector() прямо в WHERE без вычисляемой колонки, что лишает запрос возможности использовать индекс
Игнорирование языковой конфигурации (использование 'simple' вместо 'russian'), из-за чего морфология не работает
Отсутствие триггера для автоматического обновления search_vector при изменении строки
AI-тренажеры
4.7
Практика в студии
