Use the pg_concurrency_control extension of ApsaraDB RDS for PostgreSQL - ApsaraDB RDS

The pg_concurrency_control plug-in for ApsaraDB RDS for PostgreSQL lets you cap the number of concurrent SQL statements by type — SELECT queries, DML statements, slow queries, and transaction blocks. When a statement reaches the configured limit, it waits in a queue instead of executing immediately, preventing resource exhaustion under high-concurrency workloads.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance running PostgreSQL 10 or 11

To check your instance version, run SELECT version(); in your database.

Install the plug-in

Run the following command to create the extension:

CREATE EXTENSION pg_concurrency_control;

Configure concurrency limits

Each concurrency parameter defaults to 0, which disables concurrency control for that statement type. Set a parameter to a positive integer to activate the limit.

For example, set pg_concurrency_control.query_concurrency to 10 to enable concurrency control for SELECT statements. Use the same approach for other statement types.

To mark a statement as a slow query so it falls under the bigquery_concurrency limit, prepend the /*+bigsql*/ hint:

/*+bigsql*/ SELECT * FROM test;

Parameter reference

Concurrency parameters

Parameter	Default	Valid values	Description
`pg_concurrency_control.query_concurrency`	`0`	0–1024	Maximum concurrent jobs for SELECT statements. `0` disables control.
`pg_concurrency_control.bigquery_concurrency`	`0`	0–1024	Maximum concurrent jobs for slow queries. `0` disables control. Mark a statement as a slow query using the `/+bigsql/` hint.
`pg_concurrency_control.transaction_concurrency`	`0`	0–1024	Maximum concurrent jobs for transaction blocks. `0` disables control.
`pg_concurrency_control.autocommit_concurrency`	`0`	0–1024	Maximum concurrent jobs for DML statements. `0` disables control.

Timeout parameters

Parameter	Default	Valid values	Description
`pg_concurrency_control.control_timeout`	`1s`	30ms–3s	Maximum time a SELECT statement, DML statement, or transaction block waits in the queue before the timeout action fires.
`pg_concurrency_control.bigsql_control_timeout`	`1s`	30ms–3s	Maximum time a slow query waits in the queue before the timeout action fires.
`pg_concurrency_control.timeout_action`	`TCC_break`	`TCC_break`, `TCC_rollback`, `TCC_wait`	Action taken when a SELECT statement, DML statement, or transaction block times out waiting in the queue. See Timeout action values.
`pg_concurrency_control.bigsql_timeout_action`	`TCC_wait`	`TCC_break`, `TCC_rollback`, `TCC_wait`	Action taken when a slow query times out waiting in the queue. See Timeout action values.

Timeout action values

Value	Behavior
`TCC_break`	Skips the waiting statement and executes the next statement.
`TCC_rollback`	Reports an error and rolls back the transaction.
`TCC_wait`	Resets the timestamp after a timeout and continues waiting.

Verify queue status

Use pg_concurrency_control_status() to inspect the current statement queue. The function returns the number of statements currently waiting in each queue.

SELECT * FROM pg_concurrency_control_status();

Output when no statements are queued:

 autocommit_count | bigquery_count | query_count | transaction_count
------------------+----------------+-------------+-------------------
                0 |              0 |           0 |                 0
(1 row)

A non-zero count confirms that the limit is active and statements are being queued. Once queued statements complete or time out, the count decreases automatically.

Example: verify that a slow query limit is active

The following example shows the queue state while a slow query runs against a bigquery_concurrency limit.

Check the queue before running any queries:

SELECT * FROM pg_concurrency_control_status();

 autocommit_count | bigquery_count | query_count | transaction_count
------------------+----------------+-------------+-------------------
                 0 |              0 |           0 |                 0
(1 row)

Set pg_concurrency_control.query_concurrency to 10 to enable SELECT concurrency control.
Run a slow query using the /*+bigsql*/ hint:
```
/*+ bigsql */ SELECT pg_sleep(10);
```

While the slow query is running, check the queue from a second session:

SELECT * FROM pg_concurrency_control_status();

 autocommit_count | bigquery_count | query_count | transaction_count
------------------+----------------+-------------+-------------------
                 0 |              1 |           0 |                 0
(1 row)

A bigquery_count of 1 confirms that the slow query is being tracked in the queue. After the query completes, the count clears automatically.