paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

August 10, 2026

HNSW vs IVFFlat in Postgres with pgvector: Choosing the Right Vector Index

A practical comparison of pgvector's two approximate nearest neighbor indexes, with build strategies, tuning parameters, and production trade-offs

Introduction

If you have added vector search to a PostgreSQL application using pgvector, you have probably hit the same fork in the road that every team hits eventually: should you index your embeddings with HNSW or IVFFlat? The question sounds like a minor configuration detail, but it shapes your write latency, your query latency, your recall quality, and how much memory your database needs to stay healthy under load. Get it wrong and you either waste money over-provisioning RAM for an index you don't need, or you ship a search feature that silently returns the wrong "nearest" neighbors to your users.

This article walks through both index types in depth: how they work internally, how to build and tune them in real schemas, and where each one breaks down in production. The goal is not to tell you "HNSW is better" and move on - that answer is true often enough to be a reasonable default, but it is not true always, and the exceptions are exactly where engineers get burned. By the end, you should be able to look at your own dataset size, write pattern, and latency budget and pick confidently, rather than copying whatever index type showed up first in a tutorial.

The Problem: Approximate Nearest Neighbor Search at Scale

Vector similarity search asks a deceptively simple question: given a query vector, which rows in a table have embeddings closest to it? PostgreSQL can answer this exactly using a sequential scan that computes the distance between the query vector and every row, sorts the results, and returns the top K. pgvector uses exact nearest neighbor search by default and provides 100% recall, but the query speed is low. For a table with a few thousand rows, this is often fast enough that you never need an index at all.

The trouble starts at scale. Distance computation is O(d) per row, where d is the embedding dimension, so a sequential scan over a million 1536-dimensional vectors means a million 1536-element floating point comparisons for every single query. That cost grows linearly with table size, which means your p99 latency gets worse every time your embeddings table grows, with no ceiling. This is unacceptable for anything user-facing - a recommendation widget, a semantic search box, a RAG retrieval step - where queries need to return in single-digit or low double-digit milliseconds regardless of how much data has accumulated.

Approximate nearest neighbor (ANN) indexes solve this by trading a small amount of accuracy for a large amount of speed. Instead of comparing the query against every row, an ANN index narrows the search to a promising subset of candidates - a handful of graph neighbors, or a handful of nearby clusters - and returns the best matches from that subset. The result usually matches the exact answer, but not always; the gap between approximate and exact results is what the industry calls "recall", typically measured as the fraction of true top-K neighbors that the approximate index actually returns. pgvector ships two mature ANN index types that make this trade-off in structurally different ways: HNSW and IVFFlat.

HNSW: Graph-Based Approximate Search

HNSW stands for Hierarchical Navigable Small World, and the name describes the data structure fairly literally. During index build, pgvector constructs a multi-layer graph where each vector is a node, and nodes are connected to their approximate nearest neighbors. The top layers are sparse, containing only a small number of long-range connections that let a query jump across large regions of the vector space quickly. Lower layers are denser, with each node connected to many of its close neighbors, refining the search as it descends. A query starts at the top layer, greedily walks toward the region of the query vector, then drops down a layer and repeats, narrowing in on the true neighborhood by the time it reaches the base layer.

Two parameters control how this graph is built: m, the number of edges per node, with a default of 16, and ef_construction, the build-time search depth, where higher values produce better index quality at the cost of slower builds. Higher m values generally help recall for high-dimensional data at the cost of index size and build time, with 16 to 32 covering most cases and 32 to 64 helping embeddings of 1536 or more dimensions. A useful property of HNSW that distinguishes it from IVFFlat is that it requires no training step - it can be built on an empty table and delivers consistently fast queries without the clustering pass that IVFFlat needs - which matters a great deal for schemas that start empty and grow incrementally through normal application traffic.

IVFFlat: Partition-Based Approximate Search

IVFFlat takes a different approach, borrowed from the classical "inverted file" structure used in large-scale information retrieval systems. During index build, pgvector runs a k-means clustering pass over a sample of the vectors to produce a fixed number of centroids, controlled by the lists parameter. Every vector in the table is then assigned to its nearest centroid, essentially partitioning the vector space into a set of Voronoi cells. At query time, pgvector computes the distance from the query vector to each centroid, selects the closest probes centroids, and only scans the rows assigned to those cells. This is conceptually similar to how a well-designed book index groups related topics under the same heading, rather than making you scan the full table of contents for every lookup.

Because IVFFlat depends on a clustering pass to position its centroids sensibly, it needs data in the table before it can be built, unlike HNSW, and it has faster build times and lower memory usage than HNSW, with the trade-off of lower query performance in terms of the speed-recall curve. Independent benchmarks bear this out at a range of scales: in one comparison on 25,000 documents with 3072-dimension embeddings, IVFFlat built in roughly 5 seconds versus 29 seconds for HNSW, nearly six times faster, while producing an index of almost identical size. The catch is that IVFFlat's recall is sensitive to how well the centroids still describe the data; as new rows are inserted after the clustering pass, the centroids go stale and recall quietly drifts downward until the index is rebuilt. IVFFlat's recall ceiling is also generally lower than HNSW's, and it handles incremental inserts poorly, typically needing a full rebuild to recover accuracy, whereas HNSW absorbs ongoing inserts gracefully. This makes IVFFlat a poor fit for tables with a continuous write stream and a much better fit for datasets that are loaded in bulk and queried heavily afterward with infrequent updates.

Implementation Walkthrough

The SQL surface for both index types is deliberately similar, which is one of pgvector's strengths - you can prototype with one and swap to the other without changing application code. Here is a representative setup for a table storing document embeddings, shown first with HNSW:

-- Table storing 1536-dimension embeddings (e.g., from an OpenAI-compatible model)
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding VECTOR(1536) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- HNSW index using cosine distance, tuned for higher recall on high-dim vectors
CREATE INDEX CONCURRENTLY idx_documents_hnsw
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 24, ef_construction = 100);

-- Query-time recall/speed tuning (session-scoped)
SET hnsw.ef_search = 80;

SELECT id, content, embedding <=> '[0.012, -0.034, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.012, -0.034, ...]'::vector
LIMIT 10;

And the equivalent with IVFFlat, where the lists value is commonly set to roughly the square root of the expected row count for balanced cluster sizes:

-- IVFFlat index; lists should be tuned to table size (~sqrt(rows) as a starting point)
CREATE INDEX CONCURRENTLY idx_documents_ivfflat
    ON documents USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 1000);

-- Scan more clusters for better recall at query time
SET ivfflat.probes = 15;

SELECT id, content, embedding <=> '[0.012, -0.034, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.012, -0.034, ...]'::vector
LIMIT 10;

A TypeScript ingestion worker that builds the embedding and upserts it typically looks like this, batched for throughput against whichever index you have chosen:

import { Pool } from "pg";
import pgvector from "pgvector/pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function upsertEmbeddings(
  rows: { id: number; content: string; embedding: number[] }[]
) {
  const client = await pool.connect();
  await pgvector.registerType(client);
  try {
    await client.query("BEGIN");
    for (const row of rows) {
      await client.query(
        `INSERT INTO documents (id, content, embedding)
         VALUES ($1, $2, $3)
         ON CONFLICT (id) DO UPDATE
         SET content = EXCLUDED.content, embedding = EXCLUDED.embedding`,
        [row.id, row.content, pgvector.toSql(row.embedding)]
      );
    }
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Trade-offs and Pitfalls

The most common mistake teams make is picking an index type based on a benchmark blog post rather than their own write pattern. HNSW's standout advantage - graceful handling of ongoing inserts - only matters if your table actually receives ongoing inserts. If you are loading a static corpus once a week in a batch job and querying it heavily in between, IVFFlat's faster build and lower memory footprint may be the more economical choice, since you are not paying HNSW's build-time and RAM tax for a benefit (incremental insert stability) you never use.

A second pitfall involves dimension limits that catch people off guard mid-migration. Both index types share the same 2,000-dimension limit for the vector type, which is well below what some modern embedding models produce (3072 dimensions is increasingly common). The usual workaround is indexing a halfvec cast of the column instead of the full-precision vector, which roughly halves storage per element and raises the effective dimension ceiling, at a small cost in distance-computation precision. Teams that discover this limit only after their embedding model upgrade ships are a recurring support story, so it is worth checking against your actual embedding dimensionality before committing to an index strategy.

A third, subtler pitfall is forgetting that approximate indexes interact awkwardly with WHERE filters. With approximate indexes, filtering is applied after the index scan, which means a query like "find the 10 nearest products in category X" can return fewer than 10 rows, or lower-quality matches, if category X is rare and the index's candidate set happens to contain few matching rows. pgvector addresses this with iterative scan modes - both HNSW and IVFFlat support an iterative scan mode that automatically scans more of the index to obtain sufficient data, controlled by the hnsw.iterative_scan and ivfflat.iterative_scan parameters, with values of off by default, relaxed_order, and strict_order, the last of which is only supported by HNSW. Enabling this correctly is the difference between a filtered search that silently under-returns and one that behaves the way users expect.

Best Practices for Production

Start by defaulting to HNSW unless you have a specific, measured reason not to. The combination of good out-of-the-box recall, no training-step prerequisite, and graceful behavior under continuous writes covers the large majority of production workloads - anything from a customer-facing semantic search box to a RAG pipeline backing a support chatbot. Build the index with CREATE INDEX CONCURRENTLY to avoid locking writes on large tables, and treat ef_search as a per-query knob rather than a global constant: a background analytics job can tolerate a lower ef_search for speed, while a user-facing autocomplete endpoint should bias toward the higher end of your tested range.

Reach for IVFFlat deliberately, not by default, and when you do, pair it with an operational habit: schedule periodic rebuilds. Because IVFFlat's centroids degrade as the underlying data distribution shifts, a production IVFFlat index without a rebuild cadence is a recall regression waiting to happen - teams are frequently surprised months later when search quality has quietly deteriorated with no corresponding alert, because nothing about IVFFlat's degradation throws an error; it just returns worse answers. Whichever index you choose, always validate recall against a held-out exact-search baseline before shipping, using a representative query sample rather than trusting the defaults blindly, since the right m, ef_construction, lists, and probes values are genuinely dataset-dependent.

Mental Models: Choosing Between the Two

If you need a single mental image to carry into a design review, think of HNSW as a well-connected social network and IVFFlat as a filing cabinet. In the social network model, finding the right person means asking a well-connected acquaintance, who points you to someone closer, who points you closer still - a few hops through a graph of relationships gets you to your target quickly, and the network adapts naturally as new people join. In the filing cabinet model, every document is pre-sorted into a labeled drawer the moment it arrives, and finding a document means going straight to the right drawer and searching only within it - fast and cheap, as long as the labeling scheme still matches reality.

The filing cabinet breaks down when the categories themselves become outdated - new kinds of documents keep arriving that don't fit neatly into the original drawers, and without periodically reorganizing, your filing system quietly stops reflecting what's actually in the cabinet. That is IVFFlat's centroid drift in a nutshell. The social network, by contrast, simply grows a new connection when a new person joins, and the overall structure stays navigable without anyone needing to reorganize anything. Neither mental model is a substitute for real benchmarking on your own data, but both are useful shorthand when you are explaining the trade-off to a teammate who has not read the pgvector source.

The 80/20 Insight

If you only take away three facts from this entire comparison, make them these. First, HNSW is the safer default for anything with ongoing writes and user-facing latency requirements - it reaches 95%+ recall out of the box and absorbs inserts without rebuilds, at the cost of using two to five times more memory than IVFFlat. Second, IVFFlat earns its place specifically when build speed or memory footprint dominates your cost model and your data is close to static between rebuilds - it is not a worse version of HNSW, it is a different tool optimized for a different constraint.

Third, and most overlooked: the index type matters less than whether you actually measure recall against your own data and query patterns. Teams spend disproportionate time debating HNSW versus IVFFlat in the abstract and almost no time writing a small recall-evaluation script that runs both configurations against a sample of real queries and compares the results to an exact sequential scan. That one script - comparing approximate results to ground truth on a few hundred representative queries - resolves more production incidents than any amount of parameter tuning performed without it.

Key Takeaways

Five steps you can apply to your own pgvector deployment this week:

Conclusion

HNSW and IVFFlat are not competing implementations of the same idea; they are two different answers to the same underlying trade-off between build cost, memory, and query-time recall. HNSW's graph structure makes it the stronger general-purpose choice for the common case of a growing table backing a live application, while IVFFlat's partition-based approach remains genuinely useful for large, closer-to-static datasets where fast builds and lower memory win out over marginal recall. pgvector's decision to expose both through nearly identical SQL is what makes this a low-risk decision to revisit - you can build both indexes side by side on a staging copy of your data, run your real queries against each, and let measured recall and latency settle the argument rather than a blog post.

The deeper lesson, though, is one that applies beyond vector search: approximate systems need explicit validation, not implicit trust. An exact-match index either returns the right row or it doesn't, and a bug shows up immediately. An approximate nearest-neighbor index can degrade slowly and silently, returning technically valid but subtly wrong results for months before anyone notices a drop in search quality. Treat recall measurement as a first-class, recurring part of your operational practice around pgvector, and the HNSW-versus-IVFFlat decision stops being a one-time architectural bet and becomes a parameter you can safely revisit as your data and traffic evolve.

References