# Гибридный поиск в PostgreSQL: как объединить BM25 и векторы Источник: https://www.youtube.com/watch?v=kT7nXO32L-Q Канал: freeCodeCamp.org Опубликовано: 21.10.2026 --- Разработчики часто сталкиваются с дилеммой при проектировании поисковых механизмов: использовать внешние специализированные системы вроде Elasticsearch или пытаться обойтись возможностями реляционной СУБД. Бо Карнс в практическом курсе на образовательной платформе freeCodeCamp.org демонстрирует, как построить полноценную гибридную поисковую систему по Википедии исключительно средствами PostgreSQL. Решение объединяет лексическое ранжирование BM25 через расширение `pg_textsearch`, семантический векторный поиск с помощью `pgvector-scale` и алгоритм Reciprocal Rank Fusion (RRF), сохраняя строгую транзакционную целостность данных в пределах единого контура базы. ## 🏗️ Архитектура единой базы против распределённого поиска [[JUMP:00:00]] Типичный путь развития поискового функционала в проектах начинается с простых текстовых фильтров в SQL, однако рост требований пользователей к качеству выдачи вынуждает команды подключать внешние сервисы [04:06]. При таком подходе инженерам приходится выстраивать конвейеры репликации (change data capture или фоновые задачи синхронизации), что неизбежно приводит к рассинхронизации [04:16]. Задержки репликации, сбои очередей сообщений или длительная переиндексация вызывают эффект, когда пользователь обновляет статью, но в поисковой выдаче по-прежнему отображается устаревшая версия [04:29]. Размещение поисковых индексов непосредственно рядом с данными в PostgreSQL решает проблему принципиально: * Обеспечивается единая транзакционная граница для контента и индексов [04:42]. * Устраняется необходимость в синхронизирующих брокерах сообщений и промежуточных сервисах [00:26]. * Появляется возможность естественного объединения полнотекстового поиска с реляционными запросами, внешними ключами и правами доступа [04:55]. * Запись и обновление векторных представлений и текстовых индексов происходят атомарно в рамках одной транзакции [55:54]. В демонстрационном проекте «Поиск по Википедии» задействован реальный корпус Simple English Wikipedia, насчитывающий около 242 000 статей для лексического поиска и порядка 25 000 для ресурсоёмкого векторного анализа [01:16]. ## 📐 Сопоставление против ранжирования и математика BM25 [[JUMP:04:06]] Поисковая операция внутри СУБД разделяется на два независимых этапа: сопоставление (matching), определяющее пригодность документа, и ранжирование (ranking), вычисляющее порядок выдачи [04:55]. Если запрос пользователя состоит из нескольких слов (например, «извержение вулкана угроза авиации»), логика операторов `AND` или `OR` отбирает кандидатов, но ничего не говорит об их полезности [05:09]. Встроенная в PostgreSQL функция `ts_rank` оценивает совпадения внутри одного документа, но не учитывает частотность терминов по всему корпусу [05:58]. Алгоритм BM25 (Best Matching 25) ликвидирует этот разрыв, опираясь на три взаимосвязанных принципа [07:17]: 1. **Обратная частота документа (IDF)**: редкие слова несут больше полезной информации. Если из 1000 документов термин «база данных» встречается в 900, а «кластеризация» — лишь в 10, совпадение по второму слову обладает экспоненциально большим весом [08:23]. 2. **Насыщение частоты термина (Term Frequency)**: частое упоминание слова подчеркивает центральную тему текста, но сотое вхождение не должно давать такой же прирост, как первое. В BM25 насыщение регулируется коэффициентом $k_1$, значение которого по умолчанию равно 1.2 [09:41]. 3. **Нормализация длины документа**: в длинных статьях чисто статистически выше вероятность встретить любое случайное слово. Параметр $b$ (по умолчанию 0.75) нивелирует преимущество длинных текстов относительно среднего размера документа в корпусе [10:07]. Применение оптимизации Block-Max WAND в расширении `pg_textsearch` позволяет СУБД динамически отсекать блоки инвертированного индекса, которые заведомо не способны превзойти текущий топ-10 результатов, избавляя движок от полного сканирования миллиардов записей [10:48]. ## 🛠️ Настройка облачного окружения и расширений СУБД [[JUMP:11:39]] Для практической реализации разворачивается экземпляр PostgreSQL. В видеокурс включена работа с Tiger Data Cloud — управляемой платформой, где расширения `pg_textsearch`, `pgvector` и `pgvector-scale` предустановлены [03:13]. Впрочем, как отмечает Бо Карнс, стек является открытым и может быть развернут локально в Docker [03:38]. Перед запуском кода проверяется версия расширений через системный каталог: ```sql SELECT * FROM pg_available_extensions WHERE name IN ('pg_textsearch', 'vector', 'vectorscale'); ``` Для корректной работы примеров требуется `pg_textsearch` версии не ниже 1.5.1 [02:47]. Активация компонентов выполняется с каскадным разрешением зависимостей [14:37]: ```sql CREATE EXTENSION IF NOT EXISTS "vector" CASCADE; CREATE EXTENSION IF NOT EXISTS "vectorscale" CASCADE; CREATE EXTENSION IF NOT EXISTS "pg_textsearch" CASCADE; ``` Скрипты проекта написаны на Python и взаимодействуют с базой данных через драйвер `psycopg2` [14:06]. Все параметры окружения (строка подключения `DATABASE_URL`, модель энкодера и аппаратный бэкенд) изолируются в файле `.env` [12:30]. ## 📦 Загрузка корпуса Википедии и структура схемы [[JUMP:15:43]] В качестве данных используется готовый срез корпуса Simple English Wikipedia с Hugging Face от 1 ноября 2023 года (`20231101.simple`), упакованный в сжатый Parquet-файл размером около 157 МБ [15:41]. Исходный срез содержит ровно 241 787 статей [15:54]. Таблица `articles` изначально создается со следующей структурой [16:56]: ```sql CREATE TABLE IF NOT EXISTS articles ( id BIGINT PRIMARY KEY, url TEXT NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL, word_count INTEGER ); ``` Загрузчик `load.py` скачивает файл, проверяет контрольную сумму, читает записи пакетами и загружает их через команду PostgreSQL `COPY` во временную буферную таблицу [17:21]. Далее данные переносятся в целевую таблицу с директивой разрешения конфликтов `ON CONFLICT (id) DO NOTHING` [17:33]. Это предотвращает порчу данных при перезапуске импорта. Поле `word_count` рассчитывается по пробелам для грубой предварительной фильтрации текстов [17:47]. После импорта обязателен вызов `ANALYZE`, чтобы оптимизатор запросов получил корректную статистику распределения данных [20:00]. ## 🔍 Базовый уровень: встроенный поиск и разбор планов запросов [[JUMP:20:30]] Для сравнения эффективности сначала тестируется стандартный механизм PostgreSQL: построение лексического индекса GIN на базе генерации векторов лексем через `to_tsvector` [21:40]. ```sql CREATE INDEX idx_articles_body_gin ON articles USING gin(to_tsvector('english', body)); ``` При тестировании запроса «volcanic eruption aviation hazard» с использованием `plainto_tsquery` стандартный поиск с условием совпадения всех слов возвращает пустой результат [22:45]. Лексический анализатор требует обязательного присутствия всех пяти лексем (`&`), чего в реальных статьях может не быть [23:10]. При замене условия на логическое «ИЛИ» (`|`) выдача появляется, но качество ранжирования через `ts_rank` оказывается посредственным, так как отсутствует глобальная статистика корпуса [23:47]. Анализ плана выполнения через `EXPLAIN (ANALYZE, BUFFERS)` наглядно вскрывает механизм работы СУБД [24:38]: * Условие `LIMIT 10` ограничивает только размер финального ответа клиенту, но не объем работы [25:31]. * При классическом подходе база сначала находит по индексу сотни подходящих строк, затем читает их со страниц диска или буферного пула, вычисляет для каждой `ts_rank` и выполняет явную сортировку `Sort` в памяти [25:19]. * При пустой выборке запрос завершается за доли миллисекунды (0.3 мс), однако при наличии тысяч совпадений накладные расходы на сортировку резко возрастают [26:10]. ## ⚡ Переход на BM25 с pg_textsearch [[JUMP:27:40]] Расширение `pg_textsearch` предоставляет специализированный метод доступа `bm25` для таблиц. Индекс создается на уровне сессии с выделением достаточного бюджета оперативной памяти под служебные операции (`maintenance_work_mem = '256MB'`) [28:07]: ```sql CREATE INDEX idx_articles_body_bm25 ON articles USING bm25(body) WITH (text_config = 'english'); ``` Для поиска используется кастомный бинарный оператор `<@>` между текстовым полем и специальной структурой запроса [28:32]. Особенности работы с оценками BM25 в `pg_textsearch`: * СУБД возвращает отрицательные значения: чем меньше (отрицательнее) число, тем сильнее соответствие документа запросу [28:58]. Оценка `-20` свидетельствует о более высокой релевантности, чем `-5` [29:10]. * В блоке `ORDER BY` сортировка обязана выполняться по возрастанию (`ASC`), что отличается от классического убывающего порядка `ts_rank` [29:10]. * Оценка BM25 не является процентной вероятностью; это относительный скор, рассчитанный внутри контекста данного индекса и набора терминов [29:36]. Сравнение планов запросов демонстрирует качественную разницу: вместо связки `Bitmap Index Scan` + явная сортировка, индекс `bm25` возвращает строки уже в отсортированном по релевантности порядке напрямую в узел `Limit` [31:32]. Запрос по фразе из пяти слов возвращает 10 точных документов примерно за 9 миллисекунд без предварительного отсечения жестким фильтром [31:58]. Для боевого применения индекс модифицируют, объединяя заголовки и текст статей в генерируемый столбец, а также подключают функцию `ts_headline` [34:20]. Чтобы избежать бессмысленной генерации сниппетов для миллионов документов, подсветка синтаксиса накладывается исключительно на финальные 10 строк через подзапросы [35:11]. ## 🧠 Семантический поиск: эмбеддинги и pgvector-scale [[JUMP:37:32]] Полнотекстовый поиск бессилен в случаях, когда пользователь формулирует концепцию другими словами — например, «животные, производящие собственный свет» [37:32]. Лексический индекс не свяжет эту фразу со статьей о «биолюминесценции», если прямой термин отсутствует в тексте [37:46]. Семантический поиск преобразует текст в числовой вектор фиксированной размерности (эмбеддинг). Для проекта выбрана компактная локальная модель, генерирующая векторы размерности 384 [38:24]. В таблицу добавляется столбец типа `vector(384)`: ```sql ALTER TABLE articles ADD COLUMN embedding vector(384); ``` Векторизация охватывает срез из 25 000 самых длинных статей корпуса (на вход модели подаются заголовок и первые 1500 символов текста) [38:37]. Загрузчик `embed.py` сбрасывает векторы в базу пачками [40:33]. Для быстрого приближенного поиска ближайших соседей (ANN) строится индекс `DiskANN` через расширение `pgvector-scale` [41:33]: ```sql CREATE INDEX idx_articles_embeddings ON articles USING diskann(embedding); ``` Векторный запрос вычисляет косинусное расстояние между эмбеддингом поисковой фразы и векторами статей: чем меньше расстояние, тем ближе смысл [42:05]. При запросе про биолюминесценцию семантический поиск безошибочно выводит профильные биологические статьи на первые позиции даже при полном несовпадении ключевых слов [42:44]. ## 🔀 Гибридный поиск и слияние рангов через RRF [[JUMP:43:28]] Прямое арифметическое сложение оценки BM25 и векторного расстояния невозможно из-за принципиальной несовместимости шкал [43:28]. Для объединения результатов применяется алгоритм Reciprocal Rank Fusion (RRF) [43:47]. Балл RRF для каждого документа вычисляется по формуле: $$RRF\_Score = \sum \frac{w_i}{k + rank_i}$$ Где $rank_i$ — позиция документа в конкретном списке выдачи (1, 2, 3…), константа сглаживания $k$ установлена равной 60, а $w_i$ — весовой коэффициент поискового метода [44:12]. Документ, попавший в оба списка (например, 2-е место по BM25 и 3-е место по векторам), суммирует баллы и гарантированно обходит документы, найденные только одним методом [44:53]. В SQL гибридный поиск оформляется через обобщенные табличные выражения (CTE) [45:45]: ```sql WITH keyword_candidates AS ( SELECT id, row_number() OVER () AS rank FROM articles WHERE search_text <@ 'apollo 13'::bm25query ORDER BY search_text <@ 'apollo 13'::bm25query ASC LIMIT 20 ), vector_candidates AS ( SELECT id, row_number() OVER () AS rank FROM articles ORDER BY embedding <=> '[...]'::vector LIMIT 20 ) SELECT coalesce(k.id, v.id) AS id, coalesce(0.5 / (60 + k.rank), 0.0) + coalesce(0.5 / (60 + v.rank), 0.0) AS rrf_score FROM keyword_candidates k FULL OUTER JOIN vector_candidates v ON k.id = v.id ORDER BY rrf_score DESC LIMIT 10; ``` Анализ плана показывает, что база выбирает по 20 кандидатов от каждого индекса (суммарно 40 строк), дедуплицирует их через хеш-агрегацию (`HashAggregate`) до 32 уникальных записей, пересчитывает веса и отдает топ-10 [47:42]. Вся операция внутри СУБД занимает около 71 миллисекунды [48:34]. ## 🌐 Интеграция с FastAPI и транзакционная запись [[JUMP:52:13]] Поисковая подсистема выставляется наружу через веб-сервис на базе FastAPI [52:13]. Чтобы не создавать подключение к СУБД на каждый входящий HTTP-запрос, организуется пул соединений (connection pool) на 4 постоянных подключения с обязательным тайм-аутом исполнения запросов во избежание зависания сессий [53:44]. Модель для генерации эмбеддингов кэшируется в оперативной памяти приложения [52:52]. В API реализован гибкий параметр балансировки весов `semantic_weight` в диапазоне от 0.0 (чистый BM25) до 1.0 (чистый векторный поиск) [50:04]. На практике значение 0.7 (70% веса семантике и 30% ключевым словам) часто дает наиболее устойчивые результаты при смешанных пользовательских запросах [54:49]. При добавлении новых статей сервис обеспечивает строгую согласованность: 1. Бэкенд генерирует векторное представление для нового текста [55:54]. 2. Открывается транзакция в PostgreSQL [55:54]. 3. Текст, метаданные и вектор записываются одной командой `INSERT` [55:54]. 4. Индексы BM25 и DiskANN обновляются автоматически [56:07]. Если на этапе генерации вектора произошел сбой, транзакция не открывается, и в базе не возникает «висячих» записей без эмбеддингов [56:07]. ## 📊 Масштабирование и выводы [[JUMP:57:24]] В рамках нагрузочного тестирования компания Tiger Data опубликовала замеры производительности на корпусе из 138 миллионов текстовых фрагментов, сравнив `pg_textsearch` 1.0 и расширение `ParadeDB` 0.21.6 [57:24]. По данным разработчиков, `pg_textsearch` показал более высокую скорость обработки коротких запросов и выдержал больший уровень параллелизма при прогретом кэше, в то время как `ParadeDB` продемонстрировал примерно двукратное преимущество в скорости первичного построения индекса [57:38]. Бо Карнс подчеркивает, что архитектура с единой базой PostgreSQL устраняет сложность инфраструктуры для огромного пласта прикладных задач, сохраняя простоту SQL-запросов и надежность транзакций [58:29]. Дальнейшее улучшение качества поиска может включать расширение векторного покрытия на весь массив текстов, разбиение лонгридов на независимые смысловые чанки и добавление легковесной модели переранжирования (re-ranker) на финальном этапе отбора кандидатов [59:44].