Getting started with Vector Embeddings in Cloud SQL for PostgreSQL

1. Introduction

In this codelab you will learn how to use Cloud SQL for PostgreSQL AI integration by combining vector search with Vertex AI embeddings.

Prerequisites

• A basic understanding of Google Cloud, console
• Basic skills in command line interface and Cloud Shell

What you'll learn

• How to deploy a Cloud SQL for PostgreSQL instance
• How to create database and enable Cloud SQL AI integration
• How to load data to the database
• How to use Vertex AI embedding model in Cloud SQL
• How to enrich the result using Vertex AI generative model
• How to improve performance using vector index

What you'll need

• A Google Cloud Account and Google Cloud Project
• A web browser such as Chrome supporting Google Cloud console and Cloud Shell

2. Setup and Requirements

Self-paced environment setup

1. Sign-in to the Google Cloud Console and create a new project or reuse an existing one. If you don't already have a Gmail or Google Workspace account, you must create one.

• The Project name is the display name for this project's participants. It is a character string not used by Google APIs. You can always update it.
• The Project ID is unique across all Google Cloud projects and is immutable (cannot be changed after it has been set). The Cloud Console auto-generates a unique string; usually you don't care what it is. In most codelabs, you'll need to reference your Project ID (typically identified as PROJECT_ID). If you don't like the generated ID, you might generate another random one. Alternatively, you can try your own, and see if it's available. It can't be changed after this step and remains for the duration of the project.
• For your information, there is a third value, a Project Number, which some APIs use. Learn more about all three of these values in the documentation.

Caution: A project ID is globally unique and can't be used by anyone else after you've selected it. You are the only user of that ID. Even if a project is deleted, the ID can't be used again

Note: If you use a Gmail account, you can leave the default location set to No organization. If you use a Google Workspace account, choose a location that makes sense for your organization.

2. Next, you'll need to enable billing in the Cloud Console to use Cloud resources/APIs. Running through this codelab won't cost much, if anything at all. To shut down resources to avoid incurring billing beyond this tutorial, you can delete the resources you created or delete the project. New Google Cloud users are eligible for the $300 USD Free Trial program.

Start Cloud Shell

While Google Cloud can be operated remotely from your laptop, in this codelab you will be using Google Cloud Shell, a command line environment running in the Cloud.

From the Google Cloud Console, click the Cloud Shell icon on the top right toolbar:

It should only take a few moments to provision and connect to the environment. When it is finished, you should see something like this:

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

3. Before you begin

Enable API

Output:

Please be aware that some resources you enable are going to incur some cost. Under normal circumstances if the Cloud SQL instance and compute VM are destroyed upon completion of the lab the cost of all resources would not exceed $5. We recommend checking your billing and making sure the exercise is acceptable for you.

Inside Cloud Shell, make sure that your project ID is setup:

gcloud config set project [YOUR-PROJECT-ID]

Set environment variable PROJECT_ID:

PROJECT_ID=$(gcloud config get-value project)

Enable all necessary 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.

4. Create a Cloud SQL instance

Create Cloud SQL instance with database integration with Vertex AI.

Create Database password

Define 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 12`

Note the generated value for the password:

echo $CLOUDSQL_PASSWORD

Create Cloud SQL for PostgreSQL Instance

In the Cloud Shell session execute:

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

After creating the instance, we need to set a password for the default user in the instance and verify if we can connect with the password.

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

Run the command and put your password in the prompt when it is ready to connect.

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

Enable Vertex AI Integration

Grant necessary privileges to the internal cloud sql service account to be able to use Vertex AI integration.

Find out Cloud SQL internal service account email and export it as a variable.

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

Grant access to Vertex AI to the Cloud SQL service account:

gcloud projects add-iam-policy-binding $PROJECT_ID \
  --member="serviceAccount:$SERVICE_ACCOUNT_EMAIL" \
  --role="roles/aiplatform.user"

Read more about instance creation and configuration in the Cloud SQL documentation here.

5. Prepare Database

Now we need to create a database and enable vectors support.

Create Database

Create a database with the name quickstart_db .To do so we have different options such as command line database clients such as psql for PostgreSQL, SDK or Cloud SQL Studio. We will use the SDK (gcloud) for creating databases and connecting to the instance.

In the Cloud Shell execute command to create the database

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

Enable Extensions

To be able to work with Vertex AI and vectors we need to enable two extensions in our created database.

In the Cloud Shell execute command to connect to the created database (you will need to provide your password)

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

Then, after successful connection, in the the sql session you need to run two commands:

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

Exit from SQL session:

exit;

6. Load Data

Now we need to create objects in the database and load data. We are going to use fictional Cymbal Store data. The data are available on the public Google Storage bucket in CSV format.

First we need to create all required objects in our database. To do so we are going to use already familiar gcloud sql connect and gcloud storage commands to download and import the schema objects to our database.

In the cloud shell execute and provide the password noted when we were creating 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

What did we do exactly in the previous command? We connected to our database and executed the downloaded SQL code which created tables, indexes and sequences.

The next step is to load the data and to do so we need to download the CSV files from the Google 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 .

Then we need to connect to the database.

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

And import data from our 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

Exit from SQL session:

exit;

If you have your own data and your CSV files are compatible with the Cloud SQL import tool available from the Cloud console you can use it instead of the command line approach.

7. Create Embeddings

The next step is to build embeddings for our product descriptions using the textembedding-004 model from Google Vertex AI and store them as vector data.

Connect to the database:

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

And create a virtual column embedding in our cymbal_products table using the embedding function.

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

It might take some time but for 900-1000 rows it shouldn't take more than 5 min and usually it is much faster.

8. Run Similarity Search

We can now run our search using similarity search based on vector values calculated for the descriptions and the vector value we get for our request.

The SQL query can be executed from the same command line interface using gcloud sql connect or, as alternative, from Cloud SQL Studio. Any multirow and complex query is better to manage in the Cloud SQL Studio.

Start Cloud SQL Studio

In the console click on the Cloud SQL instance we've created earlier.

When it is open on the right panel we can see Cloud SQL Studio. Click on it.

It will open a dialog where you provide database name and your credentials:

• Database: quickstart_db
• User: postgres
• Password: your noted password for the main database user

And click on the "AUTHENTICATE" button.

It will open the next window where you click on the "Editor" tab on the right side to open the SQL Editor.

Now we are ready to run our queries.

Run Query

Run a query to get a list of available products most closely related to a client's request. The request we are going to pass to Vertex AI to get the vector value sounds like "What kind of fruit trees grow well here?"

Here is the query you can run to choose first 10 items most suitable for our request:

SELECT
        cp.product_name,
        left(cp.product_description,80) as description,
        cp.sale_price,
        cs.zip_code,
        (cp.embedding <=> embedding('text-embedding-004','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;

Copy and paste the query to the Cloud SQL Studio editor and push the "RUN" button or paste it in your command line session connecting to the quickstart_db database.

And here is a list of products chosen matching the query.

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

We can improve the Gen AI LLM response to a client application using the result of the executed query and prepare a meaningful output using the supplied query results as part of the prompt to a Vertex AI generative foundation language model.

To achieve that we need to generate a JSON with our results from the vector search, then use that generated JSON as addition to a prompt for a LLM model in Vertex AI to create a meaningful output. In the first step we generate the JSON, then we test it in the Vertex AI Studio and in the last step we incorporate it into a SQL statement which can be used in an application.

Generate output in JSON format

Modify the query to generate the output in JSON format and return only one row to pass to Vertex AI

Cloud SQL for PostgreSQL

Here is the example of the query:

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-004','What kind of fruit trees grow well here?')::vector) ASC
LIMIT 1)
SELECT json_agg(trees) FROM trees;

And here is the expected JSON in the 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 Vertex AI Studio

We can use the generated JSON to supply it as a part of the prompt to generative AI text model in the Vertex AI Studio

Open the Vertex AI Studio Chat in the cloud console.

Note: In the example we used all default parameters for free form text chat and the latest (at that moment) gemini-2.0-flash model with default parameters. Your output might be different depending on model version and parameters. Read more about Vertex AI and generative language models in the documentation.

It may ask you to enable additional APIs but you can ignore the request. We don't need any additional APIs to finish our lab.

Here is the prompt we are going to use:

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, "produt_id": "02056727942aeb714dc9a2313654e1b0"}
Here is the list of products:
[place your JSON here]
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.

And here how it looks when we replace the JSON placeholder by the response from the 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, "produt_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.

And here is the result when we run the prompt with our JSON values and using the gemini-2.0-flash model:

The answer we got from the model in this example follows. Note that your answer may be different because of model and parameters changes over time:

"Okay, based on the available product list, the best tree we have that might grow well in your area is the Cherry Tree.

It's priced at $75.00.

While I don't have specific details about the growing conditions in your exact zip code (93230), Cherry Trees are generally known to thrive in areas with moderate climates and well-drained soil. They typically require a certain period of winter chill to produce fruit, so that's something to keep in mind. However, they can be a wonderful addition to a garden, providing both beauty and delicious cherries when the conditions are right."

Run the prompt in PSQL

We can also use the Cloud SQL AI integration with Vertex AI to get the similar response from a generative model using SQL directly in the database. But to use the gemini-2.0-flash-exp model we need to register it first.

Run in Cloud SQL for PostgreSQL

Upgrade the extension to version 1.4.2 or higher (if the current version is lower). Connect to the quickstart_db database from gcloud sql connect as it has been shown before (or use Cloud SQL Studio) and execute:

SELECT extversion from pg_extension where extname='google_ml_integration';

If the returned value is less than 1.4.2 then execute:

ALTER EXTENSION google_ml_integration UPDATE TO '1.4.2';

Then we need to set google_ml_integration.enable_model_support database flag to "on". To perform that you can use the web console interface or run the following gcloud command.

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

The command takes around 1-3 min to execute in the background. Then you can verify the new flag in the psql session or using Cloud SQL Studio connecting to the quickstart_db database.

show google_ml_integration.enable_model_support;

The expected output from the psql session is "on":

quickstart_db => show google_ml_integration.enable_model_support;
 google_ml_integration.enable_model_support 
--------------------------------------------
 on
(1 row)

Then we need to register two models. The first one is the already used text-embedding-004 model. It needs to be registered since we enabled the model registration capabilities.

To register the model run in psql or Cloud SQL Studio the following code:

CALL
  google_ml.create_model(
    model_id => 'text-embedding-004',
    model_provider => 'google',
    model_qualified_name => 'text-embedding-004',
    model_type => 'text_embedding',
    model_auth_type => 'cloudsql_service_agent_iam',
    model_in_transform_fn => 'google_ml.vertexai_text_embedding_input_transform',
    model_out_transform_fn => 'google_ml.vertexai_text_embedding_output_transform');

And the next model we need to register is gemini-2.0-flash-001 which will be used to generate the user-friendly output.

Please replace the placeholder $PROJECT_ID by your project id in the model_request_url string value. Optionally you can also correct the model name if you decide to use a more advanced model in the following command before executing it.

CALL
  google_ml.create_model(
    model_id => 'gemini-2.0-flash-001',
    model_request_url => 'https://us-central1-aiplatform.googleapis.com/v1/projects/$PROJECT_ID/locations/us-central1/publishers/google/models/gemini-2.0-flash-001:streamGenerateContent',
    model_provider => 'google',
    model_auth_type => 'cloudsql_service_agent_iam');

You can always verify the list of registered models by selecting information from the google_ml.model_info_view.

select model_id,model_type from google_ml.model_info_view;

Here is sample output

quickstart_db=> select model_id,model_type from google_ml.model_info_view;
               model_id               |   model_type   
--------------------------------------+----------------
 textembedding-gecko                  | text_embedding
 textembedding-gecko@001              | text_embedding
 gemini-1.5-pro:streamGenerateContent | generic
 gemini-1.5-pro:generateContent       | generic
 gemini-1.0-pro:generateContent       | generic
 text-embedding-004                   | text_embedding
 gemini-2.0-flash-001                 | generic

Now we can use the generated in a subquery JSON to supply it as a part of the prompt to generative AI text model using SQL.

In the psql or Cloud SQL Studio session to the database run the query

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 <=> google_ml.embedding('text-embedding-004',
        '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
        json_array_elements(google_ml.predict_row( model_id =>'gemini-2.0-flash-001',
        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
        string_agg(resp::text,
        ' ')
FROM
        response;

And here is the expected output. Your output might be different depending on the model version and parameters.:

"That" "'s a great question! Based on your location (assuming you're" " in zip code 93230), I have a suggestion for a" " fruit tree that should thrive.\n\nWe have the **Cherry Tree** available.\n\n**Product Name:** Cherry Tree\n\n**Description:** This is a beautiful cherry" " tree that will produce delicious cherries. It's a deciduous tree (meaning it loses its leaves in the fall) growing to about 15 feet tall." " The leaves are dark green in summer, turning a beautiful red in the fall. Cherry trees are known for their beauty, shade, and privacy.\n\n**Sale Price:** $75.00\n\n**Important Considerations for Growing" " Cherry Trees:**\n\n* **Climate:** Cherry trees prefer a cool, moist climate, and 93230 falls within a suitable range (USDA zones 4-9). However, it's always a good idea to" " check the specific microclimate of your property (sun exposure, drainage etc.).\n* **Soil:** They do best in sandy soil. If your soil is different, you may need to amend it to improve drainage.\n* **Pollination:** Many cherry varieties require a second, compatible cherry tree for proper pollination" ". Check the specific pollination needs of this variety before purchase if you want a significant cherry yield.\n\nThis cherry tree is a beautiful addition to any yard and will provide you with delicious cherries if you can meet its needs. Would you like to know more about its pollination requirements, or perhaps see if we have any other" " fruit trees suitable for your area?\n" ""

10. Create a nearest-neighbor index

Our dataset is quite small and the response time primarily depends on interactions with AI models. But when you have millions of vectors the vector search can take a significant portion of our response time and put a high load on the system. To improve that we can build an index on top of our vectors.

Create HNSW index

We are going to try the HNSW index type for our test. The HNSW stands for Hierarchical Navigable Small World and represents a multilayer graph index.

To build the index for our embedding column we need to define our embedding column, distance function and optionally parameters like m or ef_constructions. You can read about the parameters in detail in the documentation.

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 Response

Now we can run the vector search query in EXPLAIN mode and verify if the index has been used.

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-004','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)
                                 Order By: (embedding <=> '[0.008864171,0.03693164,-0.024245683,...
<redacted>
...,0.017593635,-0.040275685,-0.03914233,-0.018452475,0.00826032,-0.07372604
]'::vector)
                           ->  Index Scan using product_inventory_pkey on cymbal_inventory ci  (cost=0.42..7.17 rows=1 width=37) (actual time=0.104..0.104 rows=0 loops=3)
                                 Index Cond: ((store_id = 1583) AND (uniq_id = (cp.uniq_id)::text))
                                 Filter: (inventory > 0)
                                 Rows Removed by Filter: 1
                     ->  Materialize  (cost=0.28..8.31 rows=1 width=8) (actual time=0.133..0.134 rows=1 loops=1)
                           ->  Index Scan using product_stores_pkey on cymbal_stores cs  (cost=0.28..8.30 rows=1 width=8) (actual time=0.129..0.129 rows=1 loops=1)
                                 Index Cond: (store_id = 1583)
 Planning Time: 112.398 ms
 Execution Time: 1.221 ms

From the output we can clearly see that the query was using "Index Scan using cymbal_products_embeddings_hnsw".

And if we 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-004','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"}]

We can see that the result is the same and return the same Cherry tree which was on the top in our search without index. Depending on parameters and the index type it is possible that result can be slightly different. During my tests the indexed query returned results in 131.301 ms vs 167.631 ms without any index but we were dealing with a very small dataset and the difference would be more substantial on a bigger data.

You can try different indexes available for the vectors and more labs and examples with langchain integration available in the documentation.

11. Clean up environment

Delete the Cloud SQL instance

Destroy the Cloud SQL instance when you are done with the lab

In the cloud shell define the project and environment variables if you've been disconnected and all the previous settings are lost:

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

Note: The command takes 3-5 minutes to execute

Expected console 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 for completing the codelab.

What we've covered

• How to deploy a Cloud SQL for PostgreSQL instance
• How to create database and enable Cloud SQL AI integration
• How to load data to the database
• How to use Vertex AI embedding model in Cloud SQL
• How to enrich the result using Vertex AI generative model
• How to improve performance using vector index

Try similar codelab for AlloyDB with ScaNN index instead of HNSW

13. Survey

Output:

How will you use this tutorial?

Only read through it Read it and complete the exercises