Console
    Start free

    Vector assist reference

    This page provides detailed information about all available vector assist functions, and their required and optional parameters.

    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
    Required (Auto-generated)
    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
    Optional or required
    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
    Optional or required
    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:
    • HNSW
    • IVFFlat
    • SCANN
    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
    Optional or required
    The model ID from google_ml.models to be used to generate the embeddings. This field is optional, unless the embeddings_available parameter is FALSE or NULL .
    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
    Creates 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.
    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 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 time zone The time when the recommendation was generated in the timestamp with time zone format. For example, 2024-07-18 09:00:00-08 .

    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 (Auto-generated)
    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
    Optional or required
    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
    Optional or required
    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:
    • HNSW
    • IVFFlat
    • SCANN
    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
    Optional or required
    The model ID from google_ml.models to be used to generate the embeddings. This field is optional, unless the embeddings_available parameter is FALSE or NULL .
    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 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.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 requested recommendations.

    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 
 . 
 get_recommendations 
  
 ( 
  
 spec_id 
  
 TEXT 
 ) 
 RETURNS 
  
 SETOF 
  
 vector_assist 
 . 
 RECOMMENDATIONS 
 ;

    Parameters

    Parameter Required? Description
    recommenation_id
    		Required? The 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.

    Must specify either a spec_id or table_name to run function.

    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

    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

    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. Requires the embedding_model in the vector spec. The embedding is generated using the 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.This parameter overrides the settings in the vector spec.
    target_recall
    		Optional The target recall for the search query only. This parameter overrides the settings in the vector spec and tunes ef_search .

    Return value

    Returns a text containing the vector search query for the given parameters. 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 
 ( 
  
 IN 
  
 ann_query 
  
 text 
 , 
  
 IN 
  
 vector_search_configurations 
  
 json 
  
 DEFAULT 
  
 NULL 
 , 
  
 OUT 
  
 recall 
  
 float 
 , 
  
 OUT 
  
 ann_execution_time 
  
 float 
 , 
  
 OUT 
  
 ground_truth_execution_time 
  
 float 
 , 
  
 OUT 
  
 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.

    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}' 
 );
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: