AnalyticDB:Use resource queues to perform workload management - AnalyticDB

Resource queues let you control how AnalyticDB for PostgreSQL allocates CPU and memory across concurrent workloads. By grouping queries into queues with defined limits and priorities, you prevent resource contention from degrading latency-sensitive jobs when the system is under load.

Prerequisites

Before you begin, make sure you have:

An AnalyticDB for PostgreSQL instance
An initial account or a privileged account that has the RDS_SUPERUSER role

Without the required permissions, queue operations fail with ERROR: must be superuser to alter resource queues. To grant RDS_SUPERUSER to an existing role, run: ALTER ROLE role_name WITH RDS_SUPERUSER;

How it works

Each resource queue defines two types of controls:

Admission controls — ACTIVE_STATEMENTS and MEMORY_LIMIT determine whether a query is admitted to the queue or held in a waiting state.
Execution controls — PRIORITY determines how CPU is shared among active queries at runtime. It has no effect on whether a query is admitted.

When a query is submitted, AnalyticDB for PostgreSQL checks the admission controls first. If the queue is at capacity, the query waits. Once admitted, the runtime CPU share is governed by PRIORITY relative to other active queues.

ACTIVE_STATEMENTS limits the number of queries executing at the same time within a queue. It is separate from the number of database connections, which is governed independently.

Create a resource queue

CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ...])

At least one of ACTIVE_STATEMENTS or MAX_COST is required. Omitting both returns:

ERROR:  at least one threshold ("ACTIVE_STATEMENTS", "MAX_COST") must be specified

Resource queues cannot be created inside a transaction block:

ERROR:  CREATE RESOURCE QUEUE cannot run inside a transaction block

Parameters

Parameter	Description	Default
`ACTIVE_STATEMENTS`	Maximum number of queries executing at the same time. Additional queries wait until a slot opens.	—
`MEMORY_LIMIT`	Maximum memory for all active queries combined on a single compute node. Units: KB, MB, or GB.	`-1` (unlimited)
`MAX_COST`	Maximum query cost, as estimated by the query optimizer.	`-1` (unlimited)
`COST_OVERCOMMIT`	Valid only with `MAX_COST`. `TRUE`: queries exceeding `MAX_COST` run when the system load is low. `FALSE`: those queries are rejected.	—
`MIN_COST`	Queries below this cost threshold skip the queue and run immediately.	—
`PRIORITY`	Runtime CPU share relative to other queues. Options: `MIN`, `LOW`, `MEDIUM`, `HIGH`, `MAX`.	`MEDIUM`

Examples

Limit concurrent queries:

CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=3);

Add a memory cap:

CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20, MEMORY_LIMIT='2000MB');

Set high priority:

CREATE RESOURCE QUEUE executive WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX);

How CPU priority works

When queues with different priorities have active queries, AnalyticDB for PostgreSQL distributes CPU dynamically:

Queries in queues with the same priority receive equal CPU shares.
When high-, medium-, and low-priority queues are all active: the high-priority queue gets 90% of CPU; the remaining 10% is split so medium gets 90% and low gets 10%.

For example, if reporting queries (MEDIUM) are running and an executive query (MAX) becomes active, AnalyticDB for PostgreSQL immediately shifts more CPU to the executive queue and reduces the share for reporting.

Override memory for a single query

Use statement_mem to allocate more memory to a specific query. The value must be less than both MEMORY_LIMIT and max_statement_mem.

SET statement_mem = '1GB';
SELECT * FROM test_adbpg WHERE col = 'adb4pg' ORDER BY id;
RESET statement_mem;

Which SQL statements are subject to resource queues

By default, resource_select_only is set to off in AnalyticDB for PostgreSQL, so the following statements are all subject to resource queues:

SELECT, SELECT INTO, CREATE TABLE AS SELECT, DECLARE CURSOR
INSERT, UPDATE, DELETE

Set resource_select_only to on to restrict queue management to SELECT-family statements only.

Assign users to a resource queue

After creating a queue, assign users to it. Each user belongs to exactly one resource queue.

-- Assign a queue when creating a role
CREATE ROLE analyst WITH LOGIN RESOURCE QUEUE reporting;

-- Reassign an existing role
ALTER ROLE analyst RESOURCE QUEUE executive;

Users not assigned to any queue are placed in pg_default, which allows 500 concurrent active queries with no cost limits and MEDIUM priority.

To remove a user from a resource queue and return them to pg_default:

ALTER ROLE role_name RESOURCE QUEUE none;

Modify a resource queue

-- Change the active statement limit
ALTER RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=5);

-- Change memory and cost limits
ALTER RESOURCE QUEUE adhoc WITH (MAX_COST=-1.0, MEMORY_LIMIT='2GB');

-- Change priority
ALTER RESOURCE QUEUE adhoc WITH (PRIORITY=LOW);
ALTER RESOURCE QUEUE reporting WITH (PRIORITY=HIGH);

Monitor queue activity

Check the configuration of a specific queue:

SELECT * FROM pg_resqueue WHERE rsqname = 'adhoc';

Delete a resource queue

DROP RESOURCE QUEUE name;

A queue cannot be deleted if it has assigned users or queries in a waiting state. Remove all users from the queue and clear any waiting queries first.

Troubleshooting

ERROR: must be superuser to alter resource queues

Cause: The current account does not have sufficient permissions.

Solution: Use an initial account or a privileged account with the RDS_SUPERUSER role. Alternatively, grant RDS_SUPERUSER to the current account:

ALTER ROLE role_name WITH RDS_SUPERUSER;