Hybrid Search in Postgres for a Health Assistant: Schema, Chunking, RRF and Evals

Demos of a "health AI" tend to look identical. A public dataset goes into a vector store, a prompt announces that the model is a helpful medical assistant, and the first three questions get lovely answers. Then comes the fourth question.

It contains a negation. Or its right answer hinges on a document revised eighteen months ago that replaced one written in 2011. Or two reputable public sources disagree, and the correct behaviour is to say so and explain why it matters, not to pick a winner. Or the honest answer is "I don't know, ask a clinician," and a model trained to be helpful writes a confident paragraph anyway.

That fourth question is where the product really lives, and almost none of the effort needed to answer it takes place in the language model. It sits in retrieval, which in this case meant Postgres. What follows describes that layer: schema, chunking, the hybrid search query, quantization and an evaluation harness that prevents regressions from shipping. It is independent of any one data source, and the ideas carry over to any corpus where a wrong answer is expensive.

Four ways health text defeats naive RAG

Open health corpora share four traits that quietly wreck the standard pipeline of chunk, embed, cosine similarity, stuff into the prompt.

1. Meaning is not paragraph-shaped. A drug monograph has separate sections for contraindications, interactions and dosage. Split it into 512-token sliding windows and some chunk will hold half a contraindication and half a dosage table. That chunk retrieves beautifully, with high cosine similarity and poor truth value. It is the most dangerous failure in the stack, because the retrieval metrics still look fine.
2. Vocabulary is bimodal. Users describe a burning stomach after meals while the corpus says "epigastric pain, postprandial." Semantic search bridges that gap well. But users also type exact identifiers, such as a drug name, a code or a compound, and there embeddings do worse than a keyword index. A model that places metoprolol and metoprolol succinate at neighbouring points is helping someone make a mistake. Both retrieval modes are needed, fused and not chosen between.
3. Recency is semantics. Elsewhere an old document is a slightly worse document. In health it can be actively harmful, since guidance gets revised precisely because the earlier version was wrong. Recency cannot be a tiebreaker added at the end; it must be a first-class field that both the retrieval layer and the answer layer reason about.
4. Answers have a legal shape. Some questions must be routed, not answered carefully: emergency symptoms, dosing decisions for a specific person, and anything that amounts to a diagnosis. Those need a fixed, rule-driven path that cannot be swayed by how the model happens to feel.

Each of these is a retrieval-layer problem, which leads to a boring decision that ended up carrying the most weight.

Why Postgres and not a vector database

The trade-off deserves plain statement, because the "you don't need a vector DB" genre has gotten sloppy and real cases exist where one is needed.

A dedicated vector database offers better recall per millisecond at very high scale, richer index types, filtered search designed for filtering from the start, and horizontal scaling that needs no thought. With hundreds of millions of vectors under heavy query load, that is a real advantage and should be taken.

Postgres offers one transaction boundary. It sounds minor and is not. In a corpus that changes, a two-system architecture costs real things:

• When a source document is revised, its old chunks must be deleted, new ones inserted, embeddings recomputed and metadata updated, all atomically. With a separate vector store, atomicity does not exist. For a stretch, the keyword index and the vector index disagree about what exists, and the assistant cites a document that no longer says what it is cited for.
• Filtering by licence, jurisdiction, source authority, revision date and language, then ranking by vector distance, is a WHERE clause in Postgres. Across two systems it means either a metadata replica kept in sync or an over-fetch-then-post-filter routine that silently returns fewer results than requested.
• Finding out why a given answer was retrieved six weeks ago is a join.

The scaling ceiling is real but higher than people assume, comfortably into tens of millions of chunks on one well-tuned instance with current pgvector. A sensible rule is to start in Postgres and treat a later migration as a milestone of success and not a design goal. A project that never hits the ceiling avoids an entire distributed-systems problem. One that does hit it arrives with a working product, real query logs and a clear picture of its needs.

Versions matter. pgvector 0.7.0 brought halfvec, sparsevec, bit-type indexing and binary_quantize. Version 0.8.0 added iterative index scans, which ease the filtering problem described below, and the 0.8.x line added Postgres 18 support. On 0.6.x, most of what follows will not work as written.

Schema

Four tables carry the design. The discipline is that provenance is structural, never a JSON blob: anything the answer layer must cite, filter or reason about gets its own column.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Where knowledge comes from, and how much we trust it.
CREATE TABLE source (
  id bigserial PRIMARY KEY,
  slug text UNIQUE NOT NULL,
  license text NOT NULL, -- redistribution rules are a query concern
  authority smallint NOT NULL CHECK (authority BETWEEN 1 AND 5),
  base_url text
);
-- One row per upstream document, at whatever granularity upstream uses.
CREATE TABLE document (
  id bigserial PRIMARY KEY,
  source_id bigint NOT NULL REFERENCES source(id),
  external_id text NOT NULL, -- the stable ID assigned upstream
  title text NOT NULL,
  url text,
  published_at date,
  revised_at date, -- recency is a column, not a vibe
  superseded_by bigint REFERENCES document(id),
  lang text NOT NULL DEFAULT 'en',
  raw_hash bytea NOT NULL, -- skip work on unchanged re-ingests
  meta jsonb NOT NULL DEFAULT '{}',
  UNIQUE (source_id, external_id)
);
-- Retrieval unit. Note heading_path: structure survives chunking.
CREATE TABLE chunk (
  id bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES document(id) ON DELETE CASCADE,
  ord int NOT NULL,
  heading_path text[], -- {Contraindications,"Renal impairment"}
  body text NOT NULL,
  body_tsv tsvector GENERATED ALWAYS AS
    (to_tsvector('english', body)) STORED,
  token_count int NOT NULL,
  content_hash bytea NOT NULL,
  lang text NOT NULL, -- denormalized from document
  is_current boolean NOT NULL, -- denormalized: superseded_by IS NULL
  UNIQUE (document_id, ord)
);
CREATE INDEX chunk_tsv_idx ON chunk USING gin (body_tsv);
CREATE INDEX chunk_trgm_idx ON chunk USING gin (body gin_trgm_ops);
-- Embeddings are versioned by model. This is not optional.
-- lang and is_current are DENORMALIZED from document -- see below.
CREATE TABLE embedding (
  chunk_id bigint NOT NULL REFERENCES chunk(id) ON DELETE CASCADE,
  model text NOT NULL,
  vec halfvec(1536) NOT NULL,
  lang text NOT NULL,
  is_current boolean NOT NULL,
  PRIMARY KEY (chunk_id, model)
);

Four details are load-bearing.

Embeddings are keyed on chunk and model. Embedding models will change. When that happens, both versions need to be live at once so they can be A/B tested on real traffic and swapped without a maintenance window. Put the model in the primary key from day one and build a partial index per model so each stays compact:

CREATE INDEX embedding_hnsw_v3_en
  ON embedding USING hnsw (vec halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 128)
  WHERE model = 'emb-v3' AND lang = 'en' AND is_current;

Language and the current flag are copied onto both chunk and embedding, deliberately. This is the one place normalisation was broken on purpose, and the reason took an embarrassingly long time to see. A partial-index predicate in Postgres can only mention columns of the table being indexed. An index on embedding cannot say WHERE document.is_current; Postgres rejects it outright, subqueries included. To bake hot filters into the vector index, which is wanted for reasons covered in the filtering trap below, those columns must physically sit on the embedding row.

Keeping the copies honest is fiddly, and the first attempt got it wrong twice. The current flag is not set during the document's own ingest. It changes when some later document replaces this one, so the update runs inside the replacing document's transaction and has to reach every chunk and embedding row belonging to the retired document. Put that propagation in one function with one call site, and write a test asserting that denormalised values always equal the parent document's. When the two search arms disagree on which rows are current, fusion mixes positions from two different corpora, and results degrade quietly with no error to point at.

It is not free either. Flipping the flag removes those rows from both partial HNSW indexes, and HNSW reclaims the space only on vacuum, so heavy churn means watching index bloat.

The vector column is halfvec, not vector. Storing 16-bit floats cuts the raw payload from 6,152 bytes to 3,076 at 1,536 dimensions, and the HNSW index shrinks even more because small tuples pack tighter into pages. Retrieval here only has to deliver about 50 candidates to a reranker, and in testing the recall gap stayed within noise. Bonus: halfvec can be indexed up to 4,000 dimensions where plain vector stops at 2,000, which helps with big embedding models.

Supersession is stored as a pointer back into the same table, not a boolean. A bare outdated marker tells the assistant too little. Knowing which document replaced this one lets it explain that guidance changed and link to the current version. That is a join, possible only if the edge was captured at ingest.

Chunking is where the most value sits

If there is one lesson here: the chunker matters more than the embedding model. A better model yields a few points of recall. A chunker that respects structure separates an assistant that is correct from one that is confidently wrong. Three rules apply.

Structure first, size second. Parse each document into a heading tree and keep every chunk inside one top-level section. When a section is smaller than the target size, leave it small instead of gluing it to its neighbour: a 60-token contraindication that says only that is more useful than a 500-token chunk where it drowns. Token limits are the fallback once structure gives out, never the primary rule.

Never split a table. Health tables tend to be dose grids, interaction matrices or eligibility criteria, the very content where half the rows is worse than nothing. If one exceeds the budget, cut it into groups of rows, repeat the header on each piece, and flatten the result into sentences before embedding. A line like "Age 6 to 11 years: 5 mg once daily" embeds far better than a pipe-separated grid.

Put context in front before embedding. The embedded text should not be the raw chunk but the chunk with its lineage restored: a header line naming the source, document title and revision date, then the section path such as Contraindications, then Renal impairment, then the body. Embed that, and keep the raw body for display and citation. A fragment reading "do not use in patients with reduced clearance" says almost nothing as a lone vector, since it names no drug, condition or context. It becomes retrievable only once the header tells the model what it concerns. This single change lifted recall more than any model swap tried, and it costs only tokens at index time.

Two implementation notes follow. Compute the content hash over the contextualised text, so a re-ingest that changes only a heading correctly triggers re-embedding. And run the whole document-to-chunk-to-embedding replacement inside one transaction, so the corpus is never half-updated:

BEGIN;
  DELETE FROM chunk WHERE document_id = $1; -- cascades to embedding
  -- insert new chunks + embeddings
COMMIT;

That transaction is the entire reason this lives in Postgres.

Hybrid retrieval with reciprocal rank fusion

Each query runs a semantic search and a keyword search on their own, then merges them by rank, never by raw score. Merging scores does not work: cosine distance and ts_rank_cd sit on unrelated scales, are not comparable between queries, and any weights fitted to one set of queries will overfit it. Reciprocal rank fusion sidesteps this entirely by looking only at positions.

WITH params AS (
  SELECT $1::halfvec(1536) AS qvec,
         $2::text AS qtext,
         60::int AS rrf_k, -- damping constant
         50::int AS pool
),
-- Two rules in this CTE, both non-obvious, both explained below:
-- 1. qvec comes from a SCALAR subquery, never a joined relation.
-- 2. Filters live INSIDE each arm, not in the outer query.
semantic AS (
  SELECT chunk_id, row_number() OVER (ORDER BY dist) AS rank
  FROM (
    SELECT e.chunk_id,
           e.vec <=> (SELECT qvec FROM params) AS dist
    FROM embedding e
    WHERE e.model = 'emb-v3'
      AND e.lang = 'en'
      AND e.is_current
    ORDER BY e.vec <=> (SELECT qvec FROM params)
    LIMIT (SELECT pool FROM params)
  ) s
),
lexical AS (
  SELECT chunk_id, row_number() OVER (ORDER BY score DESC) AS rank
  FROM (
    SELECT c.id AS chunk_id,
           ts_rank_cd(c.body_tsv,
                      websearch_to_tsquery('english',
                        (SELECT qtext FROM params))) AS score
    FROM chunk c
    WHERE c.body_tsv @@ websearch_to_tsquery('english',
                          (SELECT qtext FROM params))
      AND c.lang = 'en' -- same predicate as the semantic arm,
      AND c.is_current -- from the same denormalized source
    ORDER BY score DESC
    LIMIT (SELECT pool FROM params)
  ) l
),
fused AS (
  SELECT chunk_id,
         SUM(1.0 / ((SELECT rrf_k FROM params) + rank)) AS score
  FROM (
    SELECT chunk_id, rank FROM semantic
    UNION ALL
    SELECT chunk_id, rank FROM lexical
  ) u
  GROUP BY chunk_id
)
SELECT c.id, d.title, d.url, d.revised_at, s.slug, s.authority,
       c.heading_path, c.body, f.score
FROM fused f
JOIN chunk c ON c.id = f.chunk_id
JOIN document d ON d.id = c.document_id
JOIN source s ON s.id = d.source_id
ORDER BY f.score DESC, s.authority DESC, d.revised_at DESC NULLS LAST
LIMIT 20;

A chunk placed third by meaning and seventh by keywords outscores one that only a single arm ranked first. That is intended, since two independent signals agreeing counts for more than one signal shouting. websearch_to_tsquery is the sensible parser because it tolerates real user input, quoted phrases and minus-exclusions included, without failing on malformed text. ts_rank_cd beats plain ts_rank here because cover density favours terms that appear close together, which suits questions that combine several concepts.

Two features of that query look like style choices and are not.

Trap one: the query vector must be a scalar subquery

In the semantic arm, the query vector arrives as a scalar subquery, not as a column from a joined parameters relation. That is the difference between a millisecond and a minute.

The intuitive version selects from the embedding table and a parameters relation together, then orders by distance to the parameter column. It reads fine, is valid, and returns the right rows, which is exactly why it survives code review. The catch is that the planner can use a nearest-neighbour index only when the ORDER BY compares the indexed column against something fixed for the whole scan. A value pulled in through a join does not qualify, so there is no ordered path at all: Postgres reads every row and sorts.

At fifty thousand chunks that costs a few hundred milliseconds, slow enough to feel and ambiguous enough to blame on the network. At five million it is a timeout.

Verify instead of trusting. EXPLAIN the retrieval query in CI and assert that the plan contains an index scan on the HNSW index with an Order By line. A sequential scan followed by a sort means the vector index is decorative. A test that fails the build on this is worthwhile, because a well-meaning refactor can reintroduce the problem without producing a single wrong answer, only slow ones, eventually, in production.

Trap two: filtering after the limit

The filters, namely current, language and not superseded, belong inside each arm's subquery, before the LIMIT. The first version put them in the outer query, the obvious place and the wrong one.

HNSW returns approximate nearest neighbours. Take the top 50 by distance, filter afterwards, and perhaps only 12 survive. The pool of 50 silently became 12, rank fusion now works over a set contaminated by rows destined for the bin, and a final LIMIT 20 quietly returns 9 results. Recall collapses and nothing in the logs says so. Every individual result still looks plausible, so nobody files a bug. The assistant simply gets subtly worse on questions where the corpus is unevenly filtered, which in a health corpus means questions about recently revised material.

Three fixes, in order of preference:

1. Build the filter into a partial index. That is the reason language and the current flag were copied onto the embedding row. Once the index predicate equals the query's WHERE clause, the index already contains only the filtered rows and the problem is gone. It fits low-cardinality, slow-moving dimensions like language, currency or source tier, and does not fit anything with many values, since that would mean maintaining a hundred indexes.
2. Iterative index scans (pgvector 0.8.0 and later) cover filters too dynamic to bake in:

SET hnsw.iterative_scan = relaxed_order;
SET hnsw.max_scan_tuples = 100000; -- default is 20000
SET hnsw.scan_mem_multiplier = 4; -- raise if the above is not enough

With this on, the index keeps going back for more candidates until enough pass the filter. Two limits apply. The filter has to live at the same query level as the index scan or nothing changes, and the scan has a cap, so it may still return fewer rows than asked. It softens the problem without removing it. Choosing relaxed_order over strict_order improves recall at the cost of results that are not exactly sorted by distance, which is fine when a reranker follows.

Speed through binary quantization

At scale, the cheapest big win is two-stage retrieval: draw candidates from a binary index by Hamming distance, then rescore that shortlist exactly.

CREATE INDEX embedding_bit_idx ON embedding
  USING hnsw ((binary_quantize(vec)::bit(1536)) bit_hamming_ops)
  WHERE model = 'emb-v3' AND lang = 'en' AND is_current;
SELECT chunk_id
FROM (
  SELECT chunk_id, vec
  FROM embedding
  WHERE model = 'emb-v3' AND lang = 'en' AND is_current
  ORDER BY binary_quantize(vec)::bit(1536)
           <~> binary_quantize($1::halfvec(1536))
  LIMIT 400
) candidates
ORDER BY vec <=> $1::halfvec(1536)
LIMIT 50;

Stage one should over-fetch generously, around eight times the final pool. The exact rescore in stage two repairs the ordering, so the binary stage only needs candidate presence, not correct ranks.

Precision about the savings matters, because the internet is loose with them. At 1,536 dimensions a bit vector is about 200 bytes against roughly 3 KB for halfvec, a 16x cut in raw data. The 32x figure quoted everywhere is measured against full-precision vector, which this schema does not use.

The index does not shrink 16x, and the reason is instructive. Every HNSW tuple carries fixed overhead of a few hundred bytes, graph links and a tuple header, regardless of payload. Against 3 KB of halfvec that overhead is noise; against a 200-byte bit vector it exceeds the payload. With page-packing waste added, real compression stays well below the raw ratio. On this data the binary index came out about 4 to 5 times smaller than the halfvec one. That still decides whether an index fits in RAM, which is the whole point, but anyone budgeting for 32x will be unpleasantly surprised. Measure with pg_relation_size before planning capacity, and use realistic vectors: one benchmark wasted an afternoon because synthetic vectors were all positive, binary_quantize produced all ones, and Postgres compressed everything to nothing.

The rest is routine and well documented. Give maintenance_work_mem more room before building HNSW indexes, or the build spills to disk and takes hours instead of minutes. Choose hnsw.ef_search per query: 100 to 200 where mistakes are costly, around 40 for autocomplete. Create production indexes CONCURRENTLY. Test on your own data, since published recall figures rarely transfer.

Reranking and the nerve to return nothing

Fusion hands back a decent top 20, not a decent top 5, and the prompt only has room for 5. A cross-encoder reranker, which reads query and candidate as a pair instead of comparing stored vectors, gives the largest single quality gain after hybrid search. It is slow, so it sees 20 candidates, not a million.

What people skip: use the reranker's scores as an absolute threshold, not merely an ordering. If the best candidate falls below a floor, the right output is not the best of a bad set. It is an admission that the corpus holds nothing solid on the topic.

This is where most health assistants break, and the fix is a product choice that happens to be implemented in code. A system built to answer everything will invent things on rare questions. If reranking only sorts, the model always receives something, and an agreeable model always writes something back. A score floor turns a retrieval miss into a plain refusal rather than a fluent fabrication. Set the floor using labelled questions whose answers are known to be missing from the corpus, and keep it when users grumble about too many refusals.

Understanding the query first

Ahead of retrieval sits one layer worth its added delay: a compact, quick model that reformulates the question before the index sees it.

• Expand and normalise. Translate lay language into the corpus's vocabulary without discarding the original. Both enter the hybrid query, the original to the lexical arm and the expansion to the semantic arm.
• Split compound questions. A query asking whether two drugs can be combined during pregnancy is really three lookups: each drug, plus the pregnancy angle. Run them separately, merge the rankings, and rerank the combined set.
• Resolve context. A follow-up such as asking about a higher dose is meaningless alone. Fold in the previous turn and restate it as a standalone question before embedding, since embeddings keep no memory.
• Classify and route. Emergency signs, requests for personal dosing and anything diagnosis-like skip retrieval and take a deterministic response path. The classifier should be trigger-happy. A false positive is a mildly annoying "please talk to a clinician," whereas a false negative is the failure that ends the product.

Grounding with structural citations

The generation step is brief. Most of the effort goes into the output contract, not the prompt wording. Label every retrieved chunk in the prompt with a stable tag and demand structured output where each claim lists the tags it relies on:

{
  "claims": [
    { "text": "…", "sources": [2, 5] },
    { "text": "…", "sources": [1] }
  ],
  "uncertain": true,
  "route_to_clinician": false
}

Check the result in code, not in the prompt. A claim whose sources list is empty is removed before display, and if too many claims go, the entire response is thrown out and the refusal path takes over. This separates requesting grounding from guaranteeing it: a prompt is a hope, a validator is a rule. The drop rate also doubles as a free production metric, a live hallucination indicator that can trigger alerts with nobody reading transcripts.

Two smaller choices mattered. Show the document's revision date beside each citation so users can judge freshness themselves. And when retrieved sources conflict, surface the conflict instead of settling it. "Guidance differs: source A says X, source B says Y" is more useful and more honest than a synthesised average.

Evaluation, or guessing

Shipping changes on gut feeling is not an option here. Failure modes are subtle, the surface is wide, and each setting affects all the others. The harness that held up has layers.

Retrieval only, no LLM. A few hundred queries with hand-labelled relevant chunk IDs, measuring recall@20 and nDCG@10. It runs in seconds, catches most regressions and, critically, separates retrieval quality from generation quality. When answers worsen, it shows at once whether the culprit is the corpus or the prompt.

Faithfulness. For generated answers, check that each claim is entailed by the chunks it cites. Model grading works well enough provided the grader gets a tiny task: one sentence, one passage, and a yes or no on support.

Refusal correctness. A separate set holds questions whose correct handling is refusal or routing, so that threshold changes cannot quietly erode that behaviour.