This page documents AlloyDB Omni version 18.1.0 using the container orchestrator deployment option. Choose a different deployment option .

Accelerate vector search with the columnar engine

Select a documentation version:

You can use the AlloyDB Omni 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 Omni 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.

Modify your database cluster manifest to add the parameters attribute to the primarySpec section:

  apiVersion 
 : 
  
 alloydbomni.dbadmin.goog/v1 
 kind 
 : 
  
 DBCluster 
 metadata 
 : 
  
 name 
 : 
  
  CLUSTER_NAME 
 
 spec 
 : 
  
 databaseVersion 
 : 
  
 "18.1.0" 
  
 primarySpec 
 : 
  
 parameters 
 : 
  
 google_columnar_engine.enabled 
 : 
  
 "on" 
  
 google_columnar_engine.enable_index_caching 
 : 
  
 "on"

Replace CLUSTER_NAME with the name of your database cluster. It is the same database cluster name you declared when you created it .

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, run the following query:

  SELECT 
  
 google_columnar_engine_add_index 
 ( 
 ' INDEX_NAME 
' 
 );

Replace INDEX_NAME with the name of your vector index.

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, run 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.

Limitations

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