Create indexes

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

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 version 0.5.0 or later that is based on pgvector, extended by Google for AlloyDB is installed.

    CREATE EXTENSION IF NOT EXISTS vector;

  • To generate ScaNN indexes, install the alloydb_scann extension in addition to the vector extension.

    CREATE EXTENSION IF NOT EXISTS alloydb_scann;

Create an index

You can create one of the following index types for tables in your database.

Create a ScaNN index

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.

Two-level tree ScaNN 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 ScaNN 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 = MAX_NUM_LEVELS);

Replace the following:

  • 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 to 2 for three-level tree-based quantization.

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 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, and the building index: tree training phase disappears after the index is created.

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

Analyze your indexed table

After you create the ScaNN index, you must run the ANALYZE command to update statistics about your data.

ANALYZE TABLE;

What's next