RAG From Scratch With Postgres and pgvector

Retrieval-augmented generation has picked up a reputation for being complicated, mostly because people meet it through a product rather than through the four steps it actually is. Building RAG with pgvector needs no new infrastructure if you already run Postgres — one extension, one table, one similarity query. This post builds it from scratch and is honest about the part that decides whether it works: chunking.

The whole system in four steps

• Chunk your documents into passages small enough to be specific and large enough to be meaningful.
• Embed each chunk into a vector and store it.
• Retrieve the nearest chunks to the embedded question.
• Generate an answer from those chunks, with a rule about what to do when they are not enough.

That is it. Everything else is tuning.

The schema

Two tables. Documents for provenance, chunks for retrieval. Keep them separate so re-chunking a document is a delete and re-insert rather than a migration.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id         bigserial PRIMARY KEY,
    source     text NOT NULL,
    title      text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
    id          bigserial PRIMARY KEY,
    document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    ordinal     int  NOT NULL,
    heading     text,
    body        text NOT NULL,
    embedding   vector(384) NOT NULL
);

CREATE INDEX chunks_embedding_idx
    ON chunks USING hnsw (embedding vector_cosine_ops);

CREATE INDEX chunks_doc_idx ON chunks (document_id, ordinal);

The vector(384) width must match your embedding model exactly. The HNSW index is approximate nearest neighbour — fast, and worth adding before you have a performance problem, because building it later on a large table takes a while. ordinal earns its place later: it lets you widen a hit to include its neighbouring chunks.

Chunking is where RAG fails

Almost every disappointing RAG system I have looked at was let down here, not by the model. The default advice — split every 512 tokens — cuts through the middle of sentences, separates a table from the heading that explains it, and strands a pronoun in one chunk while its subject sits in another. The retrieved passage then looks plausible and answers nothing.

What to do instead

• Split on structure first — headings, then paragraphs — and only fall back to a character budget inside an oversized section.
• Prepend the heading path to every chunk body. A chunk should make sense read entirely on its own, because that is how the model will read it.
• Overlap by roughly one paragraph, so a fact spanning a boundary survives in at least one chunk.
• Aim for 200-400 words. Too small and there is no context; too large and the embedding averages several topics into a vector that matches none of them well.

def pack(paragraphs, heading, max_chars=1200, overlap=1):
    """Pack paragraphs into chunks, carrying `overlap` paragraphs across."""
    out, buf = [], []
    for para in paragraphs:
        if buf and sum(len(p) for p in buf) + len(para) > max_chars:
            out.append(f"{heading}\n\n" + "\n\n".join(buf))
            buf = buf[-overlap:]
        buf.append(para)
    if buf:
        out.append(f"{heading}\n\n" + "\n\n".join(buf))
    return out

Then read fifty of your own chunks before you index a million. If you cannot answer a question from a chunk on its own, neither can the model.

Embedding and inserting

I use a local sentence-transformers model here. It is fast enough on CPU, costs nothing per document, and keeps the corpus on your own hardware — the same argument as running local AI models on a Raspberry Pi 5. Normalise the vectors so cosine distance behaves.

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

encoder = SentenceTransformer("all-MiniLM-L6-v2")   # 384 dimensions

def index(conn, document_id, chunks):
    vectors = encoder.encode(
        [c["body"] for c in chunks], normalize_embeddings=True
    ).tolist()
    rows = [
        (document_id, i, c["heading"], c["body"], v)
        for i, (c, v) in enumerate(zip(chunks, vectors))
    ]
    with conn.cursor() as cur:
        cur.execute("DELETE FROM chunks WHERE document_id = %s", (document_id,))
        cur.executemany(
            "INSERT INTO chunks (document_id, ordinal, heading, body, embedding)"
            " VALUES (%s, %s, %s, %s, %s)",
            rows,
        )
    conn.commit()

conn = psycopg.connect("dbname=kb")
register_vector(conn)

Whichever model you pick, the corpus and the queries must be embedded by the same model. Changing it means re-embedding everything — record the model name against each document so you know when you have drifted.

The similarity query

In pgvector, <=> is cosine distance. Order by the distance ascending — that ordering is what lets the HNSW index be used at all. Wrap it in a subquery that sorts by a computed similarity descending and you will silently get a sequential scan.

SET LOCAL hnsw.ef_search = 100;

SELECT c.id,
       d.title,
       c.heading,
       c.body,
       1 - (c.embedding <=> %(q)s) AS similarity
FROM   chunks c
JOIN   documents d ON d.id = c.document_id
ORDER  BY c.embedding <=> %(q)s
LIMIT  8;

hnsw.ef_search trades latency for recall; raise it if good chunks are being missed. Run EXPLAIN ANALYZE once and confirm you see an index scan — this is the single most common silent performance bug in a pgvector setup.

Generation, and the honest prompt

context = "\n\n---\n\n".join(
    f"[{i}] {r['title']} — {r['heading']}\n{r['body']}"
    for i, r in enumerate(rows, 1)
)

response = client.messages.create(
    model="claude-opus-5",
    max_tokens=2000,
    system=(
        "Answer only from the numbered context below. Cite the bracketed number "
        "after every claim. If the context does not contain the answer, say so "
        "plainly and do not use outside knowledge."
    ),
    messages=[{
        "role": "user",
        "content": f"Context:\n{context}\n\nQuestion: {question}",
    }],
)

When retrieval returns plausible rubbish

Vector search always returns eight results, even when nothing relevant exists. Similarity is not relevance, and a top hit at 0.31 is noise dressed as an answer.

• Apply a similarity floor and return nothing rather than the best of a bad set. “I don’t know” is a correct answer.
• Add keyword search alongside it. Postgres already has websearch_to_tsquery and ts_rank_cd; merging lexical and vector results fixes the case embeddings are worst at — exact identifiers, error codes, product names.
• Always surface the sources in the UI. A cited answer a human can check is worth far more than a confident one they cannot.

What I’d do

If you already run Postgres, do not add a vector database. pgvector plus an HNSW index will carry you well past the point where the corpus stops being the bottleneck. Spend your effort on chunking and on a hybrid keyword-plus-vector query, because that is where the quality lives — and keep a small set of real questions with known answers so you can tell whether a change to chunk size actually helped.