Getting started with vector embeddings in Cloud SQL for PostgreSQL

1. Introduction

In this codelab, you learn how to use Cloud SQL for PostgreSQL AI integration by combining vector search with Gemini Enterprise Agent Platform embeddings.

Architecture diagram showing Cloud SQL integration with Gemini Enterprise Agent Platform

Prerequisites

A basic understanding of Google Cloud and the Google Cloud console
Basic experience with the command-line interface and Cloud Shell

What you'll do

Deploy a Cloud SQL for PostgreSQL instance
Create a database and enable Cloud SQL AI integration
Load data into the database
Use Cloud SQL Studio
Generate embeddings using Gemini Enterprise Agent Platform in Cloud SQL
Use Agent Platform Studio
Enrich query results using a Gemini Enterprise Agent Platform generative model
Improve query performance using an HNSW vector index

What you'll need

A Google Cloud account and a Google Cloud project
A web browser such as Chrome

2. Setup and requirements

Project setup

Sign in to the Google Cloud console. If you don't already have a Google Account (Gmail or Google Workspace), you must create a Google Account.

Use a personal account instead of a work or school account.

Create a new project or reuse an existing one. To create a new project in the Google Cloud console, click Select a project in the toolbar.

Select a project dialog in Google Cloud console

In the Select a project dialog, click New Project.

In the dialog, enter a Project name and select the Location.

The Project name is the display name for this project's participants. The project name isn't used by Google APIs, and it can be changed at any time.
The Project ID is unique across all Google Cloud projects and is immutable (it cannot be changed after it is set). The Google Cloud console automatically generates a unique ID, but you can customize it. If you don't like the generated ID, you can generate another random one or provide your own to check its availability. In most codelabs, you reference your project ID, which is typically identified with the placeholder.
A third value, the Project Number, is used by some APIs. Learn more about all three of these values in the Creating and managing projects documentation.

Enable billing

Set up a personal billing account

If you set up billing using Google Cloud credits, you can skip this step.

To set up a personal billing account, enable billing in the Google Cloud Billing console.

Notes:

Completing this lab costs less than $5 USD in Google Cloud resources.
Follow the steps at the end of this lab to delete resources and avoid further charges.
New users are eligible for the $300 USD Free Trial.

Start Cloud Shell

While you can operate Google Cloud remotely from your computer, in this codelab you use Google Cloud Shell, a command-line environment running in the cloud.

In the Google Cloud Console toolbar, click Activate Cloud Shell:

Activate Cloud Shell

Alternatively, press g then s within the Google Cloud console, or open Cloud Shell.

It takes only a few moments to provision and connect to the environment. When it is finished, you should see the connected terminal:

Screenshot of Google Cloud Shell terminal showing that the environment has connected

This virtual machine is loaded with all the development tools you need. It offers a persistent 5 GB home directory and runs on Google Cloud, enhancing network performance and authentication. All your work in this codelab can be done within a browser.

3. Enable APIs

To use Cloud SQL, Compute Engine, Service Networking, and Gemini Enterprise Agent Platform, enable their respective APIs in your Google Cloud project.

In the Cloud Shell terminal, make sure that your project ID is set:

gcloud config set project <PROJECT_ID>

Set the PROJECT_ID environment variable:

PROJECT_ID=$(gcloud config get-value project)

Enable all required services:

gcloud services enable sqladmin.googleapis.com \
                       compute.googleapis.com \
                       cloudresourcemanager.googleapis.com \
                       servicenetworking.googleapis.com \
                       aiplatform.googleapis.com

Expected output:

student@cloudshell:~ (test-project-001-402417)$ gcloud config set project test-project-001-402417
Updated property [core/project].
student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project)
Your active configuration is: [cloudshell-14650]
student@cloudshell:~ (test-project-001-402417)$ 
student@cloudshell:~ (test-project-001-402417)$ gcloud services enable sqladmin.googleapis.com \
                       compute.googleapis.com \
                       cloudresourcemanager.googleapis.com \
                       servicenetworking.googleapis.com \
                       aiplatform.googleapis.com
Operation "operations/acat.p2-4470404856-1f44ebd8-894e-4356-bea7-b84165a57442" finished successfully.

You can read about each enabled API in the documentation.

4. Create a Cloud SQL instance

Create a Cloud SQL instance with Gemini Enterprise Agent Platform database integration.

Create database password

Define a password for the default database user. You can define your own password or use a random function to generate one:

export CLOUDSQL_PASSWORD=$(openssl rand -hex 16)

Display the generated password value:

echo $CLOUDSQL_PASSWORD

Note the generated password to use it later.

Create a Cloud SQL for PostgreSQL instance

Cloud SQL instances can be created using several methods, including the Google Cloud console, Terraform, or the Google Cloud CLI (gcloud). In this codelab, you use gcloud. To learn how to create an instance with other tools, see the Create instances documentation.

In Cloud Shell, run the following command to create the instance:

gcloud sql instances create my-cloudsql-instance \
--database-version=POSTGRES_18 \
--tier=db-custom-1-3840 \
--region=us-central1 \
--edition=ENTERPRISE \
--enable-google-ml-integration \
--database-flags cloudsql.enable_google_ml_integration=on

After you create the instance, set a password for the default user (postgres) and verify that you can connect:

gcloud sql users set-password postgres \
    --instance=my-cloudsql-instance \
    --password=$CLOUDSQL_PASSWORD

Connect to the instance using gcloud sql connect. When prompted, enter the password:

gcloud sql connect my-cloudsql-instance --user=postgres

Exit from the psql session by pressing Ctrl+D or entering exit:

exit

Enable Gemini Enterprise Agent Platform integration

Grant the necessary IAM role to the Cloud SQL service account to enable Gemini Enterprise Agent Platform integration.

Retrieve the Cloud SQL service account email and export it as an environment variable:

SERVICE_ACCOUNT_EMAIL=$(gcloud sql instances describe my-cloudsql-instance --format="value(serviceAccountEmailAddress)")
echo $SERVICE_ACCOUNT_EMAIL

Grant the roles/aiplatform.user role to the Cloud SQL service account:

PROJECT_ID=$(gcloud config get-value project)
gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$SERVICE_ACCOUNT_EMAIL" \
  --role="roles/aiplatform.user"

For more information on instance creation and configuration, see Integrate Cloud SQL with Gemini Enterprise Agent Platform.

5. Prepare the database

Create a database and enable vector support.

Create a database

Create a database named quickstart_db. You can create databases using database clients (such as psql), the Google Cloud CLI, or Cloud SQL Studio. In this step, use gcloud.

In Cloud Shell, run the following command to create the database:

gcloud sql databases create quickstart_db --instance=my-cloudsql-instance

Enable extensions

To work with Gemini Enterprise Agent Platform and vectors, enable two extensions in the quickstart_db database: google_ml_integration and vector.

In Cloud Shell, connect to the database:

gcloud sql connect my-cloudsql-instance --database quickstart_db --user=postgres

When prompted, enter your database password.

In the SQL session, run the following commands:

CREATE EXTENSION IF NOT EXISTS google_ml_integration CASCADE;
CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Exit the SQL session:

exit;

6. Load data

Create tables in the database and load data using fictional Cymbal Store dataset files stored in a public Cloud Storage bucket in CSV format.

First, create the required schema objects. Run gcloud sql connect and gcloud storage to download and import the schema:

In Cloud Shell, run the following command (when prompted, enter the password created for the instance):

gcloud storage cat gs://cloud-training/gcc/gcc-tech-004/cymbal_demo_schema.sql | gcloud sql connect my-cloudsql-instance --database quickstart_db --user=postgres

This command connects to the database and executes the downloaded SQL code to create tables, indexes, and sequences.

Next, download the CSV data files from Cloud Storage:

gcloud storage cp gs://cloud-training/gcc/gcc-tech-004/cymbal_products.csv .
gcloud storage cp gs://cloud-training/gcc/gcc-tech-004/cymbal_inventory.csv .
gcloud storage cp gs://cloud-training/gcc/gcc-tech-004/cymbal_stores.csv .

Connect to the database:

gcloud sql connect my-cloudsql-instance --database quickstart_db --user=postgres

Import data from the CSV files:

\copy cymbal_products from 'cymbal_products.csv' csv header

\copy cymbal_inventory from 'cymbal_inventory.csv' csv header

\copy cymbal_stores from 'cymbal_stores.csv' csv header

If you use your own data and the CSV files are compatible with the Cloud SQL import tool in the Google Cloud console, you can use the console instead of the command line.

7. Create embeddings

Build embeddings for the product descriptions using the text-embedding-005 model from Gemini Enterprise Agent Platform and store them as vector data.

If your session disconnected, connect to the database:

gcloud sql connect my-cloudsql-instance --database quickstart_db --user=postgres

Create a generated column named embedding in the cymbal_products table using the embedding function. This command creates a stored generated column that holds the vector embeddings generated from the product_description column for all rows in the table. The model is specified as the first parameter and the source text column as the second parameter:

ALTER TABLE cymbal_products ADD COLUMN embedding vector(768) GENERATED ALWAYS AS (google_ml.embedding('text-embedding-005', product_description)) STORED;

For 900 to 1000 rows, this process typically takes 1 to 5 minutes.

When you insert a new row into the table or update product_description on an existing row, the embedding column automatically updates.

8. Run similarity search

Run a similarity search by comparing the vector embeddings calculated for product descriptions against the embedding for a search query.

You can run SQL queries from the command line using gcloud sql connect or from Cloud SQL Studio. Cloud SQL studio provides more convenient way to edit and execute long SQL statements with multiple rows in output.

Start Cloud SQL Studio

In the Google Cloud console, navigate to Cloud SQL instances and click my-cloudsql-instance.

Cloud SQL instances list in Google Cloud console

In the navigation menu, click Cloud SQL Studio.

Cloud SQL Studio menu item

In the authentication dialog, enter the database name and credentials:
- Database: quickstart_db
- User: postgres
- Password:
Click Authenticate.

Cloud SQL Studio authentication dialog

Click the Editor tab to open the SQL Editor.

Cloud SQL Studio SQL editor tab

Run query

Run a query to retrieve the top 10 products most relevant to the customer query: "What kind of fruit trees grow well here?"

SELECT
        cp.product_name,
        left(cp.product_description,80) as description,
        cp.sale_price,
        cs.zip_code,
        (cp.embedding <=> embedding('text-embedding-005','What kind of fruit trees grow well here?')::vector) as distance
FROM
        cymbal_products cp
JOIN cymbal_inventory ci on
        ci.uniq_id=cp.uniq_id
JOIN cymbal_stores cs on
        cs.store_id=ci.store_id
        AND ci.inventory>0
        AND cs.store_id = 1583
ORDER BY
        distance ASC
LIMIT 10;

In Cloud SQL Studio, enter the query and click Run (or run the query in your psql session):

Running SQL query in Cloud SQL Studio

The query returns matching products ordered by cosine distance:

product_name       |                                   description                                    | sale_price | zip_code |      distance       
-------------------------+----------------------------------------------------------------------------------+------------+----------+---------------------
 Cherry Tree             | This is a beautiful cherry tree that will produce delicious cherries. It is an d |      75.00 |    93230 | 0.43922018972266397
 Meyer Lemon Tree        | Meyer Lemon trees are California's favorite lemon tree! Grow your own lemons by  |         34 |    93230 |  0.4685112926118228
 Toyon                   | This is a beautiful toyon tree that can grow to be over 20 feet tall. It is an e |      10.00 |    93230 |  0.4835677149651668
 California Lilac        | This is a beautiful lilac tree that can grow to be over 10 feet tall. It is an d |       5.00 |    93230 |  0.4947204525907498
 California Peppertree   | This is a beautiful peppertree that can grow to be over 30 feet tall. It is an e |      25.00 |    93230 |  0.5054166905547247
 California Black Walnut | This is a beautiful walnut tree that can grow to be over 80 feet tall. It is a d |     100.00 |    93230 |  0.5084219510932597
 California Sycamore     | This is a beautiful sycamore tree that can grow to be over 100 feet tall. It is  |     300.00 |    93230 |  0.5140519790508755
 Coast Live Oak          | This is a beautiful oak tree that can grow to be over 100 feet tall. It is an ev |     500.00 |    93230 |  0.5143126438081371
 Fremont Cottonwood      | This is a beautiful cottonwood tree that can grow to be over 100 feet tall. It i |     200.00 |    93230 |  0.5174774727252058
 Madrone                 | This is a beautiful madrona tree that can grow to be over 80 feet tall. It is an |      50.00 |    93230 |  0.5227400803389093
(10 rows)

9. Improve LLM response using retrieved data

Improve the generative AI response to a client application by passing query results as grounded context in a prompt to a Gemini Enterprise Agent Platform foundation language model.

To accomplish this:

Generate a JSON payload from the vector search result in Cloud SQL.
Test the prompt in Agent Platform Studio.
Execute the end-to-end prompt directly from SQL using the google_ml integration.

Generate output in JSON format

Modify the query to format the result as JSON and return one row:

WITH trees as (
SELECT
        cp.product_name,
        left(cp.product_description,80) as description,
        cp.sale_price,
        cs.zip_code,
        cp.uniq_id as product_id
FROM
        cymbal_products cp
JOIN cymbal_inventory ci on
        ci.uniq_id=cp.uniq_id
JOIN cymbal_stores cs on
        cs.store_id=ci.store_id
        AND ci.inventory>0
        AND cs.store_id = 1583
ORDER BY
        (cp.embedding <=> embedding('text-embedding-005','What kind of fruit trees grow well here?')::vector) ASC
LIMIT 1)
SELECT json_agg(trees) FROM trees;

Expected JSON output:

[{"product_name":"Cherry Tree","description":"This is a beautiful cherry tree that will produce delicious cherries. It is an d","sale_price":75.00,"zip_code":93230,"product_id":"d536e9e823296a2eba198e52dd23e712"}]

Run the prompt in Agent Platform Studio

Supply the generated JSON as context in a prompt to a generative model in Agent Platform Studio.

In the Google Cloud console, open Agent Platform Studio.

Agent Platform Studio navigation

Enter the following prompt in Agent Platform Studio:

Entering prompt in Agent Platform Studio

You are a friendly advisor helping to find a product based on the customer's needs.
Based on the client request we have loaded a list of products closely related to search.
The list in JSON format with list of values like {"product_name":"name","description":"some description","sale_price":10,"zip_code": 10234, "product_id": "02056727942aeb714dc9a2313654e1b0"}
Here is the list of products:
<JSON_OUTPUT>
The customer asked "What tree is growing the best here?"
You should give information about the product, price and some supplemental information.
Do not ask any additional questions and assume location based on the zip code provided in the list of products.

Replace with the JSON response from your query:

You are a friendly advisor helping to find a product based on the customer's needs.
Based on the client request we have loaded a list of products closely related to search.
The list in JSON format with list of values like {"product_name":"name","description":"some description","sale_price":10,"zip_code": 10234, "product_id": "02056727942aeb714dc9a2313654e1b0"}
Here is the list of products:
[{"product_name":"Cherry Tree","description":"This is a beautiful cherry tree that will produce delicious cherries. It is an d","sale_price":75.00,"zip_code":93230,"product_id":"d536e9e823296a2eba198e52dd23e712"}]
The customer asked "What tree is growing the best here?"
You should give information about the product, price and some supplemental information.
Do not ask any additional questions and assume location based on the zip code provided in the list of products.

The prompt in Agent Platform Studio

Run the prompt.

Prompt result in Agent Platform Studio

The answer includes price, description and supplemental information the model gets from external sources based on information about the tree and location.

Run the prompt in psql

You can also use Cloud SQL AI integration with Gemini Enterprise Agent Platform to get responses from a generative model directly within SQL. First, register the model.

Upgrade the extension to version 1.4.3 or higher if necessary. Connect to quickstart_db and run:

SELECT extversion from pg_extension where extname='google_ml_integration';

If the returned version is lower than 1.4.3, run:

ALTER EXTENSION google_ml_integration UPDATE TO '1.4.3';

Check the google_ml_integration.enable_model_support database flag:

SHOW google_ml_integration.enable_model_support;

If the flag is off, update the database flag in Cloud Shell:

gcloud sql instances patch my-cloudsql-instance \
--database-flags google_ml_integration.enable_model_support=on,cloudsql.enable_google_ml_integration=on

This operation takes 1 to 3 minutes. Verify the setting again:

SHOW google_ml_integration.enable_model_support;

CALL
  google_ml.create_model(
    model_id => 'gemini-3.5-flash',
    model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.6-flash:generateContent',
    model_provider => 'google',
    model_auth_type => 'cloudsql_service_agent_iam');

Verify the registered models:

SELECT model_id, model_type FROM google_ml.model_info_view WHERE model_id='gemini-3.5-flash';

Run the complete SQL query to retrieve vector search results and pass them directly to gemini-3.5-flash:

WITH trees AS (
SELECT
        cp.product_name,
        cp.product_description AS description,
        cp.sale_price,
        cs.zip_code,
        cp.uniq_id AS product_id
FROM
        cymbal_products cp
JOIN cymbal_inventory ci ON
        ci.uniq_id = cp.uniq_id
JOIN cymbal_stores cs ON
        cs.store_id = ci.store_id
        AND ci.inventory>0
        AND cs.store_id = 1583
ORDER BY
        (cp.embedding <=> embedding('text-embedding-005',
        'What kind of fruit trees grow well here?')::vector) ASC
LIMIT 1),
prompt AS (
SELECT
        'You are a friendly advisor helping to find a product based on the customer''s needs.
Based on the client request we have loaded a list of products closely related to search.
The list in JSON format with list of values like {"product_name":"name","product_description":"some description","sale_price":10}
Here is the list of products:' || json_agg(trees) || 'The customer asked "What kind of fruit trees grow well here?"
You should give information about the product, price and some supplemental information' AS prompt_text
FROM
        trees),
response AS (
SELECT
        google_ml.predict_row( model_id =>'gemini-3.5-flash',
        request_body => json_build_object('contents',
        json_build_object('role',
        'user',
        'parts',
        json_build_object('text',
        prompt_text))))->'candidates'->0->'content'->'parts'->0->'text' AS resp
FROM
        prompt)
SELECT
REPLACE(resp::text, '\n', CHR(10))
FROM
        response;

Expected output:

"Hello there! If you're looking for a wonderful fruit tree to plant, I have a fantastic option for you: ### **Cherry Tree** * **Price:** $75.00 * **Product ID:** `d536e9e823296a2eba198e52dd23e712` --- ### **Why it's a great choice:** * **Fruit & Shade:** Not only will it produce delicious, sweet cherries, but it also grows into a beautiful 15-foot deciduous tree that provides excellent shade and privacy for your yard. * **Seasonal Beauty:** Its leaves are a lovely dark green throughout the summer and transition into a striking red during the fall. * **Ideal Growing Conditions:** Cherry trees thrive best in cool, moist climates with sandy soil and are suitable for **USDA hardiness zones 4–9**. If your local climate matches these conditions, this Cherry Tree would make a beautiful and tasty addition to your garden! Let me know if you'd like more details or help placing an order."

10. Create a nearest-neighbor index

For large datasets with millions of vectors, vector search can require significant compute resources. To improve query performance, create an index on the vector embeddings.

Create an HNSW index

Hierarchical Navigable Small World (HNSW) is a graph-based vector index.

To build an HNSW index on the embedding column, specify the distance function (vector_cosine_ops) and optional parameters such as m and ef_construction. For more information, see Work with vector embeddings.

CREATE INDEX cymbal_products_embeddings_hnsw ON cymbal_products
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Expected output:

quickstart_db=> CREATE INDEX cymbal_products_embeddings_hnsw ON cymbal_products
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
CREATE INDEX
quickstart_db=>

Compare query performance

Run the vector search query with EXPLAIN (ANALYZE) to verify that the query planner uses the index:

EXPLAIN (ANALYZE) 
WITH trees as (
SELECT
        cp.product_name,
        left(cp.product_description,80) as description,
        cp.sale_price,
        cs.zip_code,
        cp.uniq_id as product_id
FROM
        cymbal_products cp
JOIN cymbal_inventory ci on
        ci.uniq_id=cp.uniq_id
JOIN cymbal_stores cs on
        cs.store_id=ci.store_id
        AND ci.inventory>0
        AND cs.store_id = 1583
ORDER BY
        (cp.embedding <=> embedding('text-embedding-005','What kind of fruit trees grow well here?')::vector) ASC
LIMIT 1)
SELECT json_agg(trees) FROM trees;

Expected output:

 Aggregate  (cost=779.12..779.13 rows=1 width=32) (actual time=1.066..1.069 rows=1 loops=1)
   ->  Subquery Scan on trees  (cost=769.05..779.12 rows=1 width=142) (actual time=1.038..1.041 rows=1 loops=1)
         ->  Limit  (cost=769.05..779.11 rows=1 width=158) (actual time=1.022..1.024 rows=1 loops=1)
               ->  Nested Loop  (cost=769.05..9339.69 rows=852 width=158) (actual time=1.020..1.021 rows=1 loops=1)
                     ->  Nested Loop  (cost=768.77..9316.48 rows=852 width=945) (actual time=0.858..0.859 rows=1 loops=1)
                           ->  Index Scan using cymbal_products_embeddings_hnsw on cymbal_products cp  (cost=768.34..2572.47 rows=941 width=941) (actual time=0.532..0.539 rows=3 loops=1)
...
 Planning Time: 112.398 ms
 Execution Time: 1.221 ms

The execution plan shows Index Scan using cymbal_products_embeddings_hnsw.

Run the query without EXPLAIN:

WITH trees as (
SELECT
        cp.product_name,
        left(cp.product_description,80) as description,
        cp.sale_price,
        cs.zip_code,
        cp.uniq_id as product_id
FROM
        cymbal_products cp
JOIN cymbal_inventory ci on
        ci.uniq_id=cp.uniq_id
JOIN cymbal_stores cs on
        cs.store_id=ci.store_id
        AND ci.inventory>0
        AND cs.store_id = 1583
ORDER BY
        (cp.embedding <=> embedding('text-embedding-005','What kind of fruit trees grow well here?')::vector) ASC
LIMIT 1)
SELECT json_agg(trees) FROM trees;

Expected output:

[{"product_name":"Cherry Tree","description":"This is a beautiful cherry tree that will produce delicious cherries. It is an d","sale_price":75.00,"zip_code":93230,"product_id":"d536e9e823296a2eba198e52dd23e712"}]

For more information and examples with LangChain and vector indexes, see the Cloud SQL AI overview documentation.

11. Clean up resources

Delete the Cloud SQL instance after you finish the codelab.

In Cloud Shell, set the project and environment variables if your session disconnected:

export INSTANCE_NAME=my-cloudsql-instance
export PROJECT_ID=$(gcloud config get-value project)

Delete the instance:

gcloud sql instances delete $INSTANCE_NAME --project=$PROJECT_ID

Expected output:

student@cloudshell:~$ gcloud sql instances delete $INSTANCE_NAME --project=$PROJECT_ID
All of the instance data will be lost when the instance is deleted.

Do you want to continue (Y/n)?  y

Deleting Cloud SQL instance...done.                                                                                                                
Deleted [https://sandbox.googleapis.com/v1beta4/projects/test-project-001-402417/instances/my-cloudsql-instance].

12. Congratulations

Congratulations! You completed the codelab.

This lab is part of the Production-Ready AI with Google Cloud Learning Path.

Explore the full curriculum to bridge the gap from prototype to production.
Share your progress with the hashtag #ProductionReadyAI.

Summary

You learned how to:

Deploy a Cloud SQL for PostgreSQL instance
Create a database and enable Cloud SQL AI integration
Load data into the database
Use Cloud SQL Studio
Use a Gemini Enterprise Agent Platform embedding model in Cloud SQL
Use Agent Platform Studio
Enrich query results using a Gemini Enterprise Agent Platform generative model
Improve query performance using a vector index

Try the AlloyDB AI vector embeddings codelab with ScaNN index instead of HNSW.