This page provides an example of a workflow that demonstrates how theembedding() function workswith the data that's stored in your tables and thepgvector query functionality. The exampleuses plain-text input to fetch a result from a database that relies on largelanguage model (LLM)-driven semantic parsing of the text's meaning.

An example scenario of an embedding workflow

Imagine a database running on Cloud SQL for PostgreSQL with the following aspects:

• The database contains a table:items. Each row in this table describes anitem that your business sells.

• Theitems table contains a column:complaints. This column storesbuyer complaints that are logged about each item as plain text.

• The database integrates with theVertex AIModel Garden, giving it access to thetextembedding-gecko LLM.

Even though this database stores complaints about items, these complaints arestored as plain text, making it challenging to query. For example, if you wantto see which items have the most complaints from customers who received thewrong color of merchandise, then you can perform ordinary SQL queries on thetable, looking for various keyword matches. However, this approach matches onlyrows that contain those exact keywords.

For example, a basic SQL query such asSELECT * FROM item WHERE complaints LIKE"%wrong color%" doesn't return a row where thecomplaints field contains onlyThe picture shows a blue one, but the one I received was red.

SQL queries using LLM-powered embeddings can help bridge this gap. Byapplying embeddings, you can query the table in this example for items wherecomplaints have semantic similarity to a given text prompt, such as "It was thewrong color".

The following steps show how to enable this in the example scenario describedearlier.

Prepare the table

Before you run LLM-based queries on the content of theitems table, you mustprepare the table to store and index embeddings based on your existingdata.

Create a column to store embeddings

Add a column to the table to store embeddings.

This example specifies768 as an argument because that's how many dimensions thetextembedding-gecko LLM supports. For more information, seeGenerate an embedding.

The example applies thevector data type to the column to simplify usingpgvector functions and operators with the column's values.

Populate the new column

Use theembedding()function to populate this new column with embeddings basedon the value of each row's text that appears in thecomplaints column. In this example,Cloud SQL generates the embeddings using the LLM with the ID oftextembedding-gecko, version004.

UPDATEitemsSETcomplaint_embedding=embedding('text-embedding-005',complaints);

Create an index

To improve performance, add an index to theitems table.

CREATEINDEXcomplaint_embed_idxONitemsUSINGhnsw(complaint_embeddingvector_cosine_ops);

For more information on creating this type of index, seeCreate a nearest-neighbor index. Also, for more information on tuning the index by setting parameters, seeQuery and index embeddings usingpgvector.

Run LLM-powered queries with provided text

You can now make semantic nearest-neighbor queries on theitems table. Thefollowing query uses the<-> operator thatpgvectorprovidesto complete the following actions:

• Sort the table's rows on semantic proximity to the text ofIt was the wrong color.
• Return the top ten complaints.

The query displays theid andname values of the first sorted row.

SELECTid,nameFROMitemsORDERBYcomplaint_embedding<->embedding('text-embedding-005','It was the wrong color')::vectorLIMIT10;

What's next

• Build generative AI applications using Cloud SQL

• Integrate Cloud SQL with Vertex AI

• Work with vector embeddings

• Invoke online predictions from Cloud SQL instances

• Build LLM-powered applications using LangChain