ApsaraDB RDS:AI (rds_ai)

Prerequisites

Before you begin, make sure you have:

• An ApsaraDB RDS for PostgreSQL instance that meets the following version requirements: To upgrade, see Upgrade a minor engine version.

  Major version	Minimum minor engine version
  PostgreSQL 16	20241230
  PostgreSQL 14, 15, 17	20250430
  PostgreSQL 18	20251130

• A privileged account for your instance. To create one, see Create an account.

• An active Alibaba Cloud Model Studio subscription with an API key. To get one, see Obtain an API key.

Network configuration

By default, an RDS for PostgreSQL instance cannot access external networks. To enable access to external models, you must configure a NAT gateway for the instance's VPC. For more information about NAT gateways, see Use the SNAT feature of an Internet NAT gateway to access the Internet.

Install the extension

Option 1: Install from the console

1. Go to the RDS Instances page, select a region, and click the target instance ID.

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

3. On the Plugin Marketplace page, click Install next to rds_ai.

4. Select the target database and account, then click Install.

To uninstall, go to the Installed Plugins tab on the Manage Plugins page.

Option 2: Install with SQL

Run the following command as a privileged account:

CREATE EXTENSION IF NOT EXISTS rds_ai CASCADE;

Installing rds_ai also installs the pgvector and pgsql-http extensions.

To verify the installation:

SELECT * FROM pg_extension;

To remove the extension:

DROP EXTENSION rds_ai;

Default models

rds_ai ships with the following default models:

To change the default prompt model, add rds_ai to the Running Value of the shared_preload_libraries parameter, then set the model. For instructions on updating parameters, see Set instance parameters. For example, set Running Value to 'pg_stat_statements,auto_explain,rds_ai', then run:

SET rds_ai.default_prompt_model TO 'qwen-max';

Configure the extension

Before calling any model, set the API key and model URLs.

Step 1: Set the API key

-- Set the API key for a specific model
SELECT rds_ai.update_model('qwen-plus', 'token', 'sk-****');

-- Set the same API key for all models
SELECT rds_ai.update_model(model_name, 'token', 'sk-****')
FROM rds_ai.model_list;

Step 2: Set the model URLs

-- Prompt models (qwen-plus, qwen-max, qwen-turbo share the same endpoint)
SELECT rds_ai.update_model('qwen-plus', 'uri', 'https://dashscope-intl.aliyuncs.com/api/v1/services/aigc/text-generation/generation');
SELECT rds_ai.update_model('qwen-max', 'uri', 'https://dashscope-intl.aliyuncs.com/api/v1/services/aigc/text-generation/generation');
SELECT rds_ai.update_model('qwen-turbo', 'uri', 'https://dashscope-intl.aliyuncs.com/api/v1/services/aigc/text-generation/generation');

-- Embedding model
SELECT rds_ai.update_model('text-embedding-v3', 'uri', 'https://dashscope-intl.aliyuncs.com/api/v1/services/embeddings/text-embedding/text-embedding');

Step 3: (Optional) Configure request timeouts

rds_ai uses the pgsql-http extension to make remote calls. Set timeouts to interrupt long-running requests.

These settings apply to the current session only. Reset them for each new connection.

-- Request timeout (milliseconds)
SET http.timeout_msec TO 200000;
SELECT http.http_set_curlopt('CURLOPT_TIMEOUT', '200000');

-- Connection timeout (milliseconds)
SELECT http.http_set_curlopt('CURLOPT_CONNECTTIMEOUT_MS', '200000');

LLM Q&A

Use rds_ai.prompt to send a question to a Qwen text generation model and get a response. The default model is qwen-plus.

Simple call:

SELECT rds_ai.prompt('Give me a recipe using carrots, potatoes, and eggplants.');

Full call with explicit parameters:

SELECT rds_ai.prompt(
  model_name => 'qwen-plus',                                 -- model name
  content    => 'Give me a recipe using carrots, potatoes, and eggplants.',  -- prompt text
  args       => '{"top_p": 0.7}'::jsonb                      -- model parameters
);

Text-to-vector conversion

Use rds_ai.embed to convert text to a vector using the text-embedding-v3 model. The output is a 1024-dimensional dense vector by default. To request a sparse vector, pass {"output_type": "sparse"} — the output is 250,002 dimensions.

Dense vector (default):

SELECT rds_ai.embed(
  'A strong wind blows from a high sky, and monkeys cry sadly.'
);

Sparse vector:

SELECT rds_ai.embed(
  content => 'A strong wind blows from a high sky, and monkeys cry sadly.',
  args    => '{"output_type": "sparse"}'::jsonb
);

Store embeddings in a table:

1. Create the table:

   CREATE TABLE test_embed (
     a text,
     b vector(1024),         -- dense vector column
     c sparsevec(250002)     -- sparse vector column
   );

2. Insert text:

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

3. Write the embeddings:

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

Semantic vector retrieval

Use rds_ai.retrieve to find the top-N most similar records to a query. It embeds the query using text-embedding-v3 and compares against stored vectors using the distance type you specify.

Choose a distance type

The default distance type for rds_ai.retrieve is cosine.

Choose a vector index type

Without an index, PostgreSQL performs an exact sequential scan — accurate but slow for large datasets. Add a vector index for faster approximate nearest neighbor (ANN) search.

Example: Set up a table and run retrieval

1. Create a table and add an embedding column:

   CREATE TABLE test_rag (
     id    SERIAL PRIMARY KEY,
     chunk TEXT
   );
   
   ALTER TABLE test_rag ADD COLUMN embedding VECTOR(1024);
   UPDATE test_rag SET embedding = rds_ai.embed(chunk)::vector;

2. Create a vector index:

   Both index types require the vector_cosine_ops operator class.

   -- HNSW index (recommended for most workloads)
   CREATE INDEX ON test_rag USING hnsw (embedding vector_cosine_ops);
   
   -- IVFFlat index (for memory-constrained environments)
   CREATE INDEX ON test_rag USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

3. Run a similarity search:

   -- Simple call (uses defaults: text-embedding-v3, top 10, cosine distance)
   SELECT * FROM rds_ai.retrieve(
     'Why is PostgreSQL considered the most advanced open-source database?',
     'public', 'test_rag', 'chunk', 'embedding'
   );

   Full call with explicit parameters:

   SELECT * FROM rds_ai.retrieve(
     embed_model   => 'text-embedding-v3',
     question      => 'Why is PostgreSQL considered the most advanced open-source database?',
     source_schema => 'public',         -- schema of the source table
     source_table  => 'test_rag',       -- table to search
     chunk_col     => 'chunk',          -- text column
     vector_col    => 'embedding',      -- vector column
     topn          => 10,               -- number of results to return
     embed_args    => '{}'::jsonb,      -- extra parameters for the embedding model
     distance_type => 'cosine'          -- distance algorithm: L1, L2, cosine, or negative
   );

RAG Q&A

rds_ai.rag combines vector retrieval and LLM generation in a single call. It retrieves the top-N relevant chunks from your table and passes them as context to the prompt model.

The default models are text-embedding-v3 for embedding and qwen-plus for text generation.

Simple call:

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

Full call with explicit parameters:

SELECT * FROM rds_ai.rag(
  embed_model   => 'text-embedding-v3',  -- embedding model
  prompt_model  => 'qwen-plus',          -- text generation model
  question      => 'Why is PostgreSQL considered the most advanced open-source database?',
  source_schema => 'public',             -- schema of the source table
  source_table  => 'test_rag',           -- table to search
  chunk_col     => 'chunk',              -- text column
  vector_col    => 'embedding',          -- vector column
  topn          => 10,                   -- number of chunks to retrieve
  embed_args    => '{}'::jsonb,          -- extra parameters for the embedding model
  prompt_args   => '{}'::jsonb,          -- extra parameters for the prompt model
  distance_type => 'L2'                  -- distance algorithm: L1, L2, cosine, or inner product
);

Custom models

rds_ai stores model configurations in the rds_ai.model_list table. Add any HTTP-compatible model by inserting a row into this table.

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

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

-- Returns the parsed result from json_path
SELECT * FROM rds_ai.invoke_model('qwen-plus', ARRAY['who are you']);

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

API reference

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

rds_ai.embed(
  model_name TEXT,
  content    TEXT,
  args       jsonb DEFAULT '{}'
) RETURNS 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 'cosine'
) 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.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

rds_ai.del_model(model_name text) RETURNS void

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

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

What's next

• pgvector User Guide — learn more about vector storage and search in PostgreSQL

• Deploy an LLM-based RAG chatbot using PAI-EAS and ApsaraDB RDS for PostgreSQL — end-to-end RAG chatbot deployment guide