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

What's next