Retrieval that survives production · part 2
Hybrid retrieval: fuse keyword and vector search
Vector search alone misses exact identifiers and rare terms. Add Postgres full-text search, fuse the rankings with RRF, and rerank the top slice.
Pure vector search has a characteristic failure: it is excellent at "documents about roughly this topic" and unreliable at "the document containing this exact string."
Search your corpus for ERR_CONN_REFUSED_7 and a vector index returns chunks
about connection errors generally — semantically close, practically useless.
Search for a rare product name the embedding model never saw during training and
you get whatever happens to be nearby in a space that has no idea the term is
significant.
Keyword search has the mirror-image failure: it nails the exact term and completely misses "how do I stop the server from refusing connections" because none of those words appear in the document.
You want both. This part adds BM25-style keyword search to what you built in part 1, fuses the two rankings, and reranks the result.
Add a full-text index#
Postgres full-text search is a generated column plus a GIN index. It stays in sync automatically — no reindex job, no sync worker, no window where the search index disagrees with the table.
ALTER TABLE chunks ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(array_to_string(heading_path, ' '), '')), 'A') ||
setweight(to_tsvector('english', coalesce(raw_content, '')), 'B')
) STORED;
CREATE INDEX chunks_search_idx ON chunks USING GIN (search_vector);The weighting matters. A term appearing in the heading path is a far stronger
signal than the same term buried in body text, and setweight lets ts_rank
know that. 'A' outranks 'B' by default at roughly 4:1.
Note this indexes raw_content, not content. The breadcrumb is already
captured with weight A — including it twice would let a document's title
dominate every keyword query against it.
Write the two retrievers#
Keep them separate and comparable. Each returns (chunk_id, rank) — position,
not score. That is deliberate, and step 3 explains why.
from dataclasses import dataclass
@dataclass
class Hit:
chunk_id: int
rank: int # 1-based position in this retriever's ranking
KEYWORD_SQL = """
SELECT c.id
FROM chunks c, websearch_to_tsquery('english', %(q)s) AS query
WHERE c.search_vector @@ query
AND c.strategy = %(strategy)s
ORDER BY ts_rank_cd(c.search_vector, query) DESC
LIMIT %(k)s
"""
VECTOR_SQL = """
SELECT c.id
FROM chunks c
WHERE c.strategy = %(strategy)s
AND c.embedding IS NOT NULL
ORDER BY c.embedding <=> %(vec)s::vector
LIMIT %(k)s
"""
def keyword_search(conn, query: str, k: int = 50, strategy: str = "v1") -> list[Hit]:
rows = conn.execute(
KEYWORD_SQL, {"q": query, "k": k, "strategy": strategy}
).fetchall()
return [Hit(chunk_id=r[0], rank=i + 1) for i, r in enumerate(rows)]
def vector_search(conn, query: str, k: int = 50, strategy: str = "v1") -> list[Hit]:
vec = embed([query])[0]
rows = conn.execute(
VECTOR_SQL, {"vec": vec, "k": k, "strategy": strategy}
).fetchall()
return [Hit(chunk_id=r[0], rank=i + 1) for i, r in enumerate(rows)]Use websearch_to_tsquery, not plainto_tsquery. It accepts the syntax users
already know from every search box — quoted phrases, OR, and -exclusion —
and it does not throw on malformed input, which to_tsquery very much does.
ts_rank_cd (cover density) considers how close the matched terms are to each
other. For multi-word queries it consistently beats plain ts_rank.
Fuse with Reciprocal Rank Fusion#
Now you have two ranked lists and need one. The obvious approach — normalize
both scores and take a weighted sum — is a trap. Cosine distance and ts_rank_cd
live on incomparable scales, their distributions shift per query, and any
normalization you pick will need retuning every time you change the embedding
model.
Reciprocal Rank Fusion sidesteps the entire problem by discarding the scores and using only positions:
RRF_K = 60
def fuse(rankings: list[list[Hit]], weights: list[float] | None = None) -> list[int]:
"""Reciprocal Rank Fusion. Score = sum over retrievers of w / (K + rank)."""
weights = weights or [1.0] * len(rankings)
scores: dict[int, float] = {}
for hits, weight in zip(rankings, weights):
for hit in hits:
scores[hit.chunk_id] = scores.get(hit.chunk_id, 0.0) + weight / (RRF_K + hit.rank)
return [cid for cid, _ in sorted(scores.items(), key=lambda kv: kv[1], reverse=True)]Three things make RRF the right default:
- Scale-free. No normalization, so swapping embedding models changes nothing downstream.
- Rewards agreement. A chunk ranked 3rd by both retrievers beats one ranked 1st by one and absent from the other. Agreement across independent methods is genuine signal.
- One parameter, and it barely matters.
K = 60is the value from the original paper and works essentially everywhere. LargerKflattens the contribution of top ranks; you will not need to tune it.
Weights are where you express intent. [1.0, 1.0] is a fine start. If your
corpus is heavy on identifiers, error codes, and API names, push keyword up to
[1.5, 1.0] — then verify with part 3's harness rather than trusting the
intuition.
Filter in SQL, not after#
This is where keeping retrieval in Postgres pays for itself.
In a dedicated vector database, "only documents this user can see, published in
the last 90 days" is either a pre-filter that degrades the ANN index or a
post-filter that leaves you with three results out of fifty. Here it's a WHERE
clause the planner handles.
FILTERED_VECTOR_SQL = """
SELECT c.id
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE c.strategy = %(strategy)s
AND c.embedding IS NOT NULL
AND d.metadata->>'workspace_id' = %(workspace)s
AND (d.metadata->>'visibility' = 'public' OR d.metadata->>'owner' = %(user)s)
AND d.updated_at > now() - interval '90 days'
ORDER BY c.embedding <=> %(vec)s::vector
LIMIT %(k)s
"""Rerank the top slice#
Fusion gets the right chunks into the top 50. A cross-encoder reranker gets them into the right order within that 50.
The distinction is architectural. Your embedding model encodes the query and the document separately — it never sees them together, which is what makes precomputed indexes possible. A cross-encoder reads the pair jointly and scores relevance directly. Far more accurate, far too slow to run over a corpus, exactly right for 50 candidates.
def rerank(query: str, chunks: list[dict], top_n: int = 8) -> list[dict]:
scored = cross_encoder_score(query, [c["content"] for c in chunks])
ranked = sorted(zip(chunks, scored), key=lambda cs: cs[1], reverse=True)
return [chunk for chunk, _ in ranked[:top_n]]The full pipeline, end to end:
def search(conn, query: str, *, top_n: int = 8, strategy: str = "v1") -> list[dict]:
keyword = keyword_search(conn, query, k=50, strategy=strategy)
vector = vector_search(conn, query, k=50, strategy=strategy)
fused_ids = fuse([keyword, vector], weights=[1.0, 1.0])[:50]
if not fused_ids:
return []
rows = conn.execute(
"""
SELECT c.id, c.content, c.raw_content, c.heading_path,
d.title, d.source_uri
FROM chunks c JOIN documents d ON d.id = c.document_id
WHERE c.id = ANY(%s)
""",
(fused_ids,),
).fetchall()
by_id = {r[0]: dict(zip(
["id", "content", "raw_content", "heading_path", "title", "source_uri"], r
)) for r in rows}
ordered = [by_id[cid] for cid in fused_ids if cid in by_id]
return rerank(query, ordered, top_n=top_n)Note that reranking scores content (with the breadcrumb) while the caller
displays raw_content. Same split as part 1, same reason.
Assemble the context you actually send#
One more step people skip: what you retrieved is not what you should send.
Deduplicate by document. Eight chunks from the same page is not eight pieces of evidence. Cap it at two or three per document and let the rest of the budget go to other sources.
Order by relevance, but put the strongest last. Material near the end of a long prompt gets more reliable attention than material buried in the middle.
Label every chunk with its source. Not decoration — it's what makes citation possible and what lets you tell hallucination from a genuine retrieval failure when you're debugging.
MAX_PER_DOC = 2
def build_context(hits: list[dict], budget_tokens: int = 4000) -> str:
seen: dict[str, int] = {}
kept, used = [], 0
for hit in hits:
uri = hit["source_uri"]
if seen.get(uri, 0) >= MAX_PER_DOC:
continue
cost = count_tokens(hit["raw_content"]) + 32
if used + cost > budget_tokens:
continue
seen[uri] = seen.get(uri, 0) + 1
kept.append(hit)
used += cost
# Strongest match last — closest to the question.
blocks = [
f'<source id="{i + 1}" uri="{h["source_uri"]}" title="{h["title"]}">\n'
f'{h["raw_content"]}\n</source>'
for i, h in enumerate(reversed(kept))
]
return "\n\n".join(blocks)Where this lands#
Keyword search catches the exact terms. Vector search catches the paraphrases.
RRF merges them without a normalization step that would rot the moment you change
models. The reranker fixes ordering within the top slice, and metadata filtering
is a WHERE clause because everything lives in one database.
Every knob in here — RRF_K, the fusion weights, k = 50, top_n = 8,
MAX_PER_DOC — is currently set by argument rather than evidence. Some of them
are probably wrong for your corpus.
Part 3 builds the golden set and the metrics that turn those arguments into numbers, so you can tell which of these choices helped and which just felt sophisticated.
Filed under