Accelerate vector search with the columnar engine

You can use the AlloyDB for PostgreSQL columnar engine to accelerate your vector searches when you use the Scalable Nearest Neighbors (ScaNN) or Hierarchical Navigable Small World (HNSW) index. The columnar engine acts as a read-optimized, in-memory cache for these vector indexes.

Caching your indexes in the columnar engine serves queries directly from a read-optimized, in-memory representation of the index, and increases the number of queries per second (QPS) that your database can handle for vector search workloads.

You can only use HNSW with columnar engine for AlloyDB clusters running PostgreSQL 17 or later. ScaNN with columnar engine has no such limitation.

Before you begin

• Set the google_columnar_engine.enabled and google_columnar_engine.enable_index_caching database flags to on to enable the columnar engine and its index caching feature.

   gcloud  
  alloydb  
  instances  
  update  
   INSTANCE_ID 
    
   \ 
    
  --database-flags  
  google_columnar_engine.enabled = 
  on,google_columnar_engine.enable_index_caching = 
  on  
   \ 
    
  --region = 
   REGION 
    
   \ 
    
  --cluster = 
   CLUSTER_ID 
    
   \ 
    
  --project = 
   PROJECT_ID 
   
  

  Replace the following:

  • INSTANCE_ID : the ID of the instance where you want to enable the columnar engine.
  • REGION : the region where your instance is located—for example, us-central1 .
  • CLUSTER_ID : the ID of the cluster where your instance is located.
  • PROJECT_ID : the ID of the project where your cluster is located.

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

• Create a ScaNN index or Create an HNSW index in your database.

Add an index to the columnar engine

After enabling the columnar engine, you can add an existing index to the cache using the google_columnar_engine_add_index() SQL function.

To add an index to the columnar engine, follow these steps:

1. In the Google Cloud console, go to the Clusterspage.

   Go to Clusters

2. To display the cluster Overviewpage, click the name of the AlloyDB cluster in the Resource namecolumn.

3. In the navigation pane, click AlloyDB Studio.

4. Sign in to AlloyDB Studiousing the name of your database, username, and password.

5. In the Editor 1tab, enter the following query:

     SELECT 
     
    google_columnar_engine_add_index 
    ( 
    ' INDEX_NAME 
   ' 
    ); 
    
   

   Replace INDEX_NAME with the name of your vector index.

   1. Click Run.

After you add an index to the columnar engine, all queries that use this index are automatically accelerated by the columnar engine. You can verify that your vector queries are being accelerated by the columnar engine by using the EXPLAIN (ANALYZE, COLUMNAR_ENGINE) plan on your query.

Verify cache usage

To verify that your vector queries are being accelerated by the columnar engine, you can use the EXPLAIN (ANALYZE, COLUMNAR_ENGINE) plan on your query.

Example ScaNN execution plan

The following is an example of an execution plan for a query that uses an ScaNN index that has been added to the columnar engine:

  EXPLAIN 
  
 ( 
 ANALYZE 
  
 TRUE 
 , 
  
 SCANN 
  
 TRUE 
 , 
  
 COSTS 
  
 FALSE 
 , 
  
 TIMING 
  
 FALSE 
 , 
  
 SUMMARY 
  
 FALSE 
 , 
  
 VERBOSE 
  
 FALSE 
 , 
  
 COLUMNAR_ENGINE 
  
 TRUE 
 ) 
 SELECT 
  
 * 
  
 FROM 
  
 t 
  
 ORDER 
  
 BY 
  
 val 
  
< = 
>  
 '[0.5,0.5,0.5,0.5]' 
  
 LIMIT 
  
 100 
 ; 
 --This contains details about ScaNN's usage from the columnar engine. Example: 
 
 
  
 Index 
  
 Scan 
  
 using 
  
 scann_idx 
  
 on 
  
 t 
  
 t_1 
  
 ( 
 actual 
  
 rows 
 = 
 100 
  
 loops 
 = 
 1 
 ) 
  
 Order 
  
 By 
 : 
  
 ( 
 val 
  
< = 
>  
 '[0.5,0.5,0.5,0.5]' 
 :: 
 vector 
 ) 
  
 Limit 
 : 
  
 100 
  
 ScaNN 
  
 Info 
 : 
  
 (... 
  
 columnar 
  
 engine 
  
 nodes 
  
 hit 
 = 
 6 
 ...) 
  
 Columnar 
  
 Engine 
  
 ScaNN 
  
 Info 
 : 
  
 ( 
 index 
  
 found 
 = 
 true 
 ) 
 ( 
 5 
  
 rows 
 ) 
 

The presence of columnar engine nodes hit and Columnar Engine ScaNN Info: (index found=true) in the output confirms that the columnar engine is used for the query.

Example HNSW execution plan

The execution plan displays the Columnar Engine HNSW Infosection for the respective index that shows metrics such as the ratio of elements retrieved from the columnar engine ( elements_from_ce ) and from the disk ( elements_from_disk ).

The following is an example of an execution plan for a query that uses an HNSW index that has been added to the columnar engine:

  EXPLAIN 
  
 ( 
 ANALYZE 
 , 
  
 COLUMNAR_ENGINE 
 ) 
  
 SELECT 
  
 * 
  
 FROM 
  
 documents 
  
 ORDER 
  
 BY 
  
 embedding 
  
< = 
>  
 '[0.1, 0.2, 0.3, 0.4, 0.5]' 
 :: 
 vector 
  
 LIMIT 
  
 5 
 ; 
 --This contains details about HNSW's usage from the columnar engine. Example: 
 
 
  
 Limit 
  
 ( 
 actual 
  
 rows 
 = 
 5 
  
 loops 
 = 
 1 
 ) 
  
 - 
>  
 Index 
  
 Scan 
  
 using 
  
 hnsw_idx 
  
 on 
  
 documents 
  
 ( 
 actual 
  
 rows 
 = 
 5 
  
 loops 
 = 
 1 
 ) 
  
 Order 
  
 By 
 : 
  
 ( 
 embedding 
  
 '[0.1, 0.2, 0.3, 0.4, 0.5]' 
 :: 
 vector 
 ) 
  
 Columnar 
  
 Engine 
  
 HNSW 
  
 Info 
 : 
  
 ( 
 index 
  
 found 
 = 
 true 
  
 elements_from_ce 
 = 
 385 
  
 elements_from_disk 
 = 
 0 
 ) 
  
 Columnar 
  
 Check 
 : 
  
 table 
  
 is 
  
 not 
  
 in 
  
 the 
  
 columnar 
  
 store 
 ( 
 5 
  
 rows 
 ) 
 

The response shows that the index is being accelerated by the columnar engine, as all the elements were retrieved from the columnar engine ( elements_from_ce=385 ) and none were retrieved from the disk ( elements_from_disk=0 ).

When data modifications such as INSERT , UPDATE , or DELETE statements invalidate cache entries, the columnar engine uses a hybrid approach to maintain accuracy and performance. It reads valid, cached vectors directly from memory, and fetches only the modified or new vectors from disk.

If you modify a large amount of data, you might temporarily see an increase in elements_from_disk and a drop in performance until the cache refreshes.

Manage the cached index

To manage the lifecycle of your cached indexes, follow these steps:

1. In the Google Cloud console, go to the Clusterspage.

   Go to Clusters

2. To display the cluster Overviewpage, click the name of the AlloyDB cluster in the Resource namecolumn.

3. In the navigation pane, click AlloyDB Studio.

4. Sign in to AlloyDB Studiousing the name of your database, username, and password.

5. In the Editor 1tab, enter the SQL command for your selected task:

   • To refresh the cache manually, run the following command:

       SELECT 
       
      google_columnar_engine_refresh_index 
      ( 
      ' INDEX_NAME 
     ' 
      ); 
      
     

   • To verify index status, run the following command:

       SELECT 
       
      google_columnar_engine_verify 
      ( 
      ' INDEX_NAME 
     ' 
      ); 
      
     

   • To drop the index from the cache, run the following command:

       SELECT 
       
      google_columnar_engine_drop_index 
      ( 
      ' INDEX_NAME 
     ' 
      ); 
      
     

   • To view active indexes, run the following command:

       SELECT 
       
      * 
       
      FROM 
       
      g_columnar_indexes 
      ; 
      
     

   • To view partitioned indexes, run the following command:

       SELECT 
       
      * 
       
      FROM 
       
      g_columnar_index_partitions 
      ; 
      
     

   Replace INDEX_NAME with the name of your index.

6. Click Run.

Limitations

Refreshing columnar engine accelerated HNSW indexes can temporarily consume memory up to two times the index size.

What's next

• Create an HNSW index
• Create a ScaNN index
• Columnar engine overview
• Manage ScaNN indexes in the columnar engine
• Perform vector search