Use resource queues

Resource queues manage system resources in a Greengage DB cluster by dividing them into isolated limited pools. Each resource queue defines constraints such as the number of concurrent queries, memory usage, and query cost limits for the queries assigned to it.

When a user submits a query, Greengage DB routes it to the resource queue assigned to that user. This routing mechanism allows for automatic classification and prioritization of queries based on user roles and workloads. For example, resource queues can be used to give higher priority to critical queries or limit the impact of resource-intensive ad hoc queries.

By grouping classes of queries with similar resource requirements, administrators can control concurrency, memory, and CPU usage without query-level management.

Resource groups

Greengage DB also provides resource groups — a newer and more flexible resource management mechanism than resource queues. It provides more fine-grained control of CPU and memory resources and is recommended for new cluster deployments.

Resource queues define the following aspects of query processing:

Maximum number of concurrent active queries.
Absolute memory usage limit.
Estimated query cost threshold.
Queue priority (affects CPU resource allocation).

These limits are described in detail in the Attributes and limits section.

When a query is submitted, Greengage DB determines the appropriate resource queue based on the user who issued the query. It then checks whether the queue has enough available resources to start the query immediately based on the queue limits. These may include:

The number of active queries in the queue.
The available amount of the queue’s memory.
The estimated cost of active queries in the queue.

If there are enough free resources, the query begins execution. Otherwise, it enters a waiting state until sufficient resources are freed.

During execution, memory and CPU resources are assigned to the query in accordance with the queue’s configuration. Queue priority affects how CPU resources are scheduled and shared among concurrently running queries in different queues.

NOTE

Superuser queries are excluded from resource queue checks and always bypass queue limitations.

By default, Greengage DB applies resource queue constraints only to the following statements:

SELECT
SELECT INTO
CREATE TABLE AS SELECT
DECLARE CURSOR

You can extend resource queue enforcement to include DML operations (INSERT, UPDATE, and DELETE) by setting the resource_select_only configuration parameter to off. This may be useful in environments where DML operations consume significant resources and need additional supervision.

NOTE

Queries executed via EXPLAIN ANALYZE are excluded from resource queue checks. This ensures that queue limits do not block performance analysis.

Greengage DB uses resource queues as the default resource management mechanism. You can start using them out of the box or adjust their general configuration.

If you previously switched to resource groups for resource management, you can return to using resource queues by setting the gp_resource_manager configuration parameter to queue:

$ gpconfig -c gp_resource_manager -v "queue"

A cluster restart is required for the change to take effect:

$ gpstop -r

NOTE

Only one resource management model — resource queues or resource groups — can be active in Greengage DB at a time.

The following configuration parameters define the general behavior of resource queues in Greengage DB:

max_resource_queues — defines the maximum number of resource queues in the cluster.
max_resource_portals_per_transaction — limits the number of simultaneously open cursors per transaction. Each open cursor is treated as an active query and consumes a slot in the associated resource queue.
resource_select_only — controls whether only SELECT-like queries are subject to resource queue limits. If set to off, DML commands INSERT, UPDATE, DELETE are also evaluated.
resource_cleanup_gangs_on_wait — when enabled, this option forces idle segment worker processes to clean up before the query attempts to take a resource queue slot. This can help reduce memory pressure while queries are queued.
stats_queue_level — enables statistics collection for resource queue usage. When enabled, resource queue statistics are available in the pg_stat_resqueues system view.

There are also parameters that define specific aspects of resource queue behavior. They are described in corresponding subsections of Attributes and limits.

One built-in resource queue exists in Greengage DB — pg_default. It is automatically assigned to all new user roles unless another queue is explicitly assigned.

The default configuration of pg_default is shown in the table below.

pg_default limits
Attribute	Value
ACTIVE_STATEMENTS	20
MEMORY_LIMIT	-1
PRIORITY	MEDIUM
MAX_COST	-1
COST_OVERCOMMIT	false
MIN_COST	0

For detailed descriptions of resource queue attributes, see the Attributes and limits section.

You can modify the default limits of pg_default using the ALTER RESOURCE QUEUE command.

Use the following SQL commands to manage resource queues in Greengage DB:

CREATE RESOURCE QUEUE
ALTER RESOURCE QUEUE
DROP RESOURCE QUEUE

The CREATE RESOURCE QUEUE command creates a resource queue with the specified attributes:

CREATE RESOURCE QUEUE <queue_name> WITH (<queue_attribute> = <value> [, ... ])

The following attributes can be set when creating a resource queue:

MEMORY_LIMIT — the total memory available to all queries running in the queue at the same time.
ACTIVE_STATEMENTS — the maximum number of concurrent active queries in the queue.
PRIORITY — the relative priority of queries from this queue compared to others. Valid values: MIN, LOW, MEDIUM, HIGH, MAX.
MAX_COST — the total estimated cost threshold for concurrently running queries. If the combined estimated cost exceeds this limit, new queries are queued.
COST_OVERCOMMIT — if MAX_COST is defined, determines whether queries are allowed to exceed it when the system is idle.
MIN_COST — the estimated cost threshold for queries that are considered lightweight. Queries with a cost below this value are executed immediately, bypassing queue checks.

NOTE

Each resource queue must have at least one of the MEMORY_LIMIT or ACTIVE_STATEMENTS attributes defined.

For example, to create a resource queue that can run no more than 15 queries concurrently, execute:

CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS = 15);

Create a queue that allows up to 20 concurrent queries sharing 512 MB of total memory:

CREATE RESOURCE QUEUE reporting WITH (MEMORY_LIMIT = '512MB', ACTIVE_STATEMENTS = 20);

IMPORTANT

Query cost is an abstract metric that differs between the GPORCA and PostgreSQL query optimizers. Together with the fact that Greengage DB can automatically switch between optimizers in certain cases, this can make MAX_COST behavior unpredictable. Therefore, it is generally not recommended to use cost limits in resource queues unless you have specific needs. Learn more in Query cost.

To modify the limits of an existing queue, use the ALTER RESOURCE QUEUE command:

ALTER RESOURCE QUEUE <queue_name> WITH (<queue_attribute> = <value> [, ... ])

This command can also add new limits that weren’t set at the queue creation. For example, to increase the number of active statements allowed in the adhoc queue and limit their total memory consumption:

ALTER RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS = 22, MEMORY_LIMIT = '512MB');

To remove one or more limits from a queue, use ALTER RESOURCE QUEUE with a WITHOUT clause. Example:

ALTER RESOURCE QUEUE reporting WITHOUT (MEMORY_LIMIT);

This leaves reporting with only the ACTIVE_STATEMENTS limit defined.

To delete a resource queue, use the DROP RESOURCE QUEUE command:

DROP RESOURCE QUEUE <queue_name>

Example:

DROP RESOURCE QUEUE adhoc;

A resource queue can be dropped only when it is not in use. Particularly, this means that:

The queue is not assigned to any roles.
No running or queued transactions are using the queue.

To delete a resource queue:

Cancel all transactions that are using the resource queue with pg_cancel_backend(). See Cancel a query in a queue to learn to do this.
Unassign the resource queue from its users by reassigning them to another queue or setting their resource queue to NONE.
Drop the resource queue.

Example:

ALTER ROLE bob RESOURCE QUEUE NONE;
DROP RESOURCE QUEUE adhoc;

To assign a resource queue to a role, use ALTER ROLE with the RESOURCE QUEUE clause:

ALTER ROLE alice RESOURCE QUEUE reporting;

You can also assign a resource queue at the role creation in the CREATE ROLE command:

CREATE ROLE bob RESOURCE QUEUE reporting;

Each role can have one assigned resource queue, while a resource queue can be shared by multiple roles.

IMPORTANT

For effective cluster resource management, it is generally recommended to assign user-defined resource queues to all user roles in the database.

To remove a resource queue assignment, use ALTER ROLE with RESOURCE QUEUE NONE. This reverts the role to the default queue pg_default:

ALTER ROLE bob RESOURCE QUEUE NONE;

Greengage DB provides system catalog views for monitoring resource queue configuration, current utilization, and statistics. These views help track queue limits, query activity, and role assignments across the cluster.

To list all configured resource queues along with their attributes, use the gp_toolkit.gp_resqueue_status view. This view shows queue identifiers, names, and static limits such as concurrency, memory, and cost.

SELECT queueid, rsqname, rsqcountlimit, rsqcostlimit, rsqmemorylimit
FROM gp_toolkit.gp_resqueue_status;

Example output:

 queueid |  rsqname   | rsqcountlimit | rsqcostlimit | rsqmemorylimit
---------+------------+---------------+--------------+----------------
    6055 | pg_default |            21 |           -1 |             -1
   16449 | reporting  |            22 |           -1 |             -1
   16456 | adhoc      |            15 |         1000 |    2.68435e+08
(3 rows)

where:

rsqcountlimit — the maximum number of active queries allowed in the queue.
rsqcostlimit — query cost threshold.
rsqmemorylimit — memory threshold, in bytes.

A value of -1 means no limit is enforced.

Use the following views to monitor real-time utilization of resource queues, including active queries, waiting queries, and memory consumption:

gp_toolkit.gp_resqueue_status — shows current usage values in addition to static limits. This includes the number of running queries, estimated total cost, memory usage (in bytes), and the number of queued and running queries.

SELECT * FROM gp_toolkit.gp_resqueue_status;

Example output:

 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
    6055 | pg_default |            21 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0
   16449 | reporting  |            22 |             2 |           -1 |          480 |             -1 |    2.62144e+08 |          0 |          2
   16456 | adhoc      |            15 |             0 |         1000 |            0 |    2.68435e+08 |              0 |          0 |          0
(3 rows)

where:

rsqcountvalue, rsqcostvalue, and rsqmemoryvalue show the live resource usage.
rsqwaiters is the number of queries waiting for a slot.
rsqholders is the number of queries currently holding a slot (actively running).

gp_toolkit.gp_resq_activity — displays details about currently running queries in queues, including start time, status, and initiator. This view is useful for identifying which users are actively consuming resources.

SELECT * FROM gp_toolkit.gp_resq_activity;

Example output:

 resqprocpid | resqrole | resqoid | resqname  |           resqstart           | resqstatus
-------------+----------+---------+-----------+-------------------------------+------------
        3460 | charlie  |   16449 | reporting | 2025-08-04 10:25:48.318273+00 | running
        3351 | alice    |   16449 | reporting | 2025-08-04 10:25:53.574034+00 | running
(2 rows)

gp_toolkit.gp_resq_activity_by_queue — aggregates query activity per queue. It shows the number of currently running queries per queue, the latest start time, and current status. This view helps identify heavily loaded queues or queues with frequent activity patterns.

SELECT * FROM gp_toolkit.gp_resq_activity_by_queue;

Example output:

 resqoid | resqname  |           resqlast            | resqstatus | resqtotal
---------+-----------+-------------------------------+------------+-----------
   16449 | reporting | 2025-08-04 10:25:53.574034+00 | running    |         2
(1 row)

For historical monitoring, enable statistics collection on resource queues using the stats_queue_level configuration parameter:

SET stats_queue_level = 'on';

Note that enabling this option introduces minor performance overhead due to additional tracking. Enable it only if you need long-term queue usage metrics.

Once queue statistics collection is enabled, you can query the pg_stat_resqueues system view:

SELECT * FROM pg_stat_resqueues;

Example output:

 queueid | queuename  | n_queries_exec | n_queries_wait | elapsed_exec | elapsed_wait
---------+------------+----------------+----------------+--------------+--------------
   16456 | adhoc      |              5 |              0 |           10 |            0
    6055 | pg_default |              0 |              0 |            0 |            0
   16449 | reporting  |             11 |              1 |           30 |           25
(3 rows)

where:

n_queries_exec — number of queries executed.
n_queries_wait — number of queries that were queued.
elapsed_exec, elapsed_wait — total time spent in execution and waiting, in seconds.

To list roles and their assigned resource queues, use the gp_toolkit.gp_resq_role view:

SELECT * FROM gp_toolkit.gp_resq_role;

Example output:

 rrrolname | rrrsqname
-----------+------------
 gpadmin   | pg_default
 alice     | reporting
 bob       | adhoc
 charlie   | reporting
(4 rows)

Greengage DB allows administrators to manually tune query execution within resource queues. This includes cancelling long-running or blocked queries, and adjusting statement priority dynamically.

You can cancel a running or queued query in a resource queue to free up slots and resources for other workloads. To find the PIDs of transactions currently running or queued in resource queues, run a query like the following:

SELECT rolname,rsqname, pg_locks.pid as pid, granted,
       state, query, datname
FROM pg_roles,
     gp_toolkit.gp_resqueue_status,
     pg_locks,
     pg_stat_activity
WHERE pg_roles.rolresqueue = pg_locks.objid
  AND pg_locks.objid = gp_toolkit.gp_resqueue_status.queueid
  AND pg_stat_activity.pid = pg_locks.pid
  AND pg_stat_activity.usename = pg_roles.rolname;

It outputs lines like this:

 rolname |  rsqname  | pid  | granted | state  |         query         | datname
---------+-----------+------+---------+--------+-----------------------+----------
 alice   | reporting | 2895 | f       | active | SELECT * FROM orders; | postgres
 charlie | reporting | 2914 | t       | active | SELECT * FROM orders; | postgres
(2 rows)

This output helps you identify the PID and state of queries you may need to cancel. The granted column indicates whether the query has already acquired a slot (t) or is still waiting (f).

TIP

To find queries that are waiting for a slot in a resource queue, you can use the gp_toolkit.gp_locks_on_resqueue view. Queries with lorwaiting = true are still queued and have not yet started execution:

SELECT *
FROM gp_toolkit.gp_locks_on_resqueue
WHERE lorwaiting = true;

Example result:

 lorusename | lorrsqname |  lorlocktype   | lorobjid | lortransaction | lorpid |    lormode    | lorgranted | lorwaiting
------------+------------+----------------+----------+----------------+--------+---------------+------------+------------
 alice      | reporting  | resource queue |    16449 |                |   2895 | ExclusiveLock | f          | t
(1 row)

To cancel a query, run the pg_cancel_backend() function passing the query PID as an argument:

SELECT pg_cancel_backend(2895);

For more details on managing and stopping client queries safely, see Stop client queries and processes.

Greengage DB allows you to adjust the priority of running or queued statements within a resource queue. This can be useful for prioritizing critical queries during periods of high load.

To view currently executing or queued statements and their priorities, query the gp_toolkit.gp_resq_priority_statement view:

SELECT * FROM gp_toolkit.gp_resq_priority_statement;

Example result:

 rqpdatname | rqpusename | rqpsession | rqpcommand | rqppriority | rqpweight |                       rqpquery
------------+------------+------------+------------+-------------+-----------+------------------------------------------------------
 postgres   | alice      |         12 |          3 | HIGH        |      1000 | SELECT * FROM orders;
 postgres   | charlie    |         13 |          1 | HIGH        |      1000 | SELECT * FROM orders;
 postgres   | gpadmin    |         11 |         19 | MAX         |   1000000 | SELECT * FROM gp_toolkit.gp_resq_priority_statement;
(3 rows)

To raise the priority of a specific query, use the gp_adjust_priority() function. It takes three arguments:

session ID;
statement count;
target priority.

The first two arguments identify the query, whose priority is to be changed. These values are available in the rqpsession and rqpcommand columns of the gp_resq_priority_statement view. For example, the following call sets the highest priority for a query:

SELECT gp_adjust_priority(13, 1, 'MAX');

Note that gp_adjust_priority() applies only to the target statement. It does not affect future queries from the same user or resource queue.

This section provides detailed descriptions of the limits enforced by resource queues and the corresponding attributes used to control them in Greengage DB.

The ACTIVE_STATEMENTS attribute defines the maximum number of statements that can be executed concurrently in the resource queue. When the concurrency limit is reached, new queries are queued and executed in a first-in, first-out (FIFO) order as active queries complete.

ACTIVE_STATEMENTS alone determines whether a query runs or waits only if no memory (MEMORY_LIMIT) and query cost (MAX_COST) limits are defined for the queue. If any of these limits are set, the query must satisfy all of them before it can start execution.

The MEMORY_LIMIT attribute controls whether a query is executed immediately or placed in the queue based on memory availability. Its value should include units, such as kB, MB, or GB, for example: MEMORY_LIMIT = '2048 MB'.

When resource queues are used, the total memory available to each Greengage DB segment is defined by the gp_vmem_protect_limit configuration parameter.

IMPORTANT

The value of gp_vmem_protect_limit is specified without units and is interpreted in megabytes. For example, to set the limit to 8 GB, specify the value 8192:

$ gpconfig -c gp_vmem_protect_limit -v 8192

Available segment memory is distributed between resource queues according to their MEMORY_LIMIT values. A queue’s memory limit defines the maximum amount of memory that all active queries in the queue can use simultaneously on a segment. The sum of resource queues' MEMORY_LIMIT values cannot exceed gp_vmem_protect_limit.

TIP

It is recommended to leave 10-20% of gp_vmem_protect_limit unallocated to resource queues to allow processing unexpected resource-intensive queries.

Memory within each queue is divided into equal slots, one per active query (ACTIVE_STATEMENTS). Each query receives a single memory slot and holds it during execution, even if it doesn’t use the full amount.

If ACTIVE_STATEMENTS is not specified, the slot size for each query is determined by the statement_mem configuration parameter.

You can use statement_mem to adjust available memory on a per-query basis, for example, to increase memory for an unexpected resource-intensive query.

When both MEMORY_LIMIT and ACTIVE_STATEMENTS are set, increasing statement_mem for a query effectively reduces the number of memory slots available. For example:

A resource queue is configured with MEMORY_LIMIT = '2 GB' and ACTIVE_STATEMENTS = 4, which creates four 512 MB memory slots.
A query with statement_mem = '768 MB' enters the queue and consumes a larger memory slot.
Two additional queries arrive with default memory needs and receive standard 512 MB slots.

This leaves 256 MB of available memory in the queue, which is not enough for a standard 512 MB slot. If a fourth query arrives, it must wait until one of the running queries finishes so that a 512 MB slot is available.

TIP

To improve performance of queries with low memory requirements, it is recommended to execute them with a low statement_mem:

SET statement_mem = '10 MB';

If no MEMORY_LIMIT is set for the queue, the memory allocated to each query is defined solely by statement_mem. In this case, be careful to avoid memory overcommitment, as there is no upper bound on total memory usage across queries. It is important to monitor memory usage closely to prevent the risk of out-of-memory errors or performance degradation.

NOTE

The max_statement_mem configuration parameter defines an upper bound for statement_mem. Only superusers can change this setting.

The PRIORITY attribute of a resource queue defines the relative share of CPU resources allocated to queries in that queue during execution. This mechanism helps maintain predictable performance under high concurrency by allowing critical workloads to receive more CPU time.

There are five possible values for PRIORITY: MIN, LOW, MEDIUM (default), HIGH, and MAX.

Unlike memory or concurrency limits, CPU priority is applied only after a query has entered the execution phase. It does not influence admission to the queue but determines the runtime scheduling of CPU resources across active queries.

Greengage DB continuously evaluates query priorities and redistributes CPU resources among running queries. The time interval between two evaluations is controlled by the gp_resqueue_priority_sweeper_interval parameter. The default is 1000 milliseconds.

NOTE

The examples below are provided to illustrate the priority evaluation concept. They demonstrate the behavior of relative CPU share adjustments, not the actual proportions used in Greengage DB.

For example, two queries from queues with the same priority (HIGH) start running concurrently. If there are no other running queries, CPU resources are evenly split between them. This is done regardless of actual CPU requirements of the queries; PRIORITY is the only parameter that defines the CPU share.

CPU share of queries (HIGH and HIGH)
Query	Priority	CPU share
Query 1	HIGH	50%
Query 2	HIGH	50%

A third query from a LOW priority queue starts while the two HIGH priority queries are running. It receives a smaller CPU share due to its priority. The shares of the HIGH queries are reduced equally to accommodate the new query:

CPU share of queries (HIGH, HIGH, LOW)
Query	Priority	CPU share
Query 1	HIGH	45%
Query 2	HIGH	45%
Query 3	LOW	10%

A new query from a MAX priority queue arrives. It receives a larger portion of CPU time than any running query with a lower priority. Greengage DB allocates its CPU share by proportionally reducing the CPU shares of all existing queries:

CPU share of queries (HIGH, HIGH, LOW, MAX)
Query	Priority	CPU share
Query 1	HIGH	4,5%
Query 2	HIGH	4,5%
Query 3	LOW	1%
Query 4	MAX	90%

The total CPU capacity considered for priority scheduling is defined by the gp_resqueue_priority_cpucores_per_segment parameter. This configuration parameter is local, meaning that it can be set individually on master and on each segment.

For the best performance, the following values are recommended for gp_resqueue_priority_cpucores_per_segment:

Master host: set to the total number of available CPU cores.
Segment host: divide the number of available cores by the number of primary segments on the host.

For example, if a cluster runs on 16-core hosts and each segment host has four primary segments, set:

On master: gp_resqueue_priority_cpucores_per_segment = 16;
On segments: gp_resqueue_priority_cpucores_per_segment = 4.

It is generally recommended to include all CPU cores available to the operating system, including virtual cores.

To disable CPU prioritization entirely, set the gp_resqueue_priority parameter to off. In this case, all PRIORITY values are ignored, and CPU resources are distributed evenly among running queries.

The estimated query cost represents the planner’s approximation of how expensive a query will be to execute, measured in abstract cost units. These units reflect relative complexity, not absolute resource consumption.

Cost estimates differ depending on which planner is used. Two available planners — Postgres planner and GPORCA — use different costing models, and Greengage DB can automatically switch between them in certain cases. Therefore, relying on exact cost thresholds may lead to unexpected behavior.

IMPORTANT

Because of these differences, it is generally safer to rely on memory (MEMORY_LIMIT) and concurrency (ACTIVE_STATEMENTS) limits when managing workload resources. Use cost-based limits only when you have a specific requirement.

Query cost influences how queries are admitted or exempted from queue checks:

Queries with very high cost can be rejected (MAX_COST).
Lightweight queries can bypass queueing (MIN_COST).

The MAX_COST attribute defines the maximum estimated total cost of all active queries that can run in the resource queue simultaneously. It can be specified as a decimal or an exponential number, for example:

MAX_COST = 10000
MAX_COST = 3e+10

Queries that exceed this threshold are rejected unless overcommitment is enabled.

The COST_OVERCOMMIT boolean attribute can be used to allow queries to bypass the MAX_COST limit when the system is idle. If set to true, a high-cost query may still run if resources are available. By default, it is false.

To reduce queuing delays for lightweight queries with insignificant impact on the DBMS performance, you can use the MIN_COST attribute. It defines the maximum estimated cost of a query that is considered lightweight. Such queries skip queue checks and are executed immediately.

MIN_COST can be specified as a decimal or an exponential number, for example:

MIN_COST = 1000
MIN_COST = 5e+3

Use resource queues

Overview

Resource queue limits

Processing in queues

Evaluated statements

Start using resource queues

General configuration

Default resource queue: pg_default

Manage resource queues

Create a resource queue

Alter a resource queue

Drop a resource queue

Assign resource queues to roles

Check resource queues

View resource queues

View resource queue usage

View resource queue statistics

View resource queues assigned to roles

Manage queries in resource queues

Cancel a query in a queue

Change priority

Attributes and limits

Number of concurrent statements

Memory limit

Adjust memory limit per query

CPU usage

Priority evaluation

CPU cores

Turn off query prioritization

Query cost

Maximum allowed query cost

Exclude lightweight queries from limit checks