Writing ·

RAG With PostgreSQL and pgvector: Architecture

By Sovaid Shah, Software Architect · AI · RAG · PostgreSQL · pgvector · LLM · Architecture

TL;DR: RAG answers questions from your own data by retrieving relevant text and giving it to the model with the question. If you already run PostgreSQL, pgvector lets you store embeddings next to your data and search them with SQL, so you may not need a separate vector database. Most RAG quality problems come from chunking and retrieval, not the model, so build an evaluation set of real questions first.

Ask a general-purpose LLM about your company’s refund policy and it will make something up. It has never seen your policy. Retrieval-augmented generation (RAG) fixes that: find the relevant parts of your documents first, then give them to the model together with the question.

If your product already runs on PostgreSQL, you can build the whole retrieval side inside the database you already have. This post walks through the architecture.

How RAG works

There are two phases.

Indexing (ahead of time):

  1. Split your documents into chunks.
  2. Turn each chunk into an embedding, a list of numbers that represents its meaning, using an embedding model.
  3. Store chunks and embeddings.

Answering (per question):

  1. Embed the user’s question with the same embedding model.
  2. Find the chunks whose embeddings are closest to it.
  3. Send the question plus those chunks to the LLM, with an instruction to answer only from the provided text.
  4. Return the answer, ideally with links to the source chunks.

Why PostgreSQL and pgvector

pgvector is a PostgreSQL extension that adds a vector column type and similarity search. The advantages for an existing Postgres app are practical:

  • Embeddings live next to the data they describe, in one database with one backup and one set of permissions.
  • You can filter by normal columns (tenant, language, document type) in the same query as the similarity search.
  • No new service to run, secure and pay for.

A dedicated vector database can make sense at very large scale or for specialized needs. Many products never reach that point.

The schema

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  tenant_id   bigint NOT NULL,
  content     text   NOT NULL,
  embedding   vector(1536) NOT NULL  -- must match your embedding model's dimensions
);

CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON document_chunks (tenant_id);

The HNSW index gives fast approximate nearest-neighbour search. Use the operator class that matches how you compare vectors. Cosine distance (vector_cosine_ops) is a common default for text embeddings.

The retrieval query

SELECT id, document_id, content
FROM document_chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2   -- <=> is cosine distance in pgvector
LIMIT 5;

$2 is the question’s embedding. The tenant_id filter matters more than it looks. In a multi-tenant product, retrieval must never return another customer’s documents, and the filter belongs in the query, not in application code after the fact.

Where RAG quality actually comes from

When a RAG answer is bad, it’s usually because the right chunk was never retrieved. Swapping in a bigger model doesn’t fix that.

  • Chunk on structure. Split by headings, sections or paragraphs, not every N characters. A chunk should make sense on its own. Small overlaps between chunks help when an answer spans a boundary.
  • Keep context with each chunk. Prefix a chunk with its document title and section heading before embedding. “Refunds: 30 days” means more with “Billing policy” attached.
  • Combine keyword and vector search. Embeddings are weak at exact terms such as product codes and names. PostgreSQL full-text search alongside vector search (hybrid search) covers both.
  • Tell the model to say “I don’t know”. The prompt should require answers only from the provided chunks, and a clear fallback when they don’t contain the answer.

Build an evaluation set before tuning anything

Collect 30 to 50 real questions with known correct answers and the documents they should come from. For each change (chunk size, number of results, prompt, model), check two things:

  1. Retrieval: did the right chunk appear in the results?
  2. Answer: was the final answer correct and grounded in the chunks?

Without this, every change is a guess. With it, you can tell whether a change helped.

Keeping the index fresh

Documents change. Re-embed a document’s chunks whenever it’s created or updated, in a background job rather than in the request that saved it. Rails and .NET job systems handle this well; see Hangfire vs Sidekiq and LLM features with background jobs.

FAQ

What is RAG in simple terms?

RAG means finding the parts of your own documents relevant to a question, and giving them to an AI model together with the question so it answers from your data instead of guessing.

Do I need a vector database for RAG?

Not necessarily. If you already use PostgreSQL, pgvector adds vector search to it. Dedicated vector databases make sense mainly at very large scale or for specialized features.

Why does my RAG chatbot give wrong answers?

Usually because the right text wasn’t retrieved. Improve chunking, add titles and headings to chunks, use hybrid keyword plus vector search, and measure with an evaluation set of real questions.

Planning a RAG feature on an existing PostgreSQL product? See software architecture.