This page describes how to generate and store vector embeddings based on a model.

For more information, seeBuild generative AI applications using Cloud SQL.

Cloud SQL lets you use an embedding model hosted by Vertex AIto translate a text string into anembedding, which is the model's representation of the given text's semanticmeaning as a numeric vector.

Cloud SQL implements embeddings as arrays ofreal values. You canuse generated embeddings as inputs forpgvector extension functions.

Before you begin

Some requirements differ depending on whether you want to useCloud SQL to generate embeddings, or whether you only need to workwith embeddings that are stored in your database from another source.

Regional restrictions

To generate Vertex AI embeddings with Cloud SQL, yourinstance must reside in a region wheregenerative AI foundational models are supported.The Vertex AI models that Cloud SQL can use for embeddings,text-embedding andtextembedding-gecko, are located in that region.

To work with embeddings, install thegoogle_ml_integrationextension on your Cloud SQL instance. For Vertex AI models, installversion1.2 or later. For third-party or custom models, install version1.4.2 or later.

Optionally, if you want to store these embeddings, and use vector functions andoperators with the embeddings, then you also need thepgvector extension.

Cloud SQL has both of these extensions. You caninstall them on any database in your instance. For moreinformation, seeConfigure PostgreSQL extensions.

Set up model access

Before you can generate embeddings from a Cloud SQL instance, you mustconfigure Cloud SQL to work with a text embedding model.

To work with the cloud-basedtext-embedding ortextembedding-gecko model,you need tointegrate Cloud SQL with Vertex AI.

Grant database users access to generate embeddings

Grant permission for database users to use theembeddingfunction to run predictions:

1. Connect apsql client to the primary instance, as described inConnect using a psql client.

2. At thepsql command prompt, connect to the database and grant permissions:

   \cDB_NAMEGRANTEXECUTEONFUNCTIONembeddingTOUSER_NAME;

   Replace the following:

   • DB_NAME: the name of the database for which you're granting permissions

   • USER_NAME: the name of the user for whom you're granting permissions

Generate embeddings

Cloud SQL provides a function that lets you translate text into avector embedding. You can then store that embedding in your database as vectordata, and optionally usepgvector functions to run queries on it.

Generate an embedding

To generate an embedding using Cloud SQL, use theembeddingfunction that thegoogle_ml_integration extension provides:

SELECTembedding('MODEL_IDVERSION_TAG','TEXT');

Make the following replacements:

• MODEL_ID: the ID of the model to query.

  If you're using theVertex AIModel Garden, then specifytext-embedding-004 or
  text-multilingual-embedding-002. These are thecloud-based models that Cloud SQL can use for text embeddings. Formore information, seeText embeddings.

• VERSION_TAG (optional): the version tag of the model toquery. For versions oftextembedding-gecko prior totext-embedding-004ortext-multilingual-embedding-002, prepend the tag with@.

  If you're using one of thetextembedding-gecko models withVertex AI, then specify one of the version tagslisted inModel versions.

  Cloud SQL strongly recommends that youspecify the version tag.

• TEXT: the text to translate into a vector embedding.

The following example uses thetext-embedding-004 model to generate anembedding based on a provided literal string:

SELECTembedding('text-embedding-004','Cloud SQL is a managed, cloud-hosted SQL database service.');

Store a generated embedding

The return value of theembedding() function is an array ofreal values.To store this value in a table, add areal[] column:

ALTERTABLETABLEADDCOLUMNEMBEDDING_COLUMNreal[DIMENSIONS];

Make the following replacements:

• TABLE: the table name

• EMBEDDING_COLUMN: the name of the new embedding column

• DIMENSIONS: the number of dimensions that the modelsupports.

  If you're using one of thetext-embedding ortextembedding-gecko models withVertex AI, then specify768.

Optionally, if you have installed thepgvector extension, then you canstore embeddings asvector values:

ALTERTABLETABLEADDCOLUMNEMBEDDING_COLUMNvector(DIMENSIONS);

After you create a column to store embeddings, you can populate it based onthe values already stored in another column in the same table:

UPDATETABLESETEMBEDDING_COLUMN=embedding('MODEL_IDVERSION_TAG',SOURCE_TEXT_COLUMN);

Make the following replacements:

• TABLE: the table name.

• EMBEDDING_COLUMN: the name of the embedding column.

• MODEL_ID: the ID of the model to query.

  If you're using theVertex AIModel Garden, then specifytext-embedding-004 or
  text-multilingual-embedding-002. These are thecloud-based models that Cloud SQL can use for text embeddings. Formore information, seeText embeddings.

• VERSION_TAG (Optional): the version tag of the model toquery. For versions oftextembedding-gecko prior totext-embedding-004ortext-multilingual-embedding-002,Prepend the tag with@`.

  If you're using one of thetextembedding-gecko models withVertex AI, then specify one of the version tagslisted inModel versions.

  Cloud SQL strongly recommends that youspecify the version tag. By not doing so, yourisk unexpected results.

• SOURCE_TEXT_COLUMN: the name of the column that's storing the text.You translate this text into embeddings.

The previous command works for bothreal[] andvector embedding columns. Ifyour embedding column is of thevector type, then Cloud SQL casts thereturn value ofembedding() from areal array to avector value implicitly.

The following example uses thetext-embedding-004 model to populate themessages.message_vector column with embeddings based on the content of themessages.message column:

UPDATEmessagesSETmessage_vector=embedding('text-embedding-004',message);

Query and index embeddings usingpgvector

Thepgvector PostgreSQL extension lets you use vector-specific operators andfunctions when you store, index, and query text embeddings in your database.Cloud SQL has its own optimizations for working withpgvector,letting you create indexes that can speed up queries that involve embeddings.

Create a nearest-neighbor index

pgvector supports approximate nearest-neighbor (ANN) searches throughindexing.

To create anHNSW index, use thehnswfunction, as shown in the following example:

CREATEINDEXONTABLEUSINGhnsw(EMBEDDING_COLUMNDISTANCE_FUNCTION)WITH(m=M,ef_construction=EF_CONSTRUCTION);

Make the following replacements:

• TABLE: the table to which you're adding the index.

• EMBEDDING_COLUMN: a column that storesvector data.

• DISTANCE_FUNCTION: the distance function to use with thisindex. Choose one of the following:

  • L2 distance:vector_l2_ops

  • Inner product:vector_ip_ops

  • Cosine distance:vector_cosine_ops

• M (optional): the maximum number of connections with neighboringdata points in a graph. We recommend a range of 5 to 48. The default is 16.

• EF_CONSTRUCTION (optional): the size of the list that holds theclosest candidates during the graph traversal when building the index.Higher values lead the algorithm to consider more candidates, allowing abetter index to be created. The default size is 64.

To create this index on an embedding column that uses thereal[] data typeinstead ofvector, cast the column into thevector data type:

CREATEINDEXONTABLEUSINGhnsw((CAST(EMBEDDING_COLUMNASvector(DIMENSIONS)))DISTANCE_FUNCTION)WITH(m=M,ef_construction=EF_CONSTRUCTION);

ReplaceDIMENSIONS with the dimensional width of the embedding column.

The next section demonstrates an example of this kind of index.

Make a nearest-neighbor query with given text

After you store and index embeddings in your database, the full range ofpgvector queryfunctionalityis available to you.

To find the nearest semantic neighbors to a piece of text, usetheembedding() function to translate the text into a vector. In the same query,apply this vector to thepgvector nearest-neighbor operator,<->, to findthe database rows with the most semantically similar embeddings.

Becauseembedding() returns areal array, you must cast theembedding()call tovector to use these values withpgvector operators.

SELECTRESULT_COLUMNSFROMTABLEORDERBYEMBEDDING_COLUMN<->embedding('MODEL_IDVERSION_TAG','TEXT')::vectorLIMITROW_COUNT

Make the following replacements:

• RESULT_COLUMNS: the columns to display from semanticallysimilar rows.

• TABLE: the table containing the embedding to which you compare thetext.

• EMBEDDING_COLUMN: the column containing the stored embeddings.

• MODEL_ID: the ID of the model to query.

  If you're using theVertex AIModel Garden, then specifytext-embedding-004 or
  text-embedding-multilingual-002. These are thecloud-based models that Cloud SQL can use for text embeddings. Formore information, seeText embeddings.

• VERSION_TAG (Optional): the version tag of the model toquery. Prepend the tag with@.

  If you're using one of thetextembedding-gecko models withVertex AI, then specify one of the version tagslisted inModel versions.

  Cloud SQL strongly recommends that youspecify the version tag. By not doing so, you risk unexpected results.

• TEXT: the text that you want so that you can find the nearest storedsemantic neighbors.

• ROW_COUNT: the number of rows to return. If you want only thesingle best match, then specify1 as the value for this parameter.

To run this query with a stored embedding column that uses thereal[] data typeinstead ofvector, cast the column into thevector data type:

SELECTRESULT_COLUMNSFROMTABLEORDERBYEMBEDDING_COLUMN::vector<->embedding('MODEL_IDVERSION_TAG','TEXT')::vectorLIMITROW_COUNT

Use model version tags to avoid errors

If you omit the version tag when using a model in the Vertex AIModel Garden, then Vertex AI uses the latest versionof the model. This might not be the latest stable version. For moreinformation about available Vertex AI model versions, seeModel versions.

A given Vertex AI model version always returns the sameembedding() response to a given text input. If you don't specify the modelversion in your embedding calls, then it's possible that a newly published modelversion could abruptly change the returned vector for a given input. This cancause errors or other unexpected behavior in your applications.

What's next

• Build generative AI applications using Cloud SQL
• Integrate Cloud SQL with Vertex AI
• Invoke online predictions from Cloud SQL instances
• Understand an example of an embedding workflow
• Build LLM-powered applications using LangChain
• Manage your data using Cloud SQL Studio
• Write SQL with Gemini assistance