Building a Production RAG System with PostgreSQL + pgvector
You probably don't need a dedicated vector database. Here's how HNSW indexing, hybrid search, and connection pooling in Postgres get RAG to production scale.
By Naeem Akhtar · 11 min read
In short
- If you already run Postgres, pgvector removes a whole system to secure, back up and keep in sync.
- HNSW indexes usually beat IVFFlat on recall and speed in production, at the cost of more memory and slower builds.
- Apply metadata filters (tenant, permissions, recency) alongside the vector search rather than after it, and use hybrid search for exact terms.
- Vector indexes bloat under heavy writes like any Postgres index, so plan regular VACUUM and index maintenance.
Why Postgres instead of a dedicated vector database
The pitch for a dedicated vector database is usually raw scale. The reality for most companies is that their document corpus, permission model, and application data already live in Postgres — and adding a second database just to store embeddings means keeping two systems in sync, securing both, and reasoning about consistency between them. pgvector removes that split by storing embeddings as a column type alongside the rest of the row, so a single query can join vector similarity with the relational data that actually governs who's allowed to see what. For the wider architecture and the failures to plan for, see Enterprise RAG Architecture. If you are choosing a tool rather than building, read RAG knowledge base tools: build vs buy.
This isn't a claim that pgvector wins at every scale — a corpus in the hundreds of millions of vectors with extreme query-per-second requirements is a genuinely different problem. But that's not where most production RAG systems live. For the active working set most companies query against, Postgres with a properly tuned index handles it comfortably.
HNSW vs IVFFlat: pick HNSW unless you have a specific reason not to
pgvector supports two main index types. IVFFlat is faster to build and uses less memory, but has lower recall and needs the table's approximate size known ahead of time to tune well. HNSW (Hierarchical Navigable Small World) builds a graph structure alongside the vectors — it takes longer to build and uses more memory, but delivers better recall and more consistent query latency without needing to be re-tuned as the corpus grows.
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- Tune recall vs. speed per query
SET hnsw.ef_search = 100;For most production RAG systems, the memory and build-time cost of HNSW is worth paying once — it's a much smaller ongoing cost than debugging inconsistent recall in front of users.
Filtering has to be part of the search, not a step after it
The pattern that breaks naive implementations under real traffic is filtered vector search — retrieving the top-k similar vectors and then filtering by tenant, permission, or date range as a second step. If the filter is applied after retrieval, a query can return zero usable results even though relevant documents exist, simply because none of the top-k unfiltered matches happened to belong to the right tenant.
- Filter on indexed columns (tenant_id, permission tags, document type) combined with the vector search in a single query, so Postgres's planner can use both indexes together.
- For multi-tenant systems, partition or index by tenant so a single tenant's query never has to scan past another tenant's vectors to find a match.
- Keep frequently-filtered metadata as real columns, not buried inside a JSONB blob — the query planner handles indexed columns far better than JSONB path lookups at scale.
Hybrid search beats pure vector search on real queries
Pure semantic search struggles with exact-match queries — a product SKU, an error code, a proper noun that doesn't have a strong semantic neighborhood. Postgres's built-in full-text search, combined with pgvector's similarity search and re-ranked together, consistently outperforms either approach alone on real user query patterns, which are a mix of "how do I..." questions and exact terms.
Rule of thumb
If your evaluation set includes any queries containing product codes, names, or exact phrases, and your RAG system is vector-only, that's very likely where a meaningful share of your retrieval failures are coming from.Operational practices that keep it fast at scale
| Practice | Why it matters |
|---|---|
| Connection pooling (PgBouncer) | Vector queries can be expensive; unpooled connections exhaust Postgres's connection limit fast under concurrent load. |
| Regular VACUUM | HNSW and IVFFlat indexes bloat under write-heavy updates just like B-tree indexes — skipping maintenance degrades recall and latency over time. |
| Chunking strategy review | Chunk size and overlap directly affect both retrieval precision and index size — this is worth revisiting as the corpus and query patterns evolve, not set once and forgotten. |
| p95 latency monitoring | Average latency hides the tail — a small number of slow queries (usually cross-tenant scans or missing indexes) is what users actually notice. |
A starting schema and query
A single table can hold chunks, embeddings and the metadata you filter on. The details below are illustrative; adapt dimensions to your embedding model and add the columns your access model needs.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_chunks (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
document_id uuid NOT NULL,
chunk_index int NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL,
allowed_groups text[] NOT NULL DEFAULT '{}',
source_url text,
updated_at timestamptz NOT NULL DEFAULT now(),
tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED
);
CREATE INDEX ON document_chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON document_chunks USING gin (tsv);
CREATE INDEX ON document_chunks (tenant_id);
-- Permission filter and vector search in one query
SELECT id, content, source_url,
embedding <=> $1 AS distance
FROM document_chunks
WHERE tenant_id = $2
AND allowed_groups && $3
ORDER BY embedding <=> $1
LIMIT 20;pgvector or a dedicated vector database?
| Consideration | pgvector in PostgreSQL | Dedicated vector database |
|---|---|---|
| Operations | One database to run, back up and secure | Another service to operate or pay for |
| Permissions and joins | Filter on relational data in the same query | Metadata filtering, usually separate from your main data |
| Transactions and consistency | ACID with your application data | Typically eventual consistency with a sync pipeline |
| Scale | Comfortable for many production workloads; test at your size | Built for very large indexes and high query rates |
| Advanced vector features | Growing, but fewer | Rich filtering, sharding and tuning options |
| Cost | Uses existing database infrastructure | Additional service cost |
| Best fit | Permission-aware RAG on existing Postgres | Very large corpora or specialised search needs |
Tuning knobs and what they trade
| Setting | Effect | Guidance |
|---|---|---|
| HNSW m | Graph connectivity; higher improves recall and uses more memory | Start with defaults; raise only if recall tests demand it |
| HNSW ef_construction | Build-time quality versus build speed | Higher for better indexes if builds are infrequent |
| hnsw.ef_search | Query-time recall versus latency | Tune against a recall test set, not by feel |
| Embedding dimensions | Storage, index size and speed | Smaller embeddings can cut cost with modest quality loss; measure |
| Candidate count before re-rank | Recall versus latency | Retrieve more candidates, re-rank down to what fits the prompt |
| Maintenance (vacuum, reindex) | Keeps performance stable as data changes | Schedule it and monitor index bloat |
Version your embedding model in the table or index metadata. Changing models later means re-embedding the corpus, and you will want to run old and new side by side while you compare retrieval quality.
Frequently asked questions
Is pgvector good enough for production, or do I need a dedicated vector database?
For the majority of production RAG systems — especially those where the document corpus, permissions, and application data already live in Postgres — pgvector with a properly tuned HNSW index is sufficient and removes the operational overhead of running a second database. Extreme-scale, extreme-QPS workloads are the exception where a dedicated vector database may be worth the added complexity.
Should I use HNSW or IVFFlat in pgvector?
HNSW, for almost all production use cases. It costs more memory and a longer index build, but delivers better recall and doesn't need to be re-tuned as the corpus grows, unlike IVFFlat which needs the table size known ahead of time to configure well.
Why does my filtered vector search return no results even though relevant documents exist?
This usually happens when the permission or tenant filter is applied after the top-k vector search instead of being combined with it. If none of the top-k unfiltered matches belong to the allowed tenant, the query returns nothing even though matches exist elsewhere in the corpus. Combine the filter and the vector search in a single indexed query.
Is pgvector good enough for production RAG?
For many workloads, yes. It keeps vectors, permissions and application data in one transactional database. Validate recall, latency and cost at your expected corpus size and query rate, and consider a dedicated vector database if you outgrow it.
Should I use HNSW or IVFFlat in pgvector?
HNSW is generally the better default because it needs no training step and gives strong recall and speed. IVFFlat can be useful for build-time or memory constraints, but it requires tuning list and probe settings against your data.
How do I do hybrid search in PostgreSQL?
Combine a full-text search ranking (tsvector with a GIN index) with vector similarity, then merge the results using a method such as reciprocal rank fusion, and apply permission filters inside both queries.
Need help with RAG and Knowledge Systems? Talk to the engineers.
Tell us what you want to build. You'll get a straight answer on scope, approach and cost from the people who do the work.
Keep reading
Related guides
RAG
Enterprise RAG Architecture: What Actually Breaks in Production
Permission-aware retrieval, citation grounding, and evaluation loops — the three things that separate a RAG demo from a system a company can rely on.
10 min read
RAG
RAG Knowledge Base Tools: Build vs Buy, and How to Choose
Off-the-shelf knowledge base tools, managed RAG services or a custom build? A comparison by data sensitivity, permissions, quality control and total cost.
11 min read
RAG
RAG vs Fine-Tuning: What Should Your Startup Actually Use?
The honest answer is usually RAG, occasionally both, and rarely fine-tuning alone — here's the decision framework and what each path actually costs to run.
10 min read