ApsaraDB RDS: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. In the Create Service-Linked Role section of the Internet NAT Gateway page, click Create Service-Linked Role to create a service-linked role. If this is the first time you create an Internet NAT gateway, this step is required. After the service-linked role is created, you can create Internet NAT gateways.

     4. On the buy page, configure the following parameters and click Buy Now.

        Note

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

        Parameter	Description
        Region	Select the region in which you want to create the Internet NAT gateway. The region must be the same as the region of your RDS instance.
        VPC	Select the VPC to which the Internet NAT gateway belongs. The VPC must be the same as the VPC of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the VPC of your RDS instance.
        Associate vSwitch	Select the vSwitch to which the Internet NAT gateway belongs. The vSwitch must be the same as the vSwitch of your RDS instance. You can go to the Database Connection page of the ApsaraDB RDS console to view the vSwitch of your RDS instance.
        Access Mode	In this example, Configure Later is selected.

     5. On the Confirm page, confirm the information, select the Terms of Service check box, and then click Confirm.

        You can find the Internet NAT gateway on the Internet NAT Gateway page.

  2. Associate an EIP with the Internet NAT gateway.

     1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.

     2. On the Associated Elastic IP Address tab, click Bind Elastic IP Address.

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

     4. Click OK.

        After you associate an EIP with the Internet NAT gateway, the EIP is displayed on the Associated Elastic IP Address tab.

  3. Create SNAT Entry

     1. On the Internet NAT Gateway page, find the new Internet NAT gateway and click its ID to go to the Basic Information tab.

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

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

        Parameter	Description
        SNAT Entry	Specify whether you want to create an SNAT entry for a VPC, a vSwitch, an ECS instance, or a custom CIDR block. In this example, Specify vSwitch is selected. This option specifies that only RDS instances that are attached to a specified vSwitch can access the Internet by using a specified public IP address.
        Select vSwitch	Select the vSwitch of your RDS instance from the drop-down list.
        Select EIP	Select one or more EIPs to access the Internet. In this example, a single EIP is selected from the drop-down list.

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

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-intl.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-intl.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)