Vector assist function references

Reference the following detailed, vector assist functions to simplify your AlloyDB for PostgreSQL vector workloads .

`vector_assist.define_spec`

Defines a new vector specification, or vector spec , and returns recommendations necessary to step up the vector workload. This function applies user-provided specifications to generate the recommendations.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 define_spec 
 ( 
  
 spec_id 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 table_name 
  
 TEXT 
 , 
  
 schema_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 vector_column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 text_column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 vector_index_type 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- Allowed: 'hnsw', 'ivfflat' 
  
 embeddings_available 
  
 BOOL 
  
 DEFAULT 
  
 NULL 
 , 
  
 embedding_model 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 num_vectors 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 dimensionality 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 distance_func 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- Allowed: 'cosine', 'ip', 'l2', 'l1' 
  
 quantization 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- Allowed: 'none', 'halfvec', 'bit' 
  
 prefilter_column_names 
  
 TEXT 
 [] 
  
 DEFAULT 
  
 NULL 
 , 
  
 memory_budget_kb 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- In KB 
  
 target_recall 
  
 FLOAT 
  
 DEFAULT 
  
 NULL 
 , 
  
 target_top_k 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 tune_vector_index 
  
 BOOL 
  
 DEFAULT 
  
 FALSE 
 , 
  
 -- Set to true if target_recall is set 
 ) 
 RETURNS 
  
 SETOF 
  
 vector_assist 
 . 
 RECOMMENDATIONS 
 ;

Parameters

Parameter

Required?

Description

spec_id

Optional, auto-generated if not provided

A unique ID for the vector spec that you define.

table_name

Required

The table name to be used in the vector workload setup.

schema_name

Optional

The schema containing the table to be used in the vector workload. The schema is automatically inferred based on the schema search path.

vector_column_name

See description

The column name for the column containing the vector embeddings.

This column is required if you're using more than one vector column and the text_column_name is not declared. If you're only using one vector column, then this parameter is optional.

text_column_name

See description

The column name for the text column. If your vector workload requires embeddings generation from a text column, then this parameter is required.

This column is required if the vector_column_name parameter is NULL and there are no other vector columns.

This parameter uses the google_ml_integration extension to generate embeddings from the given text column.

vector_index_type

Optional

The type of the vector index. Acceptable values include:

(Default) HNSW
IVFFlat

embeddings_available

Optional

A boolean that checks if vector embeddings exist in the selected table. This field is set to FALSE if no vector columns are detected and the text_column_name parameter is set.

embedding_model

See description

The model ID used to generate the embeddings. This field is optional, unless the embeddings_available parameter is FALSE or NULL . The default value is text-embedding-005 .

num_vectors

Optional

The number of vectors in the table.

dismensionality

Optional

The dimension of the given vectors.

distance_func

Optional

The distance function to be used to compare vector. Acceptable values include:

cosine : default when quantization is not set to bit
ip
l2
l1
jaccard
hamming : default when quantization is set to bit

quantization

Optional

The quantization for the vector indexes. Acceptable values include:

none : default when dimensionality is less than 2000
halfvec : default when dimensionality is between 2000 and 4000
bit : default when dimensionality is greater than 4,000

prefilter_column_names

Optional

Recommends an index for specified column for prefiltering. This might help improve performance for queries with a high selectivity filter on the column.

memory_budget_kb

Optional

The maximum memory (in KB) for index building. The default value is 60% of instance memory.

target_recall

Optional

The target recall for standard vector queries.

target_top_k

Optional

The top-K matching values to retrieve for recall calculation. This value is used if the target_recall parameter is specified.

tune_vector_index

Optional

Tunes the vector indexes in your workload. If enabled, vector assist auto-tunes the vector index and search query for target recall goals using the vector spec, and computes the optimal database and index parameters. Acceptable values include:

(Default) FALSE : tune only the search ( ef_search ) parameter. The default index build time ( m , ef_construction ) parameter values are used.
TRUE : tune both search and index build time parameters.

Return value

Returns a table of recommendations , which includes the newly-created vector spec's ID.

`vector_assist.list_specs`

Returns the current vector spec for a given table and column.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 list_specs 
 ( 
  
 table_name 
  
 TEXT 
 , 
  
 column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 ) 
 RETURNS 
  
 SETOF 
  
 vector_assist 
 . 
 VECTOR_SPECS

Parameters

Parameter	Required?	Description
`table_name`	Required	The name of the table that the vector spec is retrieved for.
`column_name`	Optional	The column name to filter the table on before returning the spec. If the column name is specified, then specifications that contain text or vector column names that match this column name are returned.

Return value

Returns a table of specifications for the given table and optionally, column name.

`vector_assist.modify_spec`

Modifies an existing vector spec. When the vector spec is modified, the associated recommendations are regenerated based on the new specs.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 modify_spec 
 ( 
  
 spec_id 
  
 TEXT 
 , 
  
 table_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 schema_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 vector_column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 text_column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 vector_index_type 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 embeddings_available 
  
 BOOL 
  
 DEFAULT 
  
 NULL 
 , 
  
 num_vectors 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 dimensionality 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 embedding_model 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 prefilter_column_names 
  
 TEXT 
 [] 
  
 DEFAULT 
  
 NULL 
 , 
  
 distance_func 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 quantization 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 memory_budget_kb 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 target_recall 
  
 FLOAT 
  
 DEFAULT 
  
 NULL 
 , 
  
 target_top_k 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 tune_vector_index 
  
 BOOL 
  
 DEFAULT 
  
 NULL 
 ) 
 RETURNS 
  
 SETOF 
  
 vector_assist 
 . 
 RECOMMENDATIONS

Parameters

Parameter

Required?

Description

spec_id

Required

A unique ID for the vector spec that you define.

table_name

Required

The table name to be used in the vector workload setup.

schema_name

Optional

The schema containing the table to be used in the vector workload. The schema is automatically inferred based on the schema search path.

vector_column_name

See description

text_column_name

See description

vector_index_type

Optional

The type of the vector index. Acceptable values include:

HNSW
IVFFlat

embeddings_available

Optional

A boolean that checks if vector embeddings exist in the selected table. This field is set to FALSE if no vector columns are detected and the text_column_name parameter is set.

embedding_model

See description

The model ID used to generate the embeddings. This field is optional, unless the embeddings_available parameter is FALSE or NULL . The default value is text-embedding-005 .

num_vectors

Optional

The number of vectors in the table.

dismensionality

Optional

The dimension of the given vectors.

distance_func

Optional

The distance function to be used to compare vector. Acceptable values include cosine , ip , l2 , or l1 .

quantization

Optional

The quantization for the vector indexes. Acceptable values include none , halfvec , or bit .

prefilter_column_names

Optional

Identifies the columns that are made available for prefiltering in vector search queries.

memory_budget_kb

Optional

The maximum memory (in KB) for index building.

target_recall

Optional

The target recall for standard vector queries.

target_top_k

Optional

The top-K matching values to retrieve for recall calculation. This value is used if the target_recall parameter is specified.

tune_vector_index

Optional

Tunes the vector indexes in your workload. If enabled, vector assist auto-tunes the vector index or search query using the vector spec, and computes the optimal database and index parameters Acceptable values include TRUE or FALSE .

Return value

Returns an updated table of recommendations .

`vector_assist.get_recommendations`

Returns recommendations associated with a specific vector spec_id .

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 get_recommendations 
  
 ( 
  
 spec_id 
  
 TEXT 
 ) 
 RETURNS 
  
 SETOF 
  
 vector_assist 
 . 
 RECOMMENDATIONS 
 ;

Parameters

Parameter	Required?	Description
`spec_id`	Required	Gets recommendations for the specified vector `spec_id` .

Return value

Returns a table of recommendations that must be applied to set up the requested vector workload. Each row is a single recommendation that contains an executable SQL query and a detailed explanation of why to apply the recommendation. The recommendation can be applied using the apply_recommendation() function.

The recommendations table contains the following columns:

Column name	Type	Description
`recommendation_id`	Text	The ID of the recommendation. The recommendation is applied using this ID.
`vector_spec_id`	Text	The ID of the vector spec that is associated with the recommendation.
`table_name`	Text	The table name associated with the recommendation.
`schema_name`	Text	The schema name containing the given table.
`query`	Text	The executable SQL query to be run to achieve the outcome of the recommendation.
`recommendation`	Text	The explanation for the recommendation, including the expected outcome once the recommendation is applied. This column also contains the reason and rationale for the recommendation. You can use this context to tune the query as necessary and regenerate recommendations.
`applied`	Boolean	Shows whether the recommendation has been applied.
`modified`	Boolean	Shows whether the recommendation has been modified by the user.
`created_at`	Timestamp with timezone	The time when the recommendation was generated.

`vector_assist.apply_recommendation`

Applies a single recommendation using the recommendation_id .

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 apply_recommendation 
  
 ( 
  
 recommendation_id 
  
 TEXT 
 ) 
 RETURNS 
  
 BOOLEAN 
 ;

Parameters

Parameter	Required?	Description
`recommenation_id`	Required	ID of the vector recommendation to apply.

Return value

Returns a boolean that confirms if a recommendation was successfully applied and update the vector_assist.RECOMMENDATIONS table column applied to TRUE .

If vector assist is unable to apply the recommendation, then it raises an error message on failure.

`vector_assist.apply_spec`

Applies all recommendations associated with a specific vector spec_id or table name. This function goes through all recommendations and runs the vector_assist.apply_recommendation function on each recommendation.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 apply_spec 
 ( 
  
 spec_id 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 table_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 schema_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
  
 -- text_column_name or vector_column_name 
 ) 
 RETURNS 
  
 BOOLEAN 
 ;

Parameters

You must specify either a spec_id or table_name to run the function.

Parameter	Required?	Description
`spec_id`	Optional	Applies all recommendations for the given vector `spec_id` .
`table_name`	Optional	Applies all recommendations for the given table.
`schema_name`	Optional	The schema containing the specified table.
`column_name`	Optional	Identifies the `text_column_name` or `vector_column_name` to use if the table contains multiple vector specs.

Return value

Returns a boolean that confirms if all recommendations were successfully applied and sets the value in the vector_assist.RECOMMENDATIONS table column applied to TRUE for all recommendations.

If vector assist is unable to apply the recommendation, then it raises an error message on failure.

`vector_assist.modify_recommendation`

Modifies the query for a specific recommendation and sets the modified field in the vector_assist.RECOMMENDATIONS table for the associated recommendation to TRUE .

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 modify_recommendation 
 ( 
  
 recommendation_id 
  
 TEXT 
 , 
  
 modified_query 
  
 TEXT 
 ) 
 RETURNS 
  
 BOOLEAN 
 ;

Parameters

Parameter	Required?	Description
`recommendation_id`	Required	The ID of the recommendation to modify.
`modified_query`	Required	A user-modified SQL query for the recommendation.

Return value

Returns a boolean that confirms if a recommendation was successfully updated and sets the vector_assist.RECOMMENDATIONS table column modified to TRUE .

If vector assist is unable to update the recommendation, then it raises an error message on failure.

`vector_assist.generate_query`

Generates an optimized vector search query based on the defined vector spec and search parameters. This function might tune the ef_search value to meet the set target_recall if applicable.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 generate_query 
 ( 
  
 spec_id 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 table_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 schema_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 column_name 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- text_column_name or vector_column_name of the spec 
  
 search_text 
  
 TEXT 
  
 DEFAULT 
  
 NULL 
 , 
  
 search_vector 
  
 VECTOR 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- e.g. '[1.0,2.0,3.0]'::vector 
  
 output_column_names 
  
 TEXT 
 [] 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- Columns to retrieve, NULL for SELECT * 
  
 top_k 
  
 INT 
  
 DEFAULT 
  
 NULL 
 , 
  
 filter_expressions 
  
 TEXT 
 [] 
  
 DEFAULT 
  
 NULL 
 , 
  
 target_recall 
  
 FLOAT 
  
 DEFAULT 
  
 NULL 
 , 
  
 -- Inherited from spec, can be overridden 
  
 iterative_index_search 
  
 BOOL 
  
 DEFAULT 
  
 NULL 
 ) 
 RETURNS 
  
 TEXT 
 ; 
  
 -- Returns the text representing the search query

Parameters

You must specify either a spec_id or table_name to run the function.

Parameter	Required?	Description
`spec_id`	Optional	Specifies the `spec_id` to generate the query for.
`table_name`	Optional	Specifies the table name to generate the search query for if the `spec_id` isn't provided. If a table contains multiple specs, then the parameter returns an error.
`column_name`	Optional	Specifics the column name to generate the search query for if the `spec_id` isn't provided. If the column contains multiple specs, then the parameter returns an error. The `column_name` might be the `text_column_name` or the `vector_column_name` .
`output_column_name`	Optional	The column names to retrieve in the output query. To retrieve all columns, set this parameter to `NULL` .
`search_text`	Optional	The search text to generate embeddings for. The embedding is generated using the `embedding_model` from the spec. You can only set either the `search_text` or the `search_vector` parameter.
`search_vector`	Optional	The vector for the search query. You can only set either the `search_text` or the `search_vector` parameter
`top_k`	Optional	The number of nearest neighbors to return.
`filter_expressions`	Optional	The filter expressions to apply in the search query. The filters can be applied to fields in the table, such as price < 100.
`iterative_index_search`	Optional	Sets whether to perform iterative index search for filtered queries.
`target_recall`	Optional	The target recall for the search query only. If set, the function may return a recommendation for `ef_search` if it finds a better value.

Return value

Returns a text containing the vector search query for the given parameters. If target_recall is specified, a recommendation for ef_search may be included as well if a better value is found. This query can be executed or saved to run later.

`vector_assist.evaluate_query_recall`

Evaluates the recall of the vector index for a given query. This function uses the database and index configurations in a JSON string as inputs.

Function

  CREATE 
  
 OR 
  
 REPLACE 
  
 FUNCTION 
  
 vector_assist 
 . 
 evaluate_query_recall 
 ( 
  
 ann_query 
  
 text 
 , 
  
 vector_search_configurations 
  
 json 
  
 DEFAULT 
  
 NULL 
 , 
  
 recall 
  
 float 
 , 
  
 ann_execution_time 
  
 float 
 , 
  
 ground_truth_execution_time 
  
 float 
 , 
  
 index_type 
  
 text 
 ) 
 RETURNS 
  
 RECORD

Parameters

Parameter	Required?	Description
`ann_query`	Required	The SQL query for which recall needs to be evaluated. This query should be a `pgvector` ANN query.
`vector_search_configurations`	Optional	The configurations that can be set for the ANN query. This parameter must be in JSON format.

Return value

Returns a table that evaluates the recall of the vector index for the given query.

The table contains the following columns:

Column name	Type	Description
`recall`	Float	The percentage of vectors that the index returns, which are true nearest neighbors. This metric is used to quantify search quality. This function lets you tune the parameters to return the expected vector query recall results.
`ann_execution_time`	Float	The time it takes a vector query to execute using index scans.
`ground_truth_execution_time`	Float	The time it takes the query to run using a sequential scan.
`index_type`	Text	THe type of vector index, such as `HNSW` .

Example

The following example evaluates the recall for a HNSW index and overrides the HNSW.ef_search configuration parameter:

  SELECT 
  
 * 
  
 FROM 
  
 vector_assist 
 . 
 evaluate_query_recall 
 ( 
  
 ann_query 
  
 = 
>  
 'SELECT id FROM items ORDER BY embedding <=> ''[1,2,3]'' LIMIT 10' 
 , 
  
 vector_search_configurations 
  
 = 
>  
 '{"hnsw.ef_search": 30}' 
 );

Vector assist function references Stay organized with collections Save and categorize content based on your preferences.

vector_assist.define_spec

Function

Parameters

Return value

vector_assist.list_specs

Function

Parameters

Return value

vector_assist.modify_spec

Function

Parameters

Return value

vector_assist.get_recommendations

Function

Parameters

Return value

vector_assist.apply_recommendation

Function

Parameters

Return value

vector_assist.apply_spec

Function

Parameters

Return value

vector_assist.modify_recommendation

Function

Parameters

Return value

vector_assist.generate_query

Function

Parameters

Return value

vector_assist.evaluate_query_recall

Function

Parameters

Return value

Example

Vector assist function references

`vector_assist.define_spec`

`vector_assist.list_specs`

`vector_assist.modify_spec`

`vector_assist.get_recommendations`

`vector_assist.apply_recommendation`

`vector_assist.apply_spec`

`vector_assist.modify_recommendation`

`vector_assist.generate_query`

`vector_assist.evaluate_query_recall`