Full-text search: поиск по текстовым полям

Почему обычный LIKE — это медленно и неудобно? 🔍

Представь, что у тебя есть таблица с книгами:

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    content TEXT  -- полный текст книги, может быть очень большим
);

Хочешь найти все книги, где встречается слово "дракон"? Стандартный подход:

SELECT * FROM books 
WHERE content LIKE '%дракон%';

Проблемы такого подхода:

Медленно — СУБД перебирает весь текст построчно
Нет морфологии — Не найдёт "дракона", "дракону" и т.д.
Нет релевантности — Нельзя отсортировать по "важности" результата

Full-text search спешит на помощь! 🚀

Полнотекстовый поиск — это специальный механизм для быстрого и "умного" поиска по тексту. В PostgreSQL он реализован через:

TSVECTOR — специальный тип данных для хранения "поискового индекса"
TSQUERY — язык запросов для поиска
GIN-индексы — для молниеносного поиска

Создаём поисковый индекс

Добавим в таблицу новое поле и индекс:

-- Добавляем поле для хранения поискового индекса
ALTER TABLE books ADD COLUMN search_index tsvector;

-- Заполняем его данными (можно автоматизировать триггером)
UPDATE books SET search_index = 
    to_tsvector('russian', title || ' ' || author || ' ' || content);

-- Создаём специальный индекс для ускорения поиска
CREATE INDEX idx_books_search ON books USING gin(search_index);

Пишем первые "умные" запросы 🔥

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

Найдём все книги про драконов:

SELECT title, author 
FROM books 
WHERE search_index @@ to_tsquery('russian', 'дракон');

Этот запрос:

Найдёт все формы слова ("дракон", "дракона", "драконов")
Будет работать в разы быстрее LIKE
Может искать по нескольким словам сразу

Расширенные возможности

-- Поиск по фразе (слова должны идти подряд)
SELECT title FROM books 
WHERE search_index @@ to_tsquery('russian', 'красный & дракон');

-- Поиск с обязательным и необязательным условием
SELECT title FROM books 
WHERE search_index @@ to_tsquery('russian', 'дракон & !зеленый');

-- Поиск с расстоянием между словами (до 3 слов между "огненный" и "меч")
SELECT title FROM books 
WHERE search_index @@ to_tsquery('russian', 'огненный <3> меч');

Сортировка по релевантности 🏆

Одна из самых мощных фишек — сортировка результатов по "важности":

SELECT 
    title,
    author,
    ts_rank(search_index, to_tsquery('russian', 'дракон | магия')) AS rank
FROM books
WHERE search_index @@ to_tsquery('russian', 'дракон | магия')
ORDER BY rank DESC
LIMIT 10;

Функция ts_rank() вычисляет "вес" результата на основе:

Частоты встречаемости слов
Близости искомых слов друг к другу
Важности поля (можно настраивать)

Автоматизация обновления индекса ⚡

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

CREATE OR REPLACE FUNCTION update_search_index() RETURNS trigger AS $$
BEGIN
    NEW.search_index := 
        to_tsvector('russian', COALESCE(NEW.title, '') || ' ' || 
                    COALESCE(NEW.author, '') || ' ' || 
                    COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER books_search_index_update
BEFORE INSERT OR UPDATE ON books
FOR EACH ROW EXECUTE FUNCTION update_search_index();

Теперь индекс будет обновляться автоматически при любых изменениях данных!

Практический пример: книжный магазин 📚

Допустим, мы делаем поиск для интернет-магазина книг. Вот как это может выглядеть:

-- Расширенный поиск с подсветкой результатов
SELECT 
    title,
    author,
    ts_headline('russian', content, to_tsquery('russian', 'дракон'), 
                'StartSel = <b>, StopSel = </b>') AS snippet,
    ts_rank(search_index, to_tsquery('russian', 'дракон')) AS rank
FROM books
WHERE search_index @@ to_tsquery('russian', 'дракон')
ORDER BY rank DESC
LIMIT 5;

Функция ts_headline() выделяет найденные слова в тексте (отлично подходит для вывода превью).

Производительность и тонкая настройка ⚙️

Для больших проектов важно:

Выбирать правильную конфигурацию — 'russian', 'english' и др.
Настраивать веса полей — title важнее content?

Пример с весами:

UPDATE books SET search_index =
    setweight(to_tsvector('russian', title), 'A') ||
    setweight(to_tsvector('russian', author), 'B') ||
    setweight(to_tsvector('russian', content), 'C');

Теперь совпадения в title будут давать больший "вес", чем в content.

Полнотекстовый поиск — это мощный инструмент, который превращает твою СУБД в интеллектуальную поисковую систему. С ним ты можешь реализовывать сложные поисковые сценарии, которые раньше требовали отдельных решений вроде Elasticsearch!