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 · 9 min read
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.
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. |
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.