Retrieval-augmented generation on PostgreSQL

conceptThe concept

The RAG pipeline:

1. Ingest: split documents into chunks of a few hundred tokens with some overlap, keep the source id, position and a content hash.
2. Embed: call the model for each chunk, store the vector with the model name and version.
3. Retrieve: embed the question with the same model, run hybrid search with permission and freshness filters.
4. Assemble: format the best chunks with citations, within the model's context budget.
5. Generate and log: send question plus context to the model, and store which chunks were used so answers can be audited and evaluated.

Filtered approximate search is the classic trap: an HNSW index returns its nearest candidates first and a selective WHERE clause can discard most of them. pgvector 0.8 adds hnsw.iterative_scan (strict_order or relaxed_order) to keep scanning until enough rows qualify.

whyWhy it matters

Models answer confidently whether or not they know. Retrieval from current, permission-checked data with citations turns a generic chatbot into something you can trust and audit, and keeping it in PostgreSQL means the same transactions, backups and access rules as the rest of the product.

Use caseReal-world scenario

When an article is edited, a trigger marks its chunks stale. A worker re-chunks and re-embeds only those, in a transaction that swaps old chunks for new ones, so retrieval never sees a half-updated article.

Try itExamples

Run and edit these in the interactive lesson.

WITH doc AS (
  SELECT 'Refunds are issued to your original payment method within 5 working days after the returned item reaches our warehouse. Your bank may take another 3 to 5 days to show the refund on your card statement. Gift card purchases are refunded as store credit that never expires.' AS body
), words AS (
  SELECT w, n FROM doc, regexp_split_to_table(body, '\s+') WITH ORDINALITY AS t(w, n)
)
SELECT g AS starts_at_word, string_agg(w, ' ' ORDER BY n) AS chunk
FROM generate_series(1, (SELECT max(n) FROM words)::int, 15) g
JOIN words ON n >= g AND n < g + 20
GROUP BY g
ORDER BY g;

WITH hits AS (
  SELECT a.id AS article_id, a.title, c.chunk_no, c.content,
         c.embedding <=> pgm.embed('how long until my refund shows up') AS d
  FROM kb_chunks c
  JOIN kb_articles a ON a.id = c.article_id
  WHERE a.audience = 'customer'
  ORDER BY d
  LIMIT 3
)
SELECT string_agg(format('[%s.%s] %s: %s', article_id, chunk_no, title, content), E'\n' ORDER BY d) AS context
FROM hits;

BEGIN;
ALTER TABLE kb_chunks
  ADD COLUMN embedding_model text NOT NULL DEFAULT 'toy-8d-v1',
  ADD COLUMN content_md5 text;
UPDATE kb_chunks SET content_md5 = md5(content);
UPDATE kb_chunks
SET content = content || ' Sale items are refunded as store credit.'
WHERE article_id = 5 AND chunk_no = 1;
SELECT id, article_id, chunk_no
FROM kb_chunks
WHERE content_md5 IS DISTINCT FROM md5(content) OR embedding_model <> 'toy-8d-v1';
ROLLBACK;

BEGIN;
CREATE TABLE docs AS
SELECT g AS id, g % 10 AS tenant_id,
       ARRAY(SELECT random() FROM generate_series(1, 32) WHERE g > 0)::real[]::vector(32) AS v
FROM generate_series(1, 3000) g;
CREATE INDEX ON docs USING hnsw (v vector_cosine_ops);
ANALYZE docs;
SET LOCAL hnsw.ef_search = 20;
SET LOCAL hnsw.iterative_scan = strict_order;
SELECT count(*) AS rows_returned FROM (
  SELECT id FROM docs
  WHERE tenant_id = 3
  ORDER BY v <=> (SELECT v FROM docs WHERE id = 1)
  LIMIT 10
) t;
ROLLBACK;

internalsUnder the hood

Chunk size is a trade-off: small chunks match precisely but lose context, large ones carry context but dilute the embedding. Overlap prevents a sentence that answers the question from being split across two chunks. Store chunk boundaries so you can show the user exactly what was retrieved.

Avoid / preferDo this, not that

-- embed whole documents as one vector

-- chunk into a few hundred tokens with overlap, one row per chunk,
-- and keep (document_id, chunk_no) for citations

UPDATE kb_chunks SET embedding = $new WHERE id = $1;  -- new model, one row at a time, live

ALTER TABLE kb_chunks ADD COLUMN embedding_v2 vector(1024);
-- backfill embedding_v2, build its index, switch queries, then drop the old column

PitfallsCommon mistakes

• Not storing which model produced each embedding, so a model change silently corrupts search.
• Retrieving without permission filters and relying on the prompt to tell the model not to reveal restricted content.
• Not logging retrieved chunk ids with each answer, so bad answers cannot be traced or evaluated.

LimitsWhen not to use it

Do not use RAG for questions answered by structured data: "how many orders did I place last month" is a SQL query, not a retrieval problem. Text-to-SQL with a read-only role, or a normal API, is more accurate.

SecuritySecurity notes

Retrieved text is untrusted input to the model: a document can contain instructions ("ignore previous instructions and ..."), known as prompt injection. Keep the model's database role read-only and limited to the views it needs, never let model output run SQL against write-capable roles, and filter retrieval by the end user's permissions, not the service's.

ChallengeTry it yourself

WITH hits AS (
  SELECT a.title, c.content,
         c.embedding <=> pgm.embed('package arrived damaged, how do I send it back') AS d,
         array_length(regexp_split_to_array(c.content, '\s+'), 1) AS words
  FROM kb_chunks c
  JOIN kb_articles a ON a.id = c.article_id
  WHERE a.audience = 'customer'
), ranked AS (
  SELECT *, sum(words) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING) AS running_words
  FROM hits
)
SELECT title, words, running_words
FROM ranked
WHERE running_words <= 60
ORDER BY d;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Full text search
• pgvector filtering and iterative scans