PostgreSQL Hybrid Search: BM25 + Vector Search with RRF Fusion

By Fadi Updated Oct 11, 2025 14 min read Vector Databases

TL;DR — PostgreSQL hybrid search: BM25 + vector with RRF fusion. ParadeDB: "ParadeDB BM25 for lexical relevance, pgvector for semantic understanding. The missing manual for hybrid search in PostgreSQL." TigerData: "RRF is elegantly simple — industry standard for combining ranked lists, what Elasticsearch uses. Combines without normalizing raw scores." Reddit: "Vector search retrieved Error 404 for Error 503 query — semantically identical but wrong. Hybrid search fixed this." PedroAlonso: "BM25 handles keyword relevance, pgvector handles semantic meaning, RRF merges them. Most common fusion strategy — no score normalization needed." TigerData Docs: "Most search systems only do one type. Combine keyword and semantic for best results." Learn more with pgvector tutorial, semantic search, vector search API, and pgvector performance.

ParadeDB frames the problem: "ParadeDB brings production-ready full text search with BM25 scoring for lexical relevance, while pgvector delivers vector similarity for semantic understanding. But how do you combine these into a hybrid system? This is the missing manual for hybrid search in PostgreSQL."

Reddit provides the real-world motivation: "If I searched for Error 503, the vector search would often retrieve Error 404 because they are semantically identical (server errors), even though I needed the exact match. So I spent the weekend upgrading my retrieval engine to Hybrid Search."

Hybrid Search Architecture

flowchart TD subgraph Input["User Query"] Query["Search Query
'Error 503 timeout'"] end subgraph Keyword["Keyword Search Path"] BM25["BM25 / tsvector
exact term matching"] TsRank["ts_rank_cd scoring
or ParadeDB BM25 score"] KeywordResults["Top 50 keyword results
ranked by relevance"] end subgraph Vector["Vector Search Path"] Embed["Embed Query
text-embedding-3-small"] Cosine["Cosine Similarity
embedding <=> query_vec"] VectorResults["Top 50 vector results
ranked by similarity"] end subgraph Fusion["RRF Fusion"] Rank["Assign Ranks
row_number() per list"] RRF["RRF Score
sum(1/(60 + rank))
across both lists"] Merge["Merge & Sort
ORDER BY rrf_score DESC"] end subgraph Output["Results"] TopK["Top-K Results
best of both worlds
exact terms + semantic"] end Query --> BM25 Query --> Embed BM25 --> TsRank --> KeywordResults Embed --> Cosine --> VectorResults KeywordResults --> Rank VectorResults --> Rank Rank --> RRF --> Merge --> TopK

Search Method Comparison

Method	Strengths	Weaknesses	Best For
BM25 / tsvector	Exact term matching, error codes, product names	Misses synonyms, paraphrasing	Keyword queries, specific terms
Vector (pgvector)	Semantic matching, synonyms, paraphrasing	Misses exact terms (503 vs 404)	Natural language, conceptual
Hybrid (RRF)	Both exact + semantic, best recall	More complex, two indexes	Production RAG, diverse queries

RRF Fusion Methods

Method	Formula	Complexity	Score Normalization	Best For
RRF (Reciprocal Rank Fusion)	sum(1/(k + rank_i))	Simple	Not needed	Default, industry standard
Weighted Sum	alpha * vec_score + (1-alpha) * bm25_score	Medium	Required (different scales)	Tunable weighting
Reranking	Cross-encoder rerank top candidates	High	Not needed	Maximum precision

Implementation

from dataclasses import dataclass
from typing import Optional
from enum import Enum

class SearchMethod(Enum):
    KEYWORD_ONLY = "keyword_only"
    VECTOR_ONLY = "vector_only"
    HYBRID_RRF = "hybrid_rrf"
    HYBRID_WEIGHTED = "hybrid_weighted"

@dataclass
class HybridSearchEngine:
    """PostgreSQL hybrid search with BM25 + vector + RRF fusion."""

    def get_schema_sql(self) -> str:
        """Schema for hybrid search with tsvector and pgvector."""
        return (
            "CREATE EXTENSION IF NOT EXISTS vector;\n"
            "\n"
            "CREATE TABLE IF NOT EXISTS documents (\n"
            "    id BIGSERIAL PRIMARY KEY,\n"
            "    title TEXT,\n"
            "    content TEXT NOT NULL,\n"
            "    embedding vector(1536),\n"
            "    tsv tsvector GENERATED ALWAYS AS (\n"
            "        to_tsvector('english',\n"
            "            coalesce(title, '') || ' '\n"
            "            || content)\n"
            "    ) STORED,\n"
            "    metadata JSONB DEFAULT '{}'::jsonb\n"
            ");\n"
            "\n"
            "-- GIN index for keyword search\n"
            "CREATE INDEX CONCURRENTLY docs_tsv_idx\n"
            "ON documents USING gin(tsv);\n"
            "\n"
            "-- HNSW index for vector search\n"
            "CREATE INDEX CONCURRENTLY docs_hnsw_idx\n"
            "ON documents USING hnsw\n"
            "    (embedding vector_cosine_ops)\n"
            "WITH (m = 16, ef_construction = 256);"
        )

    def get_hybrid_search_sql(self) -> str:
        """Hybrid search with tsvector + pgvector + RRF."""
        return (
            "-- Hybrid search: keyword + vector + RRF fusion\n"
            "-- $1 = query text, $2 = query embedding vector\n"
            "\n"
            "WITH lex AS (\n"
            "    SELECT id, title, content, metadata,\n"
            "           ts_rank_cd(tsv,\n"
            "               plainto_tsquery('english', $1)\n"
            "           ) AS score\n"
            "    FROM documents\n"
            "    WHERE tsv @@ plainto_tsquery('english', $1)\n"
            "    ORDER BY score DESC\n"
            "    LIMIT 50\n"
            "),\n"
            "vec AS (\n"
            "    SELECT id, title, content, metadata,\n"
            "           1 - (embedding <=> $2::vector)\n"
            "               AS score\n"
            "    FROM documents\n"
            "    ORDER BY embedding <=> $2::vector\n"
            "    LIMIT 50\n"
            "),\n"
            "ranked AS (\n"
            "    SELECT id, title, content, metadata,\n"
            "           row_number() OVER\n"
            "               (ORDER BY score DESC) AS rank\n"
            "    FROM lex\n"
            "    UNION ALL\n"
            "    SELECT id, title, content, metadata,\n"
            "           row_number() OVER\n"
            "               (ORDER BY score DESC) AS rank\n"
            "    FROM vec\n"
            ")\n"
            "SELECT id,\n"
            "       max(title) AS title,\n"
            "       max(content) AS content,\n"
            "       max(metadata) AS metadata,\n"
            "       sum(1.0 / (60 + rank)) AS rrf_score,\n"
            "       count(*) AS list_count\n"
            "FROM ranked\n"
            "GROUP BY id\n"
            "ORDER BY rrf_score DESC\n"
            "LIMIT 10;"
        )

    def get_paradedb_hybrid_sql(self) -> str:
        """Hybrid search with ParadeDB BM25 + pgvector."""
        return (
            "-- Requires: CREATE EXTENSION paradedb;\n"
            "\n"
            "-- Create BM25 index\n"
            "CREATE INDEX docs_bm25_idx\n"
            "ON documents USING bm25\n"
            "    (title, content)\n"
            "WITH (text_fields =\n"
            "    '{\"title\": {\"tokenizer\":\n"
            "    \"default\"}}');\n"
            "\n"
            "-- Hybrid search with ParadeDB + pgvector\n"
            "WITH bm25 AS (\n"
            "    SELECT id, title, content,\n"
            "           paradedb.score(id) AS score\n"
            "    FROM documents\n"
            "    WHERE title @@@ $1\n"
            "       OR content @@@ $1\n"
            "    ORDER BY score DESC\n"
            "    LIMIT 50\n"
            "),\n"
            "vec AS (\n"
            "    SELECT id, title, content,\n"
            "           1 - (embedding <=> $2::vector)\n"
            "               AS score\n"
            "    FROM documents\n"
            "    ORDER BY embedding <=> $2::vector\n"
            "    LIMIT 50\n"
            "),\n"
            "ranked AS (\n"
            "    SELECT id, title, content,\n"
            "           row_number() OVER\n"
            "               (ORDER BY score DESC) AS rank\n"
            "    FROM bm25\n"
            "    UNION ALL\n"
            "    SELECT id, title, content,\n"
            "           row_number() OVER\n"
            "               (ORDER BY score DESC) AS rank\n"
            "    FROM vec\n"
            ")\n"
            "SELECT id,\n"
            "       max(title) AS title,\n"
            "       max(content) AS content,\n"
            "       sum(1.0 / (60 + rank)) AS rrf_score,\n"
            "       count(*) AS list_count\n"
            "FROM ranked\n"
            "GROUP BY id\n"
            "ORDER BY rrf_score DESC\n"
            "LIMIT 10;"
        )

    def get_weighted_sum_sql(self) -> str:
        """Weighted sum fusion (requires score normalization)."""
        return (
            "-- Weighted sum fusion\n"
            "-- alpha = vector weight, (1-alpha) = keyword weight\n"
            "-- Requires min-max normalization of scores\n"
            "\n"
            "WITH lex AS (\n"
            "    SELECT id,\n"
            "           ts_rank_cd(tsv,\n"
            "               plainto_tsquery('english', $1)\n"
            "           ) AS raw_score\n"
            "    FROM documents\n"
            "    WHERE tsv @@ plainto_tsquery('english', $1)\n"
            "    ORDER BY raw_score DESC\n"
            "    LIMIT 50\n"
            "),\n"
            "vec AS (\n"
            "    SELECT id,\n"
            "           1 - (embedding <=> $2::vector)\n"
            "               AS raw_score\n"
            "    FROM documents\n"
            "    ORDER BY embedding <=> $2::vector\n"
            "    LIMIT 50\n"
            "),\n"
            "normalized AS (\n"
            "    SELECT id,\n"
            "           (raw_score - min(raw_score) OVER ())\n"
            "           / NULLIF(max(raw_score) OVER ()\n"
            "               - min(raw_score) OVER (), 0)\n"
            "               AS norm_score,\n"
            "           'lex' AS source\n"
            "    FROM lex\n"
            "    UNION ALL\n"
            "    SELECT id,\n"
            "           (raw_score - min(raw_score) OVER ())\n"
            "           / NULLIF(max(raw_score) OVER ()\n"
            "               - min(raw_score) OVER (), 0)\n"
            "               AS norm_score,\n"
            "           'vec' AS source\n"
            "    FROM vec\n"
            ")\n"
            "SELECT id,\n"
            "       sum(CASE WHEN source = 'vec'\n"
            "           THEN 0.7 * norm_score\n"
            "           ELSE 0.3 * norm_score END\n"
            "       ) AS combined_score\n"
            "FROM normalized\n"
            "GROUP BY id\n"
            "ORDER BY combined_score DESC\n"
            "LIMIT 10;"
        )

    def get_rrf_explanation(self) -> dict:
        """Explain RRF formula and parameters."""
        return {
            "formula": "RRF_score(d) = sum(1/(k + rank_i(d)))",
            "parameters": {
                "k": {
                    "default": 60,
                    "description": (
                        "Constant that dampens the influence "
                        "of high ranks. Higher k = more "
                        "equal weighting across lists."
                    ),
                    "range": "1-1000",
                },
            },
            "example": {
                "query": "Error 503 timeout",
                "bm25_results": [
                    {"doc": "A", "rank": 1},
                    {"doc": "B", "rank": 2},
                    {"doc": "C", "rank": 3},
                ],
                "vector_results": [
                    {"doc": "B", "rank": 1},
                    {"doc": "D", "rank": 2},
                    {"doc": "A", "rank": 3},
                ],
                "rrf_scores": {
                    "A": "1/(60+1) + 1/(60+3) = 0.0164 + 0.0159 = 0.0323",
                    "B": "1/(60+2) + 1/(60+1) = 0.0161 + 0.0164 = 0.0325",
                    "C": "1/(60+3) = 0.0159",
                    "D": "1/(60+2) = 0.0161",
                },
                "final_ranking": ["B (0.0325)", "A (0.0323)", "D (0.0161)", "C (0.0159)"],
                "note": (
                    "Doc B ranks high in both lists, "
                    "so RRF boosts it above Doc A "
                    "which only ranks #1 in BM25."
                ),
            },
            "advantages": [
                "No score normalization needed",
                "Works with any ranking-based search system",
                "Industry standard (Elasticsearch uses RRF)",
                "Simple to implement in SQL",
                "Robust to different score distributions",
            ],
        }

    def recommend_method(self, query_type: str,
                         has_exact_terms: bool,
                         has_paradedb: bool) -> dict:
        """Recommend search method based on query characteristics."""
        if has_exact_terms and query_type == "technical":
            return {
                "method": SearchMethod.HYBRID_RRF.value,
                "reason": (
                    "Query contains exact terms (error codes, "
                    "API names) that vector search would miss. "
                    "Hybrid combines keyword + vector."
                ),
                "sql": self.get_hybrid_search_sql() if not has_paradedb
                       else self.get_paradedb_hybrid_sql(),
            }

        if not has_exact_terms and query_type == "natural_language":
            return {
                "method": SearchMethod.VECTOR_ONLY.value,
                "reason": (
                    "Natural language query, no exact terms. "
                    "Vector search captures semantic meaning."
                ),
            }

        if has_exact_terms and not has_paradedb:
            return {
                "method": SearchMethod.HYBRID_RRF.value,
                "reason": (
                    "Use built-in tsvector + pgvector + RRF. "
                    "No ParadeDB needed."
                ),
                "sql": self.get_hybrid_search_sql(),
            }

        return {
            "method": SearchMethod.HYBRID_RRF.value,
            "reason": (
                "Default to hybrid for best recall across "
                "diverse query types."
            ),
            "sql": self.get_hybrid_search_sql(),
        }

Hybrid Search Checklist

[ ] Hybrid search combines keyword search (BM25/tsvector) with vector search (pgvector)
[ ] Keyword search catches exact terms: error codes, product names, version numbers, API names
[ ] Vector search catches semantic matches: synonyms, paraphrasing, conceptual similarity
[ ] Vector search alone misses exact terms — Error 503 query retrieves Error 404 (semantically identical)
[ ] Hybrid search fixes this by combining both search methods
[ ] RRF (Reciprocal Rank Fusion) is the industry standard for combining ranked lists
[ ] RRF is what Elasticsearch uses for hybrid search
[ ] RRF formula: RRF_score(d) = sum(1/(k + rank_i(d))) across all lists
[ ] RRF k parameter: default 60, dampens influence of high ranks
[ ] RRF advantage: no score normalization needed — works with any ranking
[ ] RRF is robust to different score distributions between systems
[ ] A document ranked #1 in both lists scores higher than one ranked #1 in only one
[ ] ParadeDB brings production-ready BM25 full-text search to PostgreSQL
[ ] pgvector delivers vector similarity for semantic understanding
[ ] Install ParadeDB: CREATE EXTENSION paradedb
[ ] ParadeDB BM25 index: CREATE INDEX docs_bm25 ON documents USING bm25 (title, content)
[ ] ParadeDB query: WHERE title @@@ 'query' OR content @@@ 'query'
[ ] ParadeDB score: paradedb.score(id) for BM25 relevance score
[ ] Built-in tsvector: no extension needed beyond pgvector
[ ] tsvector column: GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED
[ ] GIN index on tsvector: CREATE INDEX docs_tsv ON documents USING gin(tsv)
[ ] ts_rank_cd for keyword relevance scoring
[ ] Query: WHERE tsv @@ plainto_tsquery('english', $1)
[ ] HNSW index for vector search: CREATE INDEX USING hnsw (embedding vector_cosine_ops)
[ ] Vector similarity: 1 - (embedding <=> query_vector) AS similarity
[ ] Hybrid SQL: WITH lex AS (...), vec AS (...), ranked AS (UNION ALL with row_number) GROUP BY id, sum(1/(60+rank))
[ ] LIMIT 50 per search path, fuse to top 10 with RRF
[ ] Weighted sum fusion: alpha * vec_score + (1-alpha) * bm25_score — requires normalization
[ ] Reranking: cross-encoder rerank top candidates for maximum precision
[ ] Use hybrid when queries contain exact terms that vector search would miss
[ ] Use pure vector for natural language or conceptual queries
[ ] Use hybrid for production RAG with diverse query types
[ ] Most search systems only do one type — combine for best results
[ ] RRF combines rankings without needing to normalize raw scores
[ ] BM25 handles keyword relevance, pgvector handles semantic meaning, RRF merges
[ ] Set hnsw.ef_search for vector search quality in hybrid queries
[ ] Add B-tree indexes on filter columns for WHERE + hybrid search
[ ] Use pgvector 0.8.0 iterative scans for filtered hybrid queries
[ ] Monitor: recall, latency, index sizes for both BM25 and HNSW
[ ] EXPLAIN ANALYZE on hybrid queries to verify both indexes are used
[ ] Read pgvector tutorial for setup
[ ] Read semantic search for vector search
[ ] Read vector search API for API
[ ] Read pgvector performance for tuning
[ ] Read HNSW explained for algorithm
[ ] Read generate embeddings for embedding
[ ] Test: hybrid search finds Error 503 when query is "Error 503" (not Error 404)
[ ] Test: RRF correctly boosts documents ranked high in both lists
[ ] Test: tsvector + pgvector hybrid returns better results than either alone
[ ] Test: ParadeDB BM25 + pgvector hybrid works with @@@ operator
[ ] Test: EXPLAIN ANALYZE shows both GIN and HNSW index usage
[ ] Document fusion method, k value, index setup, and benchmark results

FAQ

What is hybrid search in PostgreSQL and why do you need it?

Hybrid search combines keyword search (BM25/tsvector) with semantic vector search (pgvector) to get the best of both: exact term matching and semantic understanding. ParadeDB: "ParadeDB brings production-ready full text search with BM25 scoring for lexical relevance, while pgvector delivers vector similarity for semantic understanding. But how do you combine these into a hybrid system? This is the missing manual for hybrid search in PostgreSQL." Reddit: "If I searched for Error 503, vector search would often retrieve Error 404 because they are semantically identical (server errors), even though I needed the exact match. So I upgraded to Hybrid Search." TigerData: "RRF is elegantly simple. It is the industry standard for combining ranked lists, and it is what Elasticsearch uses for hybrid search." Need hybrid because: (1) Vector search catches semantic matches but misses exact terms (Error 503 vs 404). (2) Keyword search catches exact terms but misses synonyms and paraphrasing. (3) Hybrid combines both for best recall.

How does Reciprocal Rank Fusion (RRF) work for combining search results?

RRF combines ranked lists from multiple search systems by scoring each document as the sum of 1/(k + rank) across all lists, where k is a constant (typically 60). TigerData: "RRF (Reciprocal Rank Fusion) is elegantly simple. It is the industry standard for combining ranked lists, and it is what Elasticsearch uses for hybrid search. BM25 Results: Doc A (score 15.2), Doc B (12.1), Doc C (8.4). Vector Results: Doc B, Doc D, Doc A. RRF combines them without needing to normalize raw scores." PedroAlonso: "The most common fusion strategy is Reciprocal Rank Fusion (RRF). It combines rankings from multiple search systems without needing to normalize raw scores. The idea is simple: a document ranked #1 in both lists should score higher than one ranked #1 in only one list." Formula: RRF_score(d) = sum over all lists of 1/(k + rank_i(d)), where k=60 (default). SQL: SELECT id, sum(1.0/(60 + rank)) AS rrf_score FROM (SELECT id, row_number() OVER (ORDER BY bm25_score DESC) AS rank FROM bm25_results UNION ALL SELECT id, row_number() OVER (ORDER BY vector_distance) AS rank FROM vector_results) GROUP BY id ORDER BY rrf_score DESC.

How do you implement hybrid search with ParadeDB and pgvector?

Use ParadeDB for BM25 full-text search and pgvector for semantic vector search, then fuse results with RRF in a single SQL query. ParadeDB: "ParadeDB brings production-ready full text search with BM25 scoring for lexical relevance, while pgvector delivers vector similarity for semantic understanding." TigerData: "Combine keyword search and semantic vector search in PostgreSQL using pg_textsearch and pgvectorscale, fused with Reciprocal Rank Fusion." Implementation: (1) Install ParadeDB: CREATE EXTENSION paradedb. (2) Create BM25 index: CREATE INDEX docs_bm25 ON documents USING bm25 (title, content). (3) Create HNSW index: CREATE INDEX docs_hnsw ON documents USING hnsw (embedding vector_cosine_ops). (4) Query: WITH bm25 AS (SELECT id, paradedb.score(id) AS score FROM documents WHERE title @@@ 'query' LIMIT 50), vec AS (SELECT id, 1-(embedding <=> query_vec) AS score FROM documents ORDER BY embedding <=> query_vec LIMIT 50) SELECT id, sum(1.0/(60+rank)) AS rrf FROM (...) GROUP BY id ORDER BY rrf DESC LIMIT 10.

How do you implement hybrid search with built-in PostgreSQL tsvector?

Use PostgreSQL's built-in tsvector and ts_rank_cd for keyword search, pgvector for vector search, and RRF to fuse results — no extensions beyond pgvector needed. PedroAlonso: "Hybrid search: BM25 handles keyword relevance (green path), pgvector handles semantic meaning (purple path), RRF merges them. The most common fusion strategy is Reciprocal Rank Fusion." Implementation: (1) Add tsvector column: ALTER TABLE documents ADD COLUMN tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED. (2) Create GIN index: CREATE INDEX docs_tsv ON documents USING gin(tsv). (3) Create HNSW index on embedding. (4) Query: WITH lex AS (SELECT id, ts_rank_cd(tsv, plainto_tsquery('english', $1)) AS score FROM documents WHERE tsv @@ plainto_tsquery('english', $1) ORDER BY score DESC LIMIT 50), vec AS (SELECT id, 1-(embedding <=> $2::vector) AS score FROM documents ORDER BY embedding <=> $2::vector LIMIT 50) SELECT id, sum(1.0/(60+rank)) AS rrf FROM (SELECT id, row_number() OVER (ORDER BY score DESC) AS rank FROM lex UNION ALL SELECT id, row_number() OVER (ORDER BY score DESC) AS rank FROM vec) combined GROUP BY id ORDER BY rrf DESC LIMIT 10.

When should you use hybrid search vs pure vector search?

Use hybrid search when your queries contain exact terms (error codes, product names, version numbers) that vector search would miss. Use pure vector search for conceptual or natural language queries. Reddit: "If I searched for Error 503, vector search would often retrieve Error 404 because they are semantically identical. Hybrid search fixed this — keyword search catches the exact error code." ParadeDB: "ParadeDB brings BM25 for lexical relevance, pgvector for semantic understanding. Combine for hybrid." TigerData: "Ever searched for something and thought I know it is in here somewhere, but the search bar just is not getting it? That is because most search systems only do one type of search." Use hybrid when: (1) Queries contain exact terms (error codes, API names, version numbers). (2) Users search for specific product names or IDs. (3) You need both semantic matching and exact term matching. (4) RAG system needs high recall across diverse query types. Use pure vector when: (1) Queries are natural language or conceptual. (2) No exact term matching needed. (3) Simplicity is preferred over maximum recall.

Want a self-hosted AI company brain that does all of this out of the box?
Book a demo →