Vector Database Selection & Indexing (HNSW vs IVF, pgvector vs Qdrant)
Deep comparison of HNSW vs IVF-PQ indexing, pre-filtering vs post-filtering metadata, and choosing between pgvector, Qdrant, and Pinecone.
Architectural Summary (AEO)
For under 5 million vectors alongside existing relational data, PostgreSQL with pgvector (HNSW + halfvec) eliminates a separate database hop and guarantees transactional ACLs. Above 10M+ vectors or high-QPS filtered search, dedicated engines like Qdrant or Pinecone provide payload-aware HNSW graphs and disk-backed quantization.
Reference System Flow
Query Vector + Filter { tenant_id: "acme", role: "admin" }
│
├──► Post-Filtering (BAD): Top-10 HNSW -> Filter -> Only 1 result left!
└──► Pre-Filtered / Filtered HNSW (GOOD): Traverse graph edges matching ACLs -> Full Top-101. HNSW vs IVF-PQ Indexing Mechanics
HNSW (Hierarchical Navigable Small World) builds a multi-layer proximity graph offering > 97% recall at single-digit millisecond latency, at the cost of ~1.5x–2x RAM overhead. IVF-PQ clusters vectors into Voronoi cells and compresses residuals with Product Quantization, trading slight recall for 4x–8x lower memory at 50M+ scale.
2. Why Metadata Pre-Filtering Breaks Naive Indexes
If a tenant owns only 1% of a shared vector index, post-filtering a global `topK=10` search often returns 0–1 valid rows. Modern engines (Qdrant's filterable HNSW, pgvector 0.7+ iterative index scans) integrate metadata bitmaps directly into graph traversal.
3. pgvector vs Qdrant vs Pinecone Trade-Offs
pgvector wins on operational simplicity and ACID joins with user/permission tables. Qdrant excels at high-throughput filtered hybrid search with Rust performance and binary/scalar quantization. Pinecone shines for zero-ops serverless scaling.
pgvector HNSW Index with Half-Precision (16-bit) Storage
-- Reduces vector index RAM by 50% with <0.5% recall difference
CREATE INDEX idx_chunks_embedding_hnsw
ON knowledge_chunks
USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 16, ef_construction = 128);
-- Tune search-time accuracy per query
SET LOCAL hnsw.ef_search = 100;Architectural Trade-Offs
| Engine | Hybrid BM25 | ACID Joins | Sweet Spot Scale |
|---|---|---|---|
| PostgreSQL + pgvector | Via tsvector + RRF | Native SQL JOINs | < 5M–10M vectors |
| Qdrant | Native Sparse + Dense | Payload filtering only | 5M–200M+ vectors |
| Pinecone | Sparse-Dense serverless | Metadata filtering only | Zero-ops elastic scale |
Production Benchmarks & Field Observations
- pgvector `halfvec` (float16) cuts HNSW index memory from ~6.4GB to ~3.2GB per 1M 1536d vectors.
Related Commercial Services
Engineering FAQs
What HNSW parameters should we start with?
Start with `m = 16`, `ef_construction = 128`, and `ef_search = 64–100`. Increase `m` and `ef_search` only if golden-set recall lags.
Can we run hybrid search inside PostgreSQL without Elasticsearch?
Yes. Combine `pgvector` HNSW cosine distance with PostgreSQL `tsvector` GIN full-text ranking in a single CTE using Reciprocal Rank Fusion.