pgvector indexes on Azure PostgreSQL: IVFFlat vs HNSW vs DiskANN

Without a vector index, pgvector performs an exact search with perfect recall but scans every row. Approximate nearest neighbour (ANN) indexes trade a little recall for much lower latency and compute, and each has build-time and query-time knobs.

Comparing the index types

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops);
SET hnsw.ef_search = 100;

Tuning each index

• IVFFlat: start with lists = rows / 1000 for up to 1 million rows, or sqrt(rows) for larger tables, and probes = lists / 10 (up to 1 million rows) or sqrt(lists). More probes raise recall and cost; setting probes equal to lists makes the search exact, at which point the planner doesn’t use the index. Load data before building, because the clusters are trained on existing rows.
• HNSW: raise m or ef_construction for a better graph at higher build cost; raise hnsw.ef_search at query time for better recall and slower queries.
• DiskANN: Learn’s recommended settings are max_neighbors 32 for under 1 million rows, 64 for 1-50 million and 96 above 50 million, with l_value_ib 100 and diskann.l_value_is 100; turn on product_quantized (preview) above 1 million rows to shrink the index so more of it stays in memory. With product quantization, Learn suggests pq_param_num_chunks of one third of the embedding dimensions and a two-step rerank (fetch, say, 50 candidates by ANN, then reorder by exact distance and return 10).

Query-time parameters can be set for the session with SET or for one transaction with SET LOCAL inside BEGIN ... COMMIT, which is the safer choice with connection poolers.

Building indexes efficiently

• Load data first, then build the index: it’s faster and produces a better layout.
• Raise maintenance_work_mem for the build session (for example SET maintenance_work_mem = '8GB';, depending on server memory), and consider scaling compute up for the build and back down afterwards.
• Use parallel builds by setting the table’s parallel_workers storage parameter and the max_parallel_maintenance_workers, max_parallel_workers and max_worker_processes server parameters. max_worker_processes needs a server restart; if resources don’t allow parallel workers, PostgreSQL falls back to a serial build. The leader process doesn’t take part in a parallel index build.
• Track progress with pg_stat_progress_create_index.
• Use EXPLAIN (ANALYZE, VERBOSE, BUFFERS) to confirm an Index Scan using ... node; without an index, raising max_parallel_workers_per_gather can speed up exact searches.

Common trap: Building an HNSW or IVFFlat index on vector(3072) embeddings - both are limited to 2,000 dimensions and the build fails. Reduce dimensions (for example with the model’s dimensions option), or use DiskANN with product quantization, which supports up to 16,000.

Common trap: Creating an index with vector_l2_ops and querying with <=> - the operator must match the operator class (<=> needs vector_cosine_ops), otherwise the planner falls back to a sequential scan.

Get the whole book

This note is one section of Ultra Transcenders AI-200: Developing AI Cloud Solutions on Azure, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · AI-200 terms in the glossary · All AI-200 study notes