Retrieval that holds up: hybrid search, chunking and citations in Postgres

Most retrieval-augmented generation systems work in the demo. The demo questions were written by someone who had just read the documents, so they share the documents' vocabulary, and the corpus was twelve PDFs. Production is different. Users ask about error code E1042, a product called "Kalix 2", the policy that changed last Tuesday. The corpus is forty thousand chunks across three hundred customers. And when the answer is wrong, nobody can tell whether the model invented it or retrieval never found the right passage.

This article builds a retrieval layer that holds up under those conditions. It runs in Postgres, combines vector and full-text search in a single query, is scoped to one tenant by construction, and produces citations that can be checked mechanically. As in the rest of the series, the code is type-checked and tested in CI, and in this article that includes the SQL: the tests run it against a real Postgres with pgvector, compiled to WebAssembly, on every pull request.

Where RAG systems actually fail

When a RAG answer is wrong, the cause is almost always in one of four places, and they need different fixes.

The order matters. A prompt cannot fix a passage that is not in the context, and a reranker cannot fix a chunk that cut a table in half. Work from the top, and measure each layer on its own, which is what the last section of this article is about.

Chunking by structure

The most common chunking strategy is a fixed window of N tokens with some overlap. It is easy to implement and it is the source of a surprising share of bad answers, because it cuts wherever the counter runs out: in the middle of a table, between a heading and its first paragraph, between a question and its answer.

Documents written by people have structure, and the structure carries meaning. A sentence under "Damaged goods" means something different from the same sentence under "Returns from business customers". Our chunker uses that.

/**
 * Structure-aware chunking for Markdown: split on headings first, then on
 * paragraphs if a section is too long. Never split inside a paragraph, a
 * list or a table, because a half table retrieves well and answers badly.
 */
export function chunkMarkdown(md: string, o: ChunkOptions): Chunk[] {
  const chunks: Chunk[] = [];
  const path: string[] = [];
  let paras: string[] = [];

  const flush = () => {
    const heading = path.filter(Boolean).join(' > ');
    let current: string[] = [];
    for (const p of paras) {
      const candidate = [...current, p].join('\n\n');
      if (current.length && approxTokens(candidate) > o.maxTokens) {
        chunks.push({ ord: chunks.length, heading, body: current.join('\n\n') });
        current = current.slice(-o.overlapParagraphs); // carry context across the split
      }
      current.push(p);
    }
    if (current.length) chunks.push({ ord: chunks.length, heading, body: current.join('\n\n') });
    paras = [];
  };

  for (const block of md.replace(/\r\n/g, '\n').split(/\n{2,}/)) {
    const h = block.match(/^(#{1,6})\s+(.+)$/);
    if (h && !block.includes('\n')) {
      flush();
      const level = h[1]!.length;
      path.length = level - 1;
      path[level - 1] = h[2]!.trim();
      continue;
    }
    if (block.trim()) paras.push(block.trim());
  }
  flush();
  return chunks;
}

/** What actually gets embedded: the heading path gives a short chunk its context. */
export const embeddingText = (c: Chunk) => (c.heading ? `${c.heading}\n\n${c.body}` : c.body);

The rules are simple:

1. Split on headings first. Each section becomes its own chunk if it fits.
2. Split long sections on paragraph boundaries, never inside a paragraph, list or table. A Markdown table is one block, so it stays whole.
3. Repeat the last paragraph at the start of the next chunk when a section is split, so a chunk that starts mid-section still has some context.
4. Keep the heading path as metadata, and prepend it to the text that gets embedded. A chunk that says only "Send a photo and we ship a replacement" embeds much better as "Returns > Damaged goods: send a photo and we ship a replacement".

The size limit is approximate, characters divided by four. Exact token counts depend on the embedding model's tokenizer, and the difference rarely matters for chunking. What matters is that chunks are small enough that several fit in the context, and large enough to be understood on their own.

PDFs, HTML and office documents need to be converted to something with structure before this step. That conversion is where many pipelines lose their tables and headings, and it is worth more attention than it usually gets: check a sample of converted documents by eye before you tune anything downstream.

One table, two indexes

We keep chunks, their embeddings and their full-text representation in a single Postgres table.

create extension if not exists vector;

create table documents (
  id          bigint generated always as identity primary key,
  tenant_id   uuid not null,
  source_uri  text not null,
  -- Hash of the source content: re-ingest only what changed.
  content_sha text not null,
  updated_at  timestamptz not null default now(),
  unique (tenant_id, source_uri)
);

create table chunks (
  id          bigint generated always as identity primary key,
  tenant_id   uuid not null,
  document_id bigint not null references documents(id) on delete cascade,
  ord         int not null,
  heading     text not null default '',
  body        text not null,
  -- Dimension must match the embedding model. Changing model = new column + backfill.
  embedding   vector(64) not null,
  -- Lexical index over heading + body, maintained by Postgres.
  tsv         tsvector generated always as (
                setweight(to_tsvector('simple', heading), 'A') ||
                setweight(to_tsvector('simple', body), 'B')) stored,
  unique (document_id, ord)
);

create index chunks_tenant_idx    on chunks (tenant_id);
create index chunks_tsv_idx       on chunks using gin (tsv);
create index chunks_embedding_idx on chunks using hnsw (embedding vector_cosine_ops);

A few design choices here are deliberate.

tenant_id is on every chunk, not just on the document. Every retrieval query filters on it directly, without a join, and it is the column row-level security will use. The next-but-one article in the series covers that layer.

The full-text column is generated by Postgres. It cannot drift from the body, and nobody has to remember to update it. The heading gets a higher weight than the body.

The embedding dimension is fixed in the schema. Changing the embedding model is a migration, not a configuration change: add a new column, backfill it, switch queries, drop the old one. Mixing vectors from two models in one column produces nonsense similarity scores without any error.

The HNSW index uses cosine distance, matching the operator in the query. An index built for one distance operator is not used by a query with another, and pgvector then falls back to a sequential scan without complaint. Check the query plan.

content_sha on the document makes re-ingestion cheap: if a source has not changed, nothing is re-chunked or re-embedded. Embedding calls are the dominant ingestion cost, and most sources do not change most days.

Hybrid retrieval in one query

Vector search and full-text search fail in opposite ways. Embeddings are good at paraphrase: "can I send it back" finds the returns policy. They are weak at exact identifiers: an error code like E1042 or a product name like "Kalix 2" may be split into tokens whose embedding says little. Full-text search finds E1042 immediately and misses "send it back".

Combining them is more robust than tuning either alone. The question is how, because their scores are not comparable: one is a cosine distance, the other a text-search rank with no fixed scale. Reciprocal Rank Fusion avoids the problem by ignoring scores entirely. Each retriever produces a ranked list; each document's fused score is the sum of 1 / (k + rank) over the lists it appears in.

/**
 * Hybrid retrieval in one round trip: the top candidates from vector search
 * and from full-text search are fused with Reciprocal Rank Fusion (RRF).
 * RRF uses ranks, not scores, so cosine distance and ts_rank never have to
 * be put on the same scale. k = 60 is the constant from the original paper.
 *
 * The tenant filter is part of both candidate queries. Retrieval is where
 * cross-tenant leaks happen in RAG systems, so it is never optional here.
 */
export const HYBRID_SQL = `
with semantic as (
  select id, row_number() over (order by embedding <=> $2::vector) as rank
  from chunks
  where tenant_id = $1
  order by embedding <=> $2::vector
  limit $4
),
lexical as (
  select id, row_number() over (order by ts_rank_cd(tsv, q) desc) as rank
  from chunks, websearch_to_tsquery('simple', $3) as q
  where tenant_id = $1 and tsv @@ q
  order by ts_rank_cd(tsv, q) desc
  limit $4
),
fused as (
  select coalesce(s.id, l.id) as id,
         coalesce(1.0 / ($5 + s.rank), 0) + coalesce(1.0 / ($5 + l.rank), 0) as score
  from semantic s
  full outer join lexical l on s.id = l.id
)
select c.id, c.document_id as "documentId", c.heading, c.body, f.score::float8 as score
from fused f
join chunks c on c.id = f.id
order by f.score desc, c.id
limit $6`;

export interface SearchOptions {
  /** Candidates taken from each retriever before fusion. */
  candidates: number;
  /** Results returned after fusion. */
  limit: number;
  /** RRF constant. */
  k?: number;
}

export async function hybridSearch(db: Db, tenantId: string, query: string, embedding: number[], o: SearchOptions): Promise<Hit[]> {
  const vec = `[${embedding.join(',')}]`;
  const { rows } = await db.query<Hit>(HYBRID_SQL, [tenantId, vec, query, o.candidates, o.k ?? 60, o.limit]);
  return rows;
}

The query does all of it in one round trip:

1. Semantic candidates: the top N chunks by cosine distance, for this tenant.
2. Lexical candidates: the top N chunks by full-text rank, for this tenant, using websearch_to_tsquery so user input with quotes and minus signs behaves as people expect and malformed input does not raise a syntax error.
3. Fusion: a full outer join, so a chunk found by only one retriever still counts, and the RRF sum.
4. Result: the chunks with the highest fused score.

With k = 60, the constant from the paper that introduced the method, a chunk ranked first by both retrievers scores 2/61, and one ranked first by only one of them scores 1/61. The tests assert exactly that, along with the two behaviours that matter most: a query for "E1042" puts the error code section first through the lexical side, and a query from one tenant never returns another tenant's chunk.

Why fusion in SQL, not in application code

It would be easy to run two queries and fuse in TypeScript. Doing it in SQL keeps the tenant filter, the candidate limits and the fusion in one statement that can be read, explained with EXPLAIN ANALYZE and tested as a unit. It also halves the round trips, which matters when retrieval sits on the critical path of every answer.

Reranking

A cross-encoder reranker, a model that scores each query–chunk pair jointly, usually improves precision at the top of the list. It is also an extra model call per query and an extra dependency. Add it after you have measured retrieval and seen that the right chunk is in the top 20 but not in the top 5. If the right chunk is not in the top 20 at all, a reranker cannot help; the problem is earlier.

Packing the context

Retrieval returns more candidates than fit in the prompt. The context builder takes them best first until a token budget is spent, numbers them, and fences each one.

export interface Source {
  n: number;
  chunkId: number;
  documentId: number;
  heading: string;
  body: string;
}

/**
 * Packs retrieved chunks into the prompt, best first, until the token budget
 * is spent. Each chunk gets a number the model must cite, and the text is
 * fenced so the model can tell retrieved data from instructions.
 */
export function buildContext(hits: Hit[], budgetTokens: number): { text: string; sources: Source[] } {
  const sources: Source[] = [];
  let used = 0;
  for (const h of hits) {
    const cost = Math.ceil((h.heading.length + h.body.length) / 4) + 12;
    if (used + cost > budgetTokens) break;
    used += cost;
    sources.push({ n: sources.length + 1, chunkId: h.id, documentId: h.documentId, heading: h.heading, body: h.body });
  }
  const text = sources
    .map((s) => `<source n="${s.n}" title="${s.heading.replace(/"/g, "'")}">\n${s.body}\n</source>`)
    .join('\n');
  return { text, sources };
}

Best first, within a budget. Long contexts are not free: they cost tokens, add latency and, as research on long-context models has shown, information in the middle of a long context is used less reliably than information at the start or end. A budget forces the retrieval layer to be selective.

Numbered sources. The prompt asks the model to cite sources as [n] for every claim, and to say it does not know when the sources do not contain the answer. The numbers make citations short for the model and checkable for us.

Fenced as data. Retrieved text is wrapped in explicit <source> tags. Documents can contain text that looks like instructions, "ignore previous instructions" being the classic, and the tags help the model treat retrieved content as material to read rather than orders to follow. They are not a security boundary on their own; the security article in this series covers what is.

Citations you can check

A citation is only useful if it is true. The check is mechanical and cheap, so run it on every answer.

/**
 * Checks an answer against the sources it was given. An answer that cites a
 * source that was never provided is a hallucinated citation and fails; an
 * answer with no citations at all fails unless it says it cannot answer.
 */
export function checkCitations(answer: string, sources: Source[], refusal = /i (?:do not|don't) know|cannot find/i) {
  const cited = [...answer.matchAll(/\[(\d+)\]/g)].map((m) => Number(m[1]));
  const valid = new Set(sources.map((s) => s.n));
  const invalid = [...new Set(cited.filter((n) => !valid.has(n)))];
  const ok = invalid.length === 0 && (cited.length > 0 || refusal.test(answer));
  return { ok, cited: [...new Set(cited)], invalid };
}

An answer fails the check if it cites a source number that was not in the context, which is a hallucinated citation, or if it makes claims with no citation at all, unless it is an explicit "I don't know". What the product does with a failed check is a product decision: retry once with a stricter instruction, show the answer with a warning, or fall back to showing the top sources without a generated answer. What it should not do is show an answer with fabricated citations as if they were real.

The same check works as an eval scorer. The deterministic scorers from the evals article include exactly this kind of hard constraint, and citation validity is one of the cheapest regressions to catch.

Tenant isolation lives in retrieval

In a multi-tenant RAG system, retrieval is where cross-tenant leaks happen. The model has no idea which tenant it is serving; it answers from whatever is in the context. If one chunk from another tenant slips into the candidates, it can end up quoted verbatim in an answer.

Three rules keep this from happening:

1. The tenant id comes from the authenticated session or the job, exactly as in the gateway. It is never a request parameter.
2. The tenant filter is in the query, in every candidate set, not applied to results afterwards. Filtering after retrieval also returns fewer than N results when other tenants' chunks crowd the top, which degrades quality silently.
3. Row-level security enforces it again in the database, so a query that forgets the filter returns nothing rather than everything. That is the subject of the multi-tenancy article in this series.

The tests cover the first two by ingesting the same document path for two tenants with different content and asserting that each tenant only ever sees its own.

Measuring retrieval on its own

Retrieval quality should be measured separately from answer quality, because they fail separately. The method is the same as for any eval: a labelled set and a metric.

/** Share of relevant ids that appear in the top k results. */
export function recallAtK(retrieved: number[], relevant: Set<number>, k: number): number {
  if (relevant.size === 0) return 1;
  const top = retrieved.slice(0, k);
  return top.filter((id) => relevant.has(id)).length / relevant.size;
}

/** 1 / rank of the first relevant result, 0 if none. Rewards getting it first. */
export function reciprocalRank(retrieved: number[], relevant: Set<number>): number {
  const i = retrieved.findIndex((id) => relevant.has(id));
  return i === -1 ? 0 : 1 / (i + 1);
}

/** Mean over a set of labelled queries. */
export function evaluateRetrieval(
  runs: Array<{ retrieved: number[]; relevant: Set<number> }>,
  k: number,
): { recall: number; mrr: number } {
  const mean = (xs: number[]) => xs.reduce((a, b) => a + b, 0) / Math.max(1, xs.length);
  return {
    recall: mean(runs.map((r) => recallAtK(r.retrieved, r.relevant, k))),
    mrr: mean(runs.map((r) => reciprocalRank(r.retrieved, r.relevant))),
  };
}

The labelled set is a list of questions, each with the ids of the chunks that answer it. Start with fifty questions from real users, labelled by someone who knows the documents. Chunk ids change when you change the chunker, so label by document and heading path and map to chunk ids at evaluation time.

Recall@k is the share of relevant chunks that appear in the top k. It answers "is the answer in the context?" with k set to however many chunks fit in your budget.

Mean reciprocal rank (MRR) rewards getting the first relevant chunk near the top. It matters when the context is small or when you show sources to users.

Run these metrics on every change to chunking, embedding model, query handling, candidate count or fusion constant, and gate on them the same way the evals article gates on answer quality. A change that improves answers on your eval suite while recall@k drops is usually a prompt compensating for worse retrieval, and it will not last.

Ingestion is a pipeline, not a script

The retrieval layer is only as current as its ingestion. A few properties separate an ingestion pipeline from a script someone runs:

• Idempotent by content hash. Re-running ingestion for an unchanged source does nothing. Re-running it for a changed source replaces that document's chunks in one transaction, so a query never sees half old and half new chunks.
• Deletes propagate. When a source is removed, its chunks are removed. A RAG system that keeps answering from a retracted policy is worse than one that says "I don't know".
• Tenant on the job. Ingestion jobs carry the tenant id explicitly and write it on every row. The source system's own identifiers are mapped to a tenant through a verified mapping, never inferred from document content.
• Embedding calls go through the gateway, with their own tenant budget, so a large initial import cannot starve interactive traffic.

A checklist for your retrieval layer

The whole series is on the Luniat Engineering page.

References

1. Gordon V. Cormack, Charles L. A. Clarke and Stefan Büttcher, "Reciprocal rank fusion outperforms Condorcet and individual rank learning methods", SIGIR, 2009.
2. Patrick Lewis et al., "Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks", NeurIPS, 2020.
3. Nelson F. Liu et al., "Lost in the Middle: How Language Models Use Long Contexts", Transactions of the ACL, 2024.
4. Yu. A. Malkov and D. A. Yashunin, "Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs", IEEE TPAMI 42(4), 2020.
5. Stephen Robertson and Hugo Zaragoza, "The Probabilistic Relevance Framework: BM25 and Beyond", Foundations and Trends in Information Retrieval, 2009.
6. pgvector, open-source vector similarity search for Postgres.
7. PostgreSQL documentation, Full Text Search.