Create a ScaNN index

This page describes how to use stored embeddings to generate indexes and query embeddings using ScaNN index with AlloyDB for PostgreSQL. For more information about storing embedding, see Store vector embeddings .

AlloyDB alloydb_scann , a PostgreSQL extension developed by Google that implements a highly efficient nearest-neighbor index powered by the ScaNN algorithm .

The ScaNN index is a tree-based quantization index for approximate nearest neighbor search. It provides lower index building time and smaller memory footprint as compared to HNSW . In addition, it provides faster QPS in comparison to HNSW based on the workload.

Before you begin

Before you can start creating indexes, you must complete the following prerequisites.

• Embedding vectors are added to a table in your AlloyDB database.

• The vector extension that is based on pgvector , extended by Google for AlloyDB, and the alloydb_scann extension is installed:

    CREATE 
    
   EXTENSION 
    
   IF 
    
   NOT 
    
   EXISTS 
    
   alloydb_scann 
    
   CASCADE 
   ; 
   
  

• If you want to create automatically tuned ScaNN indexes, make sure that the scann.enable_preview_features flag is enabled. If you don't want to enable preview features, or for production instances, you can create a ScaNN index with specific parameters instead.

Create an automatically tuned ScaNN index

With the auto index feature, you can simplify index creation to automatically create indexes that are optimized for search performance or balanced index build times and search performance.

When you use the AUTO mode, you only need to specify the table name and embedding column along with the distance function that you want to use. You can optimize the index for search performance or balance between index build times and search performance.

There is also an option to use the MANUAL mode to create indexes with granular control over other index tuning parameters.

Create a ScaNN index in AUTO mode

Some points to note before creating indexes in AUTO mode are as follows:

• AlloyDB can't create a ScaNN index for tables with insufficient data.
• You can't set index creation parameters, such as num_leaves , when you create indexes in AUTO mode.
• Auto maintenance is enabled by default for all indexes created in AUTO mode.

To create an index in AUTO mode, enable the feature flag scann.zero_knob_index_creation first. After you enable the flag, run the following command:

   
 CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 \ 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
  
 \ 
  
 WITH 
  
 ( 
 mode 
 = 
 'AUTO' 
 ); 
 

Replace the following:

• INDEX_NAME : the name of the index that you want to create—for example, my-scann-index . The index names are shared across your database. Verify that each index name is unique to each table in your database.

• TABLE : the table to add the index to.

• EMBEDDING_COLUMN : the column that stores vector data.

• DISTANCE_FUNCTION : the distance function to use with this index. Choose one of the following:

  • L2 distance: l2

  • Dot product: dot_product

  • Cosine distance: cosine

• OPTIMIZATION (Optional): By default, a search optimized index is created. Set to one of the following:

  • SEARCH_OPTIMIZED (Default): to optimize both vector search recall and vector search latency at a cost of longer index build time.
  • BALANCED : to create an index that balances index build time and search performance.

Create a ScaNN index in MANUAL mode

If you enabled the scann.enable_preview_features flag and you want granular control over the tuning parameters, you can create the index in MANUAL mode.

To create a ScaNN index in MANUAL mode, run the following command:

   
 CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 \ 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
  
 \ 
  
 WITH 
  
 ( 
 mode 
 = 
 ' 
 MANUAL 
 , 
  
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 [ 
 quantizer 
  
 = 
  QUANTIZER 
 
 , 
  
 max_num_levels 
 = 
  MAX_NUM_LEVELS 
 
 ]); 
 

Replace the following:

• INDEX_NAME : the name of the index you want to create—for example, my-scann-index . The index names are shared across your database. Verify that each index name is unique to each table in your database.

• TABLE : the table to add the index to.

• EMBEDDING_COLUMN : the column that stores vector data.

• DISTANCE_FUNCTION : the distance function to use with this index. Choose one of the following:

  • L2 distance: l2

  • Dot product: dot_product

  • Cosine distance: cosine

• NUM_LEAVES_VALUE : the number of partitions to apply to this index. Set to any value between 1 to 1048576.

• QUANTIZER : the type of quantizer to use. Available options are as follows:

  • SQ8 : provides a balance of query performance with minimal recall loss, typically less than 1-2%. This is the default value.
  • AH : consider this for potentially better query performance when the columnar engine is enabled and your index and table data are populated into the columnar engine, subject to its configured size. Note that AH is up to 4x compressed when compared with SQ8 . For more information, see Best practices for tuning ScaNN .
  • FLAT : provides the highest recall of 99% or higher at the cost of search performance.

• MAX_NUM_LEVELS : the maximum number of levels of the K-means clustering tree. Set to 1 (default) for two-level tree-based quantization, and set to 2 for three-level tree-based quantization.

You can add other index creation or query runtime parameters to tune your index. For more information, see Tune a ScaNN index .

Change modes for existing indexes

If you created a ScaNN index using the AUTO mode and you want to tune the index manually, then you must change the mode to MANUAL .

To change mode to MANUAL , follow these steps:

1. Update the index to set the mode to MANUAL :

     ALTER 
     
    INDEX 
     
     INDEX_NAME 
    
     
    SET 
     
    ( 
    mode 
     
    = 
     
    'MANUAL' 
    , 
     
    num_leaves 
     
    = 
     
     NUM_LEAVES_VALUE 
     ); 
    
    
    
      
    
   

   Replace the following:

   • INDEX_NAME : the name of the index you want to create—for example, my-scann-index . The index names are shared across your database. Verify that each index name is unique to each table in your database.

   • NUM_LEAVES_VALUE : the number of partitions to apply to this index. Set to any value between 1 to 1048576.

   You can add other index creation or query runtime parameters to tune your index. For more information, see Tune a ScaNN index .

2. Rebuild your index to apply the parameters:

     REINDEX 
     
    INDEX 
     
    CONCURRENTLY 
     
     INDEX_NAME 
    
    ; 
    
   

To change mode to AUTO , complete the following steps:

1. Update the index to set the mode to AUTO :

     ALTER 
     
    INDEX 
     
     INDEX_NAME 
    
     
    SET 
     
    ( 
    mode 
     
    = 
     
    'AUTO' 
    ); 
    
   

2. Rebuild your index to apply the parameters:

     REINDEX 
     
    INDEX 
     
    CONCURRENTLY 
     
     INDEX_NAME 
    
    ; 
    
   

Create a ScaNN index with specific parameters

If your application has specific requirements for recall and index build times, then you can manually create the index. You can create a two-level or three-level tree index based on your workload. For more information about tuning parameters, see Tune a ScaNN index .

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
  
 WITH 
  
 ( 
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 quantizer 
  
 = 
  QUANTIZER 
 
 ); 
 

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
  
 WITH 
  
 ( 
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 max_num_levels 
  
 = 
  
 2 
 ); 
 

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
 CAST 
 ( 
  EMBEDDING_COLUMN 
 
  
 AS 
  
 vector 
 ( 
  DIMENSIONS 
 
 )) 
  
  DISTANCE_FUNCTION 
 
 ) 
  
 WITH 
  
 ( 
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 max_num_levels 
  
 = 
  
  MAX_NUM_LEVELS 
 
 ); 
 

  SELECT 
  
 * 
  
 FROM 
  
 pg_stat_progress_create_index 
 ; 
 

Build indexes in parallel

To build your index faster, AlloyDB might automatically spawn multiple parallel workers, depending on your dataset and the type of index that you choose.

The parallel index build is often triggered if you're creating a 3-level ScaNN index or if your dataset exceeds 100M rows.

Though AlloyDB automatically optimizes the number of parallel workers, you can tune the parallel workers using the max_parallel_maintenance_workers , max_parallel_workers , and the min_parallel_table_scan_size PostgreSQL query planning parameters.

Run a query

After you store and index the embeddings in your database, you can start querying your data. You cannot run bulk search queries using the alloydb_scann extension.

To find the nearest semantic neighbors for an embedding vector, you can run the following example query, where you set the same distance function that you used during the index creation.

   
 SELECT 
  
 * 
  
 FROM 
  
  TABLE 
 
  
 ORDER 
  
 BY 
  
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION_QUERY 
 
  
 [ 
 ' EMBEDDING 
' 
 ] 
  
 LIMIT 
  
  ROW_COUNT 
 
 

Replace the following:

• TABLE : the table containing the embedding to compare the text to.

• INDEX_NAME : the name of the index you want to use—for example, my-scann-index .

• EMBEDDING_COLUMN : the column containing the stored embeddings.

• DISTANCE_FUNCTION_QUERY : the distance function to use with this query. Choose one of the following based on the distance function used while creating the index:

  • L2 distance: <->

  • Inner product: <#>

  • Cosine distance: <=>

• EMBEDDING : the embedding vector you want to find the nearest stored semantic neighbors of.

• ROW_COUNT : the number of rows to return.

  Specify 1 if you want only the single best match.

You can also use the embedding() function to translate the text into a vector. Since embedding() returns a real array, you must explicitly cast the embedding() call to vector before applying it to one of the nearest-neighbor operators (e.g., <-> for L2 distance). These operators can then use the ScaNN index to find the database rows with the most semantically similar embeddings.

What's next

• Run vector similarity searches
• Tune vector query performance
• Vector index metrics
• Learn how to build a smart shopping assistant with AlloyDB, pgvector, and model endpoint management .