Indexing → Vector store choice

How to Choose a Vector Database for RAG: pgvector vs Qdrant vs Milvus

What it is

The vector store holds (id, vector, payload) and answers "top-k nearest to this vector, subject to these filters". Beyond that, the products differ on what production actually needs:

Needpgvector (Postgres)Qdrant / Weaviate / MilvusPinecone / Turbopuffer (managed)Elasticsearch / OpenSearch / Vespa
Native filtered ANNpgvector has HNSW + WHERE (planner-dependent; improving)✅ filter-aware HNSW, payload indexes✅ metadata filters✅ filters + BM25 in one query
Hybrid (BM25 + vector) in one enginePostgres FTS (weak BM25) + pgvector; manual fusionQdrant sparse vectors / Weaviate hybrid / Milvus sparsePinecone sparse-dense; Turbopuffer BM25✅ first-class
Joins with app data / transactional writes✅❌ (separate)❌limited
Multi-tenancyschemas/row filterscollections / payload partitions / tenant sharding (Weaviate native)namespacesindices/aliases
Scale (vectors per node)~10M comfortable100M+ with shardingmanaged100M+
Quantisation / disk indexeshalfvec, int8 (newer)✅ SQ/PQ/binary, mmapmanaged✅
Opsyou already run itnew stateful service (or cloud)noneheavy but familiar to many teams
Cost modelyour Postgresinfra or cloud tierper-pod / per-GB / per-queryinfra or cloud

Decision inputs: corpus size and growth; filter selectivity and cardinality; hybrid requirement; ACL model; consistency needs (do chunk writes need to be transactional with document metadata?); latency SLO; team ops capacity; residency.

How to implement (the decision)

  1. Write the requirements: N vectors now/in 2 years; dims; QPS; p95; filters (fields, cardinality, selectivity); hybrid yes/no; ACL model; write rate; delete semantics; residency; budget.
  2. Shortlist 2–3; load a realistic sample (≥ 10% of target scale) with real payloads and filters.
  3. Benchmark what matters: filtered recall vs flat (index types: Flat, HNSW, IVF-PQ), p95 at concurrency, ingest throughput, delete/compaction behaviour, memory.
  4. Test the ACL path end-to-end (tenant / ACL capture at ingest): tenant + group filters at realistic selectivity.
  5. Test operations: backup/restore, version upgrade, rebuild, shard rebalance, failover.
  6. Abstract behind your Retriever interface (build vs buy) and keep the chunk registry outside the store, so the store is replaceable.
  7. Pick, document the reasons, and revisit at 5× scale.

Small example

# The same retrieve() against two backends — the app never knows which.

# --- pgvector: filtered ANN + join on document table for current version and ACL ---
PG_SQL = """
SELECT c.chunk_id, c.text, 1 - (c.embedding <=> %(q)s::vector) AS score
FROM chunks c JOIN docs d ON d.doc_id = c.doc_id AND d.current_version = c.version
WHERE c.tenant_id = %(tenant)s
  AND c.acl_groups && %(groups)s::text[]
ORDER BY c.embedding <=> %(q)s::vector
LIMIT %(k)s;
"""
def retrieve_pg(conn, qvec, caller, k=30):
    conn.execute("SET hnsw.ef_search = 120")          # tune for filtered recall
    return conn.execute(PG_SQL, {"q": list(qvec), "tenant": caller.tenant_id, "groups": list(caller.groups), "k": k}).fetchall()

# --- Qdrant: payload-indexed filters, is_current in payload ---
from qdrant_client import QdrantClient, models
qc = QdrantClient(url="http://qdrant:6333")
def retrieve_qdrant(qvec, caller, k=30):
    flt = models.Filter(must=[
        models.FieldCondition(key="tenant_id", match=models.MatchValue(value=caller.tenant_id)),
        models.FieldCondition(key="is_current", match=models.MatchValue(value=True)),
        models.FieldCondition(key="acl_groups", match=models.MatchAny(any=list(caller.groups))),
    ])
    hits = qc.search(collection_name="chunks_v2", query_vector=qvec, query_filter=flt, limit=k,
                     search_params=models.SearchParams(hnsw_ef=128))
    return [(h.id, h.payload["text"], h.score) for h in hits]

# one-time: payload indexes so filters don't scan
# qc.create_payload_index("chunks_v2", "tenant_id", models.PayloadSchemaType.KEYWORD)
# qc.create_payload_index("chunks_v2", "acl_groups", models.PayloadSchemaType.KEYWORD)
# qc.create_payload_index("chunks_v2", "is_current", models.PayloadSchemaType.BOOL)

Pros

  • pgvector: one database, transactions across docs/chunks/ACLs, SQL joins, familiar ops, cheap at moderate scale.
  • Dedicated vector DBs: filter-aware ANN, quantisation, sharding, multi-tenancy features, higher scale.
  • Managed: no ops; elastic.
  • Search engines: hybrid + aggregations + mature ops in one place.

Cons

  • pgvector: filtered-ANN behaviour depends on the planner (post-filter surprises), scale ceiling per node, index build time, memory.
  • Dedicated: another stateful system; no joins (denormalise everything, metadata on every chunk); eventual consistency on payload updates.
  • Managed: cost at scale; vendor lock on features/IDs; residency limits; opaque tuning.
  • Search engines: heavy; vector performance behind specialists; complex cluster tuning.

When to use which

  • pgvector: < ~10–20M vectors, Postgres already central, ACLs need joins/transactions, small team.
  • Qdrant/Weaviate/Milvus: tens of millions+, rich filters, quantisation, self-host or cloud, multi-tenant at scale.
  • Pinecone/Turbopuffer: want zero ops, accept the cost model and residency options.
  • Elasticsearch/OpenSearch/Vespa: hybrid + aggregations + already operating the cluster.

When NOT to use

  • Don't choose by leaderboard QPS on unfiltered benchmarks; your queries are filtered.
  • Don't put bulky payload (full text, bboxes, table HTML) in any vector store — keep it in a document store (metadata on every chunk).
  • Don't pick a store that can't do native filtered ANN if you're multi-tenant (filtered search).

Production at scale

Denormalise for the store you chose. Vector DBs don't join: copy document-level fields (title, current-version flag, dates, ACL) onto chunk payloads and keep them in sync on document updates (document versioning). With pgvector, joins let you avoid some of this — but joins under ANN ordering can defeat the index; test the plan.

Consistency model. Chunk upsert + document pointer flip must be atomic or ordered (document versioning). Postgres gives transactions; vector DBs give per-point atomicity — design the ordering and the query-time safety net ("one hit per doc_id, max version").

Multi-tenancy layout. Shared collection + mandatory tenant filter (cheap, one missed filter = leak) vs collection/namespace per tenant (isolation, easy delete, operational sprawl at thousands of tenants) vs the store's native tenant sharding (Weaviate, Turbopuffer namespaces). Large/regulated tenants → physical; long tail → logical; both with the filter (tenant / ACL capture at ingest).

Backup, restore, and rebuild. The vector index is derived data: with a chunk registry you can always rebuild (versioning & migration). Still back up for RTO; test restore quarterly.

Upgrades. Vector DB versions change index formats and filtering semantics; upgrade in staging with the recall canary and the filtered-latency benchmark (index types: Flat, HNSW, IVF-PQ).

Cost at scale: memory-resident vectors are the dominant cost; quantisation and dimension choices (model choice) matter more than store choice. Managed per-query pricing can exceed self-host at high QPS — compute it.

Observability: per-collection/shard p95, recall canary, filtered zero-result rate, write queue depth, memory, tombstones.

Prod checklist

  • Requirements written: scale, filters, hybrid, ACL, consistency, latency, residency, ops capacity
  • Benchmark with realistic payloads and filtered queries at concurrency
  • ACL path tested end-to-end
  • Retriever abstraction; chunk registry outside the store
  • Payload indexes on filter fields; bulky fields in a document store
  • Multi-tenant layout decided; mandatory filter in shared collections
  • Backup/restore tested; rebuild-from-registry procedure documented
  • Upgrade path staged with recall/latency benchmarks

Metrics to watch

MetricWhy
Filtered p95 latency and recallReal workload health
Zero-result rate on filtered queriesFilter/k tuning
Memory per 1M vectors; $ per 1M vectorsCost
Write/upsert lag; tombstone ratioFreshness and hygiene