Key takeaway: Dedicated vector databases solve a scale problem most applications do not have, at the cost of a consistency problem every application does have.
The Reflex and Its Cost
Building semantic search starts with a search for “vector database”, produces a list of specialised systems, and ends with one deployed alongside the existing database.
That decision introduces a second datastore holding a derived copy of your data. Now every write must reach two systems, and the interesting question becomes what happens when the second write fails. A record deleted from Postgres but still present in the vector index will be retrieved and shown to users. A document updated in Postgres with a stale embedding will be retrieved for the wrong queries.
Dual-write consistency is a genuinely hard distributed systems problem, and it arrives on day one.
What pgvector Actually Handles
Postgres with the pgvector extension stores embeddings as a column type and supports approximate nearest neighbour search through HNSW indexes.
| Corpus size | pgvector | Dedicated vector DB |
|---|---|---|
| Under 100k | Excellent | Unnecessary overhead |
| 100k – 1M | Good with HNSW tuning | Marginal gain |
| 1M – 10M | Workable, needs care | Meaningful advantage |
| Over 10M | Struggles | Clearly appropriate |
The decisive advantage below a million vectors is not performance — it is that the embedding lives in the same row as the record. One transaction updates both. There is no synchronisation, no reconciliation job and no possibility of divergence.
It also lets you filter and join in SQL. “Semantically similar documents belonging to this tenant, created in the last thirty days, excluding archived” is one query with a WHERE clause. Achieving the same in a separate vector store means either replicating metadata into it or over-fetching and filtering in application code, both of which degrade result quality.
Signals It Is Time to Move
Migrate when you observe real constraints rather than anticipated ones:
- Index build time interfering with normal database operation
- p99 search latency exceeding your budget after HNSW parameter tuning
- Corpus growth past several million vectors with high write throughput
- A genuine need for features Postgres lacks — multi-vector documents, sophisticated hybrid ranking, per-namespace isolation at scale
Each of these is measurable. None of them is “we might grow”.
Getting pgvector Right
Use HNSW rather than IVFFlat unless memory is severely constrained. Set m around 16 and ef_construction around 64 as a starting point, then raise ef_search at query time until recall meets requirements.
Store the embedding model name and version alongside each vector. Changing embedding models invalidates every existing vector, and without version tracking you cannot tell which rows need recomputation.
The Bottom Line
Start with pgvector in the database you already run. Move to a dedicated system when a measured limit forces you to, and recognise that what you are buying is scale in exchange for a synchronisation problem.
