Overview

Resource queues in Greenplum Database provide workload management by controlling resource allocation for non-superuser roles. Superusers bypass these restrictions entirely, executing queries without queue limitations.

Resource Queue Creation Syntax

CREATE RESOURCE QUEUE queue_name WITH (attribute=value [, ...])

Supported attributes:
  ACTIVE_STATEMENTS=integer
  [ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]
  [ MIN_COST=float ]
  [ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
  [ MEMORY_LIMIT='memory_units' ]

Or alternatively:
  MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]
  [ ACTIVE_STATEMENTS=integer ]
  [ MIN_COST=float ]
  [ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
  [ MEMORY_LIMIT='memory_units' ]

Parameter Specifications

Key Considerations

• Greenplum recommends using MEMORY_LIMIT and ACTIVE_STATEMENTS over MAX_COST for simpler management
• Without MEMORY_LIMIT, per-query memory defaults to statement_mem parameter value
• By default, only SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR respect queue limits. Setting resource_select_only=off includes DML operations
• Memory allocation formula: Without MAX_COST: MEMORY_LIMIT/ACTIVE_STATEMENTS. With MAX_COST: MEMORY_LIMIT * (query_cost/max_cost)

Implementation Examples

Creating Resource Queues

-- Cost-based queue without memory constraints
CREATE RESOURCE QUEUE processing_queue WITH (MAX_COST=15000.0);

-- Queue with statement count and memory caps
CREATE RESOURCE QUEUE analytics_pool WITH (ACTIVE_STATEMENTS=15, MEMORY_LIMIT='256MB', PRIORITY=LOW);

-- Comprehensive configuration
CREATE RESOURCE QUEUE reporting_pool WITH (
  ACTIVE_STATEMENTS=8,
  MEMORY_LIMIT='2GB',
  PRIORITY=HIGH,
  COST_OVERCOMMIT=TRUE,
  MIN_COST=150,
  MAX_COST=2000000
);

Modifying Queue Attributes

-- Reset cost thresholds to unlimited
ALTER RESOURCE QUEUE processing_queue WITH (MAX_COST=-1.0, MIN_COST=-1.0);

-- Adjust concurrent statement limit
ALTER RESOURCE QUEUE analytics_pool WITH (ACTIVE_STATEMENTS=5);

-- Update memory allocation
ALTER RESOURCE QUEUE reporting_pool WITH (MEMORY_LIMIT='4GB');

-- Change priority level
ALTER RESOURCE QUEUE processing_queue WITH (PRIORITY=MAX);

Assigning Queues to Database Roles

-- Create queue and assign to role
CREATE RESOURCE QUEUE etl_queue WITH (ACTIVE_STATEMENTS=12, MEMORY_LIMIT='8GB', PRIORITY=HIGH);
ALTER ROLE etl_user RESOURCE QUEUE etl_queue;

-- Revert to default queue
ALTER ROLE analyst_user RESOURCE QUEUE NONE;

Prerequisite for Queue Deletion

Before dropping a queue, reassign all associated roles:

ALTER ROLE etl_user RESOURCE QUEUE NONE;
DROP RESOURCE QUEUE etl_queue;

Monitoring and Diagnostics

Role-to-Queue Mapping

SELECT r.rolname AS username, 
       q.rsqname AS queue_name
FROM pg_roles r
JOIN gp_toolkit.gp_resqueue_status q 
  ON r.rolresqueue = q.queueid;

Queue Configuration Details

SELECT * FROM pg_resqueue_attributes;

Runtime Queue Statistics

SELECT * FROM gp_toolkit.gp_resqueue_status;

Historical Queue Metrics

SELECT * FROM pg_stat_resqueues;

Queue-Role Associations

SELECT * FROM gp_toolkit.gp_resq_role;

Identifying Waiting Queries

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting IS TRUE;

Active Statement Priority

SELECT * FROM gp_toolkit.gp_resq_priority_statement;

Terminating Queued Queries

SELECT r.rolname, q.rsqname, l.pid, 
       l.granted, a.current_query, a.datname
FROM pg_roles r
JOIN pg_locks l ON r.rolresqueue = l.objid
JOIN gp_toolkit.gp_resqueue_status q ON l.objid = q.queueid
JOIN pg_stat_activity a ON l.pid = a.procpid
WHERE a.usename = r.rolname;

System Configuraton Parameters

Memory Policy Management

The gp_resqueue_memory_policy parameter controls memory allocation strategy:

SHOW gp_resqueue_memory_policy;  -- Returns: eager_free

Policy Values:

• auto: Memory consumption governed by statement_mem and queue MEMORY_LIMIT. Individual queries may exceed MEMORY_LIMIT.
• eager_free: Implements phase-based memory accounting. Releases memory from completed execution phases, reducing out-of-memory errors for complex queries.

Related Memory Settings:

• statement_mem: Session-level memory allocation per query
• max_statement_mem: Segment-level safety cap preventing excessive memory requests. Recommended formula: (segment_host_physical_memory) / (average_concurrent_queries)

CPU Priority Control

Enable priority-based CPU scheduling:

SHOW gp_resqueue_priority;  -- Returns: on

Configure CPU cores per segment:

SHOW gp_resqueue_priority_cpucores_per_segment;  -- Returns: 4

Calculation: For an 8-core host running 2 primary segments, set to 4. On mastter-only hosts, use full core count.

Adjust CPU accounting frequency:

SHOW gp_resqueue_priority_sweeper_interval;  -- Returns: 1000

Lower values improve priority responsiveness but increase overhead.

Troubleshooting High Concurrency

When resource queues fail to limit concurrency, investigate MIN_COST settings. Queries with estimated costs below this threshold bypas queuing entirely.

Root Cause: Inaccurate table statistics cause underestimation of query costs, allowing resource-intensive queries to circumvent limits.

Solution: Ensure regular statistics collection:

ANALYZE table_name;

Memory Utilization Guidelines

• gp_resqueue_memory_policy: Enables advanced memory management (eager_free recommended)
• gp_vmem_protect_limit: Hard cap for all query processing. Must not exceed physical memory. Queries exceeding this limit get canceled.
• gp_vmem_idle_resource_timeout: Releases memory held by idle processes
• gp_vmem_protect_segworker_cache_limit: Controls segment worker cache cleanup
• shared_buffers: Shared memory buffer allocation. Minimum: 128KB or 16KB * max_connections. Must not exceed OS shmmax limit.

Best practice: Sum of all queue memory limits should stay within gp_vmem_protect_limit boundaries.