Indexing → Vector store choice
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:
| Need | pgvector (Postgres) | Qdrant / Weaviate / Milvus | Pinecone / Turbopuffer (managed) | Elasticsearch / OpenSearch / Vespa |
|---|---|---|---|---|
| Native filtered ANN | pgvector has HNSW + WHERE (planner-dependent; improving) | ✅ filter-aware HNSW, payload indexes | ✅ metadata filters | ✅ filters + BM25 in one query |
| Hybrid (BM25 + vector) in one engine | Postgres FTS (weak BM25) + pgvector; manual fusion | Qdrant sparse vectors / Weaviate hybrid / Milvus sparse | Pinecone sparse-dense; Turbopuffer BM25 | ✅ first-class |
| Joins with app data / transactional writes | ✅ | ❌ (separate) | ❌ | limited |
| Multi-tenancy | schemas/row filters | collections / payload partitions / tenant sharding (Weaviate native) | namespaces | indices/aliases |
| Scale (vectors per node) | ~10M comfortable | 100M+ with sharding | managed | 100M+ |
| Quantisation / disk indexes | halfvec, int8 (newer) | ✅ SQ/PQ/binary, mmap | managed | ✅ |
| Ops | you already run it | new stateful service (or cloud) | none | heavy but familiar to many teams |
| Cost model | your Postgres | infra or cloud tier | per-pod / per-GB / per-query | infra 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)
- 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.
- Shortlist 2–3; load a realistic sample (≥ 10% of target scale) with real payloads and filters.
- Benchmark what matters: filtered recall vs flat (index types: Flat, HNSW, IVF-PQ), p95 at concurrency, ingest throughput, delete/compaction behaviour, memory.
- Test the ACL path end-to-end (tenant / ACL capture at ingest): tenant + group filters at realistic selectivity.
- Test operations: backup/restore, version upgrade, rebuild, shard rebalance, failover.
- Abstract behind your
Retrieverinterface (build vs buy) and keep the chunk registry outside the store, so the store is replaceable. - 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
-
Retrieverabstraction; 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
| Metric | Why |
|---|---|
| Filtered p95 latency and recall | Real workload health |
| Zero-result rate on filtered queries | Filter/k tuning |
| Memory per 1M vectors; $ per 1M vectors | Cost |
| Write/upsert lag; tombstone ratio | Freshness and hygiene |
Related
- Index types, Filtered search
- Chunk metadata — payload design
- Versioning, ACL — consistency and isolation
- Build vs buy
- Hybrid search — engine capabilities