**Correct: option A.** pgvector is the right choice when three things are true: (1) Postgres is already in your stack, (2) the corpus is under ~10M vectors, and (3) you want transactional consistency between vectors and relational data.
**Why these three.**
- *Postgres already running.* The operational win of pgvector is that you don't add a new system. One fewer database to monitor, secure, back up, and credential-manage. If you weren't running Postgres, the math changes; standing up Postgres just for pgvector is rarely the right move.
- *Sub-10M vectors.* HNSW in pgvector handles this scale comfortably with sub-50ms p99 on commodity hardware. Past ~50M on a single Postgres instance you start running into shared-buffer pressure and WAL overhead.
- *Transactional consistency.* You can JOIN vector results with relational tables in a single transaction. No dual-write problem, no eventual consistency between vector store and metadata store. This is pgvector's killer feature for B2B SaaS where 'is this document in this tenant's allowed list' must be enforced atomically with the vector query.
**Why the other options are wrong.**
- *B (prototypes only).* This is the 2022 take. By 2026, pgvector serves millions of vectors in production at many SaaS companies. Dismissing it for any production load above 100k is uninformed.
- *C (billion vectors on default Postgres).* HNSW does scale logarithmically per query, but the whole index has to fit in RAM, and a billion 1536-dim vectors is 6+ TB. Default-tuned Postgres can't handle that.
- *D (faster than every dedicated DB).* Postgres adds overhead (MVCC, WAL, shared buffers, transaction isolation) that dedicated vector DBs skip. pgvector is competitive at sub-10M, not universally faster.
**Correct answer: A.**
pgvector occupies a real, defensible niche in the 2026 vector-DB landscape. The answer is not 'use Pinecone for everything' nor 'pgvector forever' but a clean set of conditions under which each is right.
**The pgvector sweet spot.**
1. *Postgres is already running.* pgvector's primary value is operational simplicity: one database, one backup strategy, one set of credentials. Adding a dedicated vector DB is a real ops tax (security review, monitoring, runbook, on-call). If Postgres is in the stack, pgvector is the lowest-friction path to vector search.
2. *Corpus under ~10M vectors (or up to ~50M with HNSW on big hardware).* pgvector with HNSW handles this comfortably. Beyond that the WAL pressure, shared-buffer competition with normal Postgres queries, and lack of multi-node sharding start to bite.
3. *Transactional consistency with relational data.* You can run a vector search and a JOIN against relational tables in the same SELECT, and you can write to both inside the same transaction. No dual-write problem, no consistency drift between the vector store and the rest of your data.
**Why the crossover lands around 10-50M.**
- HNSW must hold the graph and the full vectors RAM-resident. 10M float32 1536-dim vectors are ~60 GB; with graph overhead ~80 GB. That fits a single 128 GB Postgres node alongside other workloads.
- Past 50M, you'd need 300+ GB of RAM dedicated to the vector index, which competes with Postgres' shared_buffers for normal SQL workloads.
- Postgres' single-writer architecture means scaling beyond one box requires sharding at the application layer (Citus or hand-rolled). Dedicated vector DBs ship this natively.
- WAL pressure from continuous vector inserts (each insert touches the HNSW graph) becomes a real bottleneck at high write rates.
**Why the distractors are wrong.**
- *B.* The 'prototypes only' framing is two years out of date.
Quick reference
TL;DR
pgvector wins when Postgres is already running, the corpus is under ~10M vectors, and transactional consistency with relational data matters. It stretches to ~50M with HNSW on big hardware.
Imagine you already own a big general purpose toolbox (Postgres). pgvector is a vector-search drawer you can add to that toolbox. If you only need to search a small to medium collection of vectors, it is silly to buy a separate, specialized vector-search toolbox just for that; using the drawer you already have is simpler. But if your collection grows huge, the general purpose toolbox starts dragging the search drawer down, and a dedicated vector-search toolbox becomes worth its own keep.
Everything you need to truly understand this topic: intuition, mechanics, step by step explanation, code, formulas, and worked example. Click to expand.
Everything you need to truly understand this topic: intuition, mechanics, step by step explanation, code, formulas, and worked example.
Corpus past 50-100M vectors Postgres' single-writer architecture and shared-buffer model do not scale to this size cleanly; dedicated vector DBs pull ahead.
Postgres is not already in the stack Standing up Postgres just for pgvector loses the operational win that makes it attractive in the first place.
Sub-millisecond p99 required at high QPS Postgres adds enough overhead that hitting sub-ms reliably is harder than with a dedicated vector DB tuned for it.
Quick scan · 2–4 min
Everything important, quickly.
3 min: pgvector's operational win + the three sweet-spot conditions + the 10-50M crossover number + why each distractor is wrong.
Side-by-side
Corpus size
Verdict on pgvector
Under 1M
Almost always right; ANN is barely needed
1M-10M
Sweet spot; pgvector with HNSW is the production answer if Postgres exists
10M-50M
pgvector still viable on big hardware; the call depends on RAM headroom
50M-100M
Mostly migrate to a dedicated vector DB unless you specifically tune Postgres for this
100M+
Dedicated vector DB; pgvectorscale is the last hold-out option
Real-world examples
Real products, models, and research that use this idea.
1Supabase ships pgvector in their managed Postgres and many production SaaS products run RAG against it.
2Neon (serverless Postgres) supports pgvector and markets it for B2B RAG use cases.
3AWS RDS for Postgres and Aurora ship pgvector for use cases where the corpus fits the sweet spot.
4pgvectorscale (Timescale) extends pgvector with disk-based HNSW for larger corpora while staying on Postgres.
5Notion's public engineering posts describe their Postgres + pgvector path for the early phases of their AI features.
Interviewer follow-ups
What an interviewer would ask next. Try answering before peeking at the approach.
QWhat does pgvectorscale add that base pgvector cannot do?
Sign in or go Pro to see the approach for this follow-up.
QHow does pgvector handle transactional updates to embeddings?
Sign in or go Pro to see the approach for this follow-up.
QWhat is the right migration path from pgvector to Pinecone or Qdrant when you outgrow it?
Sign in or go Pro to see the approach for this follow-up.
Don't say this
Red flags and common mistakes that signal junior thinking. Click to expand.
Most common mistake
Either dismissing pgvector as 'just a prototype' (it serves millions of vectors in production at many companies) or believing it can scale to billions on a single Postgres node (it cannot, regardless of how the HNSW math looks).