pgvector Index Selection: IVFFlat vs HNSW for PostgreSQL Vector Search

2026-03-03

pgvector Index Selection: IVFFlat vs HNSW for PostgreSQL Vector Search

The Problem

pgvector gives PostgreSQL two approximate nearest neighbor (ANN) index types: IVFFlat and HNSW. They solve the same problem -- making vector similarity searches fast enough to use in production -- but they make fundamentally different trade-offs. Picking the wrong one does not just slow things down a little. It can tank recall, blow out build times, or waste gigabytes of memory.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph structure. Searches navigate the graph from coarse to fine layers, converging on nearest neighbors without scanning the entire dataset. Build time is slow and memory usage is high, but query recall is excellent even without careful parameter tuning. HNSW indexes work well on empty tables -- you can create the index before loading data.

IVFFlat (Inverted File with Flat compression) partitions vectors into clusters using k-means, then searches only the most relevant clusters at query time. It builds much faster and uses less memory, but there is a critical constraint: IVFFlat requires representative data in the table before you create the index. Building an IVFFlat index on an empty or sparsely populated table produces poor cluster centroids, which means poor recall on every query that follows.

Here is a concrete scenario that plays out regularly. A team building a RAG application creates an IVFFlat index on their embeddings table during schema migration -- before any data is loaded. The index builds instantly on zero rows, the centroids are meaningless, and every similarity search returns low-quality results. The team blames the embedding model or the retrieval logic, not realizing the index itself is the problem. Alternatively, a team with 500 rows picks HNSW with high m and ef_construction values, then wonders why the index build takes 20 minutes and consumes 4 GB of maintenance_work_mem for a dataset that would have been perfectly served by IVFFlat.

The choice depends on dataset size, update patterns, recall requirements, and whether you can load data before indexing.

How to Detect It

Run these queries to audit your existing pgvector indexes and understand what you are working with:

-- Check which pgvector indexes exist and their types
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexdef LIKE '%vector_cosine_ops%'
   OR indexdef LIKE '%vector_l2_ops%'
   OR indexdef LIKE '%vector_ip_ops%'
   OR indexdef LIKE '%hnsw%'
   OR indexdef LIKE '%ivfflat%'
ORDER BY tablename, indexname;

-- Check index sizes vs table size
SELECT
    c.relname AS index_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    am.amname AS index_type
FROM pg_class c
JOIN pg_am am ON c.relam = am.oid
WHERE am.amname IN ('ivfflat', 'hnsw')
ORDER BY pg_relation_size(c.oid) DESC;

The first query shows every vector index, its table, and the full index definition including the operator class and parameters. Look for IVFFlat indexes on tables that were likely empty when the index was created -- migration-created indexes are the usual suspects. The WITH (lists = ...) parameter in IVFFlat definitions tells you the cluster count; if the table has fewer rows than lists * 10, recall will be poor.

The second query reveals index sizes. HNSW indexes are typically 2-5x larger than IVFFlat indexes on the same data. If an HNSW index is consuming significant memory relative to your shared_buffers, that is a signal to evaluate whether IVFFlat would suffice.

Cross-reference with pg_stat_user_indexes to check whether the indexes are actually being used in queries. An unused vector index is pure overhead.

How myDBA.dev Shows It

pgvector monitoring page showing index types

myDBA.dev's pgvector monitoring page surfaces this information continuously. You can see every vector index alongside its type (HNSW or IVFFlat), size, and scan statistics in a single view. Instead of running ad-hoc queries, you get a persistent record of how your vector indexes are performing over time -- which indexes are being scanned, how large they are growing, and whether scan counts are changing after configuration adjustments.

Queries page showing vector query plans

The Queries page complements this by showing the actual execution plans for vector similarity searches. You can see whether queries are hitting your ANN indexes or falling back to sequential scans, what the estimated costs look like, and how query performance trends over time. When you switch from IVFFlat to HNSW (or vice versa), myDBA.dev makes the performance impact visible immediately -- you do not need to guess whether the change helped.

How to Fix It

The fix depends on your dataset and requirements. Here is the decision framework, followed by the SQL.

Choose HNSW when:

Your dataset is under ~1 million rows
You need high recall (>95%) without careful tuning
Data is loaded incrementally (HNSW works on empty tables)
You can afford the memory and build time

Choose IVFFlat when:

Your dataset is very large (millions of rows)
Build time is a constraint (IVFFlat builds 5-10x faster)
You can load data before creating the index
Slightly lower recall is acceptable

-- For datasets under 1M rows or when recall matters most: use HNSW
CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- For very large datasets where build time matters: use IVFFlat
-- IMPORTANT: Load data BEFORE creating IVFFlat indexes
CREATE INDEX CONCURRENTLY idx_documents_embedding_ivfflat
ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);

-- Ensure enough memory for HNSW index build
SET maintenance_work_mem = '2GB';

Key parameters to tune:

HNSW m: connections per node (default 16). Higher values improve recall but increase memory and build time. Values of 16-64 cover most use cases.
HNSW ef_construction: search breadth during build (default 64). Higher values improve recall at the cost of build time. 200 is a solid starting point for production.
IVFFlat lists: number of clusters. A common rule of thumb is sqrt(row_count) for datasets under 1 million rows, and row_count / 1000 for larger datasets.

If you are replacing an existing index, drop the old one after verifying the new one is working:

DROP INDEX CONCURRENTLY idx_documents_embedding_ivfflat;

How to Prevent It

Make index type selection an explicit decision in your schema design process, not an afterthought. Document the rationale for each vector index alongside the migration that creates it.

Set a rule: never create IVFFlat indexes in schema migrations. IVFFlat indexes belong in data loading scripts that run after the initial data import. If your workflow requires an index at migration time, use HNSW -- it works correctly on empty tables.

For evolving datasets, revisit the index type when your row count crosses a threshold. An HNSW index that was fine at 100K rows may become a memory problem at 10 million rows. An IVFFlat index may need its lists parameter increased as data grows -- stale cluster centroids degrade recall over time, and the index should be rebuilt periodically.

Monitor index scan rates and query latency trends continuously. A tool like myDBA.dev that tracks pgvector index usage over time makes it straightforward to spot when an index is no longer serving your workload well, before users notice the degradation.