AI-200 Azure Database for PostgreSQL pgvector: HNSW, IVFFlat and DiskANN Indexes, Compute Sizing and RAG Vector Search

Without a vector index, pgvector runs an exact nearest-neighbour search: it computes the distance from the query vector to every row and sorts them, which gives perfect recall but reads the whole table on every query. An approximate nearest-neighbour (ANN) index gives up a little recall for much faster searches. Azure Database for PostgreSQL flexible server supports three ANN index types.

A B-tree on the embedding column is not an alternative: it supports equality and sorting of whole vectors, not ordering by distance, so similarity queries still scan the table. Raising parallel query workers only makes the exact scan faster; every row is still read.

Enabling DiskANN

Flexible server only lets you run CREATE EXTENSION for extensions listed in the azure.extensions server parameter. To use DiskANN, add pg_diskann to that allowlist, then create the extension in each database that will hold the index. CASCADE also creates the vector extension it depends on. Server-side extensions are enabled in the database itself; installing a client library changes nothing on the server.

CREATE EXTENSION IF NOT EXISTS pg_diskann CASCADE;

CREATE INDEX ON products USING diskann (embedding vector_cosine_ops);

DiskANN's product quantization (pg_diskann 0.6 and later) compresses vectors so a DiskANN index can cover up to 16,000 dimensions, a third route for very large embeddings beside the halfvec cast covered below. It is marked preview in Microsoft's documentation; treat it as such.

HNSW can be built at any time and stays sound as rows arrive; IVFFlat is trained once on the data present at build time, so it must be built after loading representative data and rebuilt after large growth.

HNSW

HNSW has two build options: m (connections per node, default 16) and ef_construction (candidate list size while building, default 64). Higher values give a better graph and higher recall but a slower build; lower values speed the build at the cost of quality. Because there is no training step, a deployment script can create an HNSW index on an empty table and every inserted vector is added to the graph.

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

IVFFlat

IVFFlat runs k-means on the existing rows to place lists centroids, and each vector is assigned to its nearest list. Built on an empty or tiny table, the centroids do not represent the real data and recall suffers; more lists do not fix that. Microsoft's starting points:

• lists: rows / 1000 for up to 1 million rows, and the square root of the row count beyond that.
• ivfflat.probes (query time): lists / 10 for up to 1 million rows, and the square root of lists beyond that.

If a table grows far beyond the size the index was built on (for example from tens of thousands of rows to several million), its centroids are stale and each list is huge. Raising probes only partly helps, and probing every list turns the search exact and slow. ANALYZE refreshes planner statistics but never retrains centroids. The fix is to rebuild the index with lists sized for the current row count, then tune probes.

A pgvector index is built for exactly one distance, and the planner uses it only when the query orders by that distance's operator, ascending, with a LIMIT.

If the index uses vector_cosine_ops but the query orders by <->, the index cannot be used and EXPLAIN shows a sequential scan. Decide which metric the ranking must use, then make the query operator and the index class agree; rebuilding the index for a different metric also changes the ranking. Settings such as hnsw.ef_search or enable_seqscan = off cannot make a mismatched index usable.

Ascending order only

Index scans return rows in ascending distance order. That is why <#> returns the negative inner product: ascending order then puts the highest inner product (most similar) first. Sorting <#> descending returns the least similar rows. Ordering by a derived value, such as 1 - (embedding <=> q) descending or a negated <#>, ranks correctly but hides the operator from the planner, which then scans and sorts. Rank by the raw operator and compute the score in the select list:

SELECT id, content, 1 - (embedding <=> %(q)s::vector) AS similarity
FROM chunks
ORDER BY embedding <=> %(q)s::vector
LIMIT 5;

For vectors normalized to length 1 (Azure OpenAI embeddings are), inner product gives the same ranking as cosine and is slightly cheaper: index with vector_ip_ops and order by <#> ascending.

Each index type has its own query-time setting that trades latency for recall without a rebuild: hnsw.ef_search for HNSW, ivfflat.probes for IVFFlat and diskann.l_value_is for DiskANN. Each setting affects only its own index type.

• hnsw.ef_search (default 40) is the size of the dynamic candidate list during an HNSW search. Raise it when searches miss neighbours an exact scan finds. m and ef_construction also affect recall, but they are build options and need a rebuild.
• ivfflat.probes (default 1) is how many of the nearest lists are searched. More probes raise recall and latency; probes equal to lists is an exact search. lists itself is a build option.
• diskann.l_value_is (default 100) is the size of DiskANN's search list; larger values raise recall and latency. DiskANN also has diskann.iterative_search (default relaxed_order) for filtered queries.

General memory settings such as work_mem size sorts and hashes; they are not recall controls.

Scoping the setting

With a connection pool shared by several endpoints, SET LOCAL inside the endpoint's transaction is the narrowest scope that works: the connection returns to the pool with the default value.

Vector index builds are governed by build-time memory and parallel maintenance workers, not by the query settings with similar names.

• maintenance_work_mem: HNSW builds are much faster while the graph fits in it. When it stops fitting, PostgreSQL logs NOTICE: hnsw graph no longer fits into maintenance_work_mem after N tuples and the build continues far more slowly. Raise it for the build session, within the server's free memory.
• max_parallel_maintenance_workers (default 2, capped by max_parallel_workers): the number of extra workers CREATE INDEX may use. Raise it when most cores sit idle during a build.
• Load first, index second: creating the index after the initial bulk load is faster than maintaining it row by row (and it is required for IVFFlat training).
• Scale up for the build: Microsoft suggests temporarily scaling the server's compute and memory for a large build and scaling back down afterwards.

Lowering ef_construction or m also shortens a build, but it lowers graph quality and recall, so it is the wrong lever when quality must be kept.

Store embeddings in a column whose type declares the model's dimension count, such as vector(1536), because pgvector can only index a column with declared dimensions. An undimensioned vector column accepts rows but CREATE INDEX fails with column does not have dimensions; the same applies to halfvec and to every index type. A declared size also rejects rows of the wrong length. A plain real[] array has no vector index support.

The 2,000-dimension limit

HNSW and IVFFlat index vector columns of up to 2,000 dimensions; above that, the build fails with column cannot have more than 2000 dimensions for hnsw index (memory settings do not change it). Larger embeddings, such as the 3,072 of text-embedding-3-large, have two options:

• Index a half-precision cast: halfvec indexes support up to 4,000 dimensions. An expression index keeps the stored column at full precision.
• Shorten the embeddings with the model's dimensions parameter, which changes what you store.

CREATE INDEX ON docs USING hnsw
  ((embedding::halfvec(3072)) halfvec_cosine_ops);

SELECT id FROM docs
ORDER BY embedding::halfvec(3072) <=> %(q)s::halfvec(3072)
LIMIT 10;

PostgreSQL uses an expression index only when the query contains the same expression. Ordering by the raw embedding column (or casting only the query vector) leaves the index unused, and neither ANALYZE nor enable_seqscan = off can fix an expression mismatch.

Vector search performs best when the index fits in memory, so choose the compute tier for memory per vCore, and choose storage and compute size together for IOPS.

HNSW searches jump around the graph. An index that does not fit in memory turns into random disk reads, which shows as high read IOPS and latency with low CPU. The fix is more memory per vCore (Memory Optimized at the same vCore count) so the index stays cached; maintenance_work_mem serves builds, not caching.

Storage and IOPS

• Premium SSD: IOPS come with the disk size, so a small disk cannot deliver high IOPS without buying unused capacity.
• Premium SSD v2: capacity, IOPS and throughput are set independently, so a small, IOPS-hungry vector database can get exactly the IOPS it needs. IOPS are provisioned separately from size (beyond a free baseline), although the maximum you can provision grows with disk size.
• Compute IOPS cap: each compute size also has a maximum IOPS and I/O bandwidth (small 2-vCore sizes have the lowest ceilings). The lower of the disk and VM limits wins: if the IOPS metric plateaus at the VM's limit while the disk has headroom, scale the compute, not the storage.

Storage size can be increased but not decreased, so grow it deliberately.

The azure_ai extension lets SQL call Azure AI services from the database, so embeddings can be generated without an application-side batch job. After allow-listing and creating the extension, set the Azure OpenAI endpoint and key with azure_ai.set_setting, then call azure_openai.create_embeddings.

The separate azure_storage extension only imports and exports data with Azure Blob Storage.

create_embeddings takes the deployment name first, then the text. The single-text overload returns real[], which Microsoft's samples cast with ::vector before using it with a distance operator; the text[] overload is the batch form and returns a set of rows.

-- Store embeddings for existing rows
UPDATE chunks
SET embedding = azure_openai.create_embeddings('text-embed', content)::vector
WHERE embedding IS NULL;

-- Rank against a question
SELECT chunk_id, content
FROM chunks
WHERE region = 'emea'
ORDER BY embedding <=>
  azure_openai.create_embeddings('text-embed', 'how do I rotate keys?')::vector
LIMIT 5;

In a retrieval query the query vector must come from the question; embedding each row's own content there compares rows with themselves and ranks nothing.

In retrieval-augmented generation (RAG), the retrieval step returns the chunks closest to the question's embedding that the user is allowed to use, and passes them to the model as grounding. In PostgreSQL that is one query: every business rule in WHERE, ranking by the index's operator ascending, and LIMIT k.

SELECT id, content
FROM chunks
WHERE team_id = %(team)s
  AND archived = false
ORDER BY embedding <=> %(q)s::vector
LIMIT 5;

• Combine rules with AND; OR admits rows that break one of them.
• Filter in the database, not in application code after the LIMIT: post-filtering in Python can leave fewer than k chunks (or none) and fetches rows the user may not see.
• Use the operator that matches the index (<=> for vector_cosine_ops).

Filtering jsonb metadata

Metadata often lives in a jsonb column. The operators you need for filters:

Distance thresholds

A rule such as "never pass a chunk beyond a set cosine distance" belongs in WHERE, but a threshold alone does not use the index and, without ORDER BY, LIMIT keeps arbitrary qualifying rows. Keep the threshold and add the ordering, so the index returns the nearest chunks first and the filter drops the far ones (fewer than k may come back):

WHERE embedding <=> %(q)s::vector < 0.4
ORDER BY embedding <=> %(q)s::vector
LIMIT 5;

Approximate indexes apply the WHERE filter after the index scan, so a selective filter can return fewer rows than the LIMIT. An HNSW scan yields at most hnsw.ef_search candidates (default 40); if the filter matches only a small fraction of the table, few or none of those candidates survive it.

Iterative index scans (pgvector 0.8.0 and later)

Flexible server ships pgvector 0.8.x, which can keep scanning the index until enough rows pass the filter:

• hnsw.iterative_scan = strict_order or relaxed_order turns it on for HNSW (off by default). relaxed_order is faster but can return results slightly out of distance order; re-sort them in an outer query (for example over a materialized CTE) if exact order matters.
• hnsw.max_scan_tuples (default 20,000) caps how far an iterative scan goes. It has no effect while iterative scans are off.
• IVFFlat has ivfflat.iterative_scan = relaxed_order and ivfflat.max_probes.

BEGIN;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SELECT id FROM chunks
WHERE tenant_id = %(t)s
ORDER BY embedding <=> %(q)s::vector
LIMIT 10;
COMMIT;

Without iterative scans, the candidate list stays capped however the planner is pushed toward the index.

An index per filter value

When the filter column has only a few distinct values (languages, regions), give each value its own ANN index:

• Partial indexes: CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WHERE language = 'en'. Every candidate passes the filter, and the index is smaller and faster.
• Partitioning: list-partition the table by the filter column and index each partition; partition pruning sends each query to one partition's index.

A B-tree on the filter column alone narrows rows but then computes distances exactly, without an approximate index; that suits very selective filters, not a requirement for ANN search per value.

Partial index predicate matching

PostgreSQL uses a partial index only when it can prove the query's WHERE clause implies the index predicate. It does not know your data, so lower(status) = 'published' (or a LIKE pattern) does not prove status = 'published', even if every value is lowercase. Write the filter in exactly the indexed form.