2.2.3. Indexing Strategies and pgvector Optimization
💡 First Principle: Every index is a prepaid query: you spend build time, memory, and write overhead now so reads are cheap later. pgvector just adds a twist — its indexes also spend accuracy. Tuning pgvector means deciding how much recall to sell for how much speed.
Standard indexing arrives first: B-tree for equality and ranges (doc_id, created_at), GIN for jsonb metadata containment (metadata @> '{"lang": "en"}'). These make the filter half of filtered vector search fast. For the vector half, pgvector offers two index types, and choosing between them is a marquee exam decision:
| HNSW | IVFFlat | |
|---|---|---|
| Structure | Multi-layer proximity graph | k-means clusters ("lists") |
| Build time / memory | Slow, memory-hungry | Fast, lighter |
| Query recall & speed | Higher recall at low latency | Good, more tuning-sensitive |
| Key parameters | m, ef_construction (build); ef_search (query) | lists (build); probes (query) |
| Data prerequisite | None — build anytime | Needs representative data before build |
| Choose when | Production RAG, evolving data | Fast rebuilds, memory-constrained |
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
SET hnsw.ef_search = 80; -- higher = better recall, slower queries
The query-time knobs (ef_search, probes) are your recall dial — the "how many shelves to check" from the library model in 2.2. Raising them buys recall with latency; lowering them does the reverse. This is also the honest answer to the syllabus bullet on reducing pgvector compute overhead: right-size the dial, build indexes with adequate maintenance_work_mem so they construct efficiently, exclude vectors you never search, and consider halfvec (16-bit floats) to halve memory when your model tolerates it.
For small tables (a few thousand rows), the correct index is no index — a sequential scan is exact, fast enough, and free of build cost. Optimization means matching machinery to scale, not maximal machinery.
⚠️ Exam Trap: IVFFlat's clusters are computed from the data present at build time. Creating an IVFFlat index on an empty table, then loading millions of rows, yields garbage clusters and poor recall. Sequence matters: load data, then build IVFFlat. HNSW has no such prerequisite — a clean discriminator between the two in scenario questions.
Reflection Question: Latency is fine but users report missing obviously relevant documents. Which single parameter do you raise first on an HNSW index, and what do you trade away?
