Overview
A knowledge-base assistant for an enterprise client kept failing on queries containing SKUs, error codes, and policy numbers — 'What does error E-4412 mean?' returned semantically similar but wrong documents, because embeddings smear exact identifiers into fuzzy neighborhoods. Users lost trust fast: a support tool that confidently retrieves the wrong policy is worse than no tool.
Rather than bolt on a separate search engine, I kept everything in Postgres: pgvector for dense retrieval, a BM25 index for lexical matching, and Reciprocal Rank Fusion to merge the two candidate lists before a final cross-encoder rerank. One database, one transaction boundary, one backup story — which mattered a lot to an ops team of three. Retrieval hit rate on the identifier-heavy eval slice went from 61% to 94%.
Architecture
- Documents are chunked at ~500 tokens with heading-path prefixes ('Billing > Refunds > EU customers: ...') so chunks carry their own context.
- Each chunk row stores both an HNSW-indexed embedding column and a BM25-indexed text column — same table, no sync problem.
- Query time: dense top-40 and BM25 top-40 run as two CTEs in a single SQL statement, fused with RRF (k=60) in the same query.
- Fused top-20 candidates go through Cohere Rerank; top-5 survivors are packed into the prompt with source URLs.
- An eval harness with 300 labeled query/chunk pairs runs on every retrieval-config change; hit@5 and MRR are tracked per query category (identifier, conceptual, multi-hop).
- Embedding writes happen in the same transaction as document upserts, so retrieval never sees a half-indexed document.
Code Snippets
WITH dense AS (
SELECT id, ROW_NUMBER() OVER (
ORDER BY embedding <=> $1::vector
) AS rank
FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT 40
),
sparse AS (
SELECT id, ROW_NUMBER() OVER (
ORDER BY paradedb.score(id) DESC
) AS rank
FROM chunks
WHERE content @@@ $2
LIMIT 40
)
SELECT c.id, c.content, c.source_url,
COALESCE(1.0 / (60 + dense.rank), 0.0)
+ COALESCE(1.0 / (60 + sparse.rank), 0.0) AS rrf_score
FROM chunks c
LEFT JOIN dense ON dense.id = c.id
LEFT JOIN sparse ON sparse.id = c.id
WHERE dense.id IS NOT NULL OR sparse.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 20;Lessons Learned
- Retrieval quality dominated model choice by a wide margin. Swapping GPT-4o for a cheaper model changed answer quality less than fixing one bad chunking rule.
- Pure vector search is blind to exact tokens. Any corpus with SKUs, error codes, or legal references needs a lexical channel — this is not optional.
- RRF is embarrassingly effective for how simple it is. We tried learned fusion weights and got less than a point of MRR over rank-based fusion with k=60.
- Prefixing chunks with their heading path was the single highest-ROI change — it cost nothing and lifted conceptual-query hit@5 by 9 points.
- Slice your evals by query type. Our aggregate hit rate looked fine at 82% while the identifier slice was silently failing at 61%.