Vector Databases and pgvector in Production
Definition
A vector database stores high-dimensional embeddings and answers approximate nearest-neighbour (ANN) queries — “which stored vectors are closest to this one?” It is the retrieval half of most RAG systems.
Two options exist: bolt vector search onto an existing database (pgvector, a PostgreSQL extension), or run a purpose-built engine (Pinecone, Weaviate, Qdrant, Turbopuffer, OpenSearch k-NN). Alex Jacobs’ argument, after building a production system on pgvector: the “you already have Postgres, just add the extension” story is true in a demo and misleading at scale.
Core Ideas
Index choice: two options, no good one
pgvector gives you IVFFlat and HNSW. “HNSW is newer and better” is technically true and unhelpful.
| IVFFlat | HNSW | |
|---|---|---|
| Structure | Partitions vector space into clusters; searches the nearest ones | Multi-layer navigable graph |
| Build memory | Lower | Much higher — 10+ GB on a few million vectors |
| Build time | Faster | Slow, hours on large datasets |
| Recall | Disappointing depending on data distribution | Better on most datasets |
| Tuning | Must pick number of lists upfront (rows / 1000 is a starting point at best) | Graph parameters, but no upfront cluster count |
| Drift | New vectors join existing clusters; clusters never rebalance without a full rebuild | Incremental insert works, but each one traverses and locks the graph |
The memory cost is not theoretical — an HNSW build will take down the production database it is running on.
Real-time search is basically impossible
The common requirement is “user uploads a document, it is searchable immediately.” Neither index gives you that cleanly:
- No index — inserts are fast, search is a sequential scan. Fine at thousands, seconds at hundreds of thousands, hopeless at millions.
- IVFFlat — cluster assignments reflect the data distribution at build time. As data skews, search quality degrades, and the fix is a periodic rebuild taking hours. What happens to inserts during the rebuild is your problem.
- HNSW — incremental insertion works, but under heavy write load graph lock contention slows both reads and writes.
The workarounds are all real and all compromises: staging table plus atomic index swap (new data invisible in the gap), dual indexes (double memory and write cost), build on a replica then promote, accept eventual consistency, or over-provision RAM well past the working set. Postgres has no good way to throttle a memory-hungry index build while serving queries.
Pre- vs post-filtering: you become the query planner
The moment vectors carry metadata (status = 'published', user_id, category, a date range), the planner has to decide whether to filter first or search first. This is not an implementation detail — it is the difference between 50 ms and 5 s, and between good results and wrong results.
- Pre-filter wins when the filter is highly selective (1,000 of 10M). It loses when the filter is permissive — you are still scanning millions of vectors.
- Post-filter breaks silently.
LIMIT 10finds the 10 nearest neighbours, then filters; if only 3 pass, the user gets 3 mediocre results and never learns that hundreds of better matches sat just outside k=10. Oversampling toLIMIT 100costs distance calculations and is still a guess.
With several filters the strategy space widens (all-pre, all-post, hybrid, and in which order). Postgres’s cost model was not built for vector similarity, so the plan will likely be suboptimal — and ANALYZE cannot help, because it can count rows matching user_id but knows nothing about how clustered those vectors are in embedding space, which is what actually drives performance. See Query Optimization for the general planner-statistics problem this is a special case of.
Dedicated engines solved this: adaptive pre/post selection by estimated selectivity, explicit strategy modes, filtered-HNSW indexes, and statistics tracked for vector operations specifically.
Hybrid search: build it yourself
Postgres has excellent full-text search. pgvector has vector search. Combining them is entirely on you — weighting similarity against text relevance, normalising two incompatible score scales, tuning the balance, probably implementing Reciprocal Rank Fusion. Many dedicated engines ship this.
pgvectorscale doesn’t close the gap
Timescale’s pgvectorscale adds StreamingDiskANN (more memory-efficient), incremental index builds, and better filtering. It helps — and it is also an admission that vanilla pgvector isn’t production-sufficient. It is another extension to manage and upgrade, and it isn’t available on AWS RDS, so using it means running your own Postgres. The “keep it simple” argument keeps getting less simple.
The honest reframe
The question is not “should I use pgvector?” It is “am I willing to own the operational complexity of vector search inside Postgres?” For a team with database expertise that needs tight transactional integration, yes. For many teams — especially small ones — a managed vector database is actually the simpler option, and often cheaper once you price in over-provisioned RAM, query-tuning time, index-rebuild operations, and the features not built while fighting the database. (Turbopuffer starts around $64/month.)
Five things worth knowing up front: index management is hard, filtered query planning matters, real-time indexing always costs something, the enthusiastic blog posts are lying by omission, and managed offerings exist for a reason.
Relationships
- Just Use Postgres — the general principle this is the strongest counter-case to; see its Contradictions section
- Retrieval-Augmented Generation (RAG) — why you are storing embeddings at all
- Query Optimization — indexing and planner statistics, which vector search breaks
- NoSQL and Document Databases — the other specialised-store-vs-Postgres decision
- System Design — datastore choice sized to real load, not imagined load
- AI Engineering Pitfalls — demo-to-production gaps in AI features generally
- Databases — parent topic
References
- The Case Against pgvector — Alex Jacobs, 2025-10-29