S119 · Vector Databases — pgvector, HNSW, IVF

Module M14: LLMs & Applications · Session 119 of 130 · Track: LLM 🧭

What you'll be able to do after this session

• Explain Vector Databases to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S117 — RAG I — Chunking Strategies & Indexing

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · Vector databases in 5 minutes — Fireship — Fastest tour of what a vector DB is and why.
• 🎥 Deep dive (30–60 min) · HNSW & IVF explained — James Briggs / Pinecone — 45-min deep dive on the two ANN indexes that matter.
• 🎥 Hands-on demo (10–20 min) · pgvector hands-on — build a semantic search in Postgres — 10-min copy-paste demo you can run right now.
• 📖 Canonical article · pgvector official README — The canonical docs — SQL DDL, index types, and operators.
• 📖 Book chapter / tutorial · Pinecone — HNSW in depth — Best long-form explainer of HNSW, layer-by-layer.
• 📖 Engineering blog · Meta Engineering — FAISS: a library for efficient similarity search — The paper-quality blog post from FAISS's authors at Meta.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: The infra behind RAG.

A vector database is a database whose index is optimized for one operation: given a query vector, find the k most similar vectors from millions or billions, in milliseconds. Ordinary relational DBs can't do this natively because their B-tree indexes assume you can order the data — but "close in 1536-dimensional cosine space" has no useful ordering.

Doing this exactly takes O(n·d) — a full scan — which at 100M vectors × 1536 dims takes seconds per query. Nobody wants that. So vector DBs use Approximate Nearest Neighbor (ANN) indexes. Two families dominate:

1. HNSW (Hierarchical Navigable Small World) — build a graph where each vector is a node connected to a few close neighbors, arranged in layers like a subway map (few long-haul lines on top, dense local lines below). Query: enter at a top-layer entry point, greedily walk toward the query, drop a layer, repeat. Very fast (~1 ms), high recall, but memory-hungry (needs the whole graph in RAM).

2. IVF (Inverted File Index) — first cluster all vectors into nlist buckets (say 1024) via k-means. For a query, find the closest nprobe buckets, scan only those. Cheaper memory, tunable recall vs speed via nprobe. Usually combined with product quantization (PQ) to compress vectors 8–32× at some recall cost.

Gotcha to carry forever: ANN is approximate. You are trading recall (fraction of true nearest-neighbors you find) for speed. Every vector DB knob (HNSW's ef_search, IVF's nprobe) is a recall-latency knob. Publish your recall number to your team — if you don't measure it, you don't know it. Also: for < 1M vectors, a Postgres pgvector table on any laptop is production-ready. You don't need a fancy managed vector DB until you hit tens of millions of vectors or thousands of QPS.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Worked example — 10M chunks, 1536-dim embeddings, cosine similarity, target: p99 < 50 ms.

Full brute-force: 10M × 1536 × 4 bytes = 60 GB memory, ~200 ms per query on CPU. Fails the SLO and barely fits RAM.

Option A: HNSW. Index memory ≈ 1.5× vectors + graph overhead ≈ 90 GB. Build once (~1 hr for 10M). Query ~2 ms, recall@10 ≈ 0.98. Best latency, worst memory.

Option B: IVF with 4096 clusters, nprobe=16. Memory = raw vectors ~60 GB. Build ~30 min. Query ~10 ms, recall@10 ≈ 0.90. Turn nprobe up to 64 → recall 0.97, query 30 ms.

Option C: IVF-PQ with 8-bit PQ (16 subvectors, 256 centroids each) — vectors compressed to ~16 bytes each. Memory ≈ 160 MB total. Build ~1 hr. Query ~5 ms, recall@10 ≈ 0.85. Cheapest by 100×, some recall lost.

When to use what. < 1M vectors → pgvector's default HNSW is perfect and free. 1–100M vectors → pick HNSW if memory allows, IVF/IVF-PQ if not. > 100M vectors → distributed vector DB (Weaviate, Qdrant, Milvus, Vespa) or FAISS-on-GPU with sharding. Prototypes → don't worry about it, brute-force 100k vectors in NumPy is 20 ms.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Set up pgvector, insert vectors, query with both HNSW and IVFFlat — pure SQL, no Python client needed:

-- 1. Enable pgvector (Postgres 14+, install via 'apt install postgresql-16-pgvector' or Docker)
CREATE EXTENSION IF NOT EXISTS vector;
 
-- 2. Table with a 1536-dim column (OpenAI text-embedding-3-small size)
DROP TABLE IF EXISTS chunks;
CREATE TABLE chunks (
    id          BIGSERIAL PRIMARY KEY,
    source      TEXT,
    content     TEXT,
    embedding   vector(1536)
);
 
-- 3. Insert some vectors (in real life: from your embedding API)
INSERT INTO chunks (source, content, embedding) VALUES
  ('doc1', 'Refunds within 30 days',        '[0.12, 0.03, ...]'::vector),
  ('doc2', 'EU customers 14-day cooling',   '[0.09, 0.11, ...]'::vector),
  ('doc3', 'Digital goods non-refundable',  '[0.15, 0.02, ...]'::vector);
-- (populate ~100k rows via COPY or your ETL)
 
-- 4. Build an HNSW index (best for high-recall, more memory)
CREATE INDEX chunks_emb_hnsw ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- (or IVFFlat: cheaper memory, needs 'lists' ~= sqrt(row_count))
-- CREATE INDEX chunks_emb_ivf ON chunks
--   USING ivfflat (embedding vector_cosine_ops) WITH (lists = 316);
 
-- 5. Query: top-5 most similar chunks to a query embedding
--    Operator meaning:  <=> cosine distance,  <-> L2,  <#> inner product
SET hnsw.ef_search = 40;                     -- higher = more recall, slower
SELECT id, source, content,
       1 - (embedding <=> '[0.11, 0.04, ...]'::vector) AS cosine_similarity
FROM chunks
ORDER BY embedding <=> '[0.11, 0.04, ...]'::vector
LIMIT 5;
 
-- 6. Combine with metadata filter (this is where SQL destroys standalone vector DBs)
SELECT id, source, content
FROM chunks
WHERE source LIKE 'legal/%'
  AND created_at > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;
 
-- 7. Recall check: brute-force vs HNSW
--    Run the same query with SET enable_indexscan = off; and compare id lists.
EXPLAIN ANALYZE SELECT id FROM chunks ORDER BY embedding <=> '[...]'::vector LIMIT 10;

Checklist:

1. CREATE EXTENSION vector; should succeed once — otherwise install pgvector.
2. Index build time scales with ef_construction (higher = slower build, better recall).
3. ef_search at query time controls recall vs latency — start at 40, tune from there.
4. <=> returns distance (0 = identical), so ORDER BY … LIMIT naturally returns nearest first.
5. EXPLAIN ANALYZE should show Index Scan using chunks_emb_hnsw — if it shows Seq Scan, your index isn't being used (check op class matches: vector_cosine_ops for <=>).

Try this: load 1M random 1536-dim vectors and time queries with (a) no index, (b) IVFFlat with lists=1000, (c) HNSW m=16. Record recall by comparing the top-10 from (a) to top-10 from (b) and (c). You'll see the classic recall-latency-memory triangle in your own numbers.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Vector DBs are the youngest layer of the LLM stack — pgvector shipped its first prod-quality release in late 2022, Pinecone raised $100M in 2023, Qdrant / Weaviate / Milvus / Chroma all jockey for the same market. The technology is stabilizing; the vendor question is not.

War stories. (1) "We picked Pinecone for prototypes and now the bill is $80k/mo at 50M vectors." Managed vector DBs charge per-index memory. Migrating to self-hosted Qdrant or pgvector routinely saves 5–10×, at the cost of an ops burden. Rule: use managed for < 10M vectors + low ops team; self-host past that. (2) "We use HNSW but recall dropped after a big batch upsert." HNSW indexes can degrade after many deletes (tombstoned nodes still traversed). Periodic reindex or use of "soft-delete + rebuild nightly" is required. IVF handles updates worse — usually you rebuild the k-means clusters weekly. (3) "pgvector is 'slow'." Almost always the query planner chose a seq scan because the operator didn't match the index op class, or because a WHERE clause was too restrictive. Fix: match vector_cosine_ops ↔ <=>, and use a partial index for hot filters. (4) "Metadata filtering murders latency." Pre-filtering (filter first, then ANN over the subset) works well for high-selectivity filters (< 5% of rows); post-filtering (ANN first, then filter) works better for low-selectivity. Some DBs (Weaviate, Qdrant) let you pick. Vespa's inverted-index-first architecture wins here at scale. (5) "Embedding model changed → we have to re-embed 20 TB of data." Dimensionality mismatch means the new vectors literally can't share an index with old ones. Version your embeddings (embedding_v1, embedding_v2 columns) and roll traffic over gradually.

How top teams pick. Startups + apps < 10M vectors: pgvector on your existing Postgres — no new infra, transactional consistency, joins with metadata come free. Serious RAG shops 10–500M vectors: Qdrant / Weaviate / Milvus self-hosted, or Pinecone if you'd rather pay someone. Web-scale search (100M–10B vectors, hybrid dense+sparse, high QPS): Vespa (used by Spotify, Yahoo) or a custom FAISS pipeline. Rule of thumb: whatever you pick, benchmark on your embeddings and your queries — public benchmarks are worthless because recall depends on your data's manifold.

(e) Quiz + exercise · 10 min

1. Why can't a regular B-tree index efficiently answer "find the 10 nearest 1536-dim vectors"?
2. In one line each, describe HNSW and IVF.
3. What is product quantization (PQ) and what does it trade off?
4. Why is metadata filtering (WHERE clause) important for a real RAG system, and where does it get expensive?
5. When would you pick pgvector over a dedicated vector database?

Stretch (connects to S117 & S118 — RAG pipeline): Design the storage layer for a 100M-chunk enterprise RAG system that must support (a) tenant-level metadata filters, (b) daily updates from a document repo, (c) p99 < 100 ms retrieval. Pick a DB, pick an index type, and explain your reindex/update strategy in 5 lines.

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Vector Databases? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S120): LLM Agents — Function Calling, Tools, Planning
• Previous (S118): RAG II — Retrieval, Hybrid Search, Reranking
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.