The schema migration looked harmless: add a vector index so RAG queries stop scanning the table. The next problem arrived during the backfill, when index build time, memory pressure, and write amplification became the real production risk. In pgvector, choosing HNSW or IVFFlat is not an academic choice. It changes the operating model.

Short Version

HNSW is usually the better query-time index when recall and latency matter, but it builds slower and uses more memory. IVFFlat is lighter to build and operate, but it requires training on existing data, depends heavily on list and probe tuning, and can have weaker recall for the same latency target.

For many new pgvector deployments, start by testing HNSW unless the table is write-heavy, memory-constrained, or still growing through large bulk loads. Use IVFFlat when build speed, lower memory use, and simpler initial backfills matter more than best speed-recall tradeoff.

This guidance targets pgvector 0.7 and later, where both index types are generally available with the tuning knobs described below (hnsw.ef_search, defaulting to 40; ivfflat.probes). Confirm the deployed extension version before applying specific parameter defaults, since managed Postgres providers occasionally pin an older release.

Situation

Postgres engineers are used to indexes with predictable semantics: a B-tree index changes performance, not result correctness.

The Problem

Approximate nearest-neighbor indexes are different from a B-tree. Adding one can change which rows are returned because the index trades recall for speed.

That shift creates three DBA problems:

  • The index is part of relevance behavior, not only performance.
  • Tuning parameters affect correctness as users experience it.
  • Build and maintenance costs can disrupt the source database.

HNSW and IVFFlat both accelerate vector search, but they fail differently. Which one is the right default for a given table depends on whether memory, build time, or recall is the binding constraint.

How It Works

flowchart TD
    Table[embedding table] --> HNSW[HNSW graph index]
    Table --> IVF[IVFFlat list index]
    HNSW --> HQuery[graph traversal at query time]
    IVF --> IQuery[probe nearest lists at query time]
    HQuery --> Recall[recall latency tradeoff]
    IQuery --> Recall
    Recall --> DBA[DBA tuning and validation]

HNSW builds a navigable graph of vectors. Queries traverse the graph to find nearby points. The key tuning concepts are graph construction quality and search candidate breadth. Higher construction and search settings can improve recall, but increase build time, memory use, insert cost, and query CPU.

IVFFlat clusters vectors into lists. Queries identify nearby lists and search only some of them. The key tuning concepts are number of lists and number of probes. More lists can improve partitioning of the vector space; more probes improve recall but increase query cost.

The difference matters during ingestion. HNSW can be created before data exists because it does not require a training step. IVFFlat should be created after representative data exists because the lists depend on the data distribution.

Architecture and Operating Model

A production pgvector table should treat index choice as a rollout.

Baseline exact search. Before adding an approximate index, run exact search on a representative subset. Save a query set and expected top results. This becomes the recall baseline.

Build the index in staging with production-shaped data. Measure build time, peak memory, WAL volume, replica lag, and query latency. Do not extrapolate from 10,000 rows if production has millions.

Tune for query classes. Admin search, tenant-scoped RAG, and product recommendation may need different recall and latency tradeoffs. Use session-local settings where possible.

Measure recall, not only latency. A query that returns fast but misses the relevant incident runbook is not healthy. Keep a labeled or manually reviewed query set.

Plan rebuilds. Embedding model changes can require a new vector column, a new index, or a full backfill. Build the migration path before the model upgrade.

Practical Comparison

AreaHNSWIVFFlat
Build modelGraph constructionList clustering
Build timingCan build before table has dataBest after representative data exists
Query behaviorStrong speed-recall tradeoffDepends heavily on lists and probes
Memory pressureHigherLower
Write behaviorMore index maintenance costUsually lighter
Tuning knobhnsw.ef_searchivfflat.probes
Operational riskMemory and build timePoor recall from bad list or probe settings
Good fitRead-heavy retrievalBulk-loaded or memory-constrained datasets

In Practice

DBAs should treat vector index creation like a heavy production change. It can compete for memory, CPU, WAL, and I/O. It can also surprise application owners because retrieval results change after the index is added.

For HNSW, watch build memory and insert latency. The pgvector README notes that HNSW builds faster when the graph fits in maintenance_work_mem; setting that too high can exhaust server memory. That is a DBA decision, not an application toggle.

For IVFFlat, watch whether the data distribution used to create lists still represents the table after months of growth. If the corpus shifts, recall can degrade. Reindexing may become a scheduled maintenance task.

Platform teams should expose index choice through a migration template, not one-off SQL copied from examples. The template should include index name, operator class, build window, rollback plan, and validation query set.

Where It Breaks

Failure modeIndexProduction symptomFix
Graph build exceeds memory budgetHNSWSlow build or host pressureLower settings, increase maintenance memory safely, build off-hours
Inserts become expensiveHNSWWrite latency rises during ingestionBatch writes, separate ingestion window, consider IVFFlat
Lists poorly represent dataIVFFlatRecall is inconsistentRebuild after representative data exists
Probes too lowIVFFlatFast but misses relevant rowsIncrease probes and measure latency
Global setting over-tunedBothCPU cost rises fleet-wideUse route-specific session-local settings

Security, Cost, Observability, and Failure Notes

Security is mostly indirect: index choice affects whether authorized rows are found after filters. A retrieval path that under-returns can push users toward broader queries and accidental exposure. Keep authorization filters in SQL.

Cost appears as larger instances, more memory, longer builds, and more expensive replicas. Vector indexes are not free metadata; they are production data structures.

Observability should include index size, build progress, query latency, recall checks, rows returned versus requested limit, buffer usage, WAL generation during backfills, and replica lag.

Failure modes include index corruption, accidental index drops, model-version mixing, and silent relevance regressions after parameter changes. Treat vector index settings as configuration that requires review.

Decision Checklist

  • Is the table read-heavy or write-heavy?
  • Can the build run after bulk loading?
  • Is memory the limiting resource?
  • Do you have a recall baseline before adding the index?
  • Are filters part of the production query?
  • Can you tune settings per query class?
  • How will you rebuild after an embedding model change?
  • What is the rollback plan if relevance gets worse?

What to Do Next

Problem: Adding a vector index is treated as a performance-only decision, but HNSW and IVFFlat trade recall for speed differently, and the wrong choice surfaces as a memory, build-time, or relevance incident during backfill.

Solution: Default to HNSW unless the table is write-heavy, memory-constrained, or still growing through large bulk loads, in which case IVFFlat’s lighter build is the better starting point. Validate the choice with production-shaped data before committing.

Proof: A staging build with production-shaped data completes within the maintenance window, and a saved query set shows recall at or above the pre-index baseline.

Action: This week, run the baseline exact-search query set against a representative subset of the table, then build both HNSW and IVFFlat in staging and compare build time, peak memory, and recall before choosing.

Sources to Verify