Ditch Standalone Vector DBs: Why We Use PostgreSQL and pgvector for Production RAG
Abhishek Madaan
Ditch Standalone Vector DBs: Why We Use PostgreSQL and pgvector for Production RAG
In early 2023, the tech ecosystem witnessed an explosion of dedicated vector databases. Startups raised hundreds of millions of dollars promising specialized indexes for high-dimensional embeddings.
Two years later, after architecting Retrieval-Augmented Generation (RAG) pipelines for dozens of client products at Object Oriented Teens, our verdict is clear:
You almost certainly do not need a standalone vector database. You need PostgreSQL with
pgvector.
Here is why keeping your embeddings alongside your operational data is the superior architectural choice for 99% of web applications.
🛑 The Hidden Cost of Dual-Database Architectures
When you introduce a standalone vector database alongside your primary relational database, you immediately inherit severe architectural penalties:
- The Sync Debt: When a user deletes a document, updates permissions, or edits a comment, you must update two separate systems. If your vector database call fails, you have orphaned or stale vectors.
- No ACID Guarantees: You lose transaction isolation. Rolling back a failed user registration doesn’t roll back the vector index without distributed transaction choreography.
- Complex Metadata Filtering: In real applications, queries aren't just "find similar text". They are: "find similar text written by users in workspace 42 created after last Tuesday that have status = 'published'". Dedicated vector stores struggle with complex relational joins.
🐘 The Power of pgvector + HNSW
The pgvector extension introduces native vector data types and high-performance indexing algorithms directly inside Postgres:
-- Enable the extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Store document chunks with vectors and metadata in one table
CREATE TABLE document_chunks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
content TEXT NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}',
embedding vector(1536) -- OpenAI text-embedding-3-small dimension
);
-- Create a blazing fast Hierarchical Navigable Small World (HNSW) index
CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
With an HNSW index, nearest-neighbor searches run in single-digit milliseconds even over millions of vectors.
🎯 Hybrid Search: Vectors + BM25 Full-Text Search
Vector similarity excels at capturing conceptual semantic intent, but it frequently stumbles on exact keyword matches: part numbers, acronyms, person names, and legal codes.
Postgres lets you combine vector similarity with traditional Full-Text Search (BM25 / tsvector) in a single SQL query using Reciprocal Rank Fusion (RRF):
WITH semantic_search AS (
SELECT id, RANK() OVER (ORDER BY embedding <=> $1) as rank
FROM document_chunks
WHERE tenant_id = $2
LIMIT 20
),
keyword_search AS (
SELECT id, RANK() OVER (ORDER BY ts_rank_cd(search_vector, plainto_tsquery($3)) DESC) as rank
FROM document_chunks
WHERE tenant_id = $2 AND search_vector @@ plainto_tsquery($3)
LIMIT 20
)
SELECT
d.id,
d.content,
COALESCE(1.0 / (60 + s.rank), 0.0) +
COALESCE(1.0 / (60 + k.rank), 0.0) AS rrf_score
FROM document_chunks d
LEFT JOIN semantic_search s ON d.id = s.id
LEFT JOIN keyword_search k ON d.id k.id
s.id k.id
rrf_score
LIMIT ;
📊 Summary: Why Simplicity Wins
- Single Backup & Restore: Run
pg_dumpand your data, relational schema, and vector embeddings are backed up in one consistent snapshot. - Unified Role-Based Access Control: Security policies apply equally to embeddings and text chunks.
- Lower Infrastructure Bill: No separate cloud cluster fees or network egress costs.
Before adding another piece of infrastructure to your stack, see how far Postgres can take you. You will be amazed at what modern pgvector can handle.