Implement throttling on specific SQL statements - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL provides the SQL throttling feature. You can use the feature to limit the maximum number of concurrent SQL queries on an ApsaraDB RDS for PostgreSQL instance and resolve the issues that are caused by resource-intensive SQL statements. This feature ensures that your database system runs stably and helps meet your business requirements.

Background information

SQL throttling is an important technique to manage databases. SQL throttling can be used to limit the maximum number of concurrent SQL statements to prevent excessive database loads, ensure database stability and reliability, and improve database performance and efficiency.

Scenarios

SQL throttling is suitable for high-concurrency access scenarios and the following scenarios:

Attack prevention
You can use the SQL throttling feature to limit the maximum number of concurrent SQL statements by type to prevent attacks including denial-of-service (DoS) attacks. This helps ensure database security.
Resource usage control
You can use the SQL throttling feature to limit the maximum number of concurrent SQL statements by type to control the usage of database resources. This prevents database failures or performance degradation that is caused by resource exhaustion.
For example, when you execute SQL statements to back up, restore, or monitor databases, you can use the SQL throttling feature to manage the execution speed of SQL statements and prevent negative impacts on database performance.

Prerequisites

The RDS instance must meet the following requirements:
- The RDS instance runs PostgreSQL 14 or later.
- The minor engine version of the RDS instance is 20230330 or later.
Note
For more information about how to update the minor engine version of an RDS instance, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
Before you use the rds_ccl extension, make sure that the following parameter settings are used:
- rds_enable_ccl: on
- compute_query_id: auto or on
Note
You can configure instance parameters in the ApsaraDB RDS console. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

Limits

None

Impacts

If SQL throttling is not properly configured, your workloads may be affected. We recommend that you configure SQL throttling based on your workloads.

For example, the RDS instance is heavily loaded due to a spike in access requests. You can use the SQL throttling feature to limit the maximum number of concurrent SQL statements by type to reduce the load on the RDS instance. However, if you set the number of concurrent SQL statements to a small value, your workloads may also be affected.

Usage notes

SQL throttling rules must be manually loaded in scenarios such as instance restart and primary/secondary switchovers. For more information, see Load an SQL throttling rule.

Billing

The extension is free of charge.

Create or delete the extension

You must use the privileged account to execute the statements in this section.

Create the extension.
```
CREATE EXTENSION rds_ccl;
```
Delete the extension.
```
DROP EXTENSION rds_ccl;
```

Examples

You must use the privileged account to execute the statements in this section.

Create an SQL throttling rule

Scenarios

The function in the following syntax can be called only on the primary RDS instance to create an SQL throttling rule.

Syntax

Syntax 1: Perform throttling on the specified SQL statement.

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: Perform throttling based on the query_id parameter.

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

Parameters

Parameter	Type	Description
query_string	varchar	The SQL statement that you want to throttle. You must use `$$` or $<Any character>$ to wrap the SQL statement that you want to throttle. For more information, see Dollar-Quoted String Constants. Examples: `-- Use $$ to wrap the SQL statement. $$SELECT * FROM my_table;$$ -- If $$ is included in the SQL statement, use $ccl$ to wrap the SQL statement. $ccl$SELECT * FROM my_table WHERE my_column = $$hello$$;$ccl$` This function obtains the value of query_id based on query_string and then throttles the same type of SQL statement based on query_id. For more information about query_id, see Introduction to query_id.
query_id	bigint	The query ID of the SQL statement that you want to throttle. For more information, see Introduction to query_id.
node_tag	int	The instance on which you want to perform SQL throttling. 1: SQL throttling is performed only on the primary RDS instance. 2: SQL throttling is performed only on read-only RDS instances. 3: SQL throttling is performed on both the primary RDS instance and read-only RDS instances. Note This parameter specifies the application scope of the SQL throttling rule. The rule is not automatically enabled on read-only RDS instances. If you want to enable the rule on read-only RDS instances, you must manually load the rule. For more information, see Load an SQL throttling rule.
max_concurrency	int	The maximum number of concurrent SQL statements by type. Valid values: 0 to 100000.
max_waiting	int	The maximum number of SQL statements that can stay in the waiting state. If the number of waiting SQL statements exceeds the value of this parameter, AliPG automatically executes the ABORT statement to terminate and roll back the transaction. For more information, see ABORT. Valid values: 0 to 100000.
is_enabled	boolean	Specifies whether to enable the rule. true: immediately enables the rule. false: disables the rule.
comment	varchar	The rule description.
search_path	varchar	The search path when the SQL statement that you want to throttle is running. If you set search_path to a pair of quotation marks (`''`), the default search path is used. You can also execute the `SHOW search_path;` statement to query the search path. A pair of quotation marks indicate an empty string.

Usage notes

In Syntax 1, you can set comment and search_path to a pair of quotation marks (''). If you set the parameters to NULL, the function does not perform any operations and directly returns a value. A pair of quotation marks indicate an empty string.
In Syntax 2, take note of the following points:
- You can set query_string and comment to a pair of quotation marks (''). If you set the parameters to NULL, the function does not perform any operations and directly returns a value. A pair of quotation marks indicate an empty string.
- If you configure other values for query_id and query_string, AliPG performs SQL throttling based on query_id that you specified.
This function returns ccl_id. ccl_id is the unique identifier of an SQL throttling rule in the current database.

Examples

The ccl_tbl table is created in the ccl_test database on the primary RDS instance. You can create an SQL throttling rule that meets the following requirements for the RDS instance:

All SELECT * FROM ccl_tbl statements are throttled. In this example, query_id is set to 1.
Up to three SQL statements of this type can be concurrently executed.
Up to two SQL statements of this type can stay in the waiting state.
The rule immediately takes effect after it is created.

Syntax 1

SELECT rds_add_ccl_rule(
  $$SELECT * FROM ccl_tbl;$$, -- The SQL statement that needs to be throttled and is wrapped by using $$.
  1,					-- SQL throttling is performed only on the primary RDS instance.
  3,					-- Up to three SQL statements of this type can be concurrently executed.
  2,					-- Up to two SQL statements of this type can stay in the waiting state.
  true,					-- The rule immediately takes effect after it is created.
  'limit constant select',		-- The description of the rule.
  ''					-- The default value of search_path is used.
);

Syntax 2

SELECT rds_add_ccl_rule_with_query_id(
  1,					-- query_id of the SQL statement that you want to throttle.
  1,					-- SQL throttling is performed only on the primary RDS instance.
  3,					-- Up to three SQL statements of this type can be concurrently executed.
  2,					-- Up to two SQL statements of this type can stay in the waiting state.
  true,					-- The rule immediately takes effect after it is created.
  '' -- The text of the SQL statement. In this example, this parameter is not specified.	
  'limit constant select',		-- The description of the rule.
);

Query SQL throttling rules

Scenarios

The statement in the following syntax can be executed on the primary RDS instance or read-only RDS instances to query the SQL throttling rules that are created.

Syntax

Query the SQL throttling rules that are created for all databases on the RDS instance.
```
SELECT * FROM rds_enabled_ccl_rule;
```
Note
In the query result, the values of query_string and comment are truncated due to display limits, and only the first 200 characters of each value are displayed. To view the complete values, execute the SELECT * FROM rds_show_current_db_ccl_rule(); statement.
Query the SQL throttling rules that are created for the current database on the RDS instance.
```
SELECT * FROM rds_show_current_db_ccl_rule();
```

Enable an SQL throttling rule

Scenarios

The function in the following syntax can be called only on the primary RDS instance and can be used in the following operations:

If is_enabled is set to false when you create an SQL throttling rule, you can call this function to enable the rule.
You can call this function to enable a disabled SQL throttling rule.

Syntax

SELECT rds_enable_ccl_rule(ccl_id int);

Parameters

Parameter	Type	Description
ccl_id	int	The ID of the SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.

Usage notes

None

Examples

SELECT rds_enable_ccl_rule(1);

Load an SQL throttling rule

Scenarios

The function in the following syntax can be called on the primary RDS instance or read-only RDS instances to load an SQL throttling rule. Only the loaded rule can be used to perform SQL throttling.

This function can be used in the following scenarios:

Primary RDS instance:
- If is_enabled is set to true when you create an SQL throttling rule, the rule is automatically loaded to the primary RDS instance. No human intervention is required.
- If is_enabled is set to false when you create an SQL throttling rule and the rds_enable_ccl_rule function is called to enable the rule, the rule is automatically loaded to the primary RDS instance. No human intervention is required.
- If the primary RDS instance is restarted, you must call this function to manually load an SQL throttling rule.
Read-only RDS instance:
If you want to enable an SQL throttling rule on a read-only RDS instance, you must set node_tag to 2 or 3 when you create an SQL throttling rule and then call the function on the read-only RDS instance to load the rule.

Syntax

SELECT rds_load_ccl_rule(ccl_id int);

Parameters

Parameter	Type	Description
ccl_id	int	The ID of the SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.

Usage notes

You can load an SQL throttling rule to a read-only RDS instance only after you call the rds_add_ccl_rule function on the primary RDS instance and set node_tag to 2 or 3 to create the rule.
A disabled rule cannot be loaded. If you want to load a disabled rule to a read-only RDS instance, you must enable the disabled rule on the primary RDS instance first. For more information, see Enable an SQL throttling rule.

Examples

SELECT rds_enable_ccl_rule(1);

Modify an SQL throttling rule

Scenarios

The function in the following syntax can be called only on the primary RDS instance to change the values of max_concurrency and max_waiting in an SQL throttling rule.

Syntax

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 SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.
new_max_concurrency	int	The new value of max_concurrency. If you increase the value of max_concurrency, the SQL statements that are in the waiting state are immediately executed. If you reduce the value of max_concurrency, the SQL statements that are being executed are not affected. The SQL statements that are in the waiting state are executed only after the number of concurrent SQL statements is less than the new value of max_concurrency.
new_max_waiting	int	The new value of max_waiting. If you increase the value of max_waiting, more SQL statements of the specified type can stay in the waiting state. If you reduce the maximum number of waiting SQL statements, the SQL statements that are in the waiting state are not affected. If a new SQL statement enters the waiting state and the number of waiting SQL statements is greater than the new value of max_waiting, AliPG automatically executes the ABORT statement to terminate and roll back the transaction.

Usage notes

After you modify an SQL throttling rule, the modification immediately takes effect.
You can change the maximum number of concurrent SQL statements and the maximum number of waiting SQL statements in an SQL throttling rule.

Examples

SELECT rds_update_ccl_rule(
 2,     -- ccl_id
 4,     -- The new maximum number of concurrent SQL statements is 4.
 5      -- The new maximum number of waiting SQL statements is 5.
);

Disable an SQL throttling rule

Scenarios

The function in the following syntax can be called only on the primary RDS instance to disable an SQL throttling rule. A disabled rule can no longer be used for SQL throttling.

Note

For more information about how to enable an SQL throttling rule, see Enable an SQL throttling rule.

Syntax

Disable an SQL throttling rule.

SELECT rds_disable_ccl_rule(ccl_id int);

Disable all SQL throttling rules for a database.
```
SELECT rds_disable_all();
```

Parameters

Parameter	Type	Description
ccl_id	int	The ID of the SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.

Usage notes

A disabled rule cannot be loaded. If you want to load a disabled rule to a read-only RDS instance, you must enable the disabled rule on the primary RDS instance first. For more information, see Enable an SQL throttling rule.
When you disable an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance, and the rule can no longer be used for SQL throttling.

Examples

SELECT rds_disable_ccl_rule(1);

Unload an SQL throttling rule

Scenarios

The function in the following syntax can be called on the primary RDS instance or read-only RDS instances to unload an SQL throttling rule. An unloaded rule can no longer be used to perform SQL throttling.

This function can be used in the following scenarios:

Primary RDS instance:
- If the rds_disable_ccl_rule or rds_disable_all function is called on the primary RDS instance to disable an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance. No human intervention is required.
- If the rds_del_ccl_rule function is called on the primary RDS instance to delete an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance. No human intervention is required.
- You can also call this function to unload the rule from the primary RDS instance.
Read-only RDS instance:
If you want to disable an SQL throttling rule for a read-only RDS instance, you can call this function on the read-only RDS instance.

Syntax

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

Parameters

Parameter	Type	Description
ccl_id	int	The ID of the SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.
db_name	varchar	The default value is an empty string that specifies the current database. You can also specify other databases to unload SQL throttling rules from the databases.

Usage notes

After an SQL throttling rule is unloaded, the rule can no longer be used to perform SQL throttling. If you want to reuse the rule, you must reload the rule. For more information, see Load an SQL throttling rule.

Examples

SELECT rds_unload_ccl_rule(1,'');

Delete an SQL throttling rule

Scenarios

The function in the following syntax can be called only on the primary RDS instance to delete an SQL throttling rule. After the rule is deleted, it is automatically unloaded from the primary RDS instance.

Syntax

SELECT rds_del_ccl_rule(ccl_id int);

Parameters

Parameter	Type	Description
ccl_id	int	The ID of the SQL throttling rule. For more information about how to query the ID of an SQL throttling rule, see Query SQL throttling rules.

Usage notes

None

Examples

SELECT rds_del_ccl_rule(1);

The following results can be returned:

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

Note

query_id is returned.
If the rule does not exist, an error is reported.

Appendix

Introduction to query_id

query_id is a special identifier for SQL statements in PostgreSQL. The same type of SQL statement has the same query_id value.
Examples:
```
-- The following statements have the same query_id value.
SELECT * FROM tbl WHERE a = 1;
SELECT * FROM tbl WHERE a = 2;
```
query_id contains the oid information about the object in an SQL statement. Tables whose names are the same in different databases are not the same object. Tables whose names are the same in different schemas are not the same object. If an SQL statement is used to query different objects in different databases, the SQL statement has different query_id values in the databases.
If an SQL statement is used to query a global table or global function, the SQL statement has the same query_id value in different databases.
- Example 1: pg_database is a global table. If you execute the same statement in different databases, the statement uses the same query_id value in different databases.
  - Execute the following statement in the ccl_test database:
```
SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);
```
    The following results can be returned:
```
   rds_get_query_id   
----------------------
 -8733244708994363681
(1 row)
```
  - Execute the following statement in the ccl_test2 database:
```
SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);
```
    The following results can be returned:
```
   rds_get_query_id   
----------------------
 -8733244708994363681
(1 row)
```
- Example 2: pg_sleep is a global function. If you execute the same statement in different databases, the statement uses the same query_id value in different databases.
  - Execute the following statement in the ccl_test database:
```
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
```
    The following results can be returned:
```
  rds_get_query_id  
--------------------
 440101247839410938
(1 row)
```
  - Execute the following statement in the ccl_test2 database:
```
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
```
    The following results can be returned:
```
  rds_get_query_id  
--------------------
 440101247839410938
(1 row)
```
In function call scenarios, if the data type of a parameter is changed or the existence of the FROM clause is changed, query_id is changed.
For example, if you call the pg_sleep function, the value of query_id varies in different situations.
- Without the FROM clause
```
SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);
```
  The following results can be returned:
```
  rds_get_query_id  
--------------------
 440101247839410938
(1 row)
```
- With the FROM clause
```
SELECT rds_get_query_id($$SELECT * FROM pg_sleep(1);$$);
```
  The following results can be returned:
```
   rds_get_query_id   
----------------------
 -3404018605099167039
(1 row)
```
- Change of the data type of a parameter
```
select rds_get_query_id($$SELECT * FROM pg_sleep(1.0);$$);
```
  The following results can be returned:
```
  rds_get_query_id   
---------------------
 3073869725037049158
(1 row)
```