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:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
To display the cluster Overview page, click the name of the AlloyDB cluster in the Resource name column.
In the navigation pane, click AlloyDB Studio.
Sign in to AlloyDB Studio using the name of your database, username, and password.
In the Editor 1 tab, 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 Info section 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:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
To display the cluster Overview page, click the name of the AlloyDB cluster in the Resource name column.
In the navigation pane, click AlloyDB Studio.
Sign in to AlloyDB Studio using the name of your database, username, and password.
In the Editor 1 tab, 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.
Click Run.

Limitations

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