Vector generation (rds_embedding)

Background

Embedding is the process of mapping high-dimensional data to a low-dimensional representation. In machine learning and natural language processing (NLP), an embedding represents discrete symbols or objects as points in a continuous vector space.

When you generate embeddings, the values of the vector data depend on the data in the referenced model. RDS PostgreSQL supports the rds_embedding extension, which generates vector data from text in the database based on an imported external model. The extension also supports using a vector similarity operator to calculate the similarity between text in the database and specified text in the model. This capability enables more business scenarios.

Prerequisites

• The RDS instance runs PostgreSQL 14 or later.

• If your major engine version meets the requirement but the extension is not supported, upgrade the minor engine version. For example, for an RDS instance that runs PostgreSQL 17, the minor engine version must be 20241030 or later. For more information, see Upgrade the minor engine version.

• This topic uses the text embedding model from Alibaba Cloud Model Studio. Activate Alibaba Cloud Model Studio and obtain an API key. For more information, see Get your API key.

  Note

  In addition to the text embedding model used in this topic, you can use the functions provided by the rds_embedding extension to add other models. For more information, see Functions provided by the rds_embedding extension.

• By default, an RDS PostgreSQL database cannot access the Internet. To allow access to external models, configure a NAT Gateway for the VPC where the RDS PostgreSQL instance resides. For more information about NAT Gateways, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

  NAT gateway configuration steps

  1. Create a NAT Gateway.

     1. Log on to the NAT Gateway console.

     2. On the Internet NAT Gateway page, click Create Internet NAT Gateway.

     3. (Optional) If you are using NAT Gateway for the first time, on the Internet NAT Gateway page, in the Service-linked Role Creation section, click Create Service-linked Role. After the role is created, you can create a NAT Gateway.

     4. On the Create Internet NAT Gateway page, set the following parameters and click Buy Now.

        Note

        The following table lists only the key parameters. For more information about all parameters, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

        Configuration	Description
        Region	Select the region where you want to create the Internet NAT gateway. The region must be the same as the region of the RDS for PostgreSQL instance.
        Network And Zone	Select the VPC and vSwitch for the Internet NAT gateway instance. They must be the same as the VPC and vSwitch of the RDS for PostgreSQL instance. You can go to the Database Connection page in the RDS console to view the VPC of the target RDS for PostgreSQL instance.
        Network Type	In this topic, Internet NAT Gateway is selected.
        EIP	In this topic, Configure Later is selected.

     5. On the Confirm page, confirm the Internet NAT gateway configuration and click Activate Now.

        After you create the instance, you can view it on the Internet NAT Gateway page.

  2. Associate an EIP with the Internet NAT gateway.

     1. On the NAT Gateway console, find the Internet NAT gateway instance that you created and click its instance ID to go to the Basic Information page.

     2. On the Associated EIP tab, click Associate EIP.

     3. In the Associate EIP dialog box, select Purchase and Associate EIP.

     4. Click OK.

        After an EIP is associated, it is listed in the Associated EIP section.

  3. Create an SNAT entry.

     1. On the NAT Gateway console, find the Internet NAT gateway instance that you created and click its instance ID to go to the Basic Information page.

     2. On the SNAT tab, click Create SNAT Entry.

     3. On the Create SNAT Entry page, configure the following parameters and click OK.

        Configuration	Description
        SNAT Entry	Select the granularity for the SNAT entry. This topic uses VSwitch Granularity as an example. This specifies that the RDS for PostgreSQL instance in the vSwitch uses the configured public IP address to access the Internet.
        Select vSwitch	From the drop-down list, select the vSwitch of the RDS for PostgreSQL instance.
        Select EIP	Select the public IP address that provides Internet access. This topic uses a single IP address as an example. From the drop-down list, select the associated EIP.

        After the entry is created, you can view it in the SNAT Entry List.

Enable and disable the extensions

• Enable the extensions.

  Before you enable the rds_embedding extension, you must enable the vector extension. The vector extension provides the required vector data type support and basic vector operations, such as calculating the distance and similarity between vectors. The rds_embedding extension focuses on converting high-dimensional text data to vectors.

  CREATE EXTENSION vector;
  CREATE EXTENSION rds_embedding;

• Disable the extensions.

  DROP EXTENSION rds_embedding;
  DROP EXTENSION vector;

Usage examples

This topic uses the text-embedding-v3 model provided by Alibaba Cloud Model Studio as an example. For more information about text embedding models, see Model introduction.

1. Create a test table named test.

   CREATE TABLE test(info text, vec vector(1024) NOT NULL);

2. Add a model.

   SELECT rds_embedding.add_model(
       'text-embedding-v3',
       'https://dashscope.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding',
       'Authorization: Bearer sk-****',
       '{"input":{"texts":["%s"]},"model":"text-embedding-v3","parameters":{"text_type":"query"}}',
       '->''output''->''embeddings''->0->>''embedding'''
   );

   Note

   For more information about the rds_embedding.add_model() function, see rds_embedding.add_model().

3. Insert text and its corresponding vector data.

   INSERT INTO test SELECT 'Windy high sky, apes cry sadly', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', 'Windy high sky, apes cry sadly')::real[];
   INSERT INTO test SELECT 'Clear islet, white sand, birds fly back', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', 'Clear islet, white sand, birds fly back')::real[];
   INSERT INTO test SELECT 'Boundless falling leaves rustle down', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', 'Boundless falling leaves rustle down')::real[];
   INSERT INTO test SELECT 'Endless Yangtze River rolls on', rds_embedding.get_embedding_by_model('text-embedding-v3', 'sk-****', 'Endless Yangtze River rolls on')::real[];

   Note

   For more information about the rds_embedding.get_embedding_by_model() function, see rds_embedding.get_embedding_by_model().

4. Calculate the vector similarity between the text Endless Yangtze River rolls on and each text entry in the test table.

   SELECT 
       info, 
       vec <=> rds_embedding.get_embedding_by_model(
           'text-embedding-v3', 
           'sk-****', 
           'Endless Yangtze River rolls on'
       )::real[]::vector AS distance 
   FROM 
       test 
   ORDER BY 
       vec <=> rds_embedding.get_embedding_by_model(
           'text-embedding-v3', 
           'sk-****', 
           'Endless Yangtze River rolls on'
       )::real[]::vector;

   Sample result:

                  info                 |      distance
   ------------------------------------+--------------------
    Endless Yangtze River rolls on     |                  0
    Boundless falling leaves rustle down | 0.42740682200152647
    Windy high sky, apes cry sadly     | 0.5247695147991147
    Clear islet, white sand, birds fly back | 0.5161883811726116
   (4 rows)

References

curl --location 'https://dashscope.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding' \
--header 'Authorization: Bearer <API-KEY>' \
--header 'Content-Type: application/json' \
--data '{
    "model": "text-embedding-v3",
    "input": {
        "texts": [
        "Windy high sky, apes cry sadly",
        "Clear islet, white sand, birds fly back", 
        "Boundless falling leaves rustle down", 
        "Endless Yangtze River rolls on"
        ]
    },
    "parameters": {
    		"text_type": "query"
    }
}'

             Objects in extension "rds_embedding"
                      Object description
---------------------------------------------------------------
 function rds_embedding.add_model(text,text,text,text,text)
 function rds_embedding.del_model(text)
 function rds_embedding.get_embedding_by_model(text,text,text)
 function rds_embedding.get_response_by_model(text,text,text)
 function rds_embedding.show_models()
 function rds_embedding.update_model(text,text,text,text,text)
 schema rds_embedding
 table rds_embedding.models
(8 rows)