Оглавление

• Зачем бизнесу поиск прямо в PostgreSQL
• Когда PostgreSQL достаточно, а когда уже нужен Elasticsearch
• Архитектура: tsvector, индексы, конфигурации
• Базовая настройка на реальном примере
  • Синонимы и словари
• Релевантность: поле важнее поля и свежесть важнее старого
• Подсветка сниппетов без сторонних сервисов
• Префиксы, опечатки и «похожие» результаты
• Производительность и обслуживание
• Миграция без простоя: как включить поиск на живой базе
• Интеграция с Django и FastAPI
  • Django (PostgreSQL backend)
  • FastAPI + asyncpg
• Ограничения и план Б

Зачем бизнесу поиск прямо в PostgreSQL

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

PostgreSQL умеет полнотекстовый поиск «из коробки»: морфология (русский, английский и не только), ранжирование, подсветка, префиксные запросы, «похожие» результаты на триграммах, словари синонимов. Этого достаточно для 70–80% продуктовых задач.

Когда PostgreSQL достаточно, а когда уже нужен Elasticsearch

Достаточно PostgreSQL, если:

• данных до десятков миллионов документов, и критическая доля поиска — по свежим данным;
• запросы относительно простые: слова/фразы, минусы (исключение слов), фильтры по структуре (категория, цена, дата);
• нет сложной аналитики по поиску (агрегации по вложенным структурам, сложные скоринги, ML-ранжирование);
• нагрузка на поиск — до нескольких тысяч запросов в минуту на одном-двух экземплярах БД.

Лучше перейти на Elasticsearch, если нужно:

• очень сложное ранжирование, вложенные агрегации, автодополнение с весами, релевантность уровня e-commerce‑гигантов;
• десятки-сотни запросов в секунду при сотнях миллионов документов, жёсткие требования к латентности типа <20 мс;
• гибкая кластеризация, репликация и геораспределённость именно для поиска.

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

Архитектура: tsvector, индексы, конфигурации

Ключевая идея: на стороне БД хранить поле типа tsvector — «мешок терминов» из текста, подготовленный к поиску. На него ставится GIN‑индекс, который даёт быстрые @@‑поиски. В PostgreSQL есть конфигурации по языкам (russian, english и др.) — они умеют стемминг и стоп-слова.

• tsvector — представление текста для поиска.
• tsquery — запрос: слова, фразы, логические операторы.
• websearch_to_tsquery — привычный формат, похожий на строку поиска в браузере: кавычки, минусы, OR.
• GIN‑индекс — быстрый поиск по tsvector и триграммам.

Базовая настройка на реальном примере

Ниже — минимальная, но рабочая схема для каталога товаров. Используем русский и английский, повышаем вес заголовка, поддерживаем подсветку и префиксы.

-- Расширения для FTS и триграмм
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Тестовая таблица
CREATE TABLE IF NOT EXISTS products (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC(12,2) NOT NULL,
  published_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- Сгенерированное поле для поиска (русский + английский, разные веса)
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('russian', coalesce(description, '')), 'D') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'D')
  ) STORED
);

-- Индекс без блокировок записи
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_fts ON products USING GIN (search_vector);

-- Быстрый префиксный поиск по заголовку (опционально)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_title_trgm ON products USING GIN (title gin_trgm_ops);

Поиск с ранжированием и фильтрами:

-- Входные параметры: :q — строка запроса, :category, :price_min, :price_max, :limit, :offset
WITH q AS (
  SELECT (
    websearch_to_tsquery('russian', :q) || websearch_to_tsquery('english', :q)
  ) AS tsq
)
SELECT p.id,
       p.title,
       p.price,
       p.category,
       ts_rank_cd(p.search_vector, q.tsq, 32) *
         (1.0 + 0.25 * (EXTRACT(EPOCH FROM (now() - p.published_at)) < 86400)::int) AS rank,
       ts_headline('russian', p.description, q.tsq,
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=5, MaxWords=15') AS snippet
FROM products p, q
WHERE p.search_vector @@ q.tsq
  AND (:category IS NULL OR p.category = :category)
  AND (:price_min IS NULL OR p.price >= :price_min)
  AND (:price_max IS NULL OR p.price <= :price_max)
ORDER BY rank DESC
LIMIT :limit OFFSET :offset;

Пояснения:

• websearch_to_tsquery позволяет писать запросы типа: смартфон "беспроводная зарядка" -чехол.
• ts_rank_cd — один из алгоритмов ранжирования. Параметр 32 уменьшает «наказание» за распространённые слова.
• ts_headline — подсветка совпадений в сниппете.

Синонимы и словари

Если пользователи часто ищут «ноут» вместо «ноутбук», добавьте словарь синонимов. Пример для Debian/Ubuntu (путь может отличаться):

echo "ноутбук ноут" | sudo tee /usr/share/postgresql/15/tsearch_data/russian_synonyms.syn

Создадим конфигурацию, которая вставляет синонимы перед стеммингом:

-- Словарь синонимов
CREATE TEXT SEARCH DICTIONARY rus_syn (
  TEMPLATE = synonym,
  SYNONYMS = russian_synonyms,
  CASESENSITIVE = false
);

-- Копируем базовую конфигурацию и подменяем токенизацию для слов
CREATE TEXT SEARCH CONFIGURATION ru_search ( COPY = russian );
ALTER TEXT SEARCH CONFIGURATION ru_search
  ALTER MAPPING FOR asciiword, word WITH rus_syn, russian_stem;

Теперь используйте ru_search вместо russian в to_tsvector/websearch_to_tsquery.

Релевантность: поле важнее поля и свежесть важнее старого

Поля имеют разные веса: заголовок обычно важнее описания. Мы уже использовали setweight с A и D. Можно добавить «бонус за свежесть» или за популярность товара.

Примеры бустов:

-- Буст за свежесть и популярность
WITH q AS (
  SELECT websearch_to_tsquery('ru_search', :q) AS tsq
)
SELECT id,
       title,
       ts_rank(search_vector, tsq) *
         (1.0 + 0.2 * (published_at > now() - interval '7 days')::int) *
         (1.0 + 0.3 * log(1 + coalesce(popularity, 0))) AS rank
FROM products, q
WHERE search_vector @@ tsq
ORDER BY rank DESC
LIMIT 20;

Подход: начинайте с простого ts_rank_cd, измеряйте CTR/конверсию и аккуратно добавляйте факторы.

Подсветка сниппетов без сторонних сервисов

ts_headline умеет подсветку с управляемой длиной и количеством фрагментов. Учитывайте язык той же конфигурации, что и у tsvector/tsquery, чтобы выделение совпадало с морфологией.

SELECT ts_headline('ru_search', description, websearch_to_tsquery('ru_search', :q),
  'StartSel=<mark>; StopSel=</mark>; MaxFragments=3; MinWords=4; MaxWords=12; ShortWord=2;') AS snippet
FROM products
WHERE id = :id;

Префиксы, опечатки и «похожие» результаты

Для префиксов и опечаток используйте триграммы:

-- Уже есть: CREATE EXTENSION pg_trgm;
-- Индекс создан ранее: idx_products_title_trgm

-- Префиксный поиск
SELECT id, title
FROM products
WHERE title ILIKE :prefix || '%'
ORDER BY title
LIMIT 20;

-- Похожие по опечаткам (threshold — порог похожести)
SET pg_trgm.similarity_threshold = 0.35;
SELECT id, title, similarity(title, :q) AS sim
FROM products
WHERE title % :q
ORDER BY sim DESC
LIMIT 20;

Комбинировать можно так: сначала пробуем точный FTS, если результатов мало — догружаем «похожие» на триграммах.

Производительность и обслуживание

Основные практики:

• Индексы GIN на tsvector и триграммах — обязательны.
• Следите за autovacuum: длинные транзакции и редкая очистка ухудшают планирование запросов и раздувают индексы.
• Для очень частых апдейтов текстов можно увеличить gin_pending_list_limit, чтобы реже сбрасывать буфер GIN. Но не переусердствуйте: память и время слияния индекса растут.
• Периодически проверяйте bloat и фрагментацию. REINDEX CONCURRENTLY помогает без простоя.

Пример минимальных настроек (оценочно, адаптируйте под нагрузку):

-- Быстрее обновляется индекс при батчевой загрузке
ALTER INDEX idx_products_fts SET (fastupdate = on);

-- На кластере
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
SELECT pg_reload_conf();

Проверьте планы запросов через EXPLAIN (ANALYZE, BUFFERS) — вы должны видеть Bitmap Index Scan по GIN, а не последовательное сканирование.

Миграция без простоя: как включить поиск на живой базе

Шаги:

1. Добавьте сгенерированное поле и индекс CONCURRENTLY — записи продолжают идти.
2. Переключите API на SQL‑запрос с @@.
3. Постепенно включайте ранжирование, подсветку и дополнительные словари.

Пример итогового плана миграции:

BEGIN;
-- 1. Добавляем STORED‑колонку (быстро, без пересчёта — вычисляется на чтении/записи)
ALTER TABLE products
  ADD COLUMN IF NOT EXISTS search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('ru_search', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('ru_search', coalesce(description, '')), 'D')
  ) STORED;
COMMIT;

-- 2. Индекс без простоя
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_fts ON products USING GIN (search_vector);

Если таблица огромная, индексация займёт время. Следите за pg_stat_progress_create_index.

Интеграция с Django и FastAPI

Django (PostgreSQL backend)

# settings.py: убедитесь, что используете postgresql и добавлен contrib.postgres

from django.db import models
from django.contrib.postgres.search import SearchQuery, SearchRank

class Product(models.Model):
    title = models.TextField()
    description = models.TextField()
    category = models.TextField()
    price = models.DecimalField(max_digits=12, decimal_places=2)
    published_at = models.DateTimeField(auto_now_add=True)

# Запрос с websearch_to_tsquery и ранжированием
from django.db.models.expressions import RawSQL

def search_products(q: str, category: str | None = None, limit: int = 20):
    tsq = RawSQL("websearch_to_tsquery('ru_search', %s)", (q,))
    where = RawSQL("search_vector @@ websearch_to_tsquery('ru_search', %s)", (q,))
    rank = RawSQL("ts_rank_cd(search_vector, websearch_to_tsquery('ru_search', %s), 32)", (q,))
    qs = Product.objects.extra(where=[str(where)])
    if category:
        qs = qs.filter(category=category)
    return qs.annotate(rank=rank).order_by('-rank')[:limit]

Для триграмм используйте TrigramSimilarity из django.contrib.postgres.search.

FastAPI + asyncpg

import asyncpg
from fastapi import FastAPI, Query

app = FastAPI()

@app.on_event('startup')
async def startup():
    app.state.pool = await asyncpg.create_pool(dsn='postgres://user:pass@localhost:5432/app')

@app.get('/search')
async def search(q: str = Query(..., min_length=1), limit: int = 20, offset: int = 0):
    sql = """
    WITH q AS (
      SELECT (websearch_to_tsquery('ru_search', $1)) AS tsq
    )
    SELECT p.id, p.title, p.price,
           ts_rank_cd(p.search_vector, q.tsq, 32) AS rank,
           ts_headline('ru_search', p.description, q.tsq,
             'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=5, MaxWords=15') AS snippet
    FROM products p, q
    WHERE p.search_vector @@ q.tsq
    ORDER BY rank DESC
    LIMIT $2 OFFSET $3
    """
    async with app.state.pool.acquire() as conn:
        rows = await conn.fetch(sql, q, limit, offset)
    return [dict(r) for r in rows]

Обратите внимание: мы используем параметризованные запросы, поэтому инъекции в tsquery исключены. Не подставляйте сырые строки в SQL.

Ограничения и план Б

Что PostgreSQL делает отлично:

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

Границы подхода:

• масштаб в сотни миллионов документов и сложные алгоритмы ранжирования — уже неудобно;
• нет из коробки продвинутого автодополнения с учётом частот и персонализации;
• тяжёлые агрегации по поисковым полям могут быть медленными.

План Б (миграция на Elasticsearch):

• на этапе «узкого места» добавьте двойную запись: в PostgreSQL и в поисковый индекс;
• синхронизируйте исторические данные батчами;
• оставьте в PostgreSQL базовый поиск как резерв и источник правды, а в Elasticsearch — тяжёлые запросы и витрины.

Итог: начинайте с PostgreSQL — быстрее выйдете в прод и сэкономите. Когда получите реальную метрику нагрузки и качества, станет ясно, нужен ли отдельный поисковый кластер. Для большинства сервисов этого шага можно избежать на годы.