All Products
Search

This Product

    AnalyticDB:Manage workloads using resource groups

    Document Center

    AnalyticDB:Manage workloads using resource groups

    Last Updated:Apr 02, 2026

    Resource groups give you fine-grained control over CPU, memory, and concurrency at the database account level. Assign different accounts to separate resource groups so ETL jobs, reporting queries, and interactive workloads don't compete for the same resources.

    Note Resource group management and resource queue management are mutually exclusive. Enabling resource groups automatically disables resource queues. For details on resource queues, see Use resource queues to perform workload management.

    Version requirements

    Version Console switching Older versions
    V6.0 V6.6.1.0 or later Supported via console —
    V6.0 earlier than V6.6.1.0 — Submit a ticket
    Note For V7.0 instances, resource groups can only be managed through SQL statements—not through the console. To enable or disable resource group management for V7.0 instances, submit a ticket.

    The SET, RESET, and SHOW statements are not subject to resource group settings.

    To check your instance's minor version, see View the minor version of an instance.

    How resource groups work

    Each resource group defines limits for CPU, memory, and concurrency. You assign one or more database accounts to a resource group. Queries submitted by those accounts are managed within the group's limits.

    When a resource group reaches its concurrency limit, new queries queue in first-in first-out (FIFO) order until a slot opens. CPU resources are shared proportionally across active groups, and a single active group can use all available CPU.

    Resource group parameters

    Parameters differ between V6.0 and V7.0 instances.

    V6.0 parameters

    Parameter Description Required Default Valid values
    CPU_RATE_LIMIT CPU share when multiple groups compete. Yes — 0–100; sum across all groups ≤ 100
    MEMORY_LIMIT Percentage of total memory reserved for the group. No 0 0–100; sum across all groups ≤ 100
    CONCURRENCY Maximum concurrent transactions. No 50 1 to max_connections
    MEMORY_SHARED_QUOTA Percentage of group memory that is shared (versus fixed per-query). No 80 0–100
    MEMORY_SPILL_RATIO Memory threshold for spilling memory-intensive operators to disk. No 0 0–100

    V7.0 parameters

    Parameter Description Required Default Valid values
    CPU_MAX_PERCENT Maximum CPU utilization for the group (hard cap). Yes — 0–100; sum across all groups can exceed 100
    CPU_WEIGHT Relative CPU allocation weight when groups compete. No 50 0–500
    MEMORY_LIMIT Maximum memory per resource group, in MB. -1 uses statement_mem. No -1 0 to gp_vmem_protect_limit (exclusive)
    CONCURRENCY Maximum concurrent transactions. No 50 1 to max_connections
    MIN_COST Optimizer cost threshold. Queries below this cost use global resources instead. No 0 0–2147483647

    For details on max_connections, statement_mem, and gp_vmem_protect_limit, see Performance parameter optimization.

    Memory management (V6.0)

    V6.0 divides memory into three pools:

    1. Intra-group fixed memory: allocated to each query slot within the group.

    2. Intra-group shared memory: shared across all queries in the group.

    3. Global shared memory: the unallocated portion when the sum of all groups' MEMORY_LIMIT values is below 100.

    When a query runs, it draws from fixed memory first. If fixed memory is insufficient, it requests shared memory, then global shared memory. If all three are exhausted, the query fails with an out-of-memory (OOM) error.

    imageimage

    The MEMORY_SHARED_QUOTA parameter controls the split between fixed and shared memory within a group:

    • MEMORY_SHARED_QUOTA = 100: all group memory is shared.

    • MEMORY_SHARED_QUOTA < 100: memory is split proportionally into fixed and shared portions.

    image

    For typical workloads, size the intra-group fixed memory to handle most queries, and keep shared memory available for queries that need extra headroom.

    Memory management (V7.0)

    V7.0 uses three parameters to determine per-query memory:

    1. If gp_resgroup_memory_query_fixed_mem > 0, each query is capped at that value.

    2. If gp_resgroup_memory_query_fixed_mem = 0 (default):

      • If MEMORY_LIMIT = -1, the cap is statement_mem.

      • If MEMORY_LIMIT > -1, the cap is MAX(MEMORY_LIMIT / CONCURRENCY, statement_mem).

    Example: A resource group with MEMORY_LIMIT = 2048 MB and CONCURRENCY = 5 allocates 400 MB per query by default.

    Scenario gp_resgroup_memory_query_fixed_mem statement_mem Memory used
    etl1 800 MB 900 MB 800 MB (fixed mem takes precedence)
    etl2 Not set 300 MB 400 MB (default, because 300 MB < 400 MB)
    etl3 Not set 700 MB 700 MB (statement_mem > default)

    CPU management (V6.0)

    CPU_RATE_LIMIT controls the share of CPU time slices a group receives when competing with other groups. A group with CPU_RATE_LIMIT = 40 gets twice the CPU of a group with CPU_RATE_LIMIT = 20 when both are active. If only one group is running queries, it can use all available CPU.

    CPU management (V7.0)

    V7.0 uses two complementary parameters:

    • CPU_MAX_PERCENT: the hard ceiling on CPU utilization per group.

    • CPU_WEIGHT: the relative allocation ratio when groups compete simultaneously.

    Example: rg1 has CPU_MAX_PERCENT = 30 and CPU_WEIGHT = 100. rg2 has CPU_MAX_PERCENT = 60 and CPU_WEIGHT = 50.

    • If only rg1 is active: rg1 can use up to 30% CPU; rg2 can use up to 60%.

    • If both are active: CPU time slices are allocated at a 100:50 ratio until one group hits its CPU_MAX_PERCENT cap. Once rg1 reaches 30%, remaining CPU goes to rg2.

    Disk spilling (V6.0)

    MEMORY_SPILL_RATIO sets the memory threshold at which memory-intensive operators (HashAgg, Join, Sort) spill intermediate results to disk.

    • MEMORY_SPILL_RATIO > 0: spilling starts when memory usage for the transaction reaches this percentage.

    • MEMORY_SPILL_RATIO = 0: the spill threshold is determined by statement_mem.

    Decrease MEMORY_SPILL_RATIO to spill earlier and reduce memory pressure under heavy workloads. Increase it to keep more computation in memory for smaller, latency-sensitive workloads.

    Default resource groups

    Important

    • V6.0: The admin_group resource group manages system workloads and reserves 10% CPU and 10% memory. For all other groups (including default_group and any groups you create), the combined CPU allocation cannot exceed 90% and the combined memory allocation cannot exceed 90%. The admin_group and default_group groups cannot be deleted.

    • V7.0: Three default groups exist: admin_group and system_group for system workloads, and default_group for all other users. None of these can be deleted.

    Enable resource group management

    Enabling resource group management restarts the instance, making it unavailable for approximately 5 minutes. Schedule this during off-peak hours.

    Enable via the console (V6.0 V6.6.1.0 or later)

    1. Log in to the AnalyticDB for PostgreSQL console.

    2. In the upper-left corner, select your region.

    3. Click the instance ID.

    4. In the left navigation pane, click Workload Management.

    5. Click Enable Resource Group Management, then click OK.

    After enabling, the system creates a default_group resource group automatically. To view resource group monitoring data, go to Monitoring and Alerts > Instance Monitoring and click the Resource Group Monitoring tab.

    Enable via ticket (V6.0 earlier than V6.6.1.0; all V7.0 instances)

    Submit a ticket to enable or disable resource group management.

    Note For V7.0 instances, you can only execute SQL statements to use resource groups.

    Create a resource group

    You need a privileged database account to create resource groups.

    Create via the console

    1. On the Workload Management page, click Create Resource Group.

    2. In the Resource Group Settings section, configure the parameters, then click Save.

    Create via SQL

    CREATE RESOURCE GROUP <group_name> WITH (group_attribute=value [, ... ]);

    V6.0 example:

    CREATE RESOURCE GROUP etl_group WITH (
    CPU_RATE_LIMIT = 30,
    MEMORY_LIMIT = 20,
    CONCURRENCY = 10,
    MEMORY_SHARED_QUOTA = 70,
    MEMORY_SPILL_RATIO = 20
);

    V7.0 example:

    CREATE RESOURCE GROUP etl_group WITH (
    CPU_MAX_PERCENT = 40,
    CPU_WEIGHT = 100,
    CONCURRENCY = 10,
    MEMORY_LIMIT = 2048,
    MIN_COST = 0
);

    View resource group settings

    View via the console

    On the Workload Management page, the Resource Group Settings section lists all resource groups and their current parameters.

    View via SQL

    SELECT * FROM gp_toolkit.gp_resgroup_config;

    Modify resource group settings

    Modify via the console

    1. On the Workload Management page, click Edit.

    2. Adjust the concurrency, CPU, or memory settings, then click Save.

    Modify via SQL

    Only privileged accounts can modify resource group settings. The admin_group cannot be modified in V6.0; admin_group and system_group cannot be modified in V7.0.

    V6.0:

    ALTER RESOURCE GROUP <group_name>
    SET CONCURRENCY | CPU_RATE_LIMIT | MEMORY_LIMIT | MEMORY_SHARED_QUOTA | MEMORY_SPILL_RATIO <value>;

    V7.0:

    ALTER RESOURCE GROUP <group_name>
    SET CPU_MAX_PERCENT | CPU_WEIGHT | CONCURRENCY | MEMORY_LIMIT | MIN_COST <value>;

    Examples:

    -- Set concurrency to 20 for the test group
ALTER RESOURCE GROUP test SET CONCURRENCY 20;

-- Set CPU limit to 20% for test_pg1 (V6.0)
ALTER RESOURCE GROUP test_pg1 SET CPU_RATE_LIMIT 20;

-- Set CPU cap to 20% for test_pg1 (V7.0)
ALTER RESOURCE GROUP test_pg1 SET CPU_MAX_PERCENT 20;

    Assign database accounts to a resource group

    Each resource group can be associated with multiple database accounts, but each account can belong to only one resource group. Accounts not explicitly assigned fall back to default_group. Privileged accounts default to default_group.

    For details on creating database accounts, see Create a database account.

    Assign via the console

    1. In the Allocate Resource Groups section of the Workload Management page, click the edit icon on the target resource group card.

    2. Select a database account from the Role drop-down list, then click OK.

    Assign via SQL

    Only privileged accounts can associate database accounts with resource groups.

    -- Assign an existing account to a resource group
ALTER ROLE <user_name> RESOURCE GROUP <group_name>;

-- Assign a resource group when creating an account
CREATE ROLE <user_name> WITH LOGIN RESOURCE GROUP <group_name>;

    Delete a resource group

    Delete via the console

    On the Workload Management page, find the resource group you want to remove, click Delete in the Actions column, then click OK.

    Delete via SQL

    Only privileged accounts can delete resource groups. The default system groups cannot be deleted.

    Version Group Deletable
    V6.0 admin_group No
    default_group No
    V7.0 admin_group No
    default_group No
    system_group No 
    DROP RESOURCE GROUP <group_name>;

    Monitor resource groups

    Monitor V6.0 resource groups

    SELECT * FROM gp_toolkit.gp_resgroup_status;
    Field Description
    rsgname Resource group name
    groupid Resource group ID
    num_running Number of running queries
    num_queueing Number of queries currently waiting
    num_queued Total queries queued (historical)
    num_executed Total queries executed
    total_queue_duration Total time queries have spent waiting, in seconds
    cpu_usage CPU utilization per compute node
    memory_used Total memory used, in MB
    memory_available Total memory available, in MB
    memory_quota_used Fixed memory used, in MB
    memory_quota_available Fixed memory available, in MB
    memory_quota_granted Fixed memory allocated, in MB
    memory_shared_used Shared memory used, in MB
    memory_shared_available Shared memory available, in MB
    memory_shared_granted Shared memory allocated, in MB

    Monitor V7.0 resource groups

    Query queue status:

    SELECT * FROM gp_toolkit.gp_resgroup_status;
    Field Description
    groupid Resource group ID
    groupname Resource group name
    num_running Number of running transactions
    num_queueing Number of queuing transactions
    num_queued Number of queued transactions
    num_executed Number of executed transactions
    total_queue_duration Total queue duration, in seconds

    Query CPU and memory usage per compute node:

    SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;
    Field Description
    groupid Resource group ID
    groupname Resource group name
    hostname Compute node hostname
    cpu_usage CPU utilization, in %
    memory_usage Memory used on the compute node, in MB

    Identify queued queries

    To find queries waiting due to concurrency limits, query pg_stat_activity:

    -- V7.0: queued queries show wait_event_type = 'ResourceGroup'
SELECT pid, usename, state, wait_event_type, query
FROM pg_stat_activity
WHERE wait_event_type = 'ResourceGroup';

-- V6.0: queued queries show waiting_reason = 'group'
SELECT pid, usename, state, waiting_reason, query
FROM pg_stat_activity
WHERE waiting_reason = 'group';

    If a large number of queries are waiting in a resource group because the CONCURRENCY limit is reached, increase the concurrency capability of the resource group to ensure query performance.

    Disable resource group management

    Disabling resource group management switches the instance back to resource queue management and restarts the instance, causing approximately 5 minutes of downtime. Schedule this during off-peak hours.

    1. Log in to the AnalyticDB for PostgreSQL console.

    2. In the upper-left corner, select your region.

    3. Click the instance ID.

    4. In the left navigation pane, click Workload Management, then click Disable Resource Group Management.

    What's next