Use the rds_ccl extension for SQL throttling on PostgreSQL - ApsaraDB RDS

The rds_ccl extension lets you cap the concurrency of specific SQL statements on ApsaraDB RDS for PostgreSQL. When a burst of identical queries—or a single resource-heavy statement—threatens database stability, throttling rules queue excess executions instead of letting them pile up or crash the instance. Common use cases include controlling backup and monitoring queries during peak hours and blocking runaway queries caused by application bugs or malicious traffic.

Prerequisites

Before you begin, make sure your instance meets the following requirements:

Engine version: To upgrade your minor engine version, see Update the minor engine version.
Major engine version Minimum minor engine version
PostgreSQL 16 and 17 All minor versions
PostgreSQL 14 and 15 20230330
PostgreSQL 13 20250430
Parameters configured (see Modify parameters):
- rds_enable_ccl = on
- compute_query_id = auto or on
A privileged account on the instance.

Major engine version	Minimum minor engine version
PostgreSQL 16 and 17	All minor versions
PostgreSQL 14 and 15	20230330
PostgreSQL 13	20250430

Billing

This feature is free of charge.

Limitations

Rules are not persisted in memory across restarts. After an instance restart or a primary/secondary failover, reload your throttling rules manually.
A misconfigured rule—such as setting max_concurrency too low—can block legitimate traffic. Understand your workload patterns before creating rules.
Throttling rules are created on the primary instance. To throttle a read-only instance, set the rule's node_tag to include read-only nodes, then load the rule on the read-only instance separately.
When DAS SQL Throttling creates a rule, it installs the plugin in information_schema. That rule cannot be invoked through SQL commands and only works through the DAS console.

Install and uninstall the extension

Install using the console

Go to the Instances page. In the top navigation bar, select a region, then click the target instance ID.
In the left navigation pane, click Plug-ins.
On the Extension Marketplace page, find the rds_ccl plugin and click Install. Alternatively, go to Extension Management, search for rds_ccl, and click Install in the Actions column.
In the dialog box, select the target database and a privileged account, then click Install.

The instance status changes from Maintaining Instance to Running when installation is complete.

To uninstall, go to Extension Management > Installed Extensions, find the plugin, and click Uninstall in the Actions column.

Install using SQL

Run the following commands as a privileged account.

-- Install the extension
CREATE EXTENSION rds_ccl;

-- Uninstall the extension
DROP EXTENSION rds_ccl;

How it works

rds_ccl throttles SQL by matching incoming statements against stored rules. Matching is based on a query_id—a special identifier computed from the normalized form of an SQL statement. Statements that differ only in literal values (such as WHERE id = 1 vs. WHERE id = 2) share the same query_id and are controlled by the same rule.

Key properties of query_id:

The query_id is derived from the object identifiers (oid) of tables and functions referenced in the query. Tables with the same name in different databases or schemas are different objects, so identical SQL can have different query_id values when run in different contexts.
Global objects—such as the pg_database system catalog or the pg_sleep function—produce the same query_id regardless of which database executes the query.
Adding or removing a FROM clause, or changing a parameter type, produces a different query_id.

To get the query_id for a specific SQL statement, use rds_get_query_id:

SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);

Example output:

   rds_get_query_id
----------------------
 -8733244708994363681
(1 row)

Manage throttling rules

Important

Run all commands below as a privileged account.

All throttling rule functions are available only on the primary instance, except rds_load_ccl_rule, rds_unload_ccl_rule, rds_enabled_ccl_rule (view), and rds_show_current_db_ccl_rule(), which can also run on read-only instances.

Create a throttling rule

Two syntaxes are available: match by SQL text, or match by query_id.

Syntax 1 — match by SQL text:

SELECT rds_add_ccl_rule(
    query_string    varchar,
    node_tag        int,
    max_concurrency int,
    max_waiting     int,
    is_enabled      boolean,
    comment         varchar,    -- Optional
    search_path     varchar     -- Optional
);

Syntax 2 — match by query_id:

SELECT rds_add_ccl_rule_with_query_id(
    query_id        bigint,
    node_tag        int,
    max_concurrency int,
    max_waiting     int,
    is_enabled      boolean,
    query_string    varchar,    -- Optional
    comment         varchar,    -- Optional
    search_path     varchar     -- Optional (version 1.2 and later)
);

Both functions return a ccl_id—the unique identifier for the rule in the current database.

Parameters:

Parameter	Type	Description
`query_string`	varchar	The SQL statement to throttle. Wrap the statement in `$$` or $<tag>$ . See Dollar-Quoted String Constants. If the statement already contains `$$`, use a custom tag such as `$ccl$...$ccl$`. In Syntax 1, this parameter is required. In Syntax 2, it is optional and used only for display; throttling is always based on `query_id`.
`query_id`	bigint	The `query_id` of the SQL statement to throttle. Use `rds_get_query_id()` to look it up.
`node_tag`	int	The node scope for the rule: `1` = primary instance only, `2` = read-only instances only, `3` = both. This defines where the rule can be loaded, not where it is automatically active. To activate it on a read-only instance, load it explicitly.
`max_concurrency`	int	Maximum number of concurrent executions allowed for matching statements. Valid values: 0–100000.
`max_waiting`	int	Maximum number of statements that can queue when the concurrency limit is reached. If a new statement would exceed this limit, the PostgreSQL kernel triggers an ABORT to stop and roll back the transaction. Valid values: 0–100000.
`is_enabled`	boolean	`true` activates the rule immediately; `false` creates it in a disabled state.
`comment`	varchar	Optional description of the rule. Set to `''` if unused. Setting to `NULL` causes the function to return immediately without creating a rule.
`search_path`	varchar	The schema search_path in effect when the target SQL runs. Set to `''` to use the default search path. Run `SHOW search_path;` to check the current default. Setting to `NULL` causes the function to return immediately without creating a rule.

Example:

The ccl_test database has a table named ccl_tbl. Create a rule that limits SELECT * FROM ccl_tbl to 3 concurrent executions and 2 queued statements, active immediately.

Using Syntax 1:

SELECT rds_add_ccl_rule(
    $$SELECT * FROM ccl_tbl;$$,   -- SQL text to throttle, wrapped in $$
    1,                             -- Primary instance only
    3,                             -- Max 3 concurrent executions
    2,                             -- Max 2 queued statements
    true,                          -- Activate immediately
    'limit constant select',       -- Rule description
    ''                             -- Default search_path
);

Using Syntax 2 (assuming the query_id is 1):

SELECT rds_add_ccl_rule_with_query_id(
    1,                             -- query_id of the SQL to throttle
    1,                             -- Primary instance only
    3,                             -- Max 3 concurrent executions
    2,                             -- Max 2 queued statements
    true,                          -- Activate immediately
    '',                            -- SQL text (not specified here)
    'limit constant select',       -- Rule description
    'public'                       -- search_path
);

Query throttling rules

Run either command on a primary instance or a read-only instance.

To view all throttling rules across all databases on the current instance:

SELECT * FROM rds_enabled_ccl_rule;

The query_string and comment columns are truncated to 200 characters in this view. To see the full values, use rds_show_current_db_ccl_rule().

To view all throttling rules in the current database:

SELECT * FROM rds_show_current_db_ccl_rule();

rds_ccl version 1.2 and later includes the search_path column in the output.

Enable a throttling rule

Call rds_enable_ccl_rule on the primary instance to activate a rule that was created with is_enabled = false or was previously disabled.

SELECT rds_enable_ccl_rule(ccl_id int);

Example:

SELECT rds_enable_ccl_rule(1);

Load a throttling rule

A rule must be loaded into memory before it actively throttles SQL. Call this function on the primary instance or a read-only instance.

SELECT rds_load_ccl_rule(ccl_id int);

When to call this function:

Scenario	Action required
Rule created with `is_enabled = true` (primary instance)	Loaded automatically—no manual call needed
Rule enabled via `rds_enable_ccl_rule` (primary instance)	Loaded automatically—no manual call needed
Primary instance restarted	Call `rds_load_ccl_rule` manually
Applying a rule to a read-only instance	Call `rds_load_ccl_rule` on the read-only instance after creating the rule on primary with `node_tag` set to `2` or `3`

Disabled rules cannot be loaded. To load a disabled rule on a read-only instance, first enable it on the primary instance using rds_enable_ccl_rule, then call rds_load_ccl_rule on the read-only instance.

Example:

SELECT rds_load_ccl_rule(1);

Modify a throttling rule

Call rds_update_ccl_rule on the primary instance to change a rule's concurrency limit or queue size. Updates take effect immediately.

SELECT rds_update_ccl_rule(
    ccl_id              int,
    new_max_concurrency int,
    new_max_waiting     int
);

Parameters:

Parameter	Type	Description
`ccl_id`	int	The ID of the rule to modify.
`new_max_concurrency`	int	The new concurrency limit. Increasing it lets queued statements run immediately. Decreasing it does not interrupt running statements, but new statements wait until active concurrency drops below the new limit.
`new_max_waiting`	int	The new queue size. Increasing it allows more statements to queue. Decreasing it does not evict currently queued statements, but any new statement that would exceed the new limit causes the kernel to trigger an ABORT.

Only max_concurrency and max_waiting can be changed. To change the target SQL or node_tag, delete the rule and create a new one.

Example:

SELECT rds_update_ccl_rule(
    2,   -- ccl_id
    4,   -- New max_concurrency
    5    -- New max_waiting
);

Disable a throttling rule

Call rds_disable_ccl_rule on the primary instance to stop a specific rule. The rule is automatically unloaded from the primary instance and no longer throttles SQL. To re-enable it later, see Enable a throttling rule.

-- Disable a specific rule
SELECT rds_disable_ccl_rule(ccl_id int);

-- Disable all rules in the current database
SELECT rds_disable_all();

Disabled rules cannot be loaded on read-only instances. Enable them on the primary instance first.

Example:

SELECT rds_disable_ccl_rule(1);

Unload a throttling rule

Unloading removes a rule from memory without deleting it. The rule still exists and can be reloaded later.

SELECT rds_unload_ccl_rule(ccl_id int, db_name varchar default '');

Parameters:

Parameter	Type	Description
`ccl_id`	int	The ID of the rule to unload.
`db_name`	varchar	The database to unload the rule from. Defaults to `''`, which means the current database. Specify another database name to target a different database.

When to call this function:

On the primary instance: Disabling or deleting a rule unloads it automatically. Call rds_unload_ccl_rule only when you want to temporarily suspend a rule without disabling it.
On a read-only instance: Call this to stop throttling on that instance without affecting the primary or other read-only instances.

To reload a rule after unloading it, see Load a throttling rule.

Example:

SELECT rds_unload_ccl_rule(1, '');

Delete a throttling rule

Call rds_del_ccl_rule on the primary instance to permanently delete a rule. The rule is automatically unloaded from the primary instance.

SELECT rds_del_ccl_rule(ccl_id int);

Example:

SELECT rds_del_ccl_rule(1);

Example output:

   rds_del_ccl_rule
----------------------
 -7851264404688445170
(1 row)

The return value is the query_id of the deleted rule. An error is returned if the rule does not exist.

Appendix: query_id reference examples

The following examples show how query_id varies with query structure and context.

Global table (`pg_database`) — same query_id across databases:

-- Run in ccl_test
SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);
-- Result: -8733244708994363681

-- Run in ccl_test2
SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);
-- Result: -8733244708994363681

Global function (`pg_sleep`) — same query_id across databases:

-- Run in ccl_test
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
-- Result: 440101247839410938

-- Run in ccl_test2
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
-- Result: 440101247839410938

`FROM` clause changes the `query_id`:

-- Without FROM
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
-- Result: 440101247839410938

-- With FROM
SELECT rds_get_query_id($$SELECT * FROM pg_sleep(1);$$);
-- Result: -3404018605099167039

Parameter type changes the `query_id`:

-- Integer parameter
select rds_get_query_id($$SELECT * FROM pg_sleep(1);$$);
-- Result: -3404018605099167039

-- Numeric parameter
select rds_get_query_id($$SELECT * FROM pg_sleep(1.0);$$);
-- Result: 3073869725037049158