Enable and use DiskANN extension in Azure Database for PostgreSQL flexible server

DiskANN is a scalable approximate nearest neighbor search algorithm for efficient vector search at any scale. It offers high recall, high queries per second, and low query latency, even for billion-point datasets. Those characteristics make it a powerful tool for handling large volumes of data.

To learn more about DiskANN, see DiskANN: Vector Search for Web Scale Search and Recommendation.

The pg_diskann extension adds support for using DiskANN for efficient vector indexing and searching.

Enable pg_diskann

To use the pg_diskann extension on your Azure Database for PostgreSQL flexible server, you need to allow the extension at the server level. Then you need to create the extension on each database in which you want to use the functionality provided by the extension.

Because pg_diskann has a dependency on the vector extension, either you allow and create the vector extension in the same database, and the run the following command:

CREATE EXTENSION IF NOT EXISTS pg_diskann;

Or you can skip explicitly allowing and creating the vector extension, and run instead the previous command appending the CASCADE clause. That clause PostgreSQL to implicitly run CREATE EXTENSION on the extension that it depends. To do so, run the following command:

CREATE EXTENSION IF NOT EXISTS pg_diskann CASCADE;

To drop the extension from the database to which you're currently connected, run the following command:

DROP EXTENSION IF EXISTS pg_diskann;

Use the diskann index access method

After you install the extension, you can create a diskann index on a table column that contains vector data. For example, to create an index on the embedding column of the demo table, use the following command:

CREATE TABLE demo (
 id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 embedding public.vector(3)
 -- other columns
);

-- insert dummy data
INSERT INTO demo (embedding) VALUES
('[1.0, 2.0, 3.0]'),
('[4.0, 5.0, 6.0]'),
('[7.0, 8.0, 9.0]');

-- create a diskann index by using Cosine distance operator
CREATE INDEX demo_embedding_diskann_idx ON demo USING diskann (embedding vector_cosine_ops)

After you create the index, you can run queries to find the nearest neighbors.

The following query finds the five nearest neighbors to the vector [2.0, 3.0, 4.0]:

SELECT id, embedding
FROM demo
ORDER BY embedding <=> '[2.0, 3.0, 4.0]'
LIMIT 5;

Postgres automatically decides when to use the DiskANN index. If it chooses not to use the index in a scenario in which you want it to use the index, execute the following command:

-- Explicit Transcation block to force use for DiskANN index.

BEGIN;
SET LOCAL enable_seqscan TO OFF;
-- Similarity search queries
COMMIT;

Scale efficiently with Quantization (Preview)

DiskANN uses product quantization (PQ) to dramatically reduce the memory footprint of the vectors. Unlike other quantization techniques, the PQ algorithm can compress vectors more effectively, significantly improving performance.  By using PQ, DiskANN can keep more data in memory, reduce the need to access slower storage, and use less compute when comparing compressed vectors. This results in better performance and significant cost savings when working with larger amounts of data (> 1 million rows).

To reduce the size of your index and fit more data into memory, use PQ:

CREATE INDEX demo_embedding_diskann_idx ON demo USING diskann(embedding vector_cosine_ops) 
WITH(
    product_quantized=true
    );    

Improve accuracy when using PQ with vector reranking

Reranking with full vectors is a technique used in approximate nearest neighbor (ANN) search systems like DiskANN with Product Quantization (PQ) to improve result accuracy by reordering the top-N retrieved candidates using the original, uncompressed (full-precision) vectors. This reranking technique is based purely on exact vector similarity metrics (for example, cosine similarity or Euclidean distance). This technique is not the same as reranking using a ranking model.

To balance speed and precision in vector similarity search, implement a two-step reranking strategy when querying with DiskANN and product quantization to improve accuracy.

1. Initial Approximate Search: The inner query uses DiskANN to retrieve the top 50 approximate nearest neighbors based on cosine distance between the stored embeddings and the query vector. This step is fast and efficient, leveraging DiskANN’s indexing capabilities.

2. Precise Reranking: The outer query reorders those 50 results by their actual computed distance and returns the top 10 most relevant matches:

Here's an example of reranking using this two-step approach:

SELECT id
FROM (
    SELECT id, embedding <=> %s::vector AS distance
    FROM demo
    ORDER BY embedding <=> %s::vector asc
    LIMIT 50
) AS t
ORDER BY t.distance
LIMIT 10;

This approach balances speed (via approximate search) and accuracy (via full vector reranking), ensuring high-quality results without scanning the entire dataset.

Support for high dimension embeddings

Advanced Generative AI applications often rely on high-dimensional embedding models such as text-embedding-3-large to achieve superior accuracy. However, traditional indexing methods like HNSW in pgvector are limited to vectors with up to 2,000 dimensions, which restricts the use of these powerful models.

Starting in pg_diskann v0.6 and later, DiskANN supports indexing vectors with up to 16,000 dimensions, significantly expanding the scope for high-accuracy AI workloads.

Recommended settings:

• product_quantized: Set to true
• pq_param_num_chunks: Set to one-third of the embedding dimension for optimal performance.
• pq_param_training_samples: Automatically determined based on table size unless explicitly set.

This enhancement enables scalable, efficient search across large vector datasets while maintaining high recall and precision.

Speed up index build

To improve your index build times, try the following recommendations.

Use more memory

To speed up the creation of the index, increase the memory allocated on your PostgreSQL server for the index build. Specify the memory usage through the maintenance_work_mem parameter.

-- Set the parameters
SET maintenance_work_mem = '8GB'; -- Depending on your resources

The CREATE INDEX command uses the specified work memory, depending on the available resources, to build the index.

CREATE INDEX demo_embedding_diskann_idx ON demo USING diskann (embedding vector_cosine_ops)

Using parallelization

To speed up the creation of the index, use parallel workers. Specify the number of workers through the parallel_workers storage parameter of the CREATE TABLE statement when creating the table. You can adjust this number later by using the SET clause of the ALTER TABLE statement.

CREATE TABLE demo (
	id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	embedding public.vector(3)
) WITH (parallel_workers = 4);
ALTER TABLE demo SET (parallel_workers = 8);

The CREATE INDEX command uses the specified number of parallel workers, depending on the available resources, to build the index.

CREATE INDEX demo_embedding_diskann_idx ON demo USING diskann (embedding vector_cosine_ops)

If you want to create the index by using parallel workers, set the max_parallel_workers, max_worker_processes, and max_parallel_maintenance_workers parameters accordingly. For more information about these parameters, see parameters that control resource usages and asynchronous behavior.

Set these parameters at different granularity levels. For example, to set them at the session level, run the following statements:

-- Set the parameters
SET max_parallel_workers = 8;
SET max_worker_processes = 8; -- Note: Requires server restart
SET max_parallel_maintenance_workers = 4;

To learn about other options to configure these parameters in Azure Database for PostgreSQL flexible server, see Configure parameters.

If the configuration of those parameters and the available resources on the server don't permit launching the parallel workers, PostgreSQL automatically falls back to create the index in the nonparallel mode.

Configuration parameters

When you create a diskann index, specify various parameters to control its behavior.

Index parameters

• max_neighbors: Maximum number of edges per node in the graph. The default value is 32. A higher value can improve the recall up to a certain point.
• l_value_ib: Size of the search list during index build. The default value is 100. A higher value makes the build slower, but the index is of higher quality.
• product_quantized: Enables product quantization for more efficient search. The default value is false.
• pq_param_num_chunks: Number of chunks for product quantization. The default value is 0, which means the system automatically determines the value based on embedding dimensions. Use one-third of the original embedding dimensions.
• pq_param_training_samples: Number of vectors to train the PQ pivot table on. The default value is 0, which means the system automatically determines the value based on table size.

CREATE INDEX demo_embedding_diskann_custom_idx ON demo USING diskann (embedding vector_cosine_ops)
WITH (
 max_neighbors = 48,
 l_value_ib = 100,
 product_quantized=true, 
 pq_param_num_chunks = 0,
 pq_param_training_samples = 0
 );

Extension parameters

• diskann.iterative_search: Controls the search behavior.

  Configurations for diskann.iterative_search:

  • relaxed_order (default): Lets diskann iteratively search the graph in batches of diskann.l_value_is, until the desired number of tuples, possibly limited by LIMIT clause, are yielded. This option might cause the results to be out of order.

  • strict_order: Similar to relaxed_order, but it ensures that the results are returned in strict order sorted by distance.

  • off: Uses noniterative search functionality. It attempts to fetch diskann.l_value_is tuples in one step. Noniterative search can only return a maximum of diskann.l_value_is vectors for a query, regardless of the LIMIT clause or the number of tuples that match the query.

  To change the search behavior to strict_order for all queries executed in the current session, run the following statement:

  SET diskann.iterative_search TO 'strict_order';
  

  To change it so that it only affects all queries executed in the current transaction, run the following statement:

  BEGIN;
  SET LOCAL diskann.iterative_search TO 'strict_order';
  -- All your queries
  COMMIT;
  

• diskann.l_value_is: L value for index scanning (defaults to 100). Increasing the value improves recall but might slow down queries.

  To change the L value for index scanning to 20 for all queries executed in the current session, run the following statement:

  SET diskann.l_value_is TO 20;
  

  To change it so that it only affects all queries executed in the current transaction, run the following statement:

  BEGIN;
  SET LOCAL diskann.l_value_is TO 20;
  -- All your queries
  COMMIT;
  

Recommended configuration of parameters

CREATE INDEX and REINDEX progress

Starting with PostgreSQL 12, you can use pg_stat_progress_create_index to check the progress of the CREATE INDEX or REINDEX operations.

SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

To learn more about the possible phases through which a CREATE INDEX or REINDEX operation goes, see CREATE INDEX phases.

Selecting the index access function

The vector type supports three types of searches on the stored vectors. Select the correct access function for your index so the database can consider your index when executing your queries.

pg_diskann supports the following distance operators:

• vector_l2_ops: <-> Euclidean distance
• vector_cosine_ops: <=> Cosine distance
• vector_ip_ops: <#> Inner Product

Troubleshooting

Error: assertion left == right failed left: 40 right: 0:

• DiskANN GA version, v0.6.x introduces breaking changes in the index metadata format. Indexes created with v0.5.x aren't forward-compatible with v0.6.x insert operations. If you try to insert into a table with an outdated index, you get an error, even if the index appears valid.

• When you encounter this error, resolve it by:

  • Option 1: Executing REINDEX or REDINDEX CONCURRENTLY statement on the index.

  • Option 2: Rebuilding the index.

    DROP INDEX your_index_name;
    CREATE INDEX your_index_name ON your_table USING diskann(your_vector_column vector_cosine_ops);
    
    

Error: diskann index needs to be upgraded to version 2...:

• When you encounter this error, resolve it by:

  • Option 1: Executing REINDEX or REDINDEX CONCURRENTLY statement on the index.

  • Option 2: Because REINDEX might take a long time, the extension also provides a user-defined function called upgrade_diskann_index(), which upgrades your index faster, when possible.

    To upgrade your index, run the following statement:

    SELECT upgrade_diskann_index('demo_embedding_diskann_custom_idx');
    

    To upgrade all diskann indexes in the database to the current version, run the following statement:

    SELECT upgrade_diskann_index(pg_class.oid)
    FROM pg_class
    JOIN pg_am ON (pg_class.relam = pg_am.oid)
    WHERE pg_am.amname = 'diskann';
    

Related content

• Enable and use pgvector in Azure Database for PostgreSQL flexible server.
• Manage PostgreSQL extensions.