Tune Vector Indexes: Recall vs Latency vs Memory

Tier: Advanced · Workflow area: Vector & AI Features · ~45 minutes

HNSW's m and ef_construction, query-time ef_search, DiskANN's graph degree — every one shifts the same three-way trade between recall, latency and resource cost. AI is genuinely useful here as a search-space planner and results interpreter, but the measurements must come from your data and your hardware: recall curves are corpus-specific, and a model's "typical" numbers are folklore. The output is a small published table — the SLA of your search feature.

When to use

• Before a vector feature goes to productionshipping on default parameters means shipping an unmeasured accuracy contract. One tuning pass turns "it seems fine" into numbers.
• When search latency or quality regressed after growthrecall and latency both move as the corpus grows; the parameter set chosen at 100k rows may be wrong at 5M.
• When memory pressure forces a tradeindex builds blowing maintenance windows or RAM budgets. You need to know exactly what recall you buy back by shrinking the graph.

When NOT to use

• When exact search is affordableunder ~50k vectors, a sequential scan with ORDER BY embedding <=> $1 may meet latency at recall 1.0. Measure that first; no index means no approximation.
• To compensate for bad embeddingsif pb-29's eval showed poor retrieval even at high recall@k, the problem is the embed-text or the model. Index tuning cannot fix meaning.
• Chasing benchmark-paper numbers on unmatched hardwarepublished ann-benchmarks figures come from specific corpora and machines. Use them to pick a starting grid, never as your target.

The recipe

1. 1.

   Build ground truth with exact scans — recall needs a truth to recall. Take 100 representative query vectors (real logged queries, embedded), and compute true top-10 neighbors by brute force:

   Copy

   SET enable_indexscan = off;  -- force exact scan for truth
   SELECT id FROM products ORDER BY embedding <=> $1 LIMIT 10;
   

   Store results in a ground_truth(query_id, rank, product_id) table. This is the answer key every configuration is graded against.

2. 2.

   Have AI design the parameter grid — then prune it to what a day can run. Give the model your constraints and let it plan the sweep:

   Copy

   Corpus: 4.2M vectors, 768 dims, cosine. Hardware: 32 GB RAM,
   8 vCPU. p95 latency target: 40 ms with a category filter.
   Propose an HNSW parameter grid over m, ef_construction and
   ef_search worth benchmarking: no more than 4 build configs
   (builds are expensive) and 5 ef_search values per build.
   Explain the expected effect of each axis, and predict which
   config wins — I will test your prediction.
   

   A sensible grid: builds at m=8/16/24/32 with ef_construction=64–128; ef_search swept 40–400. Making the model predict the winner turns it into a hypothesis you verify — the discipline of this whole track.

3. 3.

   Script the benchmark: build, sweep, score. For each build config: create the index (timing it, recording peak maintenance_work_mem usage and final size), then for each ef_search value run the 100 queries and score:

   Copy

   SET hnsw.ef_search = 100;
   EXPLAIN (ANALYZE) SELECT id FROM products
   ORDER BY embedding <=> $1 LIMIT 10;
   

   Copy

   SELECT pg_size_pretty(pg_relation_size('idx_products_hnsw'));
   

   Recall@10 per query = |returned ∩ ground truth| / 10; report mean and worst-case. Latency: p50 and p95 over the query set, warm cache. Ask AI to draft the harness script; verify it computes recall against the truth table, not against another approximate run.

4. 4.

   Benchmark the filtered case separately — it is a different workload. Repeat the sweep with your real predicate (WHERE category = $2 AND in_stock). Filtered ANN can return fewer than k rows or crater recall at low ef_search; on pgvector 0.8+, iterative scans change the curve. Sample results table:

   config	recall@10	recall@10 (filtered)	p95 ms	index size	build min
   m=16, efc=64, efs=100	0.97	0.88	18	6.1 GB	42
   m=16, efc=64, efs=250	0.99	0.96	33	6.1 GB	42
   m=32, efc=128, efs=100	0.99	0.95	24	9.8 GB	97

5. 5.

   Interpret with AI, decide as a DBA. Paste the results table and ask for the recommendation with reasoning — which config, what breaks first at 10x growth, and what to monitor. Cross-examine it: does its story match the numbers? The decision weighs things the model cannot see: your maintenance window (build minutes), RAM headroom (index size), and how bad a missed result actually is for the product.

6. 6.

   Publish the contract and pin the settings. Record the chosen build parameters in the migration, set ef_search explicitly in the application's session or per-query (never rely on a server default someone can change), and publish the table from step 4 with the storage forecast for 10x rows. That document is what "search works" means from now on.

Make it yours

• pgvectorscalethe StreamingDiskANN index changes the axes (SBQ compression, disk-resident graphs); rerun this same harness — ground truth and recall scoring are index-agnostic, which is the point of building them.
• SQL Server 2025 DiskANNsweep the graph construction parameters and query-time candidate counts with the identical truth-table method; measure through your actual T-SQL query shape.
• Oracle 23aivector indexes expose a target-accuracy parameter, letting you request recall and measure whether you got it — verify with your ground truth anyway; that is a promise, not a measurement.
• Quantization (halfvec, binary)adds a fourth axis: storage halves, recall dips. Add one quantized config to the grid and let the numbers decide.

Quality check

• Recall is scored against exact-scan ground truthcomparing one approximate config to another measures agreement, not accuracy. The truth table came from forced exact scans.
• Latency was measured through the real query shapefilters, LIMIT, and the app's access path included, warm and cold cache noted. A bare ANN benchmark flatters everyone.
• Build cost is recorded alongside query numbersa config you cannot rebuild inside a maintenance window is not a valid choice, whatever its recall.
• The AI's predicted winner was checked against the measured winnerwhen they disagree, the measurement wins and the disagreement is worth understanding; it usually reveals a workload quirk worth documenting.