S
Subhash Tatavarthi
Guest
Quick Refresher
Part 1 built the naive pipeline and watched it cite a four-year-old policy. Part 2 rebuilt ingestion — structure-aware chunking, provenance on every chunk, explicit supersession — and fixed it with a
WHERE clause rather than a better model.Retrieval is now correct. This post is about the two ways it is still wrong: it is quietly lossy, and there is an entire category of question it cannot answer.
Neither is fixed by chunking. Both are fixed here.
What HNSW is actually doing
Here is the thing most people never look at, and it explains almost everything that follows.
ORDER BY embedding <=> query LIMIT 5 looks like it returns the five nearest chunks. It doesn't. It returns five chunks that are probably among the nearest. HNSW is an approximate nearest neighbour index — it trades exactness for speed, and the trade is not optional.The structure is a layered graph. The top layer holds a sparse scattering of nodes with long-range links; each layer down is denser. A search enters at the top, greedily walks toward the query vector, drops a layer, walks again, and repeats. It's a highway system: motorways first, then A-roads, then the residential street. You arrive quickly, and occasionally you arrive at the wrong house because the road you needed branched off two exits back.
Three parameters govern it:
m(default 16) — links per node. More links, better recall, bigger index.ef_construction(default 64) — how hard the builder works to find good links. Build-time cost only.ef_search(default 40) — how many candidates the search keeps in flight. This is the runtime knob.
ef_search is the one to internalise. It's the size of the dynamic candidate list — the search explores up to 40 candidates and returns your top 5 from those. Raise it and recall improves and latency rises. This is a dial, not a setting, and the default is tuned for prototypes.None of this was a problem in Part 1. It became one the moment I added a filter.
The bug I introduced last post
Part 2 ended with this query:
Code:
SELECT content FROM chunks
WHERE status = 'current'
ORDER BY embedding <=> %s::vector
LIMIT 5;Read it as a Postgres user and it's obvious: filter to current chunks, order by distance, take five.
That is not what happens. With approximate indexes, the filter is applied after the index is scanned. HNSW collects its 40 candidates by vector distance alone, knowing nothing about
status, and only then does Postgres discard the ones that don't match.The arithmetic is unforgiving. If superseded chunks are 10% of your table, you get 36 survivors — fine. If you filter to something matching 10% of rows, you average 4 results from a 40-candidate list. And if the filter is narrow — one department, one date range, one document — you can get zero. The query succeeds. It returns an empty set. Nothing errors.
This is the single most common pgvector surprise in production, and it is nastier than the Part 1 bug because it degrades rather than fails. Your answers get slightly worse. Nobody files a ticket about slightly.
The fix
pgvector 0.8.0 added iterative index scans, which is exactly the right solution: instead of scanning once and filtering, the index keeps fetching candidates until it has enough that survive the filter.
Code:
SET hnsw.iterative_scan = 'relaxed_order';
SET hnsw.ef_search = 100;relaxed_order returns results approximately ordered by distance and is slightly faster; strict_order guarantees exact distance ordering and costs a little more. For RAG, relaxed_order is almost always right — you're handing five chunks to a language model that doesn't care which was 3rd and which was 4th. Use strict_order when the rank itself is the product.Two guardrails worth setting at the same time:
Code:
SET hnsw.max_scan_tuples = 20000; -- stop runaway scans on very selective filtersWithout this, a filter matching one row in ten million will happily walk your entire index.
And the thing everyone forgets: put a normal B-tree index on the columns you filter by. The vector index doesn't help the filter, and the filter still has work to do.
Code:
CREATE INDEX ON chunks (status) WHERE status = 'current';That partial index was already in the Part 2 schema, which was luck rather than foresight on my part.
A better option when the filter is stable
If you always filter on the same thing — and in our case, ~95% of queries want current chunks only — build the vector index itself as partial:
Code:
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WHERE status = 'current';Now the graph contains only current chunks. There is nothing to filter out, so recall is exact with respect to that predicate and the index is smaller and faster. The cost is rigidity: a second index for historical queries, and a rebuild if the predicate changes. For a filter that is genuinely stable, it's the cleanest answer available.
The query that vector search cannot answer
Someone pastes this into the assistant:
Code:
ERR_TXN_4412Every retrieval improvement so far — structure-aware chunking, contextual headers, supersession — does nothing here. The system returns five passages that are about transaction errors, beautifully semantically related, and not one of them contains that string.
This is not a bug. It's the mechanism working correctly. Embeddings encode meaning, and
ERR_TXN_4412 has very little meaning; it's an identifier. To the embedding model it's a low-information token sequence that sits near every other error code in vector space. Semantic similarity is the wrong tool for exact match, the same way a thesaurus is the wrong tool for finding a phone number.Real corpora are full of these: error codes, SKUs, config keys, function names, ticket IDs, table names, version strings. And they're disproportionately what people search for, because they're what you paste when something is broken.
Hybrid search
The fix is to run lexical search alongside vector search and fuse the results. Postgres does the lexical half natively — no new infrastructure:
Code:
ALTER TABLE chunks ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
CREATE INDEX ON chunks USING GIN (tsv);Then fuse. Reciprocal Rank Fusion is the standard approach and it's refreshingly dumb: ignore the scores entirely, use only the ranks. Vector distances and BM25 scores are on incomparable scales, and every attempt to normalise them into a weighted sum turns into a tuning nightmare that breaks whenever your corpus changes. RRF sidesteps the whole problem.
Code:
WITH semantic AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> %(vec)s::vector) AS rank
FROM chunks WHERE status = 'current'
ORDER BY embedding <=> %(vec)s::vector LIMIT 30
),
lexical AS (
SELECT id, ROW_NUMBER() OVER (
ORDER BY ts_rank_cd(tsv, plainto_tsquery('english', %(q)s)) DESC
) AS rank
FROM chunks
WHERE status = 'current'
AND tsv @@ plainto_tsquery('english', %(q)s)
LIMIT 30
)
SELECT c.id, c.content, c.source_title, c.effective_date,
COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + l.rank), 0) AS score
FROM chunks c
LEFT JOIN semantic s ON s.id = c.id
LEFT JOIN lexical l ON l.id = c.id
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY score DESC
LIMIT 5;The 60 is the standard RRF constant. It dampens the influence of the top ranks so that a single list can't dominate; it's a convention from the original paper rather than something you should tune early.
Now
ERR_TXN_4412 matches lexically and lands at rank 1 in that arm, which is enough to carry it into the final five regardless of what the vector arm thought. Conceptual questions are unaffected — the vector arm carries those, and the lexical arm contributes nothing useful, which is fine because RRF doesn't require both arms to agree.One tuning note that cost me an afternoon: each arm must retrieve more than your final limit. Pulling 5 from each and fusing to 5 gives you almost nothing to fuse. 30 in, 5 out is a reasonable starting ratio.
Reranking, and when it's worth it
Fusion gets the right chunks into the candidate set. A reranker decides the final order properly.
A cross-encoder reads the query and a chunk together and scores the pair. Embeddings can't do this — a chunk's vector is computed once at ingestion, before the query exists, so a bi-encoder compares two summaries made in isolation. A cross-encoder reads both at once and is meaningfully more accurate.
It's also far slower: one model call per candidate, so you can't run it over the corpus, only over the ~30 candidates fusion handed you.
Code:
from sentence_transformers import CrossEncoder
reranker = CrossEncoder("cross-encoder/ms-marco-MiniLM-L-6-v2")
def rerank(query: str, candidates: list[dict], top_k: int = 5) -> list[dict]:
scores = reranker.predict([(query, c["content"]) for c in candidates])
ranked = sorted(zip(candidates, scores), key=lambda x: -x[1])
return [c for c, _ in ranked[:top_k]]Worth being clear about what this does and doesn't buy you. It reorders. It cannot conjure a chunk that fusion didn't retrieve, and — as Part 1 established at some length — it cannot supply information the chunk doesn't contain. It would not have fixed the parental leave bug. It adds 50–200ms depending on model and candidate count.
I'd add it after hybrid search and after ingestion is solid, not before. It's the last 10%, and reaching for it first is how teams end up with an expensive pipeline that still returns the 2021 policy.
So when do you actually leave pgvector?
The honest answer is later than the vendor blog posts suggest and earlier than pgvector maximalists admit.
Stay on pgvector when your vectors fit comfortably in RAM alongside your normal workload, your filters are metadata filters over your own data, and you value having chunks and business data in the same transaction. That last point is underrated: in Part 2, supersession marking was a single SQL statement operating on the same rows as the vectors. In a separate vector store, that becomes a distributed consistency problem, and your embeddings and your metadata will drift apart eventually.
Sizing: a 1536-dimension vector is about 6KB. A million chunks is roughly 6GB of vectors plus graph overhead, and it wants to live in
shared_buffers or the OS page cache. Below a few million chunks on a well-provisioned box, pgvector is genuinely fine.Start looking elsewhere when you're past tens of millions of vectors, you need sharding, you need multi-tenant isolation at the index level, or your write volume makes HNSW maintenance painful — vacuuming HNSW indexes is slow, and that surprises people who arrive expecting normal Postgres behaviour.
Two settings that matter more than the choice of database, if you stay:
Code:
maintenance_work_mem = 4GB # index build speed; the 64MB default is not serious
shared_buffers = 8GB # on a 32GB boxmaintenance_work_mem is the one people miss. It's the difference between an index build that takes twenty minutes and one that takes all night.My position: the migration is a scaling decision, not a quality decision. A dedicated vector database will not retrieve better answers than pgvector at 5,000 documents. It will handle 50 million vectors better. If your retrieval is bad at your current scale, migrating will produce the same bad answers with better p99 latency.
What this still doesn't solve
Someone asks: "How many customers churned last quarter?"
Hybrid search runs. The vector arm finds passages about churn. The lexical arm finds documents containing the word. Fusion combines them, the reranker orders them, and five genuinely relevant chunks arrive at the language model — which produces a confident number that is completely made up.
There is no bug in the retrieval pipeline. Everything worked. The problem is that the answer isn't in any document. It's in a table, in a warehouse, and it changes every night. No amount of retrieval over unstructured text will find a fact that lives in structured data.
Part 4 is about that: text-to-SQL, why it collapses on a 200-table schema when every demo works on a toy one, the semantic layer that makes it survivable, and how the system decides which retrieval mode a question needs in the first place.
Previously in this series: