Choosing a Vector Database for RAG: Start With Postgres

For most retrieval-augmented generation projects, the right first choice is pgvector in a Postgres database you already run. It handles millions of embeddings comfortably, keeps vectors in the same transaction and the same backup as the data they describe, and lets you filter by ordinary SQL columns in the same query as the similarity search.

The vector database category attracted a great deal of attention and a great many products, and the comparison articles usually start from the assumption that you need a specialist system. For a corpus of a few hundred thousand documents that is rarely true, and the operational cost of a second datastore is real.

This is about when the specialist option is genuinely warranted and what you give up by reaching for it early.

What the database actually has to do
pgvector, and why it is usually enough
When a dedicated vector database earns its place
The things that matter more than the database
A sensible starting architecture
How this fits the rest of the stack
FAQ

What the database actually has to do

RAG’s retrieval step is short: embed the query, find the nearest stored embeddings, pass those chunks to the model as context.

So the store needs to hold vectors with their text and metadata, find approximate nearest neighbours quickly, and filter by metadata — tenant, document type, date, permissions — while doing so.

That last requirement is the one that quietly decides the architecture. In practice almost every real system needs filtered search: this user’s documents, this workspace, published only. How a system handles filtering combined with vector search varies enormously, and doing it badly produces either wrong results or very slow ones.

The naive approach — retrieve the top 100 by similarity, then filter — breaks when the filter is selective. If a user owns 0.1% of documents, the top 100 global matches may contain none of theirs, and you return nothing while relevant documents exist.

This is where a relational database has a genuine structural advantage: the filter is a WHERE clause on an indexed column, evaluated by a planner that knows the selectivity.

pgvector, and why it is usually enough

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES workspaces(id),
  content     text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  embedding   vector(1536),
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON documents (workspace_id);

The query is ordinary SQL, with the filter and the similarity search in one statement:

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE workspace_id = $2
  AND created_at > now() - interval '1 year'
ORDER BY embedding <=> $1
LIMIT 10;

<=> is cosine distance; <-> is L2 and <#> is inner product. Match the operator to what your embedding model was trained for — cosine for most current text embedding models.

What you get by keeping this in Postgres:

One transaction. A document and its embedding are inserted atomically. With a separate vector store, a failure between the two writes leaves them inconsistent, and reconciling that is a job you will end up writing.
One backup. Restore and both are at the same point in time.
Real joins. Retrieve chunks alongside their document titles, authors and permissions in one query.
Existing operational knowledge. You already know how to back it up, monitor it and grant access to it.

Index choice: HNSW gives better recall and faster queries at the cost of build time and memory. IVFFlat builds faster and uses less memory but needs to be built on populated data and rebuilt as the table grows. Start with HNSW unless index build time is a genuine constraint.

When a dedicated vector database earns its place

There are real cases. They are just narrower than the marketing suggests:

Scale beyond roughly 10 million vectors, where pgvector’s index build times and memory use become genuinely awkward.
Very high query throughput where you need to scale retrieval independently of your transactional database.
Advanced hybrid search out of the box — combining BM25 keyword scoring with vector similarity, with tuned fusion. Postgres can do full-text search and you can combine the scores yourself, but a purpose-built engine does it better.
Aggressive quantisation for cost reasons at very large scale.
You do not run Postgres and adding one is a bigger step than adding a managed vector service.

Note what is not on that list: “we are building a RAG application”. The workload characteristics decide this, not the use case.

The cost of the second system is worth stating plainly. You take on a dual-write problem, a second backup and restore procedure, a second thing to monitor and secure, a second bill, and a consistency question every time a document changes. That is a real ongoing tax, and it should be paid for a reason you can name.

A reasonable path: build on pgvector, keep the retrieval layer behind a small interface, and measure. If recall or latency becomes the constraint at your actual scale, migrating is a contained piece of work — and you will migrate knowing your real query patterns rather than guessing them upfront.

The things that matter more than the database

RAG quality problems are usually not retrieval-engine problems. In rough order of impact:

Chunking. Too large and the embedding is diluted across several topics; too small and it lacks the context to be useful. Chunking on semantic boundaries — headings, paragraphs, sections — beats fixed-size windows consistently. Overlap of 10-20% helps preserve context across boundaries.

The embedding model. Changing model changes everything, because embeddings are not comparable across models. Switching means re-embedding your entire corpus, so this is a decision worth making deliberately. Check whether a domain-specific model exists for your content.

Hybrid search. Pure vector search is poor at exact matches — product codes, error identifiers, names. Combining it with keyword search covers the gap, and in Postgres that is tsvector alongside the embedding with the scores combined.

Reranking. Retrieve 50 candidates cheaply, then rerank with a cross-encoder and keep the top 5. This usually improves relevance more than any database change, because the reranker sees the query and document together rather than comparing independent embeddings.

Evaluation. Without a test set of questions and expected sources, every change is a guess. This is the step teams skip and the one that makes all the others tractable.

A sensible starting architecture

Postgres with pgvector, one table with content, metadata, and embedding, indexed with HNSW.
Chunk on document structure, 500-1000 tokens with modest overlap, storing the source document reference on every chunk.
Retrieve 30-50 candidates with metadata filters applied in the same query.
Rerank to the top 5-10 before building the prompt.
Cite sources in the response, so users can verify and so you can see what was retrieved when an answer is wrong.
Keep an evaluation set of real questions with expected sources, and run it on every change.

That handles a corpus in the hundreds of thousands of chunks on a modest database instance, and it gives you the measurements you need to know whether the specialist option is justified.

The migration path stays open. Embeddings are portable — they are just arrays of floats — so moving to a dedicated store later is a re-index rather than a re-embed, provided you keep the source text.

How this fits the rest of the stack

The strongest argument for starting with Postgres is not performance — it is that your vectors, your documents and your permissions stay in one system with one transaction boundary and one backup. Consistency between two stores is a problem you have to solve continuously, and it does not show up in a benchmark.

RunxBuild offers managed Postgres with backups, connection limits and private networking, alongside the Python or Node service that does the embedding and retrieval — so the vector store and the application are one deployment rather than two systems to keep in step. We do not offer a dedicated vector database, and if your scale genuinely warrants one, a specialist provider is the honest answer. The RunxBuild hosting calculator shows the service and database costs separately.

Useful related references:

FAQ

Do I need a dedicated vector database for RAG?

Usually not. pgvector in a Postgres database you already run handles millions of embeddings and keeps vectors in the same transaction and backup as the data they describe. Dedicated stores earn their place beyond roughly 10 million vectors, at very high query throughput, or when you need advanced hybrid search out of the box.

What is the advantage of pgvector over a specialist vector store?

Atomic writes, a single backup, real joins against your other tables, and metadata filtering evaluated by a query planner that understands selectivity. A separate store means a dual-write problem, a second backup procedure, and a consistency question every time a document changes.

Should I use HNSW or IVFFlat indexes in pgvector?

HNSW for better recall and faster queries, at the cost of longer build times and more memory. IVFFlat builds faster and uses less memory but must be built on populated data and rebuilt as the table grows. Start with HNSW unless build time is a real constraint.

Why does my RAG system return irrelevant results?

Retrieval quality is usually a chunking, embedding or reranking problem rather than a database one. Chunk on semantic boundaries rather than fixed sizes, add keyword search alongside vector similarity for exact matches, and rerank a larger candidate set down to the top few before building the prompt.

Can I switch vector databases later?

Yes, and it is easier than it sounds. Embeddings are arrays of floats and are portable between stores, so a migration is a re-index rather than a re-embed as long as you kept the source text. Keeping retrieval behind a small interface makes the change contained.