Why We Chose pgvector Over a Dedicated Vector Database

When building the architecture for Expert RAG, one of our earliest infrastructure decisions was where to store and index our document vector embeddings. Specialized vector databases like Pinecone, Qdrant, and Weaviate are heavily marketed in the AI space, but for domain-specific expert sites, adding a dedicated vector database introduces unnecessary complexity and cost.

Here is why PostgreSQL with the pgvector extension was the clear winning architectural choice.

1. Zero Distributed State & Elimination of Dual-Writes

In a typical system pairing a relational database with an external vector store, every document chunk operation requires a distributed transaction:

1. Insert metadata in SQL database
2. Generate vector embedding
3. Insert vector in Pinecone / Qdrant
4. Handle partial failures and drift if step 3 fails

With pgvector, your document metadata (documents), chunk text (chunks), and vector embeddings (embedding vector(1536)) live in the exact same database table.

CREATE TABLE chunks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    embedding vector(1536)
);

An ingestion or deletion is an atomic SQL transaction. If a row rolls back, the vector embedding rolls back with it. No background reconcilers, no orphan vectors, and zero risk of state drift.

2. Shared-Nothing Multi-Tenancy

Expert RAG enforces strict domain isolation. Each vertical site operates on its own dedicated PostgreSQL instance (via Supabase or Railway).

If we used a managed vector service like Pinecone, running separate indices per domain or managing namespace access control lists would scale up monthly subscription tiers rapidly. By using pgvector, our vector store costs are $0 extra because vectors reside inside the existing Postgres database.

3. Combined Relational & Vector Search

Real-world RAG queries rarely perform unconstrained similarity searches. You frequently want to filter by document status, access levels, or vertical-specific tags:

SELECT c.content, 1 - (c.embedding <=> $1) AS similarity
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.status = 'indexed'
ORDER BY c.embedding <=> $1
LIMIT 5;

Postgres performs this filtering and vector comparison in a single query execution plan using standard HNSW or IVFFlat indices.

When Does pgvector Hit Its Limit?

pgvector scales effortlessly up to millions of vectors per table when tuned with appropriate HNSW index parameters (m=16, ef_construction=64). For domain-specific AI sites containing tens of thousands of specialized reference documents, pgvector provides sub-20ms query performance without adding $70+/month per instance in third-party database bills.

By keeping our tech stack simple, we passed those performance and cost savings directly to operators.