Why a working demo is not a production retriever
Retrieval-augmented generation, or RAG, is a data product rather than a prompt trick. A request is transformed into one or more searches, the results are filtered by identity and tenant, a model receives a bounded context, and the answer is returned with evidence. Every stage can be correct in isolation while the answer is still wrong. A retriever can find a relevant paragraph from a tenant that the caller must not see. A chunk can contain the right sentence but omit the heading that gives it meaning. A language model can cite a plausible URL that was never retrieved.
The production contract should therefore be explicit. Store a stable document identity, revision, source location, chunk order, access labels, embedding model, and lexical representation with every chunk. Keep the current revision separate from historical revisions. Enforce the tenant boundary in PostgreSQL and repeat the authorization filter in retrieval queries. Treat citations as identifiers selected from retrieved rows, not as text the model is allowed to invent.
This design fits one PostgreSQL database and can grow into separate lexical or reranking services later. The AI agent evaluations guide covers the test harness in more detail, while the AI agent observability guide describes tracing the stages below.
Define the unit of retrieval before choosing an index
Chunking is the first quality decision. Start with the source structure, not a character count. Keep a heading with the paragraphs below it, preserve list boundaries, and avoid splitting a table row from its label. A chunk should answer a small question on its own while retaining enough context to be useful to a reranker. Use token counts from the selected embedding model, because a character limit does not map consistently across languages or code.
Overlap can preserve a sentence that crosses a boundary, but it also duplicates terms, increases embedding work, and can make the generator repeat itself. Use the smallest overlap that fixes observed boundary misses. Store source_start and source_end offsets or a source anchor, along with a normalized text hash. This makes a citation precise and lets an ingestion job skip unchanged chunks. Keep code blocks intact when their syntax matters. For long documents, add the document title and heading path to the text sent to the embedder, but retain the clean body for display.
An embedding is a versioned representation, not a permanent property of the text. Record the model name, dimension, normalization policy, and creation time. Changing any of these can change nearest-neighbor ordering. A column declared as vector(1536) rejects a vector with another dimension, which is useful protection when one model is accidentally mixed with another. If several models must coexist, use separate columns or tables and separate indexes rather than silently padding vectors.
A PostgreSQL data contract with tenant boundaries
The following schema keeps a pointer to the current document revision and stores all chunk metadata needed for search and citations. The explicit english configuration is only an example. Use one text-search configuration per language or a language-aware lexical service when the corpus is multilingual.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE rag_documents (
tenant_id uuid NOT NULL,
document_id uuid NOT NULL,
current_revision bigint NOT NULL,
source_uri text NOT NULL,
deleted_at timestamptz,
PRIMARY KEY (tenant_id, document_id)
);
CREATE TABLE rag_chunks (
chunk_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id uuid NOT NULL,
document_id uuid NOT NULL,
revision bigint NOT NULL,
chunk_no integer NOT NULL,
title text NOT NULL,
content text NOT NULL,
source_start integer NOT NULL,
source_end integer NOT NULL,
acl text[] NOT NULL DEFAULT ARRAY['public']::text[],
embedding vector(1536) NOT NULL,
embedding_model text NOT NULL,
search_tsv tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', content), 'B')
) STORED,
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (tenant_id, document_id, revision, chunk_no)
);
CREATE INDEX rag_chunks_search_tsv_idx
ON rag_chunks USING gin (search_tsv);
CREATE INDEX rag_chunks_acl_idx
ON rag_chunks USING gin (acl);
CREATE INDEX rag_chunks_tenant_idx
ON rag_chunks (tenant_id);
CREATE INDEX rag_chunks_embedding_hnsw_idx
ON rag_chunks USING hnsw (embedding vector_cosine_ops);
When IVFFlat is the selected approximate index, use its list and probe settings instead of HNSW:
CREATE INDEX rag_chunks_embedding_ivfflat_idx
ON rag_chunks USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
Keep one approximate index for the distance and access path used by a route unless a migration or an intentional comparison requires both.
PostgreSQL full-text search represents normalized lexemes as tsvector and a query as tsquery. The generated column keeps indexing out of the request path, and a GIN index is the usual choice for repeated searches. If a corpus needs exact BM25 semantics, use a lexical engine or extension that exposes BM25 and store its result as a ranked candidate set. PostgreSQL's ts_rank_cd is a useful lexical rank, but it is not BM25. Do not label one as the other just because both produce a score.
For an application role, enable row-level security on both tables and use a transaction-local tenant setting derived from authenticated claims. The role that serves requests should not be the table owner, or the tables should be forced to apply policies to their owner as well.
ALTER TABLE rag_documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE rag_documents FORCE ROW LEVEL SECURITY;
ALTER TABLE rag_chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE rag_chunks FORCE ROW LEVEL SECURITY;
CREATE POLICY rag_documents_tenant ON rag_documents
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
CREATE POLICY rag_chunks_tenant ON rag_chunks
USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);
Set the value through a parameterized call at the start of every transaction, then run the search and commit or roll back. A missing setting produces no matching tenant. A malformed authenticated value should be rejected before opening the database transaction. The application still adds tenant_id, document revision, deleted_at, and ACL predicates to the search. RLS is the last boundary, not a reason to trust a caller-provided tenant ID or role list.
HNSW and IVFFlat are different operating choices
pgvector performs exact nearest-neighbor search when no approximate index is used. Exact search is a valuable recall reference and can be practical after a selective tenant or status filter. HNSW builds a multilayer graph. It generally offers a stronger speed and recall tradeoff, but takes more memory and longer to build. It does not need training data, so it can be created before a table is populated. Its m and ef_construction options affect graph size, build work, and recall.
IVFFlat divides vectors into lists and probes a subset of them. It uses less memory and builds faster, but its recall depends on the list count, data distribution, and probe count. Create it after the table contains representative data. The pgvector project suggests starting list selection from row count and tuning ivfflat.probes against a recall set. Those are starting heuristics, not benchmarks for your workload. Measure with your language mix, filter selectivity, and update rate.
Use the distance operator that matches the embedding contract. Cosine distance uses <=>, inner product uses <#>, and L2 distance uses <->. A cosine HNSW index must use vector_cosine_ops, as in the schema. Keep a deterministic secondary key in the order clause. During a migration, build a new index concurrently so normal writes are not blocked, remembering that CREATE INDEX CONCURRENTLY cannot run inside a transaction. Inspect plans with EXPLAIN (ANALYZE, BUFFERS) on representative data, and compare approximate results with exact results to track recall.
Filtered approximate search needs special care. pgvector applies a normal filter after scanning the approximate index, so a small candidate list can contain too few rows for one tenant or ACL. Increase the candidate budget, enable iterative scans where the installed pgvector version supports them, or use a selective relational index. For a small number of filter values, a partial vector index can help. For many tenants, list or hash partitioning can isolate search populations, but thousands of partitions can increase planning and memory costs. The pgvector documentation specifically warns that vectors from one tenant can affect another tenant's recall when an approximate index is shared.
Combine lexical intent with vector similarity
Vector search handles paraphrases and related concepts. Lexical search protects exact identifiers, error codes, product names, quoted phrases, and newly introduced terms that an embedding may represent poorly. A robust pipeline runs both searches over the same authorized current revision set, takes more candidates than it will display, and fuses ranks rather than raw scores.
The query below uses PostgreSQL full-text rank as the lexical leg. In a system with a BM25 service, replace text_hits with its top candidates and retain the same chunk_id, text_rank, tenant, revision, and ACL contract. Reciprocal Rank Fusion avoids assuming that a cosine distance and a BM25 score share a scale.
WITH q AS (
SELECT $1::vector AS embedding,
websearch_to_tsquery('english', $2::text) AS tsq,
$3::uuid AS tenant_id,
$4::text[] AS roles
),
vector_hits AS (
SELECT c.chunk_id,
row_number() OVER (
ORDER BY c.embedding <=> q.embedding, c.chunk_id
) AS vector_rank
FROM rag_chunks c
JOIN rag_documents d
ON d.tenant_id = c.tenant_id
AND d.document_id = c.document_id
AND d.current_revision = c.revision
CROSS JOIN q
WHERE c.tenant_id = q.tenant_id
AND d.deleted_at IS NULL
AND c.acl && q.roles
ORDER BY c.embedding <=> q.embedding, c.chunk_id
LIMIT 50
),
text_hits AS (
SELECT c.chunk_id,
row_number() OVER (
ORDER BY ts_rank_cd(c.search_tsv, q.tsq) DESC, c.chunk_id
) AS text_rank
FROM rag_chunks c
JOIN rag_documents d
ON d.tenant_id = c.tenant_id
AND d.document_id = c.document_id
AND d.current_revision = c.revision
CROSS JOIN q
WHERE c.tenant_id = q.tenant_id
AND d.deleted_at IS NULL
AND c.acl && q.roles
AND c.search_tsv @@ q.tsq
ORDER BY ts_rank_cd(c.search_tsv, q.tsq) DESC, c.chunk_id
LIMIT 50
),
ranked AS (
SELECT chunk_id, vector_rank, NULL::bigint AS text_rank
FROM vector_hits
UNION ALL
SELECT chunk_id, NULL::bigint AS vector_rank, text_rank
FROM text_hits
),
candidate_scores AS (
SELECT chunk_id,
min(vector_rank) AS vector_rank,
min(text_rank) AS text_rank
FROM ranked
GROUP BY chunk_id
)
SELECT c.chunk_id,
c.document_id,
c.chunk_no,
c.title,
c.content,
d.source_uri,
s.vector_rank,
s.text_rank,
coalesce(1.0 / (60.0 + s.vector_rank), 0.0) +
coalesce(1.0 / (60.0 + s.text_rank), 0.0) AS rrf_score
FROM candidate_scores s
JOIN rag_chunks c ON c.chunk_id = s.chunk_id
JOIN rag_documents d
ON d.tenant_id = c.tenant_id
AND d.document_id = c.document_id
AND d.current_revision = c.revision
WHERE d.deleted_at IS NULL
ORDER BY rrf_score DESC, c.chunk_id
LIMIT 8;
The role array is constructed by the authorization layer. acl && roles means that at least one label overlaps. Use a different operator or an is_public column if the policy requires all labels, ownership, or time windows. Bind the query text and embedding as parameters. websearch_to_tsquery is forgiving for user syntax, while to_tsquery expects valid operators and should not receive unchecked text.
Rerank only authorized candidates
A cross-encoder or another reranker can read the query and each candidate together, which often resolves close semantic matches better than a single embedding. It also costs more compute and adds a serial stage. Fetch a bounded candidate set, apply tenant, revision, deletion, and ACL filters before the reranker, and pass only fields needed for ranking. Never send unauthorized candidates to an external model, even if the final answer will omit them.
Keep the original vector and lexical ranks for diagnostics. Log the candidate IDs, index mode, probe or search settings, reranker model version, and final selected IDs with sensitive text redacted or protected. The prompt injection and MCP security guide covers why retrieved text is data rather than instructions. A document can contain a prompt injection, so the generation prompt should state that retrieved passages are untrusted evidence and cannot change tools, permissions, or system rules.
Citations should be data, not decoration
Every displayed citation should resolve to a retrieved document_id, revision, chunk_no, source_uri, and source offset or heading. Ask the generator to return citation IDs beside claims, then validate those IDs against the exact rows used for generation. Render the source title and URL from the database. If the model cites an unknown ID, remove it or mark the claim unsupported. Do not let it manufacture a URL from a document title.
Pass enough neighboring context to explain a result, but keep the chunk ID for every passage. If adjacent chunks are merged for the model, retain their individual provenance. When a source changes, a citation to an old revision must stop resolving in the current answer. For high-risk domains, show the revision date, access scope, and an explicit no-answer state when evidence is missing.
Measure retrieval and answer quality separately
Build a versioned evaluation set from real questions, expected source documents, acceptable no-answer cases, tenant identities, ACL labels, and language. Include exact lookups, paraphrases, multi-hop questions, stale-document questions, and adversarial requests. Keep a private test set so tuning does not overfit the examples used during development.
Measure retrieval recall at several cutoffs, reciprocal rank or nDCG for ordering, and the fraction of results that satisfy the authorization contract. For generation, measure grounded claim precision, citation precision, citation coverage, answer completeness, refusal quality, and no-answer calibration. Human review remains useful for ambiguous questions. Compare exact search with HNSW or IVFFlat on the same snapshot to quantify recall loss instead of guessing from latency.
Run negative security tests where a user asks for another tenant's known secret, where an ACL changes between turns, and where a deleted document is queried immediately. A passing answer-quality score does not compensate for one leaked chunk. Record the embedding model, chunking version, index settings, reranker version, prompt version, and corpus revision with every evaluation result so a regression has a reproducible cause.
Make updates and deletes observable and safe
Use content hashes to make ingestion idempotent. Parse and chunk a document, create embeddings in batches, and write the new revision before moving the document pointer. The pointer switch can be one short transaction, so readers see either the old complete revision or the new complete revision. Store the embedding model with each row and schedule a backfill when the model changes. Do not mix vectors with different dimensions in one column.
BEGIN;
SELECT set_config('app.tenant_id', $1::text, true);
INSERT INTO rag_chunks (
tenant_id, document_id, revision, chunk_no, title, content,
source_start, source_end, acl, embedding, embedding_model
)
VALUES ($1::uuid, $2::uuid, $3::bigint, $4::integer, $5::text, $6::text,
$7::integer, $8::integer, $9::text[], $10::vector, $11::text)
ON CONFLICT (tenant_id, document_id, revision, chunk_no) DO UPDATE
SET title = EXCLUDED.title,
content = EXCLUDED.content,
source_start = EXCLUDED.source_start,
source_end = EXCLUDED.source_end,
acl = EXCLUDED.acl,
embedding = EXCLUDED.embedding,
embedding_model = EXCLUDED.embedding_model,
updated_at = now();
INSERT INTO rag_documents (
tenant_id, document_id, current_revision, source_uri, deleted_at
)
VALUES ($1::uuid, $2::uuid, $3::bigint, $12::text, NULL)
ON CONFLICT (tenant_id, document_id) DO UPDATE
SET current_revision = EXCLUDED.current_revision,
source_uri = EXCLUDED.source_uri,
deleted_at = NULL;
COMMIT;
The example is one chunk insert. A real loader inserts every chunk for the revision before the pointer update, and it verifies that all expected chunks were written. A delete should first make the document unavailable to retrieval by setting deleted_at in the same authorization boundary. Hard-delete chunks after the retention period required by the product and compliance policy. Clean old revisions in batches, monitor dead tuples, and run VACUUM (ANALYZE) as appropriate. HNSW indexes can make vacuum work expensive after heavy churn. Reindexing an affected index concurrently before vacuuming can reduce that cost, but measure it on the actual table.
Budget latency and cost by stage
Track p50, p95, and p99 latency separately for query normalization, embedding, lexical search, vector search, fusion, reranking, generation, and citation validation. Also record candidate counts, filtered-out counts, token counts, retries, and cache hits. A fast vector query can be hidden by an embedding provider timeout. A cheap retriever can become expensive when it sends too many passages to a reranker or generator.
Batch document embeddings and retry them with bounded backoff. Cache only stable, non-sensitive artifacts, and include model version, normalization, and tenant scope in cache keys. Query-embedding caching needs a privacy review because a repeated query can reveal interest across users. Select HNSW or IVFFlat using measured recall, memory, build time, write behavior, and tail latency rather than a generic benchmark. Keep candidate limits and reranker budgets configurable per route or tenant.
A production dashboard should expose empty-result rate, answer-without-citation rate, citation validation failures, ACL denials, stale-revision hits, index build state, vacuum health, and approximate-versus-exact recall samples. These signals connect observability for AI agents with the quality suite in agent evaluations. Alert on changes in these rates, not only on database CPU.
A practical rollout sequence
Start with exact vector search and PostgreSQL full-text search on a small, representative corpus. Add the revision pointer and RLS before inviting real tenants. Freeze a labeled evaluation set, then compare HNSW and IVFFlat against exact results. Add RRF, reranking, and citations one stage at a time so each change has a measurable effect. Exercise updates, deletes, ACL changes, connection-pool reuse, and failed index builds before a broad rollout.
The official pgvector documentation describes vector types, distance operators, HNSW, IVFFlat, filtered search, iterative scans, partitioning, and maintenance. PostgreSQL's documentation covers full-text search, GIN and GiST text indexes, row security policies, index creation, partitioning, and MVCC. The SQL and tradeoffs above follow those documented interfaces. They do not claim a universal latency, recall, or cost result.