pgvector or a dedicated vector database — how do you make that call for a new product?
Quick Answer
Default to pgvector when vectors live next to relational data you already run: one system, real transactions, joins with business tables, and Postgres ops you know. Move to a dedicated engine when scale (tens of millions of actively-updated vectors), heavy filtered search, or recall/latency SLOs outgrow it. It's a boring-technology decision, not a benchmark contest.
Detailed Answer
The case for pgvector: your chunks' metadata is probably already relational (users, tenants, documents); keeping vectors in Postgres gives ACID upserts with the source row, SQL joins instead of application-side stitching, one backup/HA/monitoring story, and zero new infrastructure. Well-tuned pgvector HNSW serves single-digit-millisecond queries at low-millions scale — for most RAG products the embedding call dominates end-to-end latency anyway. The case for dedicated engines: native filtered ANN (filter-aware index traversal rather than pre/post-filtering), horizontal sharding/replication designed for vectors, quantization options, and better behavior under heavy vector churn — pgvector HNSW rebuilds hurt once you're rewriting millions of embeddings regularly (rule-of-thumb pain threshold around 3-4M actively-rewritten vectors). Decision inputs to name: corpus size and growth, update/delete rate, filter selectivity and cardinality, recall SLO (measure on your data — vendor benchmarks don't transfer), multi-tenancy isolation needs, and team ops maturity. Managed-vs-self-hosted is orthogonal: Pinecone-style serverless buys zero-ops at the cost of tunability (it exposes few index knobs) and per-query economics; Qdrant/Weaviate/Milvus self-hosted buy control at the cost of running them.
Code Example
-- pgvector: vectors joined with business data in one query SELECT c.text, 1 - (c.embedding <=> :qvec) AS score FROM chunks c JOIN docs d ON d.id = c.doc_id WHERE d.tenant_id = :tenant AND d.deleted_at IS NULL ORDER BY c.embedding <=> :qvec LIMIT 8;
Interview Tip
Anchor on 'vectors want to live near the data they describe' and give a concrete graduation trigger (heavy filtered search, millions of actively-rewritten vectors) — decision frameworks beat product loyalty in interviews.