AI (rds_ai)

Prerequisites

• Ensure your RDS for PostgreSQL instance meets the version requirements in the following table:

  Major version	Minor version
  PostgreSQL 16	20241230 or later
  PostgreSQL 14, PostgreSQL 15, and PostgreSQL 17	20250430 or later
  PostgreSQL 18	20251130 or later

  To update the minor engine version, see Update the minor engine version.

• You must have a privileged account for your RDS for PostgreSQL instance. For more information, see Create an account.

• The model used in this document is provided by Alibaba Cloud Model Studio. You must first activate the service and obtain an API key. For more information, see Get an API key.

Network configuration

By default, an RDS PostgreSQL instance cannot access external networks. To enable access to external models, you must either use PrivateLink to connect your VPC to Model Studio, or configure a NAT Gateway for the instance's VPC.

• Connect over PrivateLink

  Accessing Model Studio over a private connection enhances data security and transfer efficiency. You can use PrivateLink to connect your VPC to Model Studio. For details, see Access model and application APIs in Model Studio over a private network.

• Connect over a public network

  Alternatively, you can configure a NAT Gateway for the VPC hosting your RDS PostgreSQL instance. For more information, see Use the SNAT feature of a public NAT gateway to access the internet.

Create and drop extensions

• Install an extension on the Plug-ins page.

  1. Go to the Instances page, select a region at the top, and then click the ID of the target instance.

  2. In the navigation pane on the left, click Plug-ins.

  3. On the Extension Marketplace tab, find the rds_ai extension and click Install.

  4. In the dialog box, select the target database and account, and then click Install.

  (Optional) On the Extension Management page, you can uninstall extensions from the Installed Extensions tab.

• Manage extensions with SQL statements

  Create an extension

  CREATE EXTENSION IF NOT EXISTS rds_ai CASCADE;

  Note

  • Only a privileged account can execute this statement. To create a privileged account, see Create an account.

  • Creating the rds_ai extension also creates the pgvector and pgsql-http extensions.

  • To view all installed extensions, execute the SELECT * FROM pg_extension; statement.

  Drop an extension

  DROP EXTENSION rds_ai;

Default models

The rds_ai extension supports the default models listed below. You can also create custom models.

The default models in the rds_ai extension provide the following capabilities:

• LLM-based Q&A

• Text-to-vector conversion

• Text ranking

• Top-N similar vector retrieval

• RAG-based Q&A

Basic settings

1. To call a large language model using rds_ai, you must first configure the API key.

   -- Set the API key for the target model.
   SELECT rds_ai.update_model('qwen-plus', 'token', 'sk-****'); 
   
   -- Set the API key for all models in rds_ai.model_list.
   SELECT rds_ai.update_model(model_name,'token','sk-****') FROM rds_ai.model_list;

2. The rds_ai extension uses the pgsql-http extension to remotely call models. Configure the following timeout settings to interrupt long-running requests.

   Note

   These timeout settings are session-level and must be reconfigured for each new connection.

   -- Set the request timeout period in milliseconds.
   SET http.timeout_msec TO 200000;
   
   SELECT http.http_set_curlopt('CURLOPT_TIMEOUT', '200000');
   
   -- Set the connection timeout period.
   SELECT http.http_set_curlopt('CURLOPT_CONNECTTIMEOUT_MS', '200000');

3. (Optional) If you use PrivateLink to connect the VPC of your RDS for PostgreSQL instance to Alibaba Cloud Model Studio, run the following statement to update the URL of the target model.

   SELECT rds_ai.update_model('qwen-plus', 'uri', '<URL_after_connecting_via_PrivateLink>'); 

   After you connect the VPC of your RDS for PostgreSQL instance to Alibaba Cloud Model Studio over PrivateLink, see Access model or application APIs of Alibaba Cloud Model Studio over a private network for the model URLs. For example, if your VPC is in the China (Beijing) region, replace the public domain name dashscope.aliyuncs.com in https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation with your custom service domain name, such as vpc-cn-beijing.dashscope.aliyuncs.com.

   The following SQL statement provides an example of how to update the URLs for multiple models:

   SELECT rds_ai.update_model(
       model_name,
       'uri',
       'https://vpc-cn-beijing.dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation'
   ) 
   FROM rds_ai.model_list 
   WHERE model_name IN ('qwen-max', 'qwen-turbo', 'qwen-plus');

LLM-based Q&A

By default, the rds_ai extension uses the Qwen text generation model for LLM-based Q&A.

1. (Optional) Set the default model for LLM-based Q&A. If you do not set this parameter, the system uses the qwen-plus model by default.

   Note

   To change a default model, you must add rds_ai to the Running Value of the shared_preload_libraries instance parameter. For more information about how to configure parameters, see Set instance parameters. For example, change the Running Value to 'pg_stat_statements,auto_explain,rds_ai'.

   SET rds_ai.default_prompt_model TO 'qwen-max';

2. Use rds_ai.prompt to call the default large language model for Q&A. For example:

   SELECT rds_ai.prompt('Give me a recipe for a dish made with carrots, potatoes, and eggplants.');

   Full API call:

   SELECT rds_ai.prompt(
     model_name=>'qwen-plus', -- The model name.
     content=>'Give me a recipe for a dish made with carrots, potatoes, and eggplants.',  -- The question.
     args=>'{"top_p": 0.7}'::jsonb -- The parameters for calling the large language model.
   );

Text-to-vector conversion

By default, the rds_ai extension uses the text-embedding-v3 model for text-to-vector conversion.

• By default, the output is a dense vector.

  SELECT rds_ai.embed(
      'On the wind-swept plain, gibbons cry; the islet is clear, the sand is white, birds wheel back. Leaves fall without end; the Yangtze rolls on, never ceasing.' -- The text to convert to a vector.
  );

• Specify the output as a sparse vector.

  -- Convert text to a sparse vector, which has 250002 dimensions by default.
  SELECT rds_ai.embed(
      content=>'On the wind-swept plain, gibbons cry; the islet is clear, the sand is white, birds wheel back. Leaves fall without end; the Yangtze rolls on, never ceasing.',
      args=>'{"output_type": "sparse"}'::jsonb -- The parameter list.
  );

Store the resulting vectors in a destination table.

1. Create the test_embed table.

   CREATE TABLE test_embed(a text, b vector(1024), c sparsevec(250002));

2. Insert the text that you want to convert.

   INSERT INTO test_embed (a) values ('hello world');

3. Use rds_ai.embed to call the text-embedding-v3 model and write the results to the test_embed table.

   • Write a dense vector.

     UPDATE test_embed 
     SET b = rds_ai.embed(a, '{"output_type": "dense"}'::jsonb)::vector;

   • Write a sparse vector.

     UPDATE test_embed 
     SET c = rds_ai.embed(a, '{"output_type": "sparse"}'::jsonb)::sparsevec;

-- Call the default text ranking model gte-rerank.
SELECT * FROM rds_ai.rank
(
  'What is a text ranking model?', -- The query.
  ARRAY[
      'Text ranking models are widely used in search engines and recommendation systems. They rank candidate texts based on relevance.', 
      'Quantum computing is a cutting-edge area of computer science.', 
      'The development of pretrained language models has brought new advances to text ranking models.'
  ]
);

SELECT * FROM rds_ai.rank
(
  query=>'who are you',
  document=>ARRAY['a','b','c'],
  args=>'{"top_n":2}'::jsonb
);

Top-N similar vector retrieval

By default, the rds_ai extension uses the text-embedding-v3 model for similar vector retrieval. For example:

1. Create a test table.

   -- Create a test table named test_rag.
   CREATE TABLE test_rag (
       id SERIAL PRIMARY KEY,
       chunk TEXT
   );
   
   -- Add an embedding column to store the vector converted from the chunk.
   ALTER TABLE test_rag ADD COLUMN embedding VECTOR(1024);
   
   -- Populate the embedding column.
   UPDATE test_rag SET embedding=rds_ai.embed(chunk)::vector;

2. Create a vector index.

   Note

   When you create a vector index, you must use the vector_cosine_ops operator class.

   -- Create an HNSW index.
   CREATE INDEX ON test_rag USING hnsw (embedding vector_cosine_ops);
   -- Create an IVFFlat index.
   CREATE INDEX ON test_rag USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

3. Use rds_ai.retrieve, which calls the text-embedding-v3 model, to perform vector retrieval in the test table.

   SELECT * FROM rds_ai.retrieve
   ('Why is PostgreSQL considered the most advanced open source database',
   'public', 'test_rag', 'chunk', 'embedding');

   Full API call:

   SELECT * FROM rds_ai.retrieve
   (
     embed_model=>'text-embedding-v3', -- The embedding model to use.
     question=>'Why is PostgreSQL considered the most advanced open source database',
     source_schema=>'public', -- The schema of the table for the similarity search.
     source_table=>'test_rag', -- The table for the similarity search.
     chunk_col=>'chunk', -- The column that contains text chunks.
     vector_col=>'embedding', -- The column that contains vectors.
     -- The following are optional parameters.
     topn=>10, -- The number of top results to return.
     embed_args=>'{}'::jsonb, -- The parameters for the embedding model.
     distance_type=>'cosine' -- The distance algorithm. Valid values: L1, L2, cosine, and inner product.
   );

RAG-based Q&A

By default, the rds_ai extension uses the text-embedding-v3 model and the Qwen text generation model for RAG-based Q&A.

-- Embedding model: text-embedding-v3. Text generation model: specified by the rds_ai.default_prompt_model parameter.
SELECT * FROM rds_ai.rag
('Why is PostgreSQL considered the most advanced open source database', 
'public', 'test_rag', 'chunk', 'embedding');

Full API call:

SELECT * FROM rds_ai.rag
(
  embed_model=>'text-embedding-v3', -- The embedding model to use.
  prompt_model=>'qwen-plus', -- The prompt model to use.
  question=>'Why is PostgreSQL considered the most advanced open source database',
  source_schema=>'public', -- The schema of the table for the similarity search.
  source_table=>'test_rag', -- The table for the similarity search.
  chunk_col=>'chunk', -- The column that contains text chunks.
  vector_col=>'embedding', -- The column that contains vectors.
  -- The following are optional parameters.
  topn=>10,
  embed_args=>'{}'::jsonb,  -- The parameters for the embedding model.
  prompt_args=>'{}'::jsonb, -- The parameters for the prompt model.
  distance_type=>'L2' -- The distance algorithm. Valid values: L1, L2, cosine, and inner product.
);

Custom models

You can add a custom model using the rds_ai.model_list metadata table. The following table describes the fields in rds_ai.model_list.

Add a custom model

SELECT rds_ai.add_model(
    'test-model',                   -- Model name
    'POST',                         -- HTTP request method
    ARRAY[('Authorization', 'Bearer %s')]::http.http_header[], -- Request header
    'https://****.com',           -- Request URL
    'application/json',            -- Content type
    '{"key":"%s"}',               -- Request body template
    'SELECT %L'          -- SQL statement to parse the response
);

Use a custom model

• Use the rds_ai.raw_invoke_model function to send a request based on the model's configuration. The function populates the request template with an ARRAY and returns the complete HTTP response.

  SELECT * FROM
  rds_ai.raw_invoke_model('qwen-plus', ARRAY['who are you']);

• Use the rds_ai.invoke_model function to send a request based on the model's configuration and extract specific fields from the response using the json_path setting.

  SELECT * FROM
  rds_ai.invoke_model('qwen-plus', ARRAY['who are you']);

Delete a custom model

SELECT rds_ai.del_model('test-model');

SELECT rds_ai.invoke_model(
    'deepseek', 
    ARRAY[
        'deepseek-r1', 
        'Introduce yourself'
    ]
);

Add a PAI-RAG model

We recommend deploying PAI-EAS and your RDS for PostgreSQL instance in the same VPC. When you configure the model in the rds_ai extension, use the VPC endpoint of the PAI service. This keeps all communication within your VPC for enhanced security.

1. Deploy a RAG service in PAI-EAS. For more information, see Deploy an LLM-based RAG chatbot by using PAI-EAS and RDS for PostgreSQL.

2. Obtain the invocation information for the RAG service.

   1. Click the name of the RAG service to open the Service Details page.

   2. In the Basic Information section, click View Invocation Information.

   3. In the Invocation Information dialog box, obtain the service endpoint and token.

3. Add the PAI-RAG model.

   SELECT rds_ai.add_model(
       'pai-rag',      -- Model name
       'POST',         -- Request method
       ARRAY[('Authorization','%s')]::http.http_header[],  -- Request header,
       'http://rds-pai-rag-demo.****.cn-hangzhou.pai-eas.aliyuncs.com/service/query',  -- Request URL
       'application/json',  -- Content type
       '{
           "question": "%s"
       }',  -- Request body
       'SELECT (%L::jsonb->''answer'')::text'  -- Parse path
   );

   Note

   • Replace the model URL with your actual service endpoint and append /service/query.

   • For more information about invoking PAI-RAG models, see Call PAI models by using an API.

4. Configure the token for the PAI-RAG model.

   SELECT rds_ai.update_model('pai-rag', 'token','MTFkYjMwZjgzYzA1YmE2N2YyNWMxM2NkNDVjMjEzNjYxMDAzMzE5****');

Verify the new PAI-RAG model. For example:

SELECT rds_ai.invoke_model('pai-rag', ARRAY['Which parameters are related to WAL log accumulation?']);

Add a Function Compute RAG model

1. Deploy an AgentCraft application in Function Compute. For more information, see Deploy AgentCraft in the cloud.

2. Create an agent and configure its client access.

   For example, as described in Connect an agent to a DingTalk chatbot, obtain and save the service endpoint and token when you configure client access.

3. Add the Function Compute RAG model.

   SELECT rds_ai.add_model(
       'fc-rag',      -- Model name
       'POST',         -- Request method
       ARRAY[('Authorization','Bearer %s')]::http.http_header[],  -- Request header,
       'https://agentcrckend-de-obnhjsknam.cn-hangzhou.fcapp.run/v1/chat/completions',  -- Request URL
       'application/json',  -- Content type
       '{ 
           "messages":[ { "role": "user", "content": "%s" } ], 
           "stream": false, 
           "max_tokens": 1024 
        }', 
       'SELECT (%L::jsonb->''choices''->0->''message''->>''content'')::text'  -- Parse path
   );

   Note

   Replace the model URL with the actual URL and append /completions.

4. Configure the token for the Function Compute RAG model.

   SELECT rds_ai.update_model('fc-rag', 'token','8UiGAziWgYGPxM3qR5sAChBfDJRt****');

Verify the new Function Compute RAG model. For example:

SELECT rds_ai.invoke_model('fc-rag', ARRAY['Which parameters are related to WAL log accumulation?']);

SELECT rds_ai.invoke_model(
    'text-embedding-v2-self',        -- Custom model name
    ARRAY['The wind is swift, the sky is high, and the apes wail','{"text_type":"query"}']         -- The array elements sequentially replace the %s placeholders. The token is automatically added to the header and does not need to be specified.
);

Functions

rds-ai.raw_invoke_model(
    model_name TEXT, 
    params_list TEXT[]
) 
RETURNS http.http_response

rds-ai.invoke_model(
    model_name TEXT, 
    params_list TEXT[]
) 
RETURNS http.http_response

rds_ai.embed(
    model_name TEXT, 
    content TEXT, 
    args jsonb DEFAULT '{}' 
) RETURNS text

rds_ai.prompt(
    model_name TEXT, 
    content TEXT, 
    args jsonb DEFAULT '{}'
) 
RETURNS text

rds_ai.rank(
    query TEXT, 
    document TEXT[], 
    args jsonb DEFAULT '{}' 
) 
RETURNS TABLE(score_value float8, text_value TEXT);

rds_ai.retrieve(
    model_name TEXT, 
    question TEXT, 
    source_schema TEXT, 
    source_table TEXT, 
    chunk_col TEXT, 
    vector_col TEXT, 
    topn INT DEFAULT 10, 
    embed_args jsonb DEFAULT '{}', 
    distance_type TEXT DEFAULT 'L2'
) 
RETURNS TABLE(chunk TEXT, distance float);

rds_ai.rag(
    embed_model TEXT,
    prompt_model TEXT,
    question TEXT,
    source_schema TEXT,
    source_table TEXT,
    chunk_col TEXT,
    vector_col TEXT,
    topn INT DEFAULT 10,
    embed_args jsonb DEFAULT '{}',
    prompt_args jsonb DEFAULT '{}',
    distance_type TEXT DEFAULT 'L2'
) 
returns text

rds_ai.show_models() 
RETURNS setof rds_ai.model_list

rds_ai.del_model (model_name text) 
RETURNS void

rds_ai.add_model(
    model_name TEXT,
    request_type TEXT,
    request_header http.http_header[],
    uri TEXT,
    content_type TEXT,
    content_template TEXT,
    json_path TEXT
) 
RETURNS TEXT

rds_ai.update_model(
    model_name TEXT,
    config_name TEXT, 
    value TEXT
) 
RETURNS void