Catalog retrieval at 500 million rows
Our enterprise database holds 500 million product metadata rows. Standard top-k vector retrieval is returning irrelevant chunks, which causes hallucination and blows through our token budgets. How do you re-architect this context ingestion pipeline to improve accuracy and control cost?
The constraint that decides it. Product metadata is structured. Brand, category, price, dimensions, compatibility, stock — these are columns, not prose. Embedding a row into a chunk and running cosine similarity discards precisely the signal that makes catalog retrieval exact. Most catalog queries are not semantic: "Bosch dishwashers under €700 with a third rack" is a WHERE clause with one fuzzy brand match. Vector search should handle the residual, not the primary path.
The stated symptom names the bug. "Irrelevant chunks" at 500M rows is almost always post-filtering: retrieve top-100 globally, then apply the tenant and category filter, and three candidates survive. The pipeline pads the context back to k with whatever ranked next, and the model dutifully grounds on garbage.
Memory arithmetic, which sets the whole architecture. At 1024 dimensions, fp32:
| Representation | Size | Note |
|---|---|---|
| fp32 | 2.0 TB | not a candidate |
| int8 scalar quantization | 512 GB | ~99% recall retained with rerank |
| binary + rescore | 64 GB | needs a rerank stage to be usable |
| HNSW graph overhead (M=16) | ~64 GB | on top of any of the above |
In-memory HNSW over the full corpus is off the table. This forces one of two choices: quantize hard and rescore, or move to an object-storage-native index. That decision precedes every other one.
Architecture.
- 01Collapse cardinality before indexing. 500M rows are not 500M distinct products. Collapse SKU variants to product models — colour, size and packaging variants share one canonical record. A 10-50x reduction is typical. Retrieve at model level; expand to variants deterministically in SQL afterwards. This is the cheapest accuracy win available and it is usually skipped.
- 02Stop chunking rows. A product row is not a document. Emit one vector per product from a templated canonical string, and separate vectors only for genuinely long fields — description, review digest, manual excerpt. Chunking structured records is what manufactures "irrelevant chunks."
- 03Query understanding before retrieval. Extract structured constraints — attributes, numeric ranges, entities — with grammar-constrained decoding on a small model. Route: fully filterable queries go to SQL and never touch the vector index; ambiguous ones go to hybrid. This removes a large fraction of traffic from the expensive path entirely.
- 04Filter inside the ANN traversal, not after it. The index must accept the predicate and apply it during graph traversal. Qdrant payload filtering, Vespa, and Elasticsearch filtered kNN do this. If your engine only post-filters, over-fetch by the inverse of selectivity or replace the engine.
- 05Two stages: recall, then precision. BM25 and dense in parallel, fused with RRF, top-100 into a cross-encoder reranker, top-5 to 10 into context. Adding a reranker beats swapping vector databases, consistently.
- 06Budget by tokens, not by k. Fixed k is what blows the budget. Fill the context to a token ceiling, ordered by rerank score, and stop.
- 07Threshold, then abstain. If nothing clears the rerank score threshold, return "no confident match" rather than shipping ten weak candidates. This is the hallucination fix — the model hallucinates because you handed it irrelevant context and implied it was relevant.
- 08Pass rows, not prose. Structured JSON with product IDs. Require citations by ID, then verify every cited ID appears in the retrieved set and reject the response otherwise. A deterministic check, not a judge.
- 09Re-embed on content hash, not on write. Hash the embedding template output. A price change does not alter the template, so it triggers no re-embedding. Without this, catalog churn re-embeds the corpus continuously.
Token effect. Twenty chunks at ~400 tokens is 8,000 tokens of mostly noise. Five structured rows at ~150 tokens is 750. Better accuracy at a tenth of the context.
Stack.
| Layer | Pick | Why |
|---|---|---|
| System of record | Postgres | Variants, stock, pricing stay relational |
| Vector | Qdrant, Vespa, or Turbopuffer | Pre-filtered ANN, quantization, object-storage economics |
| Lexical | OpenSearch or Vespa native | RRF fusion partner |
| Embeddings | Qwen3-Embedding | Open weights, Matryoshka dims for cheap rescoring |
| Rerank | bge-reranker-v2-m3 self-host, or Cohere Rerank 4 | The accuracy lever |
| Constrained extraction | XGrammar or llguidance | Query understanding, no schema drift |
| Eval | RAGAS for components, custom ID-grounding check | Citation validity is deterministic; do not judge it |
Choosing the engine. Two inputs decide it, and neither is a vendor comparison table.
Input one: the memory budget, computed above. It eliminates anything that assumes the working set is resident, and it settles whether you are buying RAM or buying object storage.
Input two: the selectivity distribution of real filters. Pull the histogram from the query log rather than estimating it. If most traffic sits above 10% selectivity, filtered ANN carries you. If there is a long tail below 1%, you need an engine that can fall back to exact scan over a materialized ID set — which means you need cardinality statistics, which means a relational planner is in the picture.
| Engine | Earns it when | What it costs you |
|---|---|---|
| Vespa | The catalog is the product. Multi-phase ranking, filtering and lexical in one system — this architecture as one config | Operational weight, small talent pool, its own query language |
| Qdrant + OpenSearch + Postgres | You want replaceable parts and explicit control of each stage | Three systems and a sync pipeline between them |
| Turbopuffer or LanceDB | The 2 TB is the thing that hurts, and cheap per-tenant namespaces matter | Newer, higher cold-read latency, less filtering sophistication |
| Elasticsearch or OpenSearch alone | You already run it and the vector half is secondary | Filtered kNN is competent, not best in class |
| Astra DB (IBM) | You are already on Cassandra. Colocating vectors with operational rows removes the sync pipeline, and sync pipelines are where staleness bugs live | Cassandra is a partition-key store with no cost-based planner, so there are no cardinality statistics to route between filtered ANN and exact scan. findAndRerank does bundle lexical, vector, RRF and rerank into one call, but the lexical half is not Lucene-grade and you adopt the whole data model to get it |
| pgvector alone | Below roughly 50 million vectors | Not this problem |
Adopting Cassandra for a catalog search problem, rather than because you already run it, is choosing a data model that fights the query pattern. Note also that DataStax has been IBM since May 2025 and Astra is folding into watsonx.data — a procurement fact rather than a technical one, but it belongs in the decision.
Where answers fail. Saying "hybrid search plus a reranker" without addressing pre- versus post-filtering, without noticing the data is structured, and without ever computing how much memory 500M vectors need.
Hybrid retrieval with RRF is the recall half only. It reorders a fused candidate list; it does not remove weak candidates, and it has no way to express "nothing here is good enough." The two stated symptoms are precision and cost, and both are closed by what comes after fusion — the rerank stage, the abstain threshold, and the token ceiling. Naming a vendor before naming the memory budget and the selectivity distribution reads as not having worked the problem.