pgvector HNSW vs IVFFlat: Choose by Workload

pgvector HNSW vs IVFFlat is a workload decision, not a universal benchmark result. This guide defines an exact-search oracle, runs both index families on one matched cohort, and chooses a nondominated configuration using recall, tail latency, filters, churn, memory, and build constraints.

pgvector HNSW vs IVFFlat starts with workload shape

pgvector HNSW vs IVFFlat is not a contest with one permanent winner. The right index depends on row count, vector dimension, distance operator, recall target, latency percentile, concurrency, filters, insertion rate, memory budget, build window, and how quickly new rows must become searchable. Exact scan also remains a valid baseline for small or highly filtered workloads.

For pgvector HNSW vs IVFFlat, write a workload contract before creating indexes. Name the query cohort, table snapshot, hardware, PostgreSQL and extension versions, warmup, cache condition, concurrency, filter distribution, top-k, and allowed result error. A benchmark without those facts cannot be replayed or compared.

The pgvector index comparison workbench in this article does not run PostgreSQL or invent measurements. It accepts workload facts and pasted benchmark rows, rejects incomparable trials, identifies nondominated candidates, and exports a decision receipt. That separation keeps a planning tool from making performance promises.

Start with the operational tuning concepts in HNSW vector search tuning, then bring the same measurement discipline to both index families. The decision should survive changes in fashionable defaults because its evidence belongs to the workload.

Use exact search as the recall oracle

Approximate recall needs an exact reference. For each sampled query, run the same distance operator, candidate population, filters, and top-k without an approximate index path. Store the ordered ground-truth identifiers. Then measure recall at k as the fraction of exact neighbors recovered by the candidate result.

Confirm the exact plan with PostgreSQL EXPLAIN and disable or avoid index paths in a controlled session. The PostgreSQL EXPLAIN documentation describes how plans and actual execution evidence should be read. A sequential scan is not automatically the oracle if its predicates or operator differ.

Build a query cohort that represents production. Include common filters, rare filters, empty-result cases, recent rows, and vectors near cluster boundaries. Freeze identifiers so two configurations see the same questions. Report per-query recall and tail latency rather than only their averages.

Reject any benchmark row that lacks an exact-oracle version. Synthetic vectors can test machinery, but label them as synthetic and do not present them as measured production behavior. The oracle is what converts “the results looked close” into a quantified index comparison.

Understand the HNSW index trade space

HNSW builds a multilayer proximity graph. Searches enter sparse upper layers, move toward promising neighborhoods, and explore a denser base layer. In pgvector, construction and query parameters influence graph quality, build time, memory, search work, and recall. More exploration can improve recall at the cost of latency.

HNSW can provide strong query performance without a separate training phase, and newly indexed rows join the graph. The tradeoff is a substantial graph structure, potentially expensive construction, and maintenance behavior that must be measured under churn. Dataset geometry and filtering can change the practical result.

The pgvector official README documents supported index types, operators, parameters, and iterative scans. Treat it as the contract for available controls, then verify plans and outcomes on the deployed version.

Do not collapse every configuration into the label “HNSW.” Record opclass, metric, dimensions, m, ef-construction, query exploration settings, index bytes, build duration, recall distribution, latency distribution, and filtered candidate behavior. A recommendation without those fields is not reproducible.

Understand the IVFFlat index trade space

IVFFlat partitions vectors around learned centroids and stores rows in lists. A query probes selected lists, then ranks candidates within them. More lists make partitions narrower; more probes inspect more partitions. Too few probes can miss neighbors, while probing many lists approaches the cost of broader search.

Unlike HNSW, IVFFlat needs a representative population when the index is built. Building too early or training on an unrepresentative snapshot can produce poor partitions. Growth and distribution drift may justify rebuilding. Measure how quickly newly inserted rows participate and how recall changes as the table evolves.

Its appeal can include simpler structures or different build and memory characteristics, but those benefits are workload claims to measure. Record lists, probes, training snapshot, row count, index bytes, build duration, maintenance window, and the same query-level outputs used for HNSW.

IVFFlat may be an excellent choice when its partitioning matches the data and an acceptable probe budget reaches the recall target. It may be a poor choice for a small table where exact scan already wins. The index type is a hypothesis, not an architecture identity.

Run one matched vector search benchmark

For pgvector HNSW vs IVFFlat, create one immutable snapshot and one query cohort. Build exact, HNSW, and IVFFlat candidates with the same vector column, distance operator, filter semantics, top-k, server resources, client concurrency, and measurement window. Randomize run order or repeat cycles to reduce cache-order bias. Separate cold build cost from warmed query behavior.

Collect p50, p95, and p99 latency; per-query recall; throughput at declared concurrency; index bytes; build time; peak build resources when available; insertion throughput; vacuum or maintenance observations; and plan evidence. Retain raw query rows so percentiles can be recomputed.

The phrase vector search benchmark is often used for synthetic leaderboards. Here it means a production-shaped cohort with an exact oracle and comparable trials. A mixed metric, opclass, filter, or snapshot invalidates the comparison and should be rejected rather than normalized away.

If keyword evidence also matters, benchmark the full retrieval composition described in hybrid search with reciprocal rank fusion. An excellent vector index cannot compensate for evaluating a component while the product ranks a hybrid result.

Measure filters and churn as first-class costs

In pgvector HNSW vs IVFFlat, a selective WHERE clause can change approximate behavior. If the index explores neighbors before enough rows satisfy a filter, recall may fall or the engine may need more iterative work. Benchmark filter cohorts separately: no filter, common tenant, rare tenant, time window, access-control predicate, and combinations used by the product.

Security filters are correctness boundaries. The RAG access-control vector search guide explains why unauthorized candidates must not leak through retrieval or diagnostics. An index benchmark should verify both result authorization and recall within the authorized population.

Churn matters too. Run an insert-update-delete phase that resembles production, then repeat the frozen query cohort. Measure index growth, write latency, fresh-row recall, maintenance work, and whether a rebuild is operationally acceptable. Do not substitute a static bulk-loaded snapshot for an application that changes continuously.

For build observability, PostgreSQL exposes progress views described in the CREATE INDEX progress documentation. Capture build phase and elapsed evidence, but avoid presenting one machine's duration as a promise for another environment.

Choose a nondominated configuration

An HNSW index or IVFFlat index candidate is dominated when another candidate is at least as good on every required dimension and strictly better on one. Remove dominated rows before debating preferences. The remaining Pareto frontier makes the real tradeoffs visible: one option may be faster but larger; another may build cheaply but need more query probes.

Apply hard constraints first. Reject configurations below recall target, above p99 latency budget, beyond memory or build windows, incompatible with filters, or unable to meet freshness needs. Then compare the survivors using explicitly ranked business priorities. Avoid a weighted score unless stakeholders understand how weights can hide a failed operational requirement.

Exact scan can remain on the frontier. For a small filtered partition, it may deliver perfect recall with predictable operations. An unconditional “always use HNSW” rule fails precisely because it erases that workload context.

The workbench requires pasted measurements and labels synthetic examples. It produces a frontier and rejection reasons, not a universal winner. That receipt is a better design artifact than a screenshot of three average-latency bars.

Migrate, observe, and revisit the decision

Conclude pgvector HNSW vs IVFFlat by rolling out the chosen index behind a query-path control. Build concurrently when the workload and PostgreSQL operation allow it, monitor build progress, validate index validity, and shadow a sample against the exact oracle. Increase traffic only while recall, tail latency, errors, resources, and freshness remain inside bounds.

Keep the previous path available until rollback has been exercised. Store the chosen DDL, extension and server versions, workload receipt, benchmark rows, decision constraints, and owner. Dashboards should segment by filter cohort and query family so a healthy global percentile cannot hide a failing tenant.

Revisit after significant row growth, embedding-model changes, dimension or metric changes, filter shifts, churn changes, PostgreSQL or pgvector upgrades, and hardware moves. Representation drift should trigger the same caution as embedding drift monitoring: yesterday's frontier may not describe today's geometry.

The durable answer to pgvector HNSW vs IVFFlat is therefore procedural. Maintain an exact oracle, replay a matched cohort, preserve raw rows, select a nondominated configuration under explicit constraints, and migrate with rollback. The index earns its place through measured workload evidence.

Runnable local artifact — The workbench analyzes user-supplied rows; it does not connect to PostgreSQL, execute queries, synthesize production evidence, or guarantee performance.

Enter workload constraints and pasted benchmark rows; require an exact oracle and matching metric, opclass, snapshot, and filter cohort; inspect the frontier; export JSON or CSV.