pgvector vs Dedicated Vector Databases

pgvector is a Postgres extension that adds a vector column type and ANN index support. For teams already running Postgres, it removes a whole class of operational work: no second datastore, same transactions, same auth, same backups.

What you get

• vector(1536) column type.
• <-> (L2), <=> (cosine), <#> (inner product) distance operators.
• HNSW and IVFFlat indexes.
• Standard SQL for everything else — joins against tenants, permissions, timestamps, whatever you have.

A single Postgres box comfortably holds 5-20M vectors with acceptable recall and p95 under 100ms. That covers the majority of production RAG apps.

Where pgvector wins

• No new service. Supabase, Neon, RDS all ship it. Your app already talks to Postgres.
• Real transactions. Insert a document row and its embedding atomically.
• Metadata filters are free. WHERE tenant_id = $1 AND category = $2 composes with vector search naturally.
• Cheap. You're paying for Postgres anyway.

Where dedicated stores win

• Past 50M vectors. HNSW memory requirements get painful; IVFFlat recall gets shaky. Pinecone and Weaviate are designed for this regime.
• High write throughput. Vector writes block on HNSW index updates in pgvector. Dedicated stores batch and tier.
• Cold-storage economics. Turbopuffer's object-storage backend is an order of magnitude cheaper than keeping vectors hot in Postgres RAM.
• Purpose-built hybrid search. Weaviate bundles BM25 and vectors with re-ranking; pgvector leaves that to you.

Failure modes to watch

• IVFFlat recall collapse after inserts. You built the index on 100k rows; now you have 2M. Recall silently drops to 60%. Rebuild the index.
• HNSW memory pressure. Index lives in shared_buffers. Underprovision RAM and queries fall off a cliff.
• ORM pitfalls. Many ORMs can't emit the <=> operator cleanly. Drop to raw SQL for the vector clause.

When NOT to use pgvector

You don't run Postgres and don't want to. Starting from scratch just to get vector search is backwards — use a managed vector store and skip the database-admin overhead.