Create a ScaNN index

Use stored embeddings to generate ScaNN vector indexes and query embeddings with AlloyDB for PostgreSQL.

The ScaNN index is a Google-made, 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.

  If you try to generate a ScaNN index on an empty or partitioned table, then you might encounter some issues. For more information about the errors generated, see Troubleshoot ScaNN index errors . To create an index on an empty or small table, see Deferred index creation for empty or nearly empty tables .

• vector and alloydb_scann extensions are installed:

    CREATE 
    
   EXTENSION 
    
   IF 
    
   NOT 
    
   EXISTS 
    
   alloydb_scann 
    
   CASCADE 
   ; 
   
  

  Installing the alloydb_scann extension automatically checks to see if the vector extension is installed and installs it if it isn't. You don't need to manually install vector separately.

• If you want to create a four-level ScaNN index, you must first enable the Preview feature for your AlloyDB instance. To enable the Preview feature, choose one of the following two methods:

  • Enable the scann.enable_preview_features database flag.

    For more information on configuring database flags, see Configure database flags .

  • Set scann.max_allowed_num_levels database flag to 3 at the session or instance-level. To set the flag at the session level, run the following command:

      SET 
      
     scann 
     . 
     max_allowed_num_levels 
      
     = 
      
     3 
     ; 
     
    

    To set the flag at the instance level, run gcloud alloydb alloydb instances update using the --database-flags field.

Create an automatically-tuned index

Automatically-tuned ScaNN indexes simplify index creation by letting AlloyDB manage and tune the index structure. If you need granular control over index tuning, create a manually-tuned ScaNN index .

Automatically-tuned indexes can be optimized in two ways:

• (Default) Vector search recall and latency at the cost of index build time
• Balance index build time and search performance

To create an automatically-tuned ScaNN index, run the following command.

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
 

Replace the following:

• INDEX_NAME : name of the index that you want to create. For example, my_scann_index . Index names are shared across your database. Verify that each index name is unique to each table in your database.

• TABLE : table to add the index to.

• EMBEDDING_COLUMN : column that stores vector data.

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

  • L2 distance: l2

  • Dot product: dot_product

  • Cosine distance: cosine

This command creates a ScaNN index that is optimized for search performance and performs automatic index maintenance . If you want to change either of these settings, run the following command:

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
 WITH 
  
 ( 
 MODE 
 = 
 'AUTO' 
 , 
  
 OPTIMIZATION 
 = 
 ' OPTIMIZATION 
' 
 , 
  
 auto_maintenance 
 = 
 ' AUTO_MAINTENANCE 
' 
 ) 
 

Replace the following:

• INDEX_NAME : name of the index that you want to create. For example, my_scann_index . Index names are shared across your database. Verify that each index name is unique to each table in your database.

• TABLE : table to add the index to.

• EMBEDDING_COLUMN : column that stores vector data.

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

  • L2 distance: l2

  • Dot product: dot_product

  • Cosine distance: cosine

• (Optional) OPTIMIZATION : set to one of the following:

  • (Default) SEARCH_OPTIMIZED : optimize both vector search recall and vector search latency at the cost of longer index build times.

  • BALANCED : balance index build time and search performance.

  If OPTIMIZATION is set, MODE='AUTO' must also be included.

• (Optional) AUTO_MAINTENANCE : controls whether automatic maintenance of the index is enabled or disabled. For more information on automatic maintenance, see Maintain vector indexes .

  • (Default) ON : AlloyDB performs automatic maintenance on the index.

  • OFF : AlloyDB doesn't perform automatic maintenance on the index.

Create a manually-tuned index

If your application has specific requirements for recall and index build times, you can manually create and tune the ScaNN index.

To manually create a ScaNN index for a column containing stored vector embeddings, see the following commands.

 CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
 WITH 
  
 ( 
 mode 
 = 
 'MANUAL' 
 , 
  
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 quantizer 
 = 
  QUANTIZER 
 
 , 
  
 auto_maintenance 
 = 
  AUTO_MAINTENANCE 
 
 ); 

 CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
  
 USING 
  
 scann 
  
 ( 
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION 
 
 ) 
 WITH 
  
 ( 
 mode 
 = 
 'MANUAL' 
 , 
  
 num_leaves 
 = 
  NUM_LEAVES_VALUE 
 
 , 
  
 quantizer 
 = 
  QUANTIZER 
 
 , 
  
 auto_maintenance 
 = 
  AUTO_MAINTENANCE 
 
 , 
  
 max_num_levels 
  
 = 
  
 2 
 ); 

 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 
  
 = 
  
 3 
 ); 

Convert a manually-tuned index to an automatically-tuned index

To convert a manually-tuned index to an automatically-tuned index, complete the following steps:

1. Reset all query parameters defined for your manually-tuned index.

     ALTER 
     
    INDEX 
     
     INDEX_NAME 
    
     
    RESET 
     
    ( 
     PARAMETER_NAME 
    
    ); 
    
   

   Replace the following variables:

   • INDEX_NAME : name of the index that you want to convert. For example, my_scann_index . Index names are shared across your database. Verify that each index name is unique to each table in your database.

   • PARAMETER_NAME : comma-separated list containing the names of the query parameters you want to reset. For example, num_leaves, quantization .

     Note that you must reset all other query parameters before resetting num_leaves .

2. Reindex your manually-tuned index to convert it to an automatically-tuned index.

     REINDEX 
     
    INDEX 
     
    CONCURRENTLY 
     
     INDEX_NAME 
    
    ; 
    
   

Create a ScaNN index for real[] data types

To create an index for an embedding column that uses the real[] data type instead of vector , cast the column into the vector data type:

  CREATE 
  
 INDEX 
  
  INDEX_NAME 
 
  
 ON 
  
  TABLE 
 
 USING 
  
 scann 
  
 ( 
 CAST 
 ( 
  EMBEDDING_COLUMN 
 
  
 AS 
  
 vector 
 ( 
  DIMENSIONS 
 
 )) 
  
  DISTANCE_FUNCTION 
 
 ) 
 

Replace the following:

• INDEX_NAME : name of the index that you want to create. For example, my_scann_index . Index names are shared across your database. Verify that each index name is unique to each table in your database.

• TABLE : table to add the index to.

• EMBEDDING_COLUMN : column that stores vector data.

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

  • L2 distance: l2

  • Dot product: dot_product

  • Cosine distance: cosine

View indexing progress

To view the indexing progress, use the pg_stat_progress_create_index view:

  SELECT 
  
 * 
  
 FROM 
  
 pg_stat_progress_create_index 
 ; 
 

The phase column shows the current state of your index creation. After the index building phase is complete, the row for the index isn't visible.

Create a deferred index for empty tables or tables with insufficient rows

By default, you can't create a ScaNN index on a table that is empty or has fewer rows than the value of the num_leaves index option.

To bypass this limitation, enable deferred index creation to let AlloyDB defer index creation until the number of rows in the table reaches the threshold that num_leaves defines. After the threshold is met, AlloyDB starts the index build in the background.

This deferred operation is a non-blocking process, allowing other database operations like reads and writes to continue without interruption. Because the index rebuild happens in the background, deferred index creation is suitable when tables ingest data rows in small batches. The index rebuild is automatically triggered after the number of rows reaches the threshold.

However, if you are planning to insert large number of rows into the table in a single transaction, we recommend that you split the transaction into multiple transactions or that you generate a ScaNN index without enabling deferred index creation.

Enable deferred index creation

To enable deferred index creation, follow these steps:

1. Enable the scann.enable_index_maintenance flag (enabled by default) and the scann.enable_preview_features flag. The scann.enable_preview_features flag also enables other preview features.

    gcloud  
   alloydb  
   instances  
   update  
    INSTANCE_ID 
     
    \ 
     
   --database-flags  
   scann.enable_index_maintenance = 
   on  
    \ 
     
   --database-flags  
   scann.enable_preview_features = 
   on  
    \ 
     
   --region = 
    REGION_ID 
     
    \ 
     
   --cluster = 
    CLUSTER_ID 
     
    \ 
     
   --project = 
    PROJECT_ID 
    
   

   Replace the following:

   • INSTANCE_ID : The ID of the instance.
   • REGION_ID : The region where the instance is placed—for example, us-central1 .
   • CLUSTER_ID : The ID of the cluster where the instance is placed.
   • PROJECT_ID : The ID of the project where the cluster is placed.

2. Create a ScaNN index. If you create an index in manual mode, make sure that the auto_maintenance parameter is set to on . For more information, see Create a manually-tuned index .

Limitations

• The automatic index creation background process uses database-level flag values. Even if you set any session-level flags using the SET LOCAL command, the process considers the flag value set at the database-level.
• If you plan to bulk insert a large amount of data into an empty table in a single transaction, we recommend that you run the single insert transaction and then create a ScaNN index.

Force index creation on empty or small tables

AlloyDB uses validations to prevent the creation of a ScaNN index on an empty table or a table with very few rows for the following reasons:

• ScaNN index trains on insufficient data. This can result in poor recall for vector similarity searches.

• Write performance to the database might degrade.

We recommend that you defer index creation in suboptimal performance.

However, in some development or testing scenarios, you might need to create an index on an empty or small table. In these cases, you can enforce index creation. Note that forcing index creation requires SUPERUSER privileges.

To force index creation, complete the following steps:

1. Set the scann.allow_blocked_operations creation session-level parameter to true on the database:

     SET 
     
    scann 
    . 
    allow_blocked_operations 
     
    = 
     
    true 
    ; 
    
   

2. If the user you're using to run these queries doesn't have SUPERUSER privileges, assign them:

     CREATE 
     
    USER 
     
     USERNAME 
    
     
    WITH 
     
    SUPERUSER 
     
    PASSWORD 
     
     PASSWORD 
    
    ; 
    
   

   Replace the following variables:

   • USERNAME : name of the user you want to grant SUPERUSER privileges to.
   • PASSWORD : user's password.

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. This often triggers when creating a three or four-level ScaNN index or if your dataset exceeds 100 million rows.

Though AlloyDB automatically optimizes the number of parallel workers, you can tune the parallel workers using the following PostgreSQL query planning parameters:

• max_parallel_maintenance_workers
• max_parallel_workers
• min_parallel_table_scan_size

To avoid out-of-memory issues when you create the ScaNN index, ensure that the maintenance_work_mem and shared_buffers database flags are set to a value less than the total machine memory.

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 a text string, you can use the google_ml.embedding() function to translate the text into a vector.

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

  SELECT 
  
 * 
  
 FROM 
  
  TABLE 
 
 ORDER 
  
 BY 
  
  EMBEDDING_COLUMN 
 
  
  DISTANCE_FUNCTION_QUERY 
 
  
 google_ml 
 . 
 embedding 
 ( 
  
 model_id 
  
 = 
>  
 ' MODEL_ID 
' 
 , 
  
 content 
  
 = 
>  
 ' CONTENT 
' 
 ):: 
 vector 
 LIMIT 
  
  ROW_COUNT 
 
 

Replace the following variables:

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

• INDEX_NAME : name of the index you want to use. For example, my_scann_index .

• EMBEDDING_COLUMN : column containing the stored embeddings.

• DISTANCE_FUNCTION_QUERY : distance function to use with this query. Choose the query equivalent for the distance function when you created the index:

  • L2 distance: <->

  • Inner product: <#>

  • Cosine distance: <=>

• MODEL_ID : ID of the registered embedding model you want to use.

• CONTENT : the text string you want to translate into an embedding and search for.

• ROW_COUNT : number of rows to return. For example, specify 1 if you want the single, best match.

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 .