Create a ScaNN index

This page describes how to use stored embeddings to generate indexes and query embeddings using ScaNN index with AlloyDB for PostgreSQL. For more information about storing embedding, see Store vector embeddings.

AlloyDB alloydb_scann, a PostgreSQL extension developed by Google that implements a highly efficient nearest-neighbor index powered by the ScaNN algorithm.

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

  • The vector extension that is based on pgvector, extended by Google for AlloyDB, and the alloydb_scann extension is installed:

    CREATE EXTENSION IF NOT EXISTS alloydb_scann CASCADE;

  • If you want to create automatically tuned ScaNN indexes, make sure that the scann.enable_preview_features flag is enabled. If you don't want to enable preview features, or for production instances, you can create a ScaNN index with specific parameters instead.

Create an automatically tuned ScaNN index

With the auto index feature, you can simplify index creation to automatically create indexes that are optimized for search performance or balanced index build times and search performance.

When you use the AUTO mode, you only need to specify the table name and embedding column along with the distance function that you want to use. You can optimize the index for search performance or balance between index build times and search performance.

There is also an option to use the MANUAL mode to create indexes with granular control over other index tuning parameters.

Create a ScaNN index in AUTO mode

Some points to note before creating indexes in AUTO mode are as follows:

  • AlloyDB can't create a ScaNN index for tables with insufficient data.
  • You can't set index creation parameters, such as num_leaves, when you create indexes in AUTO mode.
  • Auto maintenance is enabled by default for all indexes created in AUTO mode.

To create an index in AUTO mode, run the following command:

      CREATE INDEX INDEX_NAME ON TABLE \
      USING scann (EMBEDDING_COLUMN DISTANCE_FUNCTION) \
      WITH (mode=AUTO', optimization='OPTIMIZATION');

Replace the following:

  • INDEX_NAME: the name of the index that you want to create—for example, my-scann-index. The index names are shared across your database. Verify that each index name is unique to each table in your database.

  • TABLE: the table to add the index to.

  • EMBEDDING_COLUMN: the column that stores vector data.

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

    • L2 distance: l2

    • Dot product: dot_product

    • Cosine distance: cosine

  • OPTIMIZATION: Set to one of the following:

    • SEARCH_OPTIMIZED: to optimize both vector search recall and vector search latency at a cost of longer index build time.
    • BALANCED: to create an index that balances index build time and search performance.

Create a ScaNN index in MANUAL mode

If you enabled the scann.enable_preview_features flag and you want granular control over the tuning parameters, you can create the index in MANUAL mode.

To create a ScaNN index in MANUAL mode, run the following command:

      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=MAX_NUM_LEVELS]);

Replace the following:

  • INDEX_NAME: the name of the index you want to create—for example, my-scann-index. The index names are shared across your database. Verify that each index name is unique to each table in your database.

  • TABLE: the table to add the index to.

  • EMBEDDING_COLUMN: the column that stores vector data.

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

    • L2 distance: l2

    • Dot product: dot_product

    • Cosine distance: cosine

  • NUM_LEAVES_VALUE: the number of partitions to apply to this index. Set to any value between 1 to 1048576.

  • QUANTIZER: the type of quantizer you want to use for the K-means tree. The default value is set to SQ8 which provides better query performance with minimal recall loss (typically less than 1-2%). You can also set it to FLAT for a recall of 99% or higher.

  • MAX_NUM_LEVELS: the maximum number of levels of the K-means clustering tree. Set to 1(default) for two-level tree-based quantization, and set to 2 for three-level tree-based quantization.

You can add other index creation or query runtime parameters to tune your index. For more information, see Tune a ScaNN index.

Change modes for existing indexes

If you created a ScaNN index using the AUTO mode and you want to tune the index manually, then you must change the mode to MANUAL.

To change mode to MANUAL, follow these steps:

  1. Update the index to set the mode to MANUAL:

    ALTER INDEX INDEX_NAME SET (mode = 'MANUAL', num_leaves = NUM_LEAVES_VALUE);

    Replace the following:

    • INDEX_NAME: the name of the index you want to create—for example, my-scann-index. The index names are shared across your database. Verify that each index name is unique to each table in your database.

    • NUM_LEAVES_VALUE: the number of partitions to apply to this index. Set to any value between 1 to 1048576.

    You can add other index creation or query runtime parameters to tune your index. For more information, see Tune a ScaNN index.

  2. Rebuild your index to apply the parameters:

    REINDEX INDEX CONCURRENTLY INDEX_NAME;

To change mode to AUTO, complete the following steps:

  1. Update the index to set the mode to AUTO:

    ALTER INDEX INDEX_NAME SET (mode = 'AUTO');

  2. Rebuild your index to apply the parameters:

    REINDEX INDEX CONCURRENTLY INDEX_NAME;

Create a ScaNN index with specific parameters

If your application has specific requirements for recall and index build times, then you can manually create the index. You can create a two-level or three-level tree index based on your workload. For more information about tuning parameters, see Tune a ScaNN index.

Two-level tree index

To apply a two-level tree index using the ScaNN algorithm to a column containing stored vector embeddings, run the following DDL query:

CREATE INDEX INDEX_NAME ON TABLE
  USING scann (EMBEDDING_COLUMN DISTANCE_FUNCTION)
  WITH (num_leaves=NUM_LEAVES_VALUE);

Replace the following:

  • INDEX_NAME: the name of the index you want to create—for example, my-scann-index. The index names are shared across your database. Ensure that each index name is unique to each table in your database.

  • TABLE: the table to add the index to.

  • EMBEDDING_COLUMN: a column that stores vector data.

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

    • L2 distance: l2

    • Dot product: dot_product

    • Cosine distance: cosine

  • NUM_LEAVES_VALUE: the number of partitions to apply to this index. Set to any value between 1 to 1048576. For more information about how to decide this value, see Tune a ScaNN index.

Three-level tree index

To create a three-level tree index using the ScaNN algorithm to a column containing stored vector embeddings, run the following DDL query:

CREATE INDEX INDEX_NAME ON TABLE
  USING scann (EMBEDDING_COLUMN DISTANCE_FUNCTION)
  WITH (num_leaves=NUM_LEAVES_VALUE, max_num_levels = 2);

After you create the index, you can run nearest-neighbor search queries that make use of the index by following the instructions in Make a nearest-neighbor query with given text.

The index parameters must be set to strike a right balance between QPS and recall. For more information about tuning the ScaNN index, see Tune a ScaNN index.

To create this index on 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)
  WITH (num_leaves=NUM_LEAVES_VALUE, max_num_levels = MAX_NUM_LEVELS);

Replace DIMENSIONS with the dimensional width of the embedding column. For more information about how to find the dimensions, see the vector_dims function in Vector functions.

To achieve a consistent search experience, enable auto-maintenance when you create a ScaNN index. For more information, see Maintain vector indexes. This feature is available in Preview.

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.

To tune your index for an average recall and QPS balance, see Tune a ScaNN index.

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.

The parallel index build is often triggered if you're creating a 3-level ScaNN index or if your dataset exceeds 100M rows.

Though AlloyDB automatically optimizes the number of parallel workers, you can tune the parallel workers using the max_parallel_maintenance_workers, max_parallel_workers, and the min_parallel_table_scan_size PostgreSQL query planning parameters.

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 an embedding vector, you can run the following example query, where you set the same distance function that you used during the index creation.

  SELECT * FROM TABLE
    ORDER BY EMBEDDING_COLUMN DISTANCE_FUNCTION_QUERY ['EMBEDDING']
    LIMIT ROW_COUNT

Replace the following:

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

  • INDEX_NAME: the name of the index you want to use—for example, my-scann-index.

  • EMBEDDING_COLUMN: the column containing the stored embeddings.

  • DISTANCE_FUNCTION_QUERY: the distance function to use with this query. Choose one of the following based on the distance function used while creating the index:

    • L2 distance: <->

    • Inner product: <#>

    • Cosine distance: <=>

  • EMBEDDING: the embedding vector you want to find the nearest stored semantic neighbors of.

  • ROW_COUNT: the number of rows to return.

    Specify 1 if you want only the single best match.

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

What's next