Accelerate queries using optimized AI functions

You can reduce operational costs and improve query responsiveness by using optimized AI functions in AlloyDB for PostgreSQL. Optimized AI functions minimize the use of large language models (LLMs) for row-by-row classification or filtering tasks by processing most of your queries with a smaller, faster proxy model, and falling back to a larger LLM only when necessary.

AlloyDB AI functions like ai.if() can have high latency due to remote calls to LLMs. Optimized functions address this latency issue by using smaller, locally trained proxy models (such as logistic regression models) to process your queries. These models are trained on a sample of your data, using the LLM's output as the source of truth.

You can configure accuracy checks to be performed at runtime using the google_ml_integration.proxy_models_enable_accuracy_check flag, which is set to off by default. Accuracy checks evaluate a sample of rows from the query using the baseline LLM to ensure that accuracy meets the threshold defined by the google_ml_integration.proxy_models_f1_score_threshold flag. If the accuracy check fails, the query falls back to using the LLM (or throws an error if fallback is disabled).

Optimized AI functions workflow

When you use an optimized AI function and you set the google_ml_integration.proxy_models_enable_accuracy_check flag to true, AlloyDB does the following.

  1. Schedules training: When you execute a query containing the ai.if() function on a table, AlloyDB automatically schedules the training of a lightweight proxy model in the background.

  2. Falls back to the LLM: While the proxy model is training, or if a proxy model isn't available, AlloyDB falls back to using the remote LLM to evaluate the query (unless fallback is disabled).

  3. Executes optimized queries: After the model is trained, AlloyDB uses the trained proxy model to process subsequent queries locally, which accelerates performance.

The following diagram shows the logic flow for processing queries with optimized AI functions:

Optimized AI functions flow diagram

Before you begin

Before you use optimized AI functions, do the following:

Execute an optimized query

Follow this example to execute a standard SELECT statement with the ai.if() function:

  1. Create a restaurant_reviews table. The review column that holds the source data is of type TEXT, and the review_embedding column used for queries is of type VECTOR(768).

    CREATE TABLE restaurant_reviews (
        id SERIAL,
        name VARCHAR(64),
        city VARCHAR(64),
        review TEXT,
        review_embedding VECTOR(768)
    );
    

  2. Generate vector embeddings for the source data by using the ai.initialize_embeddings() procedure:

    CALL ai.initialize_embeddings(
        model_id => 'text-embedding-005',
        table_name => 'restaurant_reviews',
        content_column => 'review',
        embedding_column => 'review_embedding'
    );
    

    Generating embeddings is required because optimized AI functions rely on vector embeddings as feature inputs to train and evaluate the lightweight proxy model. Instead of sending raw text to a remote LLM for every row, the proxy model performs fast, local inference directly on the vector representations. If embeddings aren't generated, AlloyDB can't train the proxy model and falls back to evaluating all rows with the remote LLM.

    Note the following embedding requirements:

    • The ai.if() output evaluates to NULL if the embedding column contains NULL values. Both model training and inference require valid embeddings.
    • The ai.if() function throws an error if the querying embedding dimensions don't match the dimensions stored in the trained proxy model.

    For more information about embedding options and incremental refreshes, see Generate and manage auto vector embeddings for large tables.

  3. Run a SELECT statement with the ai.if() function. This query automatically schedules the training of a proxy model for optimized AI functions in the background:

    SELECT r.name, r.city
    FROM restaurant_reviews r
    WHERE ai.if('Is the following a positive review? Review: ' || r.review, r.review_embedding, 'gemini-3.5-flash-lite')
    GROUP BY r.name, r.city
    HAVING COUNT(*) > 500;
    

    The first query execution triggers training asynchronously. While the proxy model trains, if the google_ml_integration.proxy_models_enable_llm_fallback flag is set to on (the default), AlloyDB falls back to evaluating the query with the remote LLM and returns a warning that the optimized AI function is not yet available. If google_ml_integration.proxy_models_enable_llm_fallback is set to off, the query returns an error instead of falling back.

    Subsequent queries matching the same content expression (content_expr), embedding column, and table use the trained proxy model for acceleration. Updating row values, deleting rows, or adding data to the table doesn't cause AlloyDB to fall back to the LLM. The proxy model continues to process the data locally, and data drift is addressed by periodic background retraining.

    Proxy models are uniquely identified by the function type, table OID, content expression, and embedding column (func_type, table_oid, content_expr, embedding_column). With this architecture, you can share models across different SQL queries as long as the inputs to ai.if() remain unchanged.

    AlloyDB doesn't use the existing proxy model, and instead initiates background training for a new proxy model and falls back to the remote LLM (subject to the google_ml_integration.proxy_models_enable_llm_fallback flag), in the following scenarios:

    • The CONTENT expression inside ai.if() changes (for example, the prompt string or concatenated column expression is modified).
    • The EMBEDDING_COLUMN name inside ai.if() changes.
    • The llm_model_id parameter changes.
    • If runtime accuracy checks are enabled, and the proxy model fails the accuracy check at the beginning of the query.

Online training and blocking execution

AlloyDB supports an online training workflow for optimized functions, controlled by the google_ml_integration.proxy_models_enable_online_training database flag. By default, this flag is set to off.

When this flag is enabled and an untrained ai.if() function is executed, the query triggers the training process and uses blocking execution. The initial query actively blocks and waits for the background proxy model training to finish before proceeding. This ensures the query utilizes the optimized proxy model immediately, avoiding remote LLM inference costs on the initial execution.

The maximum duration the query blocks and waits for online training to complete is controlled by the google_ml_integration.proxy_models_training_wait_timeout flag (in seconds, default is 60). If training doesn't complete within this interval, the query times out or falls back according to your fallback settings.

To enable online training:

-- Enable online training (blocking execution)
SET google_ml_integration.proxy_models_enable_online_training = on;

-- Set the maximum wait timeout in seconds (default is 60)
SET google_ml_integration.proxy_models_training_wait_timeout = 30;

Configure remote LLM fallback

By default, fallback to the remote LLM is enabled. If a proxy model is untrained, is being trained, or fails accuracy checks, the query falls back to the remote LLM and incurs standard Agent Platform API costs.

You can configure fallback behavior using the google_ml_integration.proxy_models_enable_llm_fallback database flag:

  • When fallback is enabled (on, default): If the proxy model is unavailable, AlloyDB evaluates the query using the remote LLM and returns a warning:

    WARNING: Optimized AI Function is unavailable. Expect reduced performance.
    
  • When fallback is disabled (off): If the proxy model is unavailable or fails accuracy validation, the query immediately raises an error instead of calling the remote LLM:

    ERROR: Optimized AI Function: Optimized model fallback to LLM prevented by GUC google_ml_integration.proxy_models_enable_llm_fallback
    

To disable LLM fallback for the current session:

SET google_ml_integration.proxy_models_enable_llm_fallback = off;

To disable LLM fallback for the entire database:

ALTER DATABASE DATABASE_NAME SET google_ml_integration.proxy_models_enable_llm_fallback = off;

Replace DATABASE_NAME with the name of your database.

Configure runtime accuracy checks

Runtime accuracy checks evaluate a sample of rows from the query using the remote LLM to ensure the proxy model meets baseline accuracy requirements before executing.

Accuracy checks are controlled by the google_ml_integration.proxy_models_enable_accuracy_check database flag (disabled by default).

To enable accuracy checks for the current session:

SET google_ml_integration.proxy_models_enable_accuracy_check = on;

To disable accuracy checks for the current session:

SET google_ml_integration.proxy_models_enable_accuracy_check = off;

To enable accuracy checks for the entire database:

ALTER DATABASE DATABASE_NAME SET google_ml_integration.proxy_models_enable_accuracy_check = on;

View existing proxy models

To inspect existing proxy models, check their training status, and review their model metadata, query the google_ml.optimized_functions_view view (or its alias in the ai schema, ai.optimized_functions_view):

sql SELECT * FROM ai.optimized_functions_view;

For descriptions of the columns in this view, see ai.optimized_functions_view view.

You can filter this view by table_name or status to verify whether a model has completed training (READY), or to identify the exact content_expr and embedding_column values needed for manual retraining.

Retrain and manage proxy models

Proxy models are trained on the data present in your table at the time of initial training. If newer data is added to the table, or if rows are modified or deleted, the proxy model might become stale over time. To maintain accuracy as data changes, proxy models can be automatically retrained periodically in the background or manually on demand.

Periodic background retraining

On instances with 32 or more vCPUs with default configuration settings, AlloyDB can retrain proxy models periodically in the background.

Periodic retraining is controlled by the following database flags:

Manual retraining

You can manually retrain proxy models using functions in the ai schema (or their equivalents in the google_ml schema).

Retrain all models for a table

To retrain all proxy models associated with a specific table, or to retrain a filtered subset of models for a table, call ai.retrain_optimized_models():

SELECT ai.retrain_optimized_models(
  table_name => 'restaurant_reviews',
  embedding_column => 'review_embedding', -- Optional
  content_expr => '(''Is the following a positive review? Review: ''::text || review)', -- Optional
  llm_model_id => 'LLM_MODEL_ID' -- Optional
);

For parameter descriptions, see ai.retrain_optimized_models() function.

Retrain a specific model by model ID

To retrain a specific proxy model using its unique model identifier, call ai.retrain_optimized_model() with the model_id parameter:

SELECT ai.retrain_optimized_model(model_id => 'MODEL_ID');

For parameter descriptions, see ai.retrain_optimized_model() function.

Retrain a specific model by model training parameters

To retrain a specific proxy model using its table name, content expression, and embedding column, call ai.retrain_optimized_model():

SELECT ai.retrain_optimized_model(
  table_name => 'restaurant_reviews',
  embedding_column => 'review_embedding',
  content_expr => '(''Is the following a positive review? Review: ''::text || review)',
  llm_model_id => 'LLM_MODEL_ID' -- Optional
);

For parameter descriptions, see ai.retrain_optimized_model() function.

Delete proxy models

You can delete individual proxy models or all models using the following functions. These operations must be performed by a user with alloydbsuperuser privileges, or with permissions granted by an administrator.

To find the internal model_id required to delete a specific proxy model, query the google_ml.proxy_models_expr_mapping table:

SELECT model_id, table_oid::regclass, content_expr
FROM google_ml.proxy_models_expr_mapping;
-- Delete a specific proxy model
SELECT ai.delete_optimized_model('com_if_12345_67890');

-- Delete all proxy models and stop the background automated retraining job
CALL ai.delete_all_optimized_models();

Monitor

You can monitor background worker training jobs and check model operational status.

Check model training status and history

To verify whether a proxy model completed training, failed, or is in the process of retraining, query ai.optimized_functions_view:

SELECT table_name, content_expr, status, last_trained_at, training_duration
FROM ai.optimized_functions_view;

The status column indicates the current operational status of the model and provides the following values:

  • READY: The proxy model completed training successfully and is ready to accelerate incoming queries.
  • UNTRAINED: The model has been registered or scheduled, but background training has not yet finished. If initial background training fails, the model remains in the UNTRAINED state and queries fall back to the remote LLM (or error if fallback is disabled).
  • RETRAINING_IN_PROGRESS: Background retraining is underway.
  • RETRAINING_FAILED: The most recent background or manual retraining run failed. In most scenarios, if pre-retraining model weights exist, AlloyDB continues using those existing model weights for inference. If existing model weights aren't available (or if runtime accuracy checks fail), AlloyDB falls back to evaluating queries using the remote LLM (subject to google_ml_integration.proxy_models_enable_llm_fallback) until the model is successfully retrained.

For more information about all columns available in this view, see View existing proxy models.

Limitations

  • Optimized AI functions only support the ai.if() function.
  • Periodic background auto-retraining of proxy models is not supported on database instances with fewer than 32 vCPUs.
  • Row-level security (RLS): Models trained on tables using row-level security (RLS) policies might leak security information by training under the permissions of a specific initiating user. Training proxy models on tables with RLS policies is disabled.
  • Input modifications: If you modify the source content column, the embedding column, the prompt string, or the base LLM model ID supplied to ai.if(), the existing proxy model is bypassed and a new background training request is initiated for that combination.
  • SQL restrictions:
    • The query must contain a valid FROM clause.
    • All arguments to the AI function must originate from the same table alias.
    • Include columns in the ai.if() expression as source columns for the embedding.
    • Don't place the ai.if() function inside a subquery or a common table expression (CTE).

What's next