Build a RAG Chatbot Over Your Docs With PostgreSQL pgvector
Retrieval-augmented generation (RAG) has become the default pattern for grounding large language models in your own data. But you don't always need a dedicated vector database to ship a production-quality system. If you already run PostgreSQL, the pgvector extension lets you store embeddings, run similarity search, and keep everything in one place — no extra infrastructure to operate.
This walkthrough shows how to build a pgvector RAG chatbot over your documents: from schema design to chunking, embedding, retrieval, and answer generation.
Why pgvector Instead of a Dedicated Vector DB
Standalone vector databases are excellent, but they add another service to deploy, secure, back up, and monitor. For many teams, that's overhead they don't need yet. pgvector gives you a compelling alternative when:
- You already have PostgreSQL in production and want to avoid a new datastore.
- Your document corpus is small to medium — think hundreds of thousands to a few million chunks.
- You need to combine vector search with regular SQL filters (tenant IDs, timestamps, access control).
- You want transactional consistency between your metadata and your embeddings.
Modern pgvector supports both IVFFlat and HNSW indexes. HNSW generally gives better recall and query speed for read-heavy workloads, at the cost of more memory and slower inserts, so it's the usual choice for a chatbot.
Step 1: Set Up PostgreSQL and pgvector
Install the extension in your database. On a managed service like Amazon RDS, Azure Database for PostgreSQL, or Google Cloud SQL, pgvector is available as a supported extension — you just enable it.
CREATE EXTENSION IF NOT EXISTS vector;Next, decide on your embedding dimensions. This must match the model you choose. For example, a 1,536-dimension model requires a vector(1536) column. Pick your embedding model first, because changing dimensions later means re-embedding everything.
Step 2: Design the Schema
Keep documents and chunks separate. A document is the source file; a chunk is a searchable slice of it. Store metadata alongside embeddings so you can filter during retrieval.
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
source_url TEXT,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE chunks (
id BIGSERIAL PRIMARY KEY,
document_id BIGINT REFERENCES documents(id) ON DELETE CASCADE,
content TEXT NOT NULL,
embedding vector(1536),
chunk_index INT,
token_count INT
);Once you have data loaded, add an HNSW index for fast approximate nearest-neighbor search using cosine distance:
CREATE INDEX ON chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);Step 3: Chunk Your Documents Thoughtfully
Chunking quality has an outsized effect on answer quality. A few practical rules:
- Aim for 300–800 tokens per chunk. Too small and you lose context; too large and retrieval becomes noisy.
- Add overlap of 10–15% between adjacent chunks so ideas that span boundaries aren't cut off.
- Respect structure. Split on headings, paragraphs, or Markdown sections rather than arbitrary character counts.
- Store the source and position so you can cite where an answer came from.
For technical docs, splitting on section headers and keeping code blocks intact usually beats naive fixed-length splitting.
Step 4: Generate and Store Embeddings
For each chunk, call your embedding model and insert the vector. Batch requests to reduce API round-trips, and normalize your text (strip boilerplate, collapse whitespace) before embedding.
import psycopg
with psycopg.connect(DSN) as conn:
for chunk in chunks:
vec = embed(chunk.text) # returns list[float]
conn.execute(
"INSERT INTO chunks (document_id, content, embedding, chunk_index) "
"VALUES (%s, %s, %s, %s)",
(chunk.doc_id, chunk.text, vec, chunk.index),
)Use the same embedding model at query time that you used at ingestion time. Mixing models produces incompatible vector spaces and poor retrieval.
Step 5: Retrieve Relevant Chunks
At query time, embed the user's question and run a similarity search. The <=> operator computes cosine distance in pgvector, so ordering ascending returns the closest matches first.
SELECT content, document_id,
1 - (embedding <=> %s) AS similarity
FROM chunks
ORDER BY embedding <=> %s
LIMIT 8;Because this is plain SQL, you can add filters for free — restrict to a tenant, a date range, or a document set:
WHERE document_id = ANY(%s)
AND created_at > now() - interval '90 days'You can tune recall per query by adjusting hnsw.ef_search. Higher values improve accuracy at the cost of latency.
Step 6: Generate a Grounded Answer
Pass the retrieved chunks into your LLM prompt as context. A clear instruction reduces hallucination:
System: Answer using only the context below.
If the answer isn't in the context, say you don't know.
Context:
{retrieved_chunks}
Question: {user_question}Include the source document ID or title with each chunk so the model can produce citations. Returning sources builds user trust and makes wrong answers easier to debug.
Improving Quality and Performance
Add a reranker
Vector similarity alone sometimes surfaces near-misses. Retrieve a larger candidate set (say 20 chunks), then use a cross-encoder reranker to pick the best 5 before sending them to the LLM. This often improves answer precision noticeably.
Combine keyword and vector search
PostgreSQL's full-text search (tsvector) pairs naturally with pgvector for hybrid retrieval. Vectors catch semantic matches; keyword search catches exact terms, product names, and error codes that embeddings sometimes miss. Blend both scores for the best of both worlds.
Watch your index and memory
HNSW indexes live in memory during queries. Monitor work_mem and shared buffers as your corpus grows, and reindex after large bulk loads. Build the index after inserting data rather than before, since incremental HNSW inserts are slower.
Handle updates cleanly
When a document changes, delete its old chunks and re-ingest. The ON DELETE CASCADE on the foreign key keeps orphaned embeddings from piling up.
When to Graduate to a Dedicated Vector DB
pgvector scales further than many people expect, but there are signals it's time to consider a specialized store: sustained tens of millions of vectors, strict sub-10ms latency at high concurrency, or the need for advanced features like distributed sharding across nodes. Until you hit those limits, keeping everything in PostgreSQL is simpler and cheaper to run.
For most internal knowledge bases, support bots, and documentation assistants, a pgvector RAG chatbot is a production-ready architecture you can stand up in an afternoon and grow with confidence.
Ready to build real AI skills? Join the September 2026 cohort at Class For Jobs. Explore Advanced AI — a hands-on, live program to build and ship production AI applications, live and instructor-led with career support, resume help, and job-placement assistance.
Related reading
