How to Build a RAG Pipeline With Postgres and pgvector (2026)

This guide is for engineers who already run Postgres and want retrieval-augmented generation without adding a separate vector database. By the end you will have a working pipeline: documents chunked and embedded into a Postgres table, an HNSW index for vector search, a GIN index for keyword search, a single SQL query that fuses both with reciprocal rank fusion, and a small eval script that tells you whether retrieval is any good. I tested every command here on 20 October 2026 against Postgres 18.6 with pgvector 0.8.7 (released 1 October 2026), psycopg 3.3.6, pgvector-python 0.5.1 and the openai Python SDK 3.35.1. You need Docker, Python 3.10 or later, and an OpenAI API key (any embedding provider works with small changes). If you are still deciding whether you need retrieval at all, read RAG vs long context vs fine-tuning first.

Step 1: Run Postgres 18 with pgvector 0.8.7 in Docker

The pgvector project publishes images tagged by extension and Postgres version. Pin both; latest-style tags are how a minor upgrade lands in your laptop database on a Tuesday.

# Postgres 18 + pgvector 0.8.7. --shm-size matters for parallel index builds (see Step 5).
docker run -d --name ragdb \
  --shm-size=1g \
  -e POSTGRES_PASSWORD=devpass \
  -p 55432:5432 \
  pgvector/pgvector:0.8.7-pg18-trixie

export DATABASE_URL="postgresql://postgres:devpass@localhost:55432/postgres"
psql "$DATABASE_URL" -c "CREATE EXTENSION IF NOT EXISTS vector;" \
  -c "SELECT extversion FROM pg_extension WHERE extname = 'vector';"

Expected output:

CREATE EXTENSION
 extversion
------------
 0.8.7
(1 row)

On a managed service (RDS, Cloud SQL, Azure, Supabase, Neon and others), pgvector is usually preinstalled and you only run CREATE EXTENSION vector. Check which version your provider ships with the same query; iterative index scans need 0.8.0 or later, and 0.8.3 and 0.8.4 fixed HNSW vacuuming bugs including possible index corruption, so I would not run anything older than 0.8.4 in production.

Step 2: Create the schema for documents and chunks

Two tables: one row per source document, one row per chunk. The chunk table carries the text, the embedding, and a generated tsvector column so keyword search needs no application code.

-- schema.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  source     text NOT NULL UNIQUE,          -- file path or URL; used for re-ingest
  title      text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index int    NOT NULL,
  content     text   NOT NULL,
  embedding   vector(1536),                 -- text-embedding-3-small dimensions
  tsv         tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  UNIQUE (document_id, chunk_index)
);

CREATE INDEX chunks_tsv_idx         ON chunks USING gin (tsv);
CREATE INDEX chunks_document_id_idx ON chunks (document_id);

psql "$DATABASE_URL" -f schema.sql

Why 1536: OpenAI's text-embedding-3-small returns 1536-dimension vectors, normalised to length 1, and costs $0.02 per million tokens. pgvector's HNSW index supports vector up to 2,000 dimensions and halfvec up to 4,000, so if you pick text-embedding-3-large (3072 dimensions) either pass dimensions=1536 to the API or store it as halfvec(3072). Each vector value takes 4 * dimensions + 8 bytes, so a 1536-dimension embedding is about 6 KB before any index.

Do not skip the ON DELETE CASCADE and the unique source. Re-ingesting a changed document without removing its old chunks is the most common way a RAG system starts quoting outdated policy.

Step 3: Chunk your documents

Chunking decides what the model can ever see. Start simple: split on paragraph boundaries, pack paragraphs up to roughly 500 tokens (about 2,000 characters of English), and carry a small overlap so a sentence that straddles a boundary survives. Anthropic's retrieval experiments describe chunks as usually no more than a few hundred tokens, and that is a reasonable default until your evals say otherwise.

import re

def chunk_text(text: str, max_chars: int = 2000, overlap: int = 200) -> list[str]:
    """Split on blank lines, pack paragraphs up to ~max_chars (~500 tokens), carry a small overlap."""
    paras = [p.strip() for p in re.split(r"\n\s*\n", text) if p.strip()]
    chunks, buf = [], ""
    for p in paras:
        if buf and len(buf) + len(p) + 2 > max_chars:
            chunks.append(buf)
            buf = buf[-overlap:]          # tail of previous chunk keeps context across the boundary
        buf = f"{buf}\n\n{p}" if buf else p
    if buf:
        chunks.append(buf)
    return chunks

Two refinements pay off quickly once the basic pipeline works. Prepend the document title (or a heading path such as "Runbook > Database > Failover") to each chunk before embedding, so a paragraph that says "restart it" still knows what "it" is. And for long documents where chunks lose meaning on their own, Anthropic's contextual retrieval approach (generate a one-sentence summary of where the chunk sits) cut top-20 retrieval failures from 5.7% to 2.9% when combined with BM25, per their published results.

Step 4: Generate embeddings and load them with Python

Install the client libraries into a virtual environment, pinned:

python3 -m venv .venv && . .venv/bin/activate
pip install "psycopg[binary]==3.3.6" "pgvector==0.5.1" "openai==3.35.1" numpy
export OPENAI_API_KEY=...   # set in your shell, never in the script

Here is the full pipeline script. register_vector teaches psycopg to send NumPy arrays as vector values. Re-ingesting a file replaces its chunks inside one transaction, so readers never see half a document.

# rag.py: ingest markdown files into Postgres + pgvector, then hybrid search.
# Tested with: Postgres 18.6, pgvector 0.8.7, psycopg 3.3.6, pgvector-python 0.5.1, openai 3.35.1
import os, sys, re, pathlib
import numpy as np
import psycopg
from pgvector.psycopg import register_vector
from openai import OpenAI

DSN = os.environ.get("DATABASE_URL", "postgresql://postgres:devpass@localhost:55432/postgres")
EMBED_MODEL = "text-embedding-3-small"   # 1536 dimensions, normalised to length 1
client = OpenAI()                        # reads OPENAI_API_KEY

def chunk_text(text: str, max_chars: int = 2000, overlap: int = 200) -> list[str]:
    paras = [p.strip() for p in re.split(r"\n\s*\n", text) if p.strip()]
    chunks, buf = [], ""
    for p in paras:
        if buf and len(buf) + len(p) + 2 > max_chars:
            chunks.append(buf)
            buf = buf[-overlap:]
        buf = f"{buf}\n\n{p}" if buf else p
    if buf:
        chunks.append(buf)
    return chunks

def embed(texts: list[str]) -> list[np.ndarray]:
    resp = client.embeddings.create(model=EMBED_MODEL, input=texts)
    return [np.array(d.embedding, dtype=np.float32) for d in resp.data]

def ingest(conn, path: pathlib.Path):
    text = path.read_text(encoding="utf-8")
    pieces = chunk_text(text)
    with conn.transaction():
        doc_id = conn.execute(
            """INSERT INTO documents (source, title) VALUES (%s, %s)
               ON CONFLICT (source) DO UPDATE SET title = EXCLUDED.title
               RETURNING id""",
            (str(path), path.stem)).fetchone()[0]
        conn.execute("DELETE FROM chunks WHERE document_id = %s", (doc_id,))  # re-ingest replaces old chunks
        for start in range(0, len(pieces), 100):                            # batch embedding calls
            batch = pieces[start:start + 100]
            vecs = embed(batch)
            with conn.cursor() as cur:
                cur.executemany(
                    "INSERT INTO chunks (document_id, chunk_index, content, embedding) VALUES (%s, %s, %s, %s)",
                    [(doc_id, start + i, c, v) for i, (c, v) in enumerate(zip(batch, vecs))])
    return len(pieces)

HYBRID_SQL = """
WITH semantic AS (
    SELECT id, RANK() OVER (ORDER BY embedding <=> %(emb)s) AS rnk
    FROM chunks
    ORDER BY embedding <=> %(emb)s
    LIMIT 40
),
keyword AS (
    SELECT id, RANK() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS rnk
    FROM chunks, websearch_to_tsquery('english', %(q)s) q
    WHERE tsv @@ q
    ORDER BY ts_rank_cd(tsv, q) DESC
    LIMIT 40
)
SELECT c.id, c.document_id, c.content,
       COALESCE(1.0 / (%(k)s + s.rnk), 0.0) + COALESCE(1.0 / (%(k)s + kw.rnk), 0.0) AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword kw ON s.id = kw.id
JOIN chunks c ON c.id = COALESCE(s.id, kw.id)
ORDER BY rrf_score DESC
LIMIT %(limit)s
"""

def search(conn, query: str, limit: int = 8, k: int = 60):
    emb = embed([query])[0]
    with conn.transaction():
        conn.execute("SET LOCAL hnsw.ef_search = 100")
        return conn.execute(HYBRID_SQL, {"emb": emb, "q": query, "k": k, "limit": limit}).fetchall()

if __name__ == "__main__":
    conn = psycopg.connect(DSN, autocommit=True)
    register_vector(conn)
    if sys.argv[1] == "ingest":
        for p in sys.argv[2:]:
            print(p, ingest(conn, pathlib.Path(p)), "chunks")
    elif sys.argv[1] == "search":
        for row in search(conn, " ".join(sys.argv[2:])):
            print(f"{row[3]:.4f}  chunk={row[0]} doc={row[1]}  {row[2][:80]!r}")

python rag.py ingest docs/*.md

When I ran this against a folder of 110 markdown files, it produced 1,629 chunks. For bulk loads in the millions, switch from executemany to binary COPY, which pgvector's README recommends for loading, and build the index after the load rather than before.

Step 5: Create the HNSW index

HNSW gives a better speed-recall trade-off than IVFFlat at the cost of slower builds and more memory, and it needs no training step, per the pgvector docs. Use the operator class that matches your query operator: <=> is cosine distance, so the index uses vector_cosine_ops.

-- Give the build enough memory; the graph should fit in maintenance_work_mem.
SET maintenance_work_mem = '1GB';
SET max_parallel_maintenance_workers = 3;   -- plus the leader

-- CONCURRENTLY avoids blocking writes on a live table.
-- m and ef_construction shown at their defaults (16 and 64).
CREATE INDEX CONCURRENTLY chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- In another session, watch progress:
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%"
FROM pg_stat_progress_create_index;

A real failure I hit while testing this guide: my first container ran with Docker's default /dev/shm, and the parallel build died immediately.

ERROR:  could not resize shared memory segment "/PostgreSQL.1229179912" to 1070632448 bytes: No space left on device

Parallel index builds put the shared graph in dynamic shared memory, which in a container lives in /dev/shm, 64 MB by default in Docker. Recreating the container with --shm-size=1g fixed it; on Kubernetes the equivalent is a memory-backed emptyDir mounted at /dev/shm. After that, the index on a padded test table of 50,499 rows built in about 54 seconds on my laptop and took 395 MB, against 990 MB for the whole table. Your numbers will differ; the ratio is the point. HNSW indexes are large, and pgvector suggests halfvec or binary quantisation when the working set outgrows memory.

Confirm the index is used:

EXPLAIN (ANALYZE, COSTS OFF)
SELECT id FROM chunks
ORDER BY embedding <=> (SELECT embedding FROM chunks WHERE id = 42)
LIMIT 10;

 Limit (actual time=44.586..45.049 rows=10.00 loops=1)
   ->  Index Scan using chunks_embedding_hnsw on chunks (actual time=44.584..45.041 rows=10.00 loops=1)
         Order By: (embedding <=> (InitPlan 1).col1)
 Execution Time: 45.198 ms

That was a cold cache. The same query written as ORDER BY 1 - (embedding <=> ...) DESC fell back to a sequential scan and took 1,031 ms on the same table, which is the most common "pgvector is slow" bug. More on that in the troubleshooting table, and deeper tuning lives in the pgvector HNSW performance guide.

Step 6: Hybrid search with full-text and reciprocal rank fusion

Vector search finds paraphrases; keyword search finds exact identifiers like ERR_4012, kubectl drain or an invoice number. You want both. The HYBRID_SQL in Step 4 runs each search to a depth of 40, then merges them with reciprocal rank fusion: each result scores 1 / (k + rank) in each list it appears in, summed. RRF ignores the raw scores, which matters because cosine distance and ts_rank_cd live on unrelated scales. The constant k = 60 comes from the original RRF paper and is what pgvector-python's reference example uses.

Three choices in that query are deliberate:

• websearch_to_tsquery accepts what users actually type (quotes, -exclusions, or) without throwing syntax errors, unlike to_tsquery.
• The vector CTE orders by the bare distance expression with a LIMIT, which is the shape the HNSW index needs.
• SET LOCAL hnsw.ef_search = 100 raises the candidate list from the default 40 for this query only. Since each branch asks for 40 rows, an ef_search of 40 leaves no headroom.

python rag.py search "how do I clear a stuck terraform state lock"

The output is a ranked list of chunk ids, document ids and RRF scores. A chunk found by both searches near the top scores around 0.033 (two times 1/61); one found by only one list tops out near 0.016. If you need better precision than RRF, the next step is a cross-encoder reranker over the top 40 to 150 fused results, which pgvector-python also has an example for.

Step 7: Generate the answer with retrieved context

Retrieval is the hard part; generation is a prompt. Number the chunks, tell the model to answer only from them and cite the numbers, and tell it what to do when the answer is not there.

# answer.py
from openai import OpenAI
import psycopg
from pgvector.psycopg import register_vector
import rag

client = OpenAI()
conn = psycopg.connect(rag.DSN, autocommit=True)
register_vector(conn)

question = "How do I clear a stuck Terraform state lock?"
rows = rag.search(conn, question, limit=8)
context = "\n\n".join(f"[{r[0]}] {r[2]}" for r in rows)

resp = client.responses.create(
    model="gpt-6.1-sol",   # any capable model; pin the one you evaluated
    instructions=("Answer using only the numbered context. Cite chunk ids in square brackets. "
                  "If the context does not contain the answer, say so."),
    input=f"Context:\n{context}\n\nQuestion: {question}",
)
print(resp.output_text)

Eight chunks of about 500 tokens is roughly 4,000 input tokens per question. Treat the retrieved text as untrusted input: a document can contain instructions aimed at the model, so keep tool access away from this step, as covered in the prompt injection defence guide.

Step 8: Evaluate retrieval before you tune anything

Without an eval you are tuning by vibes. You need two measurements: does the retriever find the right document (application quality), and does the HNSW index return what an exact search would (index quality).

For the first, write 30 to 50 real questions with the document that answers each, and measure hit rate and mean reciprocal rank:

# eval_retrieval.py: hit rate and MRR of hybrid search against a labelled question set.
# eval.jsonl lines look like: {"question": "how do I clear a stuck terraform lock", "source": "docs/terraform-state.md"}
import json, sys
import psycopg
from pgvector.psycopg import register_vector
import rag

K = 8
conn = psycopg.connect(rag.DSN, autocommit=True)
register_vector(conn)
src = dict(conn.execute("SELECT id, source FROM documents").fetchall())

hits, rr, n = 0, 0.0, 0
for line in open(sys.argv[1], encoding="utf-8"):
    case = json.loads(line)
    rows = rag.search(conn, case["question"], limit=K)
    sources = [src[r[1]] for r in rows]
    n += 1
    if case["source"] in sources:
        hits += 1
        rr += 1.0 / (sources.index(case["source"]) + 1)
    else:
        print("MISS:", case["question"])
print(f"questions={n}  hit_rate@{K}={hits/n:.2f}  mrr@{K}={rr/n:.2f}")

python eval_retrieval.py eval.jsonl
# questions=40  hit_rate@8=0.xx  mrr@8=0.xx   (plus one MISS line per failure)

Read the MISS lines, not just the score. Misses on exact identifiers mean the keyword branch is weak; misses on paraphrases mean the embedding or chunking is. Run it in CI on every change to chunking, model or SQL, the same way the LLM evals in CI guide does for prompts.

For index quality, compare approximate against exact results, as the pgvector docs suggest for monitoring:

-- recall.sql: recall@10 of the HNSW index against exact search, over 50 sample queries
CREATE TEMP TABLE q AS
  SELECT id AS qid, embedding AS qv FROM chunks ORDER BY random() LIMIT 50;

BEGIN;
SET LOCAL enable_indexscan = off;            -- exact search
CREATE TEMP TABLE exact AS
  SELECT q.qid, n.id FROM q CROSS JOIN LATERAL (
    SELECT id FROM chunks ORDER BY embedding <=> q.qv LIMIT 10) n;
COMMIT;

SET hnsw.ef_search = 40;                     -- try 40, 100, 200
CREATE TEMP TABLE approx AS
  SELECT q.qid, n.id FROM q CROSS JOIN LATERAL (
    SELECT id FROM chunks ORDER BY embedding <=> q.qv LIMIT 10) n;

SELECT round(count(a.id)::numeric / (SELECT count(*) FROM exact), 3) AS recall_at_10
FROM exact e LEFT JOIN approx a USING (qid, id);

A warning about my own test numbers: on a padded table of random synthetic vectors, which is the worst case for any graph index because there is no cluster structure, this script reported recall@10 of 0.196 at ef_search = 40 and 0.358 at 200. Real embeddings cluster and score far higher. Run it on your data; the direction (more ef_search, more recall, more latency) is what transfers.

Troubleshooting

Symptom	Likely cause	Fix
Vector query does a Seq Scan and takes seconds	ORDER BY is an expression such as 1 - (embedding <=> x) DESC, or there is no LIMIT	Order by the bare distance ascending with a LIMIT; compute similarity in the SELECT list instead
Index exists but is never used	Operator class does not match the operator (vector_l2_ops index, <=> query)	Recreate with vector_cosine_ops for <=>, vector_ip_ops for <#>, vector_l2_ops for <->
Fewer than LIMIT rows with a WHERE filter	HNSW filters after the scan; default ef_search is 40	SET hnsw.iterative_scan = relaxed_order (pgvector 0.8.0+); in my test a 7% filter returned 4 of 10 rows without it, 10 with it
could not resize shared memory segment ... No space left on device during CREATE INDEX	Docker's 64 MB /dev/shm too small for a parallel build	Run with --shm-size=1g, or a memory emptyDir at /dev/shm on Kubernetes, or lower max_parallel_maintenance_workers
NOTICE: hnsw graph no longer fits into maintenance_work_mem and a slow build	Graph exceeds maintenance_work_mem	Raise it for the session (without exhausting server RAM), build after bulk load, or use halfvec
column cannot have more than 2000 dimensions for hnsw index	3072-d embeddings in a vector column	Request dimensions=1536, or use halfvec(3072) with halfvec_cosine_ops
Exact names, codes or SKUs never retrieved	Vector-only search	Add the keyword branch and RRF from Step 6; check the tsv language config matches your text
Answers quote outdated content	Old chunks left behind on re-ingest	Delete chunks by document_id inside the ingest transaction, as in ingest()
Recall drops after many updates and deletes	Dead tuples in the HNSW graph	REINDEX INDEX CONCURRENTLY then VACUUM, per the pgvector docs; stay on 0.8.4+ for the vacuum fixes

FAQ

Do I need a dedicated vector database instead of pgvector?

Usually not until you are well into tens of millions of vectors or need features Postgres lacks. pgvector keeps vectors next to your relational data, so filters, joins, permissions and backups work the way they already do. The trade-offs are covered in do you need a vector database.

HNSW or IVFFlat in pgvector?

Start with HNSW. It has a better speed-recall trade-off and needs no training step, so you can create it on an empty table. IVFFlat builds faster and uses less memory, but needs data present before the build and a tuned lists and probes.

What chunk size should I use for RAG?

Start around 300 to 800 tokens with a small overlap and the document title prepended, then let your eval decide. Smaller chunks raise precision but lose context; larger chunks carry context but dilute the embedding and cost more tokens at generation time.

Why use reciprocal rank fusion instead of adding scores?

Cosine distance and ts_rank_cd are on unrelated scales, so adding them lets one dominate arbitrarily. RRF uses only each result's rank in each list, which needs no calibration and is robust with the standard k = 60.

In production the first things I check are boring: that the vector query plan shows the HNSW index, that iterative scans are on wherever a WHERE clause meets a vector search, that re-ingest deletes old chunks, and that the eval runs on every change. Most "RAG quality" problems I have seen were one of those four, not the model. The references worth bookmarking are the pgvector README, its CHANGELOG, the pgvector-python examples, the PostgreSQL full-text search docs and OpenAI's embeddings guide.

Related: RAG vs Long Context vs Fine-Tuning in 2026: 1M Tokens Isn't Free, Agent Memory in 2026: Vendors Claim 92.5 on LoCoMo, a Plain Filesystem Scored 74, and Every Memory Product Lost to BM25 and LLM Inference in 2026: Prefill Is a Different Machine Now, and the KV Cache Has a Disk Tier.