Hybrid Search and Reranking Inside Postgres: Measure Recall, Don’t Guess

1. The Problem: Vectors Miss Exact Terms, Keywords Miss Meaning

Ask a dense retriever for ’error E1042 on checkout’ and it returns pages about payment failures in general: semantically close, practically useless. Ask a keyword index ’why was my card declined?’ when the document says ’transaction rejected by the issuer’ and it returns nothing. Dense search generalises meaning and blurs identifiers; lexical search nails identifiers and has no idea about paraphrase. Real corpora — docs, tickets, code, contracts — contain both kinds of query every day.

The fix has three moving parts, and all of them fit inside Postgres: two cheap retrievers that fail differently, a rank-based fusion that needs no score calibration, and an expensive cross-encoder that only ever sees a short candidate list. I deliberately skip pgvector basics and chunking here — the vector databases guide and the chunking guide in the related articles cover them — and start where they stop.

2. Minimum Concepts Before Touching Code

Six ideas cover almost everything below. Get these straight and the SQL and Python read like plain English.

3. Pick the Lexical Leg: tsvector, pg_textsearch or pg_search

Postgres gives you three realistic options for the keyword side, and the right one depends less on features than on what your hosting lets you install. I checked the facts below against each project’s own docs in October 2026; these projects move fast, so verify before you pin a version.

Ask your database what it can actually install

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('vector', 'pg_textsearch', 'pg_search');

4. Step 1 — Schema and the Fused Query (One SQL Statement)

One table holds the text, the embedding and a generated tsvector. Both indexes are standard: GIN for the lexical leg, HNSW with cosine ops for the dense leg. The 384 dimensions match the sentence-transformers model used in the Python steps; change it to match yours.

SQL — table and indexes

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id        bigserial PRIMARY KEY,
    doc_id    text NOT NULL,
    content   text NOT NULL,
    embedding vector(384),
    tsv       tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);

CREATE INDEX chunks_tsv_idx ON chunks USING GIN (tsv);
CREATE INDEX chunks_emb_idx ON chunks USING hnsw (embedding vector_cosine_ops);

The fusion lives in one statement, adapted from the RRF example in pgvector’s own Python repo. Each leg is an ordered LIMIT subquery, so the index does the work; ROW_NUMBER turns position into rank; a FULL OUTER JOIN sums weight / (k + rank) with COALESCE for chunks that only one leg returned. The %(name)s placeholders are psycopg named parameters. I use k = 60 and equal weights as starting points, not as conclusions.

SQL — hybrid.sql (psycopg named parameters)

WITH semantic AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY dist, id) AS rank
    FROM (
        SELECT id, embedding <=> %(embedding)s AS dist
        FROM chunks
        ORDER BY embedding <=> %(embedding)s
        LIMIT %(n)s
    ) s
),
lexical AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY score DESC, id) AS rank
    FROM (
        SELECT id, ts_rank_cd(tsv, q) AS score
        FROM chunks, websearch_to_tsquery('english', %(query)s) AS q
        WHERE tsv @@ q
        ORDER BY score DESC, id
        LIMIT %(n)s
    ) l
),
fused AS (
    SELECT COALESCE(s.id, l.id) AS id,
           COALESCE(%(w_dense)s / (%(k)s + s.rank), 0.0)
         + COALESCE(%(w_lex)s   / (%(k)s + l.rank), 0.0) AS score
    FROM semantic s
    FULL OUTER JOIN lexical l ON s.id = l.id
)
SELECT c.id, c.doc_id, c.content, f.score
FROM fused f
JOIN chunks c ON c.id = f.id
ORDER BY f.score DESC, c.id
LIMIT %(limit)s;

When the harness says the keyword side is the weak link, swap only the lexical CTE. This is the pg_textsearch version, following its README: create the bm25 index with a text_config, then order by the <@> operator, which returns negative BM25 scores, so ascending order puts the best match first.

SQL — BM25 lexical leg with pg_textsearch

-- after adding pg_textsearch to shared_preload_libraries and restarting
CREATE EXTENSION pg_textsearch;

CREATE INDEX chunks_bm25_idx ON chunks
    USING bm25 (content) WITH (text_config = 'english');

-- replaces the "lexical" CTE in hybrid.sql
lexical AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY score, id) AS rank
    FROM (
        SELECT id, content <@> to_bm25query(%(query)s, 'chunks_bm25_idx') AS score
        FROM chunks
        ORDER BY content <@> to_bm25query(%(query)s, 'chunks_bm25_idx')
        LIMIT %(n)s
    ) l
),

5. Step 2 — Cross-Encoder Rerank (Local or Hosted)

Fetch the top-N from SQL, score every (query, chunk) pair with a cross-encoder, sort by that score. The rerank function takes the rows the SQL returned, so you can swap models without touching retrieval. The local version below uses BAAI/bge-reranker-v2-m3 through FlagEmbedding, following its model card.

Python — retrieval.py (hybrid SQL + local rerank)

from pathlib import Path

import psycopg
from FlagEmbedding import FlagReranker
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

conn = psycopg.connect("dbname=hybrid_demo", autocommit=True)
register_vector(conn)

embedder = SentenceTransformer("multi-qa-MiniLM-L6-cos-v1")  # 384 dims, as in the schema
reranker = FlagReranker("BAAI/bge-reranker-v2-m3", use_fp16=True)

HYBRID_SQL = Path("hybrid.sql").read_text()


def hybrid(query, n=50, k=60, limit=50, w_dense=1.0, w_lex=1.0):
    params = {
        "embedding": embedder.encode(query), "query": query,
        "n": n, "k": k, "limit": limit, "w_dense": w_dense, "w_lex": w_lex,
    }
    return conn.execute(HYBRID_SQL, params).fetchall()  # (id, doc_id, content, score)


def rerank(query, rows, top_k=10):
    pairs = [[query, content] for _, _, content, _ in rows]
    scores = reranker.compute_score(pairs, normalize=True)  # sigmoid -> 0..1
    if isinstance(scores, float):  # a single pair returns a scalar
        scores = [scores]
    order = sorted(range(len(rows)), key=lambda i: scores[i], reverse=True)
    return [rows[i] for i in order[:top_k]]

Which model? Both open options below are Apache-2.0 on their Hugging Face cards, and both hosted APIs expose the same shape: send a query and a list of texts, get back indexes and relevance scores. Treat the sizes and limits as the docs state them today and re-check before you commit.

Python — drop-in alternatives (same signature)

# Local: Qwen3-Reranker through sentence-transformers (needs transformers>=4.51.0)
from sentence_transformers import CrossEncoder

qwen = CrossEncoder("Qwen/Qwen3-Reranker-0.6B")

def rerank_qwen(query, rows, top_k=10):
    scores = qwen.predict([(query, r[2]) for r in rows])
    order = sorted(range(len(rows)), key=lambda i: scores[i], reverse=True)
    return [rows[i] for i in order[:top_k]]


# Hosted: Cohere Rerank v2 API (the client reads the API key from the environment)
import cohere

co = cohere.ClientV2()

def rerank_cohere(query, rows, top_k=10):
    resp = co.rerank(
        model="rerank-v4.0-pro",  # or rerank-v4.0-fast / rerank-v3.5
        query=query,
        documents=[r[2] for r in rows],
        top_n=top_k,
    )
    return [rows[r.index] for r in resp.results]


# Hosted: Voyage rerank (uses the VOYAGE_API_KEY environment variable)
import voyageai

vo = voyageai.Client()

def rerank_voyage(query, rows, top_k=10):
    out = vo.rerank(query, [r[2] for r in rows], model="rerank-3", top_k=top_k)
    return [rows[r.index] for r in out.results]

6. Step 3 — Measure recall@k Before and After

Without this step everything above is vibes. Write 50 to 100 real queries (from search logs, support tickets, or the people who know the corpus), and for each one list the doc_ids that actually answer it. The chunking guide in the related articles shows how to build such a set; here it is just a JSONL file.

gold.jsonl — one labelled query per line (format)

{"query": "error E1042 on checkout", "relevant": ["doc-0172"]}
{"query": "how do I rotate refresh tokens?", "relevant": ["doc-0031", "doc-0032"]}

Python — eval.py: four strategies, same queries

import json
import statistics
import time

from retrieval import conn, embedder, hybrid, rerank

N = 50  # candidates per leg and fused list length
KS = (5, 10, N)  # recall@N is the candidate ceiling

DENSE_SQL = """
SELECT id, doc_id, content, 0.0 AS score FROM chunks
ORDER BY embedding <=> %(embedding)s LIMIT %(n)s
"""
LEXICAL_SQL = """
SELECT id, doc_id, content, ts_rank_cd(tsv, q) AS score
FROM chunks, websearch_to_tsquery('english', %(query)s) AS q
WHERE tsv @@ q ORDER BY score DESC LIMIT %(n)s
"""


def dense(q):
    return conn.execute(DENSE_SQL, {"embedding": embedder.encode(q), "n": N}).fetchall()


def lexical(q):
    return conn.execute(LEXICAL_SQL, {"query": q, "n": N}).fetchall()


STRATEGIES = {
    "dense": dense,
    "lexical": lexical,
    "hybrid RRF": lambda q: hybrid(q, n=N, limit=N),
    "hybrid + rerank": lambda q: rerank(q, hybrid(q, n=N, limit=N), top_k=N),
}


def doc_order(rows):  # chunks -> unique doc_ids in rank order
    seen, out = set(), []
    for _, doc_id, _, _ in rows:
        if doc_id not in seen:
            seen.add(doc_id)
            out.append(doc_id)
    return out


gold = [json.loads(line) for line in open("gold.jsonl", encoding="utf-8")]
hybrid(gold[0]["query"])  # warm-up: model load and caches must not count

for name, fn in STRATEGIES.items():
    recall, rr, lat = {k: 0.0 for k in KS}, 0.0, []
    for ex in gold:
        t0 = time.perf_counter()
        docs = doc_order(fn(ex["query"]))
        lat.append((time.perf_counter() - t0) * 1000)
        rel = set(ex["relevant"])
        for k in KS:
            recall[k] += len(rel & set(docs[:k])) / len(rel)
        first = next((i for i, d in enumerate(docs, 1) if d in rel), None)
        rr += 1.0 / first if first else 0.0
    n = len(gold)
    p95 = statistics.quantiles(lat, n=20)[18]  # needs at least 2 queries
    cols = " ".join(f"recall@{k}={recall[k] / n:.3f}" for k in KS)
    print(f"{name:16} {cols} MRR={rr / n:.3f} p50={statistics.median(lat):.0f}ms p95={p95:.0f}ms")

Run it once now to get your dense-only baseline, then again after each change. This is the output shape you should expect. I am deliberately leaving the numbers as placeholders: any figure I printed here would be invented, and yours depend on your corpus, chunks, embedder and hardware.

Expected output shape (placeholders, not results)

dense            recall@5=?.??? recall@10=?.??? recall@50=?.??? MRR=?.??? p50=??ms p95=??ms
lexical          recall@5=?.??? recall@10=?.??? recall@50=?.??? MRR=?.??? p50=??ms p95=??ms
hybrid RRF       recall@5=?.??? recall@10=?.??? recall@50=?.??? MRR=?.??? p50=??ms p95=??ms
hybrid + rerank  recall@5=?.??? recall@10=?.??? recall@50=?.??? MRR=?.??? p50=??ms p95=??ms

7. Operating It: Latency, Filters and Index Hygiene

The query path is the easy part. Here is the routine that keeps it honest once real traffic and real data changes arrive:

8. Common Errors (All of Them Fixable in an Afternoon)

Most hybrid-search disappointments trace back to one of these buckets. The left column is what working stacks do; the right column is what makes retrieval quietly worse.

Conclusion

You now have the full loop inside Postgres: a lexical leg (built-in tsvector, or BM25 through pg_textsearch or pg_search), a pgvector leg, Reciprocal Rank Fusion with k = 60 in one SQL statement, a cross-encoder rerank that is a one-function swap between local Apache-2.0 models and hosted Cohere or Voyage, and a harness that turns ’it feels better’ into recall@k and MRR.

This week: write 50 labelled queries, run eval.py on today’s dense-only retrieval to get the baseline, then add one thing at a time (lexical leg, fusion, reranker) and keep only what the numbers justify. One last honesty note: I did not run benchmarks for this article, so every claim about quality is something your own harness has to confirm.

Sources

• pgvector — README (hybrid search, HNSW, iterative index scans, filtering)
• pgvector-python — Hybrid search with Reciprocal Rank Fusion (example)
• PostgreSQL docs — Text search: ranking search results (ts_rank, ts_rank_cd, websearch_to_tsquery)
• PostgreSQL docs — Text search: tables and indexes (generated tsvector column, GIN)
• Cormack, Clarke, Büttcher — Reciprocal Rank Fusion outperforms Condorcet and individual rank learning methods (SIGIR 2009)
• pg_textsearch — README (BM25 index, <@> operator, limitations, version history)
• Tiger Data — pg_textsearch on Google Cloud AlloyDB and Cloud SQL (press release, September 21, 2026)
• ParadeDB — Reciprocal Rank Fusion (pg_search + pgvector query shape, k, weights, branch limit)
• ParadeDB — Create an index (USING paradedb, bm25 alias)
• ParadeDB — Self-hosted PostgreSQL extension install (versions, shared_preload_libraries, pgvector)
• Neon docs — pg_search removal and the lakebase_text replacement
• BAAI/bge-reranker-v2-m3 — Hugging Face model card (Apache-2.0, FlagReranker usage)
• Qwen3-Reranker-0.6B — Hugging Face model card (Apache-2.0, 32k context, CrossEncoder usage)
• Cohere — Rerank models and API reference (v2)
• Voyage AI — Rerankers (models, limits, Python API)