Create and manage databases with the Database Service

Authorization and roles

Users must be authorized to access the Database Service. Authorization is required to access Database Service with both the GDC console and the Distributed Cloud CLI.

The following roles grant permissions to users:

project-viewer

Users with this role can access the GDC console.

project-db-viewer

Users with this role can view database clusters.

project-db-editor

Users with this role have the capabilities conferred by project-db-viewer and can also edit database clusters.

project-db-admin

Users with this role have the capability conferred by project-db-editor and can also create and delete database clusters.

project-bucket-object-viewer

Users with this role can use the storage browser in the Database Service interface in the GDC console. This interface is used to specify files for database imports and to specify destinations for files generated by database exports.

project-monitoring-viewer

Users with this role can access the monitoring instance. See Observe metrics for more information about observing Database Service metrics. You should only grant this role to users who need to access the monitoring instance.

project_mp_admin

Users with this role have the capabilities conferred by both project_mp_editor and project_mpb_editor roles, so they can create, edit, and delete both maintenance policies and maintenance policy bindings.

project_mp_viewer

Users with this role can view maintenance policies.

project_mp_editor

Users with this role have the capabilities conferred by the project_mp_viewer role, and can also create, edit, and delete maintenance policies.

project_mpb_viewer

Users with this role can view maintenance policy bindings.

project_mpb_editor

Users with this role have the capabilities conferred by the project_mpb_viewer role, and can also create, edit, and delete maintenance policy bindings.

Available database engines

The following database engines are available to use in a GDC environment:

Choose a database engine type and create a database cluster

If you want to enable backups for the database cluster, first create a Distributed Cloud storage bucket or any bucket that is accessible with a S3-compatible endpoint, then create a backup repository named dbs-backup-repository. If using storage bucket outside of Distributed Cloud, it's your responsibility to ensure the bucket is properly encrypted.

The following is a sample BackupRepository custom resource created for Database Service backups:

apiVersion: backup.gdc.goog/v1
kind: BackupRepository
metadata:
  name: dbs-backup-repository
spec:
  secretReference:
    namespace: "object-storage-secret-ns"
    name: "object-storage-secret"
  endpoint: "https://objectstorage.google.gdch.test"
  type: S3
  s3Options:
    bucket: "fully-qualified-bucket-name"
    region: "us-east-1"
    forcePathStyle: true
  importPolicy: ReadWrite

A user with the Project DB Admin role must perform the following steps. Use either the GDC console or the Distributed Cloud CLI to create database clusters:

apiVersion: v1
kind: Secret
metadata:
  name: db-pw-DBCLUSTER_NAME
  namespace: USER_PROJECT
type: Opaque
data:
  DBCLUSTER_NAME: "BASE64_PASSWORD"
---
apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: DBCluster
metadata:
  name: DBCLUSTER_NAME
  namespace: USER_PROJECT
spec:
  primarySpec:
    adminUser:
      passwordRef:
        name: db-pw-DBCLUSTER_NAME
    version: "DB_VERSION"
    resources:
      memory: DB_MEMORY
      cpu: DB_CPU
      disks:
      - name: DataDisk
        size: DB_DATA_DISK

apiVersion: v1
kind: Secret
metadata:
  name: db-pw-DBCLUSTER_NAME
  namespace: USER_PROJECT
type: Opaque
data:
  DBCLUSTER_NAME: "BASE64_PASSWORD"
---
apiVersion: oracle.dbadmin.gdc.goog/v1
kind: DBCluster
metadata:
  name: DBCLUSTER_NAME
  namespace: USER_PROJECT
spec:
  primarySpec:
    adminUser:
      passwordRef:
        name: db-pw-DBCLUSTER_NAME
    version: "DB_VERSION"
    cdbName: GCLOUD
    resources:
      memory: DB_MEMORY
      cpu: DB_CPU
      disks:
      - name: DataDisk
        size: DB_DATA_DISK
      - name: LogDisk
        size: DB_LOG_DISK

List database clusters

Work through the following steps to list database clusters with the GDC console or the Distributed Cloud CLI:

kubectl get DBCluster.DBENGINE_NAME.dbadmin.gdc.goog -n USER_PROJECT

Start and stop database clusters

Stopping a database cluster pauses the cluster to save resources. You can stop and start database clusters with the GDC console or with the gdcloud CLI tool.

gdcloud database clusters stop CLUSTER_NAME

gdcloud database clusters start CLUSTER_NAME

kubectl patch dbcluster.DBENGINE_NAME.dbadmin.gdc.goog DBCLUSTER_NAME -p '{"spec":{"primarySpec": {"isStopped": IS_STOPPED}}}' --type=merge -n USER_PROJECT

Update database cluster attributes

You can change the following database cluster attributes with the GDC console or the gdcloud CLI:

• Database password for the admin user
• External connections (enabled/disabled)
• Availability level (AlloyDB Omni and PostgreSQL only)
• Backup enabled and backup retention days
• Database flags
• CPU, memory, or storage allocated to the database cluster

For information on how to modify an attribute, see the workflow corresponding to the attribute type you want to update:

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: BackupPlan
metadata:
  name: DBCLUSTER_NAME-backupplan
  namespace: USER_PROJECT
spec:
  dbclusterRef: DBCLUSTER_NAME
  backupRetainDays: RETENTION_DAYS

Configure database flags

Database images that ship with GDC come with default settings. However, you can customize the default database engine to satisfy requirements for your applications. Database clusters provide predefined flags that you can set using the GDC console or gdcloud CLI:

Available database flags

The available database flags to configure for your database cluster are provided next based on the database engine you configured.

PostgreSQL database flags

The following table specifies default values for flags different from the vendor default:

Oracle database flags

The following table specifies default values for flags different from the vendor default:

AlloyDB Omni database flags

Configure database extensions

This section contains information about configuring the database extensions that Database Service supports.

Install an extension

Extensions can only be installed on the primary DBCluster. Once installed, the extension is replicated to standby instances.

Most of the extensions can be installed directly by connecting to the database cluster and running the CREATE EXTENSION command.

However, there are a few extensions that require additional configuration, hence users must set database flag, "dbs.enable_<extension-name>": "on" then connect to the database cluster and run the CREATE EXTENSION command.

Only database users who are members of the cloudsqlsuperuser or alloydbsuperuser role can run the CREATE EXTENSION command. By default, this includes the dbsadmin user.

Drop an extension

In order to drop an extension, the database user attempting to do so must be the owner of the extension. This implies that only the database user who initially created the extension has the authority to drop it. No other database user possesses the ability to drop the extension, ensuring controlled management of extensions.

Supported database extensions

The following table lists all of the supported extensions.

Configure high availability

The purpose of a high availability configuration is to reduce downtime when a database cluster instance becomes unavailable. This might happen when an instance runs out of memory. With high availability, your data continues to be available to client applications.

Within a site, the configuration is made up of a primary instance and a standby replica. All writes made to the primary instance are replicated to the standby replica before a transaction is reported as committed. In the event of an instance failure, you can request that the standby replica become the new primary instance. Application traffic is then rerouted to the new primary instance. This process is called a failover.

You can manually trigger a failover at any time. The failover involves the following process, in order:

1. GDC takes the primary instance offline.

2. GDC turns the standby replica into the new active database cluster.

3. GDC deletes the previous active database cluster.

4. GDC creates a new standby replica.

For AlloyDB Omni and PostgreSQL database clusters, you can enable or disable same zone high availability. For information on enabling high availability when creating a database cluster, see Choose a database engine type and create a database cluster.

Update an existing cluster

You can update your high availability settings for an existing database cluster:

Trigger a failover

If you have configured high availability for your database cluster, you can trigger a failover. To trigger a failover, complete the following steps:

  apiVersion: fleet.dbadmin.gdc.goog/v1
  kind: Failover
  metadata:
    name: FAILOVER_NAME
  spec:
    dbclusterRef: DBCLUSTER_NAME

Build generative AI applications using AlloyDB AI

This section describes how to invoke predictions and query and index embeddings using the pgvector extension. These machine learning-powered AI functions are available through AlloyDB AI, which is a suite of AlloyDB for PostgreSQL features that let you apply the semantic and predictive power of machine learning (ML) models to your data.

Learn more about AlloyDB AI at https://cloud.google.com//alloydb/docs/ai.

Invoke predictions

To integrate Vertex AI with AlloyDB Omni and run predictions on Vertex AI stored models, follow these steps.

Before you begin

1. Enable Vertex AI online predictions in GDC.

2. Create a Kubernetes secret based on the service account key downloaded in the preceding steps by running the following command. Ensure that you create the Kubernetes secret in the same namespace as your DBCluster resource.

   kubectl create secret generic SECRET_NAME \
   --from-file=PATH_TO_SERVICE_ACCOUNT_KEY/private-key.json \
   -n NAMESPACE

   Replace the following:

   • SECRET_NAME: the name of the secret used when you create a DBCluster manifest to enable AlloyDB Omni to access Distributed Cloud AI features. For example, vertex-ai-key-alloydb.

   • PATH_TO_SERVICE_ACCOUNT_KEY: the path to the location where you downloaded the private-key.json service account key.

   • NAMESPACE: the namespace of the database cluster.

3. Install the AlloyDB Omni operator using steps listed in Choose a database engine type and create a database cluster.

4. Create a database cluster with AlloyDB AI and set vertexAIKeyRef to the Kubernetes secret created in the preceding steps in the googleMLExtension field in the DBCluster manifest.

   apiVersion: v1
   kind: Secret
   metadata:
     name: db-pw-DBCLUSTER_NAME
     namespace: USER_PROJECT
   type: Opaque
   data:
     DBCLUSTER_NAME: "BASE64_PASSWORD"
   ---
   apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
   kind: DBCluster
   metadata:
     name: DBCLUSTER_NAME
     namespace: USER_PROJECT
   spec:
     primarySpec:
       adminUser:
         passwordRef:
           name: db-pw-DBCLUSTER_NAME
       features:
         googleMLExtension:
           config:
             vertexAIKeyRef: SECRET_NAME
       version: "DB_VERSION"
       resources:
         memory: DB_MEMORY
         cpu: DB_CPU
         disks:
         - name: DataDisk
           size: DB_DATA_DISK
   

   Replace the following variables:

   • DBCLUSTER_NAME: the name of the database cluster.
   • USER_PROJECT: the name of the user project where the database cluster will be created.
   • BASE64_PASSWORD: the base64 encoding of the database's administrator password.
   • DBENGINE_NAME: the name of the database engine. Set to alloydbomni.
   • DB_VERSION: the version of the database engine.
   • DB_MEMORY: the amount of memory allocated to the database cluster, for example 5Gi.
   • DB_CPU: the amount of CPUs allocated to the database cluster, for example 2.
   • DB_DATA_DISK: the amount of space allocated to the database cluster, for example 10 Gi.

   Apply the manifest.

   kubectl apply -f DB_CLUSTER_YAML

   Replace the following:

   • DB_CLUSTER_YAML: the name of this database cluster manifest file—for example, alloydb-omni-db-cluster.yaml.

5. Install the google_ml_integration extension.

   CREATE EXTENSION google_ml_integration CASCADE;
   

Invoke a prediction

Invoke an online prediction using a Vertex AI model endpoint by running the following ml_predict_row() SQL function:

  SELECT ml_predict_row('PREDICTION_ENDPOINT/PROJECT_NAMESPACE/ORGANIZATION/ZONE/DNS/DNS_SUFFIX', '{ "instances": [ INSTANCES ], "parameters":
  PARAMETERS');

Replace the following:

• PREDICTION_ENDPOINT: the Vertex AI endpoint qualified name

• PROJECT_NAMESPACE: the namespace in which the Vertex AI endpoint is deployed

• ORGANIZATION: the name of the organization in which the Vertex AI endpoint is deployed

• ZONE: the zone in which your Vertex AI endpoint is deployed

• DNS: the DNS for your organization

• DNS_SUFFIX: the suffix of the DNS object

• INSTANCES: the inputs to the prediction call, in JSON format

• PARAMETERS: the parameters to the prediction call, in JSON format

Query and index embeddings using pgvector

The pgvector PostgreSQL extension lets you use vector-specific operators and functions when you store, index, and query text embeddings in your database. AlloyDB provides optimizations for working with pgvector, which let you create indexes that can speed up certain queries that involve embeddings.

Learn more about using AlloyDB as an LLM, and generating and storing vector embeddings based on an LLM at https://cloud.google.com/alloydb/docs/ai/work-with-embeddings#index.

Create indexes and query vectors using ScaNN

This section shows you how to use stored embeddings to generate indexes and query embeddings. You can create ScaNN indexes with AlloyDB.

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 postgres_ann extension in addition to the vector extension.

  CREATE EXTENSION IF NOT EXISTS postgres_ann;
  

Create a ScaNN index

You can create a ScaNN index for tables in your database.

AlloyDB postgres_ann, 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 a low index building time and small memory footprint. In addition, it provides fast QPS 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.

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.

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.

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.

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 postgres_ann 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.

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. Note that you must first register the text embedding Gecko model to use this function.

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;
  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 Distributed Cloud can use for 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.

  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.

Vector index metrics

This section lists the metrics related to the vector indexes that you generate in AlloyDB. You can view these metrics using the pg_stat_ann_indexes view that is available when you install the postgres_ann extension.

Usability metrics

The usability metrics include metrics that help you understand the state of index utilization with metrics such as, index configuration and number of index scans.

Tuning metrics

Tuning metrics provide insights into your current index optimization, allowing you to apply recommendations for faster query performance.

Tuning recommendation based on the metrics

Mutation

The insertcount, updatecount, and deletecount metrics together show the changes or mutations in the vector for the index.

The index is created with a specific number of vectors and partitions. When operations such as insert, update, or delete are performed on the vector index, it only affects the initial set of partitions where the vectors reside. Consequently, the number of vectors in each partition fluctuates over time, potentially impacting recall, QPS, or both.

If you encounter slowness or accuracy issues such as low QPS or poor recall, in your ANN search queries over time, then consider reviewing these metrics. A high number of mutations relative to the total number of vectors could indicate the need for reindexing.

Distribution

The distribution metric shows the vector distributions across all partitions.

When you create an index, it is created with a specific number of vectors and fixed partitions. The partitioning process and subsequent distribution occurs based on this consideration. If additional vectors are added, they are partitioned among the existing partitions, resulting in a different distribution compared to the distribution when the index was created. Since the final distribution does not consider all vectors simultaneously, the recall, QPS, or both might be affected.

If you observe a gradual decline in the performance of your ANN search queries, such as slower response times or reduced accuracy in the results (measured by QPS or recall), then consider checking this metric and reindexing.

Call models using model endpoints in AlloyDB

Overview

The Model endpoint management preview lets you register a model endpoint, manage model endpoint metadata in your database cluster, and then interact with the models using SQL queries. It provides the google_ml_integration extension that includes functions to add and register the model endpoint metadata related to the models, and then use the models to generate vector embeddings or invoke predictions.

Some of the example model types that you can register using model endpoint management are as follows:

• Vertex AI text embedding models
• Embedding models provided by third-party providers.
• Custom-hosted text embedding models
• Generic models with a JSON-based API—for example, gemini-pro model from the Vertex AI Model Garden

How it works

You can use model endpoint management to register a model endpoint that complies to the following:

• Model input and output supports JSON format.
• Model can be called using the REST protocol.

When you register a model endpoint with the model endpoint management, it registers each endpoint with a unique model ID that you provided as a reference to the model. You can use this model ID to query models:

• Generate embeddings to translate text prompts to numerical vectors. You can store generated embeddings as vector data when the pgvector extension is enabled in the database.

• Invoke predictions to call a model using SQL within a transaction.

Your applications can access the model endpoint management using the google_ml_integration extension. This extension provides the following functions:

• The google_ml.create_model() SQL function, which is used to register the model endpoint that is used in the prediction or embedding function.
• The google_ml.create_sm_secret() SQL function, which uses secrets in the Google Cloud Secret Manager, where the API keys are stored.
• The google_ml.embedding() SQL function, which is a prediction function that generates text embeddings.
• The google_ml.predict_row() SQL function that generates predictions when you call generic models that support JSON input and output format.
• Other helper functions that handle generating custom URL, generating HTTP headers, or passing transform functions for your generic models.
• Functions to manage the registered model endpoints and secrets.

Key concepts

Before you start using the model endpoint management, understand the concepts required to connect to and use the models.

Model provider

Model provider indicates the supported model hosting providers. The following table shows the model provider value you must set based on the model provider you use:

The default model provider is custom.

Based on the provider type, the supported authentication method differs. The Vertex AI models use the Distributed Cloud service account to authenticate, while other providers can use the Secret Manager to authenticate.

Model type

Model type indicates the type of the AI model. The extension supports text embedding as well as any generic model type. The supported model type you can set when registering a model endpoint are text-embedding and generic. Setting model type is optional when registering generic model endpoints as generic is the default model type.

Text embedding models with built-in support

The model endpoint management provides built-in support for all versions of the textembedding-gecko model by Vertex AI. To register these model endpoints, use the google_ml.create_model() function. Distributed Cloud automatically sets up default transform functions for these models.

The model type for these models is text-embedding.

Other text embedding models

For other text embedding models, you need to create transform functions to handle the input and output formats that the model supports. Optionally, you can use the HTTP header generation function that generates custom headers required by your model.

The model type for these models is text-embedding.

Generic models

The model endpoint management also supports registering of all other model types apart from text embedding models. To invoke predictions for generic models, use the google_ml.predict_row() function. You can set model endpoint metadata, such as a request endpoint and HTTP headers that are specific to your model.

You cannot pass transform functions when you are registering a generic model endpoint. Ensure that when you invoke predictions the input to the function is in the JSON format, and that you parse the JSON output to derive the final output.

The model type for these models is generic.

Authentication

Auth types indicate the authentication type that you can use to connect to the model endpoint management using the google_ml_integration extension. Setting authentication is optional and is required only if you need to authenticate to access your model.

For Vertex AI models, the Distributed Cloud service account is used for authentication. For other models, API key or bearer token that is stored as a secret in the Secret Manager can be used with the google_ml.create_sm_secret() SQL function.

The following table shows the auth types that you can set:

Prediction functions

The google_ml_integration extension includes the following prediction functions:

google_ml.embedding()

Used to call a registered text embedding model endpoint to generate embeddings. It includes built-in support for the textembedding-gecko model by Vertex AI.

For text embedding models without built-in support, the input and output parameters are unique to a model and need to be transformed for the function to call the model. Create a transform input function to transform input of the prediction function to the model specific input, and a transform output function to transform model specific output to the prediction function output.

google_ml.predict_row()

Used to call a registered generic model endpoint, as long as they support JSON-based API, to invoke predictions.

Transform functions

Transform functions modify the input to a format that the model understands, and convert the model response to the format that the prediction function expects. The transform functions are used when registering the text-embedding model endpoint without built-in support. The signature of the transform functions depends on the prediction function for the model type.

You cannot use transform functions when registering a generic model endpoint.

The following shows the signatures for the prediction function for text embedding models:

// define custom model specific input/output transform functions.
CREATE OR REPLACE FUNCTION input_transform_function(model_id VARCHAR(100), input_text TEXT) RETURNS JSON;

CREATE OR REPLACE FUNCTION output_transform_function(model_id VARCHAR(100), response_json JSON) RETURNS real[];

HTTP header generation function

The HTTP header generation function generates the output in JSON key value pairs that are used as HTTP headers. The signature of the prediction function defines the signatures of the header generation function.

The following example shows the signature for the google_ml.embedding() prediction function.

CREATE OR REPLACE FUNCTION generate_headers(model_id VARCHAR(100), input TEXT) RETURNS JSON;

For the google_ml.predict_row() prediction function, the signature is as follows:

CREATE OR REPLACE FUNCTION generate_headers(model_id VARCHAR(100), input JSON) RETURNS JSON;

Register a model

To invoke predictions or generate embeddings using a model, register the model endpoint with model endpoint management.

For more information about the google_ml.create_model() function, see Model endpoint management reference.

Before you register a model endpoint with model endpoint management, you must enable the google_ml_integration extension and set up authentication based on the model provider, if your model endpoint requires authentication.

Make sure that you access your database with the postgres default username.

Set up authentication

The following sections show how to set up authentication before adding a Vertex AI model endpoint or model endpoints by other providers.

Set up authentication for Vertex AI

To use the Google Vertex AI model endpoints, you must add Vertex AI permissions to the service account that you used while installing AlloyDB Omni.

Set up authentication for other model providers

For all models except Vertex AI models, you can store your API keys or bearer tokens in Secret Manager. This step is optional if your model endpoint doesn't handle authentication through Secret Manager—for example, if your model endpoint uses HTTP headers to pass authentication information or doesn't use authentication at all.

This section explains how to set up authentication if you are using Secret Manager.

To create and use an API key or a bearer token, complete the following steps:

1. Create the secret in Secret Manager.

   The secret name and the secret path is used in the google_ml.create_sm_secret() SQL function.

2. Grant permissions to the Distributed Cloud cluster to access the secret.

     gcloud secrets add-iam-policy-binding 'SECRET_ID' \
         --member="serviceAccount:SERVICE_ACCOUNT_ID" \
         --role="roles/secretmanager.secretAccessor"
   

   Replace the following:

   • SECRET_ID: the secret ID in Secret Manager.

   • SERVICE_ACCOUNT_ID: the ID of the service account that you created in the previous step. Ensure that this is the same account you used during AlloyDB Omni installation. This includes the full PROJECT_ID.iam.gserviceaccount.com suffix. For example: my-service@my-project.iam.gserviceaccount.com

     You can also grant this role to the service account at the project level.

Generate embeddings

This section describes a preview that lets you experiment with registering an AI model endpoint and invoking predictions with model endpoint management.

After the model endpoints are added and registered in the model endpoint management, you can reference them using the model ID to generate embeddings.

Before you begin

Make sure that you have registered your model endpoint with model endpoint management.

Generate embeddings

Use the google_ml.embedding() SQL function to call the registered model endpoint with the text embedding model type to generate embeddings.

To call the model and generate embeddings, use the following SQL query:

SELECT
  google_ml.embedding(
    model_id => 'MODEL_ID',
    content => 'CONTENT');

Replace the following:

• MODEL_ID: the model ID you defined when registering the model endpoint.
• CONTENT: the text to translate into a vector embedding.

Examples

Some examples for generating embeddings using registered model endpoint are listed in this section.

Text embedding models with in-built support

To generate embeddings for a registered textembedding-gecko@002 model endpoint, run the following statement:

    SELECT
      google_ml.embedding(
        model_id => 'textembedding-gecko@002',
        content => 'AlloyDB is a managed, cloud-hosted SQL database service');

Invoke predictions

This section describes a preview that lets you experiment with registering an AI model endpoint and invoking predictions with model endpoint management.

After the model endpoints are added and registered in the model endpoint management, you can reference them using the model ID to invoke predictions.

Before you begin

Make sure that you have registered your model endpoint with model endpoint management.

Invoke predictions for generic models

Use the google_ml.predict_row() SQL function to call a registered generic model endpoint to invoke predictions. You can use google_ml.predict_row() function with any model type.

SELECT
  google_ml.predict_row(
    model_id => 'MODEL_ID',
    request_body => 'REQUEST_BODY');

Replace the following:

• MODEL_ID: the model ID you defined when registering the model endpoint.
• REQUEST_BODY: the parameters to the prediction function, in JSON format.

Examples

Some examples for invoking predictions using registered model endpoints are listed in this section.

To generate predictions for a registered gemini-pro model endpoint, run the following statement:

    SELECT
        json_array_elements(
        google_ml.predict_row(
            model_id => 'gemini-pro',
            request_body => '{
        "contents": [
            {
                "role": "user",
                "parts": [
                    {
                        "text": "For TPCH database schema as mentioned here https://www.tpc.org/TPC_Documents_Current_Versions/pdf/TPC-H_v3.0.1.pdf , generate a SQL query to find all supplier names which are located in the India nation."
                    }
                ]
            }
        ]
        }'))-> 'candidates' -> 0 -> 'content' -> 'parts' -> 0 -> 'text';

Model endpoint management API reference

This section lists parameters for different functions provided by the google_ml_integration extension to register and manage model endpoints, and secrets with model endpoint management.

You must set the google_ml_integration.enable_model_support database flag to on before you can start using the extension.

Models

Use this reference to understand parameters for functions that let you manage model endpoints.

google_ml.create_model() function

The following shows how to call the google_ml.create_model() SQL function used to register model endpoint metadata:

  CALL
    google_ml.create_model(
      model_id => 'MODEL_ID',
      model_request_url => 'REQUEST_URL',
      model_provider => 'PROVIDER_ID',
      model_type => 'MODEL_TYPE',
      model_qualified_name => 'MODEL_QUALIFIED_NAME',
      model_auth_type => 'AUTH_TYPE',
      model_auth_id => 'AUTH_ID',
      generate_headers_fn => 'GENERATE_HEADER_FUNCTION',
      model_in_transform_fn => 'INPUT_TRANSFORM_FUNCTION',
      model_out_transform_fn => 'OUTPUT_TRANSFORM_FUNCTION');

google_ml.alter_model()

The following shows how to call the google_ml.alter_model() SQL function used to update model endpoint metadata:

    CALL
    google_ml.alter_model(
      model_id => 'MODEL_ID',
      model_request_url => 'REQUEST_URL',
      model_provider => 'PROVIDER_ID',
      model_type => 'MODEL_TYPE',
      model_qualified_name => 'MODEL_QUALIFIED_NAME',
      model_auth_type => 'AUTH_TYPE',
      model_auth_id => 'AUTH_ID',
      generate_headers_fn => 'GENERATE_HEADER_FUNCTION',
      model_in_transform_fn => 'INPUT_TRANSFORM_FUNCTION',
      model_out_transform_fn => 'OUTPUT_TRANSFORM_FUNCTION');

For information about the values that you must set for each parameter, see Create a model.

google_ml.drop_model() function

The following shows how to call the google_ml.drop_model() SQL function used to drop a model endpoint:

  CALL google_ml.drop_model('MODEL_ID');

google_ml.list_model() function

The following shows how to call the google_ml.list_model() SQL function used to list model endpoint information:

  SELECT google_ml.list_model('MODEL_ID');

google_ml.model_info_view view

The following shows how to call the google_ml.model_info_view view that is used to list model endpoint information for all model endpoints:

  SELECT * FROM google_ml.model_info_view;

Secrets

Use this reference to understand parameters for functions that let you manage secrets.

google_ml.create_sm_secret() function

The following shows how to call the google_ml.create_sm_secret() SQL function used to add the secret created in Secret Manager:

    CALL
    google_ml.create_sm_secret(
      secret_id => 'SECRET_ID',
      secret_path => 'projects/project-id/secrets/SECRET_MANAGER_SECRET_ID/versions/VERSION_NUMBER');

google_ml.alter_sm_secret() function

The following shows how to call the google_ml.alter_sm_secret() SQL function used to update secret information:

  CALL
    google_ml.alter_sm_secret(
      secret_id => 'SECRET_ID',
      secret_path => 'projects/project-id/secrets/SECRET_MANAGER_SECRET_ID/versions/VERSION_NUMBER');

For information about the values that you must set for each parameter, see Create a secret.

google_ml.drop_sm_secret() function

The following shows how to call the google_ml.drop_sm_secret() SQL function used to drop a secret:

  CALL google_ml.drop_sm_secret('SECRET_ID');

Prediction functions

Use this reference to understand parameters for functions that let you generate embeddings or invoke predictions.

google_ml.embedding() function

The following shows how to generate embeddings:

SELECT
  google_ml.embedding(
    model_id => 'MODEL_ID',
    contents => 'CONTENT');

google_ml.predict_row() function

The following shows how to invoke predictions:

SELECT
  google_ml.predict_row(
    model_id => 'MODEL_ID',
    request_body => 'REQUEST_BODY');

Transform functions

Use this reference to understand parameters for input and output transform functions.

Input transform function

The following shows the signature for the prediction function for text embedding model endpoints:

  CREATE OR REPLACE FUNCTION INPUT_TRANSFORM_FUNCTION(model_id VARCHAR(100), input_text TEXT) RETURNS JSON;

Output transform function

The following shows the signature for the prediction function for text embedding model endpoints:

  CREATE OR REPLACE FUNCTION OUTPUT_TRANSFORM_FUNCTION(model_id VARCHAR(100), response_json JSON) RETURNS real[];

Transform functions example

To better understand how to create transform functions for your model endpoint, consider a custom-hosted text embedding model endpoint that requires JSON input and output.

The following example cURL request creates embeddings based on the prompt and the model endpoint:

  curl -m 100 -X POST https://cymbal.com/models/text/embeddings/v1 \
    -H "Content-Type: application/json"
    -d '{"prompt": ["AlloyDB Embeddings"]}'

The following example response is returned:

[[ 0.3522231  -0.35932037  0.10156056  0.17734447 -0.11606089 -0.17266059
   0.02509351  0.20305622 -0.09787305 -0.12154685 -0.17313677 -0.08075467
   0.06821183 -0.06896557  0.1171584  -0.00931572  0.11875633 -0.00077482
   0.25604948  0.0519384   0.2034983  -0.09952664  0.10347155 -0.11935943
  -0.17872004 -0.08706985 -0.07056875 -0.05929353  0.4177883  -0.14381726
   0.07934926  0.31368294  0.12543282  0.10758053 -0.30210832 -0.02951015
   0.3908268  -0.03091059  0.05302926 -0.00114946 -0.16233777  0.1117468
  -0.1315904   0.13947351 -0.29569918 -0.12330773 -0.04354299 -0.18068913
   0.14445548  0.19481727]]

Based on this input and response, we can infer the following:

• The model expects JSON input through the prompt field. This field accepts an array of inputs. As the google_ml.embedding() function is a row level function, it expects one text input at a time. Thus,you need to create an input transform function that builds an array with single element.

• The response from the model is an array of embeddings, one for each prompt input to the model. As the google_ml.embedding() function is a row level function, it returns single input at a time. Thus, you need to create an output transform function that can be used to extract the embedding from the array.

The following example shows the input and output transform functions that is used for this model endpoint when it is registered with model endpoint management:

CREATE OR REPLACE FUNCTION cymbal_text_input_transform(model_id VARCHAR(100), input_text TEXT)
RETURNS JSON
LANGUAGE plpgsql
AS $$
DECLARE
  transformed_input JSON;
  model_qualified_name TEXT;
BEGIN
  SELECT json_build_object('prompt', json_build_array(input_text))::JSON INTO transformed_input;
  RETURN transformed_input;
END;
$$;

CREATE OR REPLACE FUNCTION cymbal_text_output_transform(model_id VARCHAR(100), response_json JSON)
RETURNS REAL[]
LANGUAGE plpgsql
AS $$
DECLARE
  transformed_output REAL[];
BEGIN
SELECT ARRAY(SELECT json_array_elements_text(response_json->0)) INTO transformed_output;
RETURN transformed_output;
END;
$$;

HTTP header generation function

The following shows signature for the header generation function that can be used with the google_ml.embedding() prediction function when registering other text embedding model endpoints.

  CREATE OR REPLACE FUNCTION GENERATE_HEADERS(model_id VARCHAR(100), input_text TEXT) RETURNS JSON;

For the google_ml.predict_row() prediction function, the signature is as follows:

CREATE OR REPLACE FUNCTION GENERATE_HEADERS(model_id TEXT, input JSON) RETURNS JSON;

Header generation function example

To better understand how to create a function that generates output in JSON key value pairs that are used as HTTP headers, consider a custom-hosted text embedding model endpoint.

The following example cURL request passes the version HTTP header which is used by the model endpoint:

  curl -m 100 -X POST https://cymbal.com/models/text/embeddings/v1 \
      -H "Content-Type: application/json" \
      -H "version: 2024-01-01" \
      -d '{"prompt": ["AlloyDB Embeddings"]}'

The model expects text input through the version field and returns the version value in JSON format. The following example shows the header generation function that is used for this text embedding model endpoint when it is registered with model endpoint management:

CREATE OR REPLACE FUNCTION header_gen_fn(model_id VARCHAR(100), input_text TEXT)
RETURNS JSON
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN json_build_object('version', '2024-01-01')::JSON;
END;
$$;

Header generation function using API Key

The following examples show how to set up authentication using the API key.

CREATE OR REPLACE FUNCTION header_gen_func(
  model_id VARCHAR(100),
  input_text TEXT
)
RETURNS JSON
LANGUAGE plpgsql
AS $$
#variable_conflict use_variable
BEGIN
  RETURN json_build_object('Authorization', 'API_KEY')::JSON;
END;
$$;

CREATE OR REPLACE FUNCTION header_gen_func(
  model_id VARCHAR(100),
  response_json JSON
)
RETURNS JSON
LANGUAGE plpgsql
AS $$
#variable_conflict use_variable
DECLARE
transformed_output REAL[];
BEGIN
  -- code to add Auth token to API request
RETURN json_build_object('x-api-key', 'API_KEY', 'model-version', '2023-06-01')::JSON;
END;
$$;

Request URL generation

Use the request URL generation function to infer the request URLs for the model endpoints with built-in support. The following shows the signature for this function:

CREATE OR REPLACE FUNCTION GENERATE_REQUEST_URL(provider google_ml.model_provider, model_type google_ml.MODEL_TYPE, model_qualified_name VARCHAR(100), model_region VARCHAR(100) DEFAULT NULL)

Supported models

You can use model endpoint management to register any text embedding or generic model endpoint. Model endpoint management also includes pre-registered Vertex AI models and models with built-in support.

Pre-registered Vertex AI models

Models with built-in support

Delete database clusters

You can delete database clusters with the GDC console or the Distributed Cloud CLI.

gdcloud database clusters delete CLUSTER_NAME

kubectl patch dbcluster.DBENGINE_NAME.dbadmin.gdc.goog DBCLUSTER_NAME -p '{"spec":{"isDeleted": true}}' --type=merge -n USER_PROJECT
kubectl delete dbcluster.DBENGINE_NAME.dbadmin.gdc.goog DBCLUSTER_NAME -n USER_PROJECT # for database clusters with backup enabled, this will permanently delete the database.

Connect to a database cluster

By default, a database cluster only allows connection from within the user cluster and the same project.

To enable connections to all database clusters in your project from another project, see Enable cross-project connections.

To enable connections to a database cluster from IP addresses outside your GDC organization, see Enable external connections.

Sign in to the GDC console with an account bound to the project-db-admin role to find the following information for connecting to your database cluster. This information is in the Connectivity section of the Database Service page.

These steps include an example for connecting to the database using psql. The exact steps will vary depending on the client software you choose.

Create a user

To create a user, connect to the database cluster and complete the following steps:

1. At the psql prompt, create the user:

     CREATE USER USER_NAME
         WITH PASSWORD PASSWORD
         ATTRIBUTE1
         ATTRIBUTE2...;
     

   Enter the password when prompted.

   For more information about role attributes, see the PostgreSQL documentation.

2. You can confirm the user creation by displaying the user table:

     SELECT * FROM pg_roles;
     

Clone a database cluster

You can clone a database cluster to create a new database cluster that contains the same data as the original cluster. Cloning is a good way to make database clusters for testing purposes.

You can specify any point in time to base the clone on. You aren't limited to cloning the present state of a database cluster. The database service clones a new database cluster at the exact point in time you specify.

You can clone a database cluster with the GDC console or with the gdcloud CLI tool:

gdcloud database clusters clone SOURCE \
   DESTINATION --point-in-time POINT_IN_TIME

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: Restore
metadata:
  name: NEW_DBCLUSTER_NAME_restore
spec:
  sourceDBCluster: DBCLUSTER_NAME
  pointInTime: POINT_IN_TIME
  clonedDBClusterConfig:
    dbclusterName: NEW_DBCLUSTER_NAME

Plan maintenance windows

GDC offers you the ability to configure maintenance windows to schedule times for automatic updates. Maintenance windows are designed to target times where a brief downtime causes the lowest impact to your database clusters. You can schedule maintenance windows based on day of the week and hour, and length in which the maintenance window is open. For example, you could set a maintenance window to start at 3:00 AM on Tuesdays that spans eight hours.

You can also plan maintenance exclusions, which prevents disruptions to your workloads during date ranges where, due to unique circumstances, you don't want to allow the set maintenance window.

Create a maintenance window

To create a maintenance window for your database cluster, complete the following steps:

Remove a maintenance window

To remove a maintenance window for your database cluster, complete the following steps:

Manage maintenance exclusions

If you created a maintenance exclusion for your maintenance window, you can edit or remove the exclusion without affecting the maintenance window. To edit or remove a maintenance exclusion, complete the following steps:

Enable cross-project connections

By default, a database cluster only allows connections from within the user cluster and the same project. To allow connections from workloads in another project to all database clusters in your project:

apiVersion: networking.gdc.goog/v1
kind: ProjectNetworkPolicy
metadata:
  name: dbs-allow-from-CLIENT_PROJECT
  namespace: USER_PROJECT
spec:
  subject:
    managedServices:
      matchTypes:
      - dbs
    subjectType: ManagedService
  ingress:
  - from:
    - projects:
        matchNames:
        - CLIENT_PROJECT
  policyType: Ingress

Enable external connections

By default, a database cluster only allows connections from within the user cluster and the same project. To allow external connections from IP addresses outside of your Google Distributed Cloud air-gapped organization:

Sign and upload a server certificate

When you create a database cluster, a server certificate that is signed by the default GDC CA will be generated and configured for use by your database server. To sign and upload a certificate for your database that is issued by your own PKI, perform the following procedure. Your organization's default issuer must be in BYO certificate mode to use this feature.

Export a database cluster

You can export a database cluster to a data dump file using either the GDC console or the Distributed Cloud CLI:

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: Export
metadata:
  name: EXPORT_NAME
  namespace: USER_PROJECT
spec:
  dbclusterRef: DBCLUSTER_NAME
  exportLocation:
    s3Options:
      bucket: BUCKET_NAME
      key: dbs-export
    type: S3

Import from a dump file

Before importing data, you must:

1. Create a database cluster to import the data to.

2. Upload the dump file to a storage bucket. See Upload objects to storage buckets for instructions.

   The Database Service import service account must have access to the dump file. The service account is named postgresql-import-DATABASE_CLUSTER_NAME or oracle-import-DATABASE_CLUSTER_NAME, depending on the type of database you are importing.

   Replace DATABASE_CLUSTER_NAME with the name of the database cluster where you are importing data.

You can import a dump file into a database cluster using either the GDC console or the Distributed Cloud CLI:

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: Import
metadata:
  name: IMPORT_NAME
  namespace: USER_PROJECT
spec:
  dbclusterRef: DBCLUSTER_NAME
  dumpStorage:
    s3Options:
      bucket: BUCKET_NAME
      key: DUMP_FILE_PATH
    type: S3

Preserve database clusters before an upgrade

Depending on specific GDC releases, existing database clusters may not be migrated forward from previous GDC versions. In these cases, before applying the release, you must confirm that there is no critical data that must be preserved. For data that must be preserved, follow these steps to migrate them forward:

Before applying the release, you must confirm that there is no critical data that must be preserved. For data that must be preserved, follow these steps to migrate them forward:

1. List database clusters and identify the ones that must be preserved.
2. For each database cluster, export the data and store it in a GDC bucket.
3. Optional: To avoid additional changes, completely delete the database cluster before applying the new GDC version.
4. After the upgrade is complete, recreate the database cluster and import the dump file into it.

Manage advanced migration

Advanced migration is a solution for migrating data for large-sized databases with less downtime. This feature is only available for AlloyDB Omni and PostgreSQL.

A user with the Project DB Admin role must perform the following steps. Use either the GDC console or the Distributed Cloud CLI to manage migrations:

apiVersion: v1
data:
  ca.crt:  SOURCE_DB_CA_CERT
kind: Secret
metadata:
  annotations:
    propagation.gdch.gke.io/target-namespace:  USER_PROJECT
  name: es-crt-EXTERNAL_SERVER_NAME
  namespace: USER_PROJECT
type: Opaque

apiVersion: v1
data:
  password: SOURCE_DB_USER_PASSWORD
kind: Secret
metadata:
  annotations:
    propagation.gdch.gke.io/target-namespace: USER_PROJECT
  name: es-pw-EXTERNAL_SERVER_NAME
  namespace: USER_PROJECT
type: Opaque

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: ExternalServer
metadata:
  name: EXTERNAL_SERVER_NAME
  namespace: USER_PROJECT
spec:
  host: SOURCE_DB_HOST
  port: 5432
  username: SOURCE_DB_USERNAME
  password:
    name: es-pw-EXTERNAL_SERVER_NAME
    namespace: USER_PROJECT
  certRef:
    name: es-crt-EXTERNAL_SERVER_NAME
    namespace: USER_PROJECT

apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
kind: Migration
metadata:
  name: MIGRATION_NAME
  namespace: USER_PROJECT
spec:
  source:
    reference:
      apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
      kind: ExternalServer
      name: EXTERNAL_SERVER_NAME
  target:
    reference:
      apiVersion: DBENGINE_NAME.dbadmin.gdc.goog/v1
      kind: DBCluster
      name: DBCLUSTER_NAME
  control: MIGRATION_CONTROL

Observe metrics

You can observe Database Service metrics with the monitoring instance. See Monitoring and visualizing metrics for general information about the monitoring and visualizing processes for Application Operators (AO) in GDC.

You must create a database cluster before you can observe its metrics.

View diagnostic logs

1. Navigate to the monitoring instance UI to access the database diagnostic logs for a database cluster.

2. Click the explore Explore button from the menu to open the Explore page.

3. Enter a query to search for Database Service logs using LogQL.

   1. Using the Label filters drop-down menu, create a filter for service_name=ods.

   2. Click the add Operations button and select Line contains. Enter the database cluster's name in the text box.

   3. Click the add Operations button again and select Line contains. Enter PROJECT_NAME in the text box.

   4. Click the Run query button.

View metrics

1. Navigate to the monitoring instance UI to access the metrics for a database cluster.

2. From the drop-down menu, select prometheus as the data source to retrieve metrics.

3. Application operators can access Database Service metrics that have the ods_ prefix. Enter ods_ in the Select a metric text box in the Metrics browser panel to view all Database Service metric types.

4. Application operators can also access metrics with the pg_ prefix.