← All writing
RAGPostgres

Denormalized pgvector on the retrieval hot path

Keep the source of truth normalized. Give retrieval a flat copy of its own, so the hot path never pays for a join.

The Kuwaiti law agent embeds 77 laws and 7,399 articles, and it builds a nine-level knowledge tree on top of them. All of it lives in Postgres. The question this post answers is where the vectors should live, and the answer I chose is in two places.

Two jobs, two shapes

The data has two jobs that want opposite designs.

The first is being the source of truth. Laws, articles and lines have real relationships, and they are corrected and re-ingested over time. That job wants a normalized schema, where every fact is stored once and an update touches one row.

The second is being searched, many times, under a latency budget. Every answer starts with a retrieval, and a retrieval that has to join several tables to turn a matching vector into something citable is doing extra work on every single question. That job wants a flat shape, where the row you find is already the row you need.

Normalized Postgressource of truth, one fact one place
Denormalized pgvectorflat copy for retrieval, no joins
One write path, two read shapes. The hot path only ever touches the flat copy.

The call

I kept both. The normalized tables stay clean and remain the source of truth. Next to them sits a denormalized pgvector table that carries what retrieval needs in one row: the embedding, the text and the identifying fields that let the answer cite the exact law, article and line.

A retrieval then reads one table. Finding the nearest vectors and knowing what to cite happen in the same row, so there is no join on the path that runs for every question.

-- A simplified shape, not the production schema.
SELECT law, article, line, content
FROM retrieval_chunks
WHERE law = ANY(:candidate_laws)          -- pre-filter
ORDER BY embedding <=> :query_embedding   -- pgvector distance
LIMIT 20;

The pre-filter, the vector search and the fields the citation needs all come from the same place. The BM25 side of the hybrid search and the rank fusion then work on that same flat data.

What it costs

Denormalizing is not free. The same facts now exist twice, so the copy has to be written whenever the source changes. In this system that fits naturally, because ingestion is already a pipeline: laws come in, get chunked by structure, get clustered into the tree, and the flat rows are written at the end. Ingestion is continuous, so the copy is kept current as part of that same flow rather than by a separate job someone has to remember.

The trade I am making is storage and one extra write in exchange for a simpler, faster read. When the read happens on every question and the write happens when a law changes, that is the right direction to pay in.

When I would not do this

If retrieval were rare, or the join were cheap, or the data changed constantly, I would keep one normalized schema and accept the join. The pattern is for a read path that is hot and a write path that is calm.

That is the same rule as my framework choice for this agent: size the tool to the problem. Here the problem is a read that runs on every question, so the read gets its own shape.

Hussam ElnemrAI & Backend Engineer in Cairo, open to remote roles in the EU and US.
See the work
Next postHybrid retrieval with BM25 and RRF