Vector DBs at Scale — pgvector, Milvus, HNSW/IVF

Why this session matters

Part of a 36-topic learning series on engineering, ML, and LLM systems. Each session is a 90-minute deep-dive on one topic — designed so anyone can pick it up cold. Every two topics are followed by a revision session with recall prompts and hands-on drills.

Session 24 · DE track 🧭

Once you understand embeddings (Session 15) and RAG (Session 22), the next question is: how do you index and query 100M+ vectors with p99 < 50ms? This session covers ANN algorithms (HNSW, IVF, PQ, ScaNN), when to use pgvector vs Milvus vs Pinecone vs Qdrant, hybrid search (BM25 + vector), filtering, and cost/latency tradeoffs at scale.

Pre-read (30 min before session)

Watch 1–2 of these before the deep-dive:

• pgvector Deep Dive — Supabase
• HNSW Algorithm — James Briggs
• Vector Databases Compared — Pinecone

Then skim the pgvector README.

Deep-dive (90 min)

1. ANN algorithms (30 min)

Brute force baseline (O(N) per query — fine to ~10k vectors). IVF (inverted file, k-means clusters, search top-nprobe clusters). HNSW (hierarchical navigable small world, layered graph, O(log N) — the default for most workloads). PQ (product quantization, compression for memory-bound). ScaNN (Google, tree + quantization hybrid). Recall/latency/memory tradeoffs.

2. pgvector deep (20 min)

Postgres extension. Index types: ivfflat (needs tuning lists), hnsw (PG16+, better recall). Operator classes: L2, inner product, cosine. Hybrid queries (vector + WHERE filter — index-first vs filter-first). Sizing guidance: <10M vectors → pgvector fine; >10M → dedicated store.

3. Dedicated vector stores (20 min)

Milvus (open source, HNSW/IVF/DiskANN, sharding). Qdrant (Rust, filterable HNSW). Pinecone (managed, no-ops). Weaviate (built-in embeddings). When to pick which: scale, latency, hybrid needs, ops budget.

4. Production patterns (20 min)

Hybrid search (BM25 + vector via RRF or weighted sum). Filtering with high selectivity (pre-filter vs post-filter — index matters). Metadata indexing (payload filters). Reindexing without downtime. Sharding by tenant. Cost modeling: RAM-resident vs disk-resident vectors.

Reading list

• Milvus docs — https://milvus.io/docs
• Qdrant blog — vector search deep dives
• Missing Manuals for Vector DBs — Pinecone learning center

Research links

• HNSW original paper (Malkov & Yashunin 2016)
• Vector DB benchmarks — ANN-Benchmarks.com

Hands-on drill

Load 100k Wikipedia article embeddings into pgvector. Build an HNSW index. Query top-10 by cosine similarity with a metadata filter (WHERE lang = 'en'). Measure p50/p99 latency. Then repeat with ivfflat — compare recall @ 10.

Post-session checklist

• Can you explain how HNSW achieves O(log N) search in 60 seconds?
• Can you explain when pgvector is enough vs when you need Milvus in 60 seconds?
• Can you explain hybrid search combining BM25 + vector in 60 seconds?
• Did you complete the hands-on drill above?
• Did you write 3 flashcards for tomorrow's recall?
• What's the one thing you'd want to revisit in the next revision session?

What's next

Session 25 continues the series. See the hub page for the full sequence and revision pattern.