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 version0.5.0
or later that is based onpgvector
, extended by Google for AlloyDB is installed.CREATE EXTENSION IF NOT EXISTS vector;
To generate
ScaNN
indexes, install thealloydb_scann
extension in addition to thevector
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 storesvector
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 aScaNN
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 to1
(default) for two-level tree-based quantization and to2
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.
Run a query
After you have stored and indexed embeddings in your database, you can start
querying using the pgvector
query
functionality. 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.
For more information about other query examples, see Querying.
You can use also use the embedding()
function to translate the
text into a vector. You apply the vector to one of the
pgvector
nearest-neighbor operator, <->
for L2 distance, to find the database rows with the
most semantically similar embeddings.
Because embedding()
returns a real
array, you must explicitly cast the
embedding()
call to vector
in order to use these values with pgvector
operators.
CREATE EXTENSION google_ml_integration VERSION '1.2';
CREATE EXTENSION IF NOT EXISTS vector;
SELECT * FROM TABLE
ORDER BY EMBEDDING_COLUMN::vector
<-> embedding('MODEL_IDVERSION_TAG', 'TEXT')
LIMIT ROW_COUNT
Replace the following:
MODEL_ID
: the ID of the model to query.If you are using the Vertex AI Model Garden, then specify
textembedding-gecko@003
as the model ID. These are the cloud-based models that AlloyDB can use for text embeddings. For more information, see Text embeddings.Optional:
VERSION_TAG
: the version tag of the model to query. Prepend the tag with@
.If you are using one of the
textembedding-gecko
English models with Vertex AI, then specify one of the version tags—for example,textembedding-gecko@003
, listed in Model versions.Google strongly recommends that you always specify the version tag. If you don't specify the version tag, then AlloyDB always uses the latest model version, which might lead to unexpected results.
TEXT
: the text to translate into a vector embedding.
What's next
- An example embedding workflow
- Tune vector query performance
- Vector index metrics
- Learn how to build a smart shopping assistant with AlloyDB, pgvector, and model endpoint management.