PostgreSQL Docs RAG

A retrieval-augmented generation system over the official PostgreSQL documentation: it indexes four chapters — Indexes (11), Full Text Search (12), Routine Database Maintenance (24) and Backup and Restore (25) — and answers questions with a citation to the exact section the answer came from, or declines to answer.

Repository | Webpage

Two bugs in it actually mattered. Neither one raised an error.

The first returned exactly the expected number of chunks and left every one of them empty. The second answered questions it had no business answering — confidently, with a citation attached. Both passed every check I had at the time, and each needed a different kind of test to surface. That is the part of this project worth writing down.

Why another retrieval project #

I have built RAG systems before, and those write-ups are about the model: which open-source LLM answers best, how they compare. This one deliberately isn’t. The model here is the least interesting component — every failure that mattered happened in the layers on either side of it: in the HTML parser, before anything was embedded, and in the acceptance test, after everything was retrieved.

That is also where I think real retrieval systems break. The embedding model is the part you choose. The parsing and the admitting-you-don’t-know are the parts you build.

The first silent failure: 74 chunks, all empty #

PostgreSQL’s documentation is generated by DocBook, which wraps every section like this:

<div class="sect2" id="INDEXES-TYPES-BTREE">
  <div class="titlepage"><div><div>
    <h3 class="title">11.2.1. B-Tree</h3>
  </div></div></div>
  <p>…the actual content…</p>
</div>

My first parser found the heading and walked up to its parent to get the section’s container. That parent is the titlepage wrapper — three nested anonymous divs holding the heading and nothing else.

Nothing complained. The scraper produced a chunk for every section in the table of contents, the count was exactly right, and each chunk was empty or nearly so. Ingest embedded them. Chroma indexed them. Retrieval ranked them and returned the closest one. The system answered questions, badly, and every component reported success.

The fix is to find the sect1/sect2/sect3 container by class or anchor id, then walk its children skipping two things: the titlepage wrapper, and any nested sectN div, because those are chunks of their own.

What made it findable was caching the raw HTML to disk. The bug is invisible in the output — an empty chunk looks like a short section — and only becomes obvious when you put the fetched page next to the parse and see which div the code actually grabbed. That is the concrete argument against off-the-shelf scrapers that hand back clean Markdown: they discard the one artefact you need in order to debug them.

And the reason it leads this page: no embedding model can rescue a chunk with no content in it. This failure happened before a single vector was computed, in the layer most retrieval write-ups cover in one sentence about “scraping the docs”.

The table of contents already knows where the chunks are #

The usual default is to split text every N tokens with some overlap. On this corpus that cuts a code block in half, separates a table from the paragraph explaining it, or glues the end of one topic to the start of an unrelated one.

But this documentation is already divided — by its authors, into the units readers actually ask questions at. “11.2.1 B-Tree” is one idea. “24.1.2 Recovering Disk Space” is one idea. So the table of contents defines the boundaries: one section or subsection, one chunk. 74 chunks across four chapters, 37,708 words.

Everything that makes this corpus worth querying is structure — the operator table in 11.2.1, the CREATE INDEX ... USING HASH syntax, the caution box about transaction ID wraparound — so the parser preserves it: <pre> becomes fenced code, <table> becomes a Markdown table, and note/tip/caution boxes become labelled blockquotes.

The cost of the decision is that sections are not uniform, and I am not going to pretend otherwise:

A 250-fold range, which a fixed-size splitter would not have. I accept it here because all 74 chunks would fit in a single prompt if you retrieved every one of them, so an oversized chunk costs context, not correctness. At a larger scale I would split the 11 long sections into paragraphs while keeping the section as their parent, so a hit on a paragraph could still cite its section.

This generalises to documentation with real section structure — technical manuals, API references, legal documents. It does not generalise to prose without headings, where a fixed-size splitter with overlap remains the better default. Structural chunking is not universally better; it is better when the document has structure worth respecting.

The second silent failure: a threshold that said yes #

A retrieval system that always answers is worse than useless on a bounded corpus, because its confident answer to an out-of-scope question looks exactly like its correct one. So: if the closest chunk is farther than a cosine-distance threshold, the system declines.

I set that threshold to 0.58, and the reasoning looked sound. I checked questions about things these chapters plainly do not cover — client authentication, streaming replication, triggers — saw them land at 0.62 and above, saw real questions land below 0.47, and picked a number in between.

The questions I validated with were too easy. They were about obviously different topics, so they were far away in every sense. The hard case is a question about a real PostgreSQL feature documented in a neighbouring chapter, phrased in the vocabulary of the chapters I did index — close enough to look like a hit.

So I wrote ten of those and measured. Two broke it:

Both sat under 0.58, so both were accepted as answerable. Both would have been answered from sections that cannot answer them — with a citation attached, lending the whole thing credibility.

With those questions in the set, the real picture:

The groups do separate, but the gap is 0.431 to 0.514 — a width of 0.083 — and 0.58 was sitting inside the second group, not between them. The threshold is now 0.47, its midpoint. At that value nothing answerable is rejected and nothing out-of-corpus is accepted.

I want to be precise about what that last sentence is worth, because it sounds like a result and is not: the threshold is calibrated on the same questions it is then scored against. A number fitted to its own test set will always look perfect. The honest claim is narrower — it no longer fails the two specific cases that defeated it, and I now know the shape of the questions that find this class of bug.

What no threshold can fix #

There is a failure here that no choice of number addresses. Ask: “How does the BRIN index store its summary tuples internally?” That retrieves §11.2.6, “BRIN”, at distance 0.350 — far inside the accept region — and the retrieval is not wrong. BRIN is genuinely in the corpus. But the internals the question asks about are in Chapter 64, and §11.2.6 is an overview. The question is finer-grained than the chunk.

A distance threshold measures whether a question resembles the corpus. What you want to know is whether the retrieved text contains the answer. Those two come apart exactly here, and it is not a tuning problem. The real defence lives one layer up: the generation prompt instructs the model to answer only from the provided excerpts and to say plainly when they are insufficient. The threshold is a cheap first filter that catches the easy half.

What it is made of, and what it costs #

Embeddings are all-MiniLM-L6-v2 running locally — 384 dimensions, no API key, a few seconds for the whole corpus on an M1. The vector store is ChromaDB, local and persistent; at 74 chunks the performance question does not arise, and in production I would move to pgvector if PostgreSQL were already in the stack, which costs one fewer system to operate.

Generation is optional, and the system is useful without it: with no API key it returns the most relevant section with full citations, which answers the question and cites the source — it just does not write prose around it. With a key it uses Gemini Flash Lite’s free tier, or Claude Haiku 4.5.

At Haiku 4.5’s published rates — $1.00 per million input tokens, $5.00 per million output — a query sends roughly 1.5k tokens of context and returns about 300, so $0.003 per query: $0.0015 of input plus $0.0015 of output. At 10,000 queries a day, about $30/day. Retrieval itself is free, since the embeddings run on the machine.

What I would fix next #

Re-ranking, first. For two questions the expected section arrives at rank 3, not rank 1 — a question about what a tsvector represents ranks “Parsing Documents” above the section that defines basic text matching. Both count as hits under a top-3 metric and both would fail at top-1. A cross-encoder over the retrieved four is the standard fix, and the metric I chose is currently hiding the problem.

Then an evaluation set I did not write. All 22 questions are mine, written with the corpus open. That makes the evaluation a smoke test — proof the pipeline does what I think it does, end to end — not a benchmark. The adversarial questions earned their place by catching the threshold bug, which is the argument for writing them. But a set written by the author of the retriever cannot measure how the system behaves on questions it was never shaped against, and a perfect score on it should be discounted accordingly.

And change detection. The corpus is a snapshot taken on 7 August 2026. Re-scraping it on 3 October returned one changed section: in §24.1.5, PostgreSQL had reworded an error message and replaced a reference to pg_stat_replication with pg_replication_slots. Today the scraper refetches everything and the index is rebuilt whole. For documentation that changed weekly, I would hash each page and reindex only what moved.

The thread running through both bugs is that a retrieval system fails quietly. It has no equivalent of a crash: an empty chunk and a wrong-but-plausible answer both look like output. What caught them was not better code — it was keeping the raw artefact around in one case, and writing deliberately hostile questions in the other.

The full code, the 22-question evaluation set and the reasoning behind each design decision are in the repository. Everything above is reproducible without an API key:

python3 scraper.py && python3 ingest.py && python3 evaluate.py --no-generate