Vector generation (rds_embedding)

Prerequisites

Before you begin, ensure that you have:

• An RDS instance running PostgreSQL 14 or later

• An instance where the rds_embedding extension is already created. The extension is restricted from being created on all versions, and upgrading the minor engine version does not lift this restriction

• An Alibaba Cloud Model Studio API key. To get one, see Get your API key

• A NAT Gateway configured for the virtual private cloud (VPC) where your RDS instance runs. RDS PostgreSQL instances cannot reach the internet by default, so a NAT Gateway is required to call external embedding models

  NAT gateway configuration steps

  Step 1: Create an internet NAT gateway

  1. Log on to the NAT Gateway console.

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

  3. If this is your first time creating an internet NAT gateway, click Create Service-Linked Role in the Create Service-Linked Role section.

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

     Note

     For a full list of parameters, see Use the SNAT feature of an internet NAT gateway to access the internet.

     Parameter	Description
     Region	Select the same region as your RDS instance.
     VPC	Select the same VPC as your RDS instance. Find the VPC on the Database Connection page of the ApsaraDB RDS console.
     Associate vSwitch	Select the same vSwitch as your RDS instance. Find the vSwitch on the Database Connection page of the ApsaraDB RDS console.
     Access Mode	Select Configure Later.

  5. On the Confirm page, review the details, select the Terms of Service check box, and click Confirm. The gateway appears on the Internet NAT Gateway page.

     

  Step 2: Associate an elastic IP address (EIP) with the gateway

  1. On the Internet NAT Gateway page, click the gateway ID to open 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. The EIP appears on the Associated Elastic IP Address tab.

     

  Step 3: Create a SNAT entry

  1. On the Internet NAT Gateway page, click the gateway ID to open 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. The SNAT entry appears in the SNAT Entry List section.

     Parameter	Description
     SNAT Entry	Select Specify vSwitch so that only instances attached to the selected vSwitch can access the internet.
     Select vSwitch	Select the vSwitch of your RDS instance.
     Select EIP	Select one or more EIPs to access the internet. In this example, a single EIP is selected from the drop-down list.

     

Enable the extension

Run the following commands using a privileged account. Enable vector first — it provides the vector data type and operations that rds_embedding depends on. The rds_embedding extension is restricted from being created on all versions. The following statement applies only to instances where the extension is already created.

CREATE EXTENSION vector;
CREATE EXTENSION rds_embedding;

To disable the extensions:

DROP EXTENSION rds_embedding;
DROP EXTENSION vector;

Generate and query embeddings

The following example uses the text-embedding-v3 model from Alibaba Cloud Model Studio to generate 1024-dimensional vectors and run a cosine similarity query. For more information about the model, see Model introduction.

Step 1: Create a table

Create a table with a text column for your content and a vector(1024) column for the embeddings.

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

Step 2: Register the embedding model

Register text-embedding-v3 by providing its endpoint URL, authorization header template, request body template, and the JSON path to extract the embedding from the response.

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'''
);

Replace sk-**** with your API key.

Step 3: Insert text and embeddings

Insert each row by calling rds_embedding.get_embedding_by_model() inline. The function calls the model API and returns the embedding vector for the given text.

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[];

Replace sk-**** with your API key.

Step 4: Query by vector similarity

Use the <=> operator (cosine distance) to rank rows by semantic similarity to a query string. A distance of 0 means identical vectors; lower values indicate greater similarity.

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;

Replace sk-**** with your API key.

Expected output:

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

The query returns "Endless Yangtze River rolls on" first (distance 0, exact match), followed by "Boundless falling leaves rustle down" as the closest semantic neighbor.

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"
    }
}'

\dx+ rds_embedding

             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)

rds_embedding.add_model(mname text, murl text, mauth_header_template text, mbody_template text, membedding_path text)

rds_embedding.get_embedding_by_model(mname text, api-key text, texts text)

rds_embedding.del_model(mname text)