/postgres-hybrid-text-search

name: postgres-hybrid-text-search
description: |
  Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF).

  **Trigger when user asks to:**
  - Combine keyword and semantic search
  - Implement hybrid search or multi-modal retrieval
  - Use BM25/pg_textsearch with pgvector together
  - Implement RRF (Reciprocal Rank Fusion) for search
  - Build search that handles both exact terms and meaning


  **Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder

  Covers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with pgvector and pg_textsearch extensions
metadata:
  author: tigerdata

Hybrid Text Search

Hybrid search combines keyword search (BM25) with semantic search (vector embeddings) to get the best of both: exact keyword matching and meaning-based retrieval. Use Reciprocal Rank Fusion (RRF) to merge results from both methods into a single ranked list.

This guide covers combining [pg_textsearch](https://github.com/timescale/pg_textsearch) (BM25) with [pgvector](https://github.com/pgvector/pgvector). Requires both extensions. For high-volume setups, filtering, or advanced pgvector tuning (binary quantization, HNSW parameters), see the **pgvector-semantic-search** skill.

pg_textsearch is a new BM25 text search extension for PostgreSQL, fully open-source and available hosted on Tiger Cloud as well as for self-managed deployments. It provides true BM25 ranking, which often improves relevance compared to PostgreSQL's built-in ts_rank and can offer better performance at scale. Note: pg_textsearch is currently in prerelease and not yet recommended for production use. pg_textsearch currently supports PostgreSQL 17 and 18.

When to Use Hybrid Search

• **Use hybrid** when queries mix specific terms (product names, codes, proper nouns) with conceptual intent
• **Use semantic only** when meaning matters more than exact wording (e.g., "how to fix slow queries" should match "query optimization")
• **Use keyword only** when exact matches are critical (e.g., error codes, SKUs, legal citations)

Hybrid search typically improves recall over either method alone, at the cost of slightly more complexity.

Data Preparation

Chunk your documents into smaller pieces (typically 500–1000 tokens) and store each chunk with its embedding. Both BM25 and semantic search operate on the same chunks—this keeps fusion simple since you're comparing like with like.

Golden Path (Default Setup)

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_textsearch;

-- Table with both indexes
CREATE TABLE documents (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content TEXT NOT NULL,
  embedding halfvec(1536) NOT NULL
);

-- BM25 index for keyword search
CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');

-- HNSW index for semantic search
CREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);

BM25 Notes

• **Negative scores**: The `<@>` operator returns negative values where lower = better match. RRF uses rank position, so this doesn't affect fusion.
• **Language config**: Change `text_config` to match your content language (e.g., `'french'`, `'german'`). See [PostgreSQL text search configurations](https://www.postgresql.org/docs/current/textsearch-configuration.html).
• **Tuning**: BM25 has `k1` (term frequency saturation, default 1.2) and `b` (length normalization, default 0.75) parameters. Defaults work well; only tune if relevance is poor.

  CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english', k1 = 1.5, b = 0.8);

• **Partitioned tables**: Each partition maintains local statistics. Scores are not directly comparable across partitions—query individual partitions when score comparability matters.

RRF Query Pattern

Reciprocal Rank Fusion combines rankings from multiple searches. Each result's score is `1 / (k + rank)` where `k` is a constant (typically 60). Results are summed across searches and re-sorted.

**Run both queries in parallel from your client** for lower latency, then fuse results client-side:

-- Query 1: Keyword search (BM25)
-- $1: search text
SELECT id, content FROM documents ORDER BY content <@> $1 LIMIT 50;

-- Query 2: Semantic search (separate query, run in parallel)
-- $1: embedding of your search text as halfvec(1536)
SELECT id, content FROM documents ORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;

# Client-side RRF fusion (Python)
def rrf_fusion(keyword_results, semantic_results, k=60, limit=10):
    scores = {}
    content_map = {}

    for rank, row in enumerate(keyword_results, start=1):
        scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)
        content_map[row['id']] = row['content']

    for rank, row in enumerate(semantic_results, start=1):
        scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)
        content_map[row['id']] = row['content']

    sorted_ids = sorted(scores, key=scores.get, reverse=True)[:limit]
    return [{'id': id, 'content': content_map[id], 'score': scores[id]} for id in sorted_ids]

// Client-side RRF fusion (TypeScript)
type Row = { id: number; content: string };
type Result = Row & { score: number };

function rrfFusion(keywordResults: Row[], semanticResults: Row[], k = 60, limit = 10): Result[] {
  const scores = new Map<number, number>();
  const contentMap = new Map<number, string>();

  keywordResults.forEach((row, i) => {
    scores.set(row.id, (scores.get(row.id) ?? 0) + 1 / (k + i + 1));
    contentMap.set(row.id, row.content);
  });

  semanticResults.forEach((row, i) => {
    scores.set(row