Hologres:Automatic throttling for query queue (beta)

Use cases

Use automatic throttling when the following conditions apply:

• High-concurrency, similar-sized queries: When many queries run concurrently and each request consumes comparable resources, automatic throttling keeps CPU utilization stable below 100%.

  If baseline concurrency is already low, dynamic adjustments may cause CPU usage to oscillate in a sawtooth pattern, reducing efficiency. In extreme cases — such as a single large query monopolizing the compute group — automatic throttling has no effect.

• Acceptable queue wait time: Queries that exceed the concurrency cap are held in the queue. Enable this feature only if some increase in response time is tolerable.

• Unpredictable traffic spikes: When you cannot anticipate sudden surges in query volume or pre-provision additional compute resources, automatic throttling reduces stability risks without manual intervention.

Prerequisites

Before you begin, make sure you have:

• A query queue. See Query queue.

• A compute group instance running Hologres V3.1 or later. General-purpose instances do not support this feature.

  To use this feature on a general-purpose instance, first convert it to a compute group instance. See Convert a general-purpose instance to a compute group instance.

Usage notes

• Automatic throttling only applies to queries routed through a query queue. Affected engine types: HQE, PQE, SQE, and HiveQE. Affected query types: SELECT, INSERT, UPDATE, DELETE, and INSERT statements generated by COPY and CTAS.

• Queries that use a fixed plan bypass the query queue and are not subject to concurrency limits from automatic throttling. See Accelerate SQL execution with fixed plan. To isolate fixed-plan queries from other workloads, assign them to a dedicated compute group. See Quick start for compute group instances.

Configure automatic throttling

Configure automatic throttling at the compute group level first, then optionally override it at the query queue level.

-- Enable automatic throttling at the compute group level
CALL hg_set_warehouse_adaptive_concurrency_limiting ('<warehouse_name>', true);

-- Disable automatic throttling at the compute group level
CALL hg_set_warehouse_adaptive_concurrency_limiting ('<warehouse_name>', false);

-- Enable automatic throttling for a query queue (enabled by default when compute group level is on)
CALL hg_set_query_queue_property ('<warehouse_name>', '<query_queue_name>', 'enable_adaptive_concurrency_limiting', 'true');

-- Disable automatic throttling for a query queue (exempts this queue from throttling)
CALL hg_set_query_queue_property ('<warehouse_name>', '<query_queue_name>', 'enable_adaptive_concurrency_limiting', 'false');

-- Set the minimum concurrency floor (default: 1, minimum: 1)
CALL hg_set_query_queue_property ('<warehouse_name>', '<query_queue_name>', 'adaptive_concurrency_limiting_min_concurrency', '<min_concurrency>');

Monitor automatic throttling

In the Hologres Management Console, click your instance ID and go to the Monitoring Information page. View the Compute Group CPU Utilization (%) metric to observe how automatic throttling adjusts concurrency in response to load changes.

Example

This example uses pgbench, the native benchmarking tool of PostgreSQL, to show the effect of enabling and disabling automatic throttling during a sustained load test.

1. Create test tables and load data:

   CREATE TABLE tbl_1 (col1 INT, col2 INT, col3 TEXT);
   CREATE TABLE tbl_2 (col1 INT, col2 INT, col3 TEXT);
   INSERT INTO tbl_1 SELECT i, i+1, md5(RANDOM()::TEXT) FROM GENERATE_SERIES (0, 500000) AS i;
   INSERT INTO tbl_2 SELECT i, i+1, md5(RANDOM()::TEXT) FROM GENERATE_SERIES (0, 500000) AS i;

2. Set the maximum concurrency for the query queue to 100. This example uses the init_warehouse compute group and the default_queue query queue:

   CALL hg_set_query_queue_property ('init_warehouse', 'default_queue', 'max_concurrency', '100');

3. In the bin directory of the pgbench client, create a benchmark SQL file named select.sql with the following statement:

   EXPLAIN ANALYZE SELECT * FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.col3 = tbl_2.col3 ORDER BY 1;

4. Set your password as an environment variable in the server configuration file:

   export PGPASSWORD='<AccessKey_Secret>'

5. From the bin directory of the pgbench client, run the benchmark:

   ./pgbench \
     -c 30 \
     -j 30 \
     -f select.sql \
     -d <Database> \
     -U <AccessKey_ID> \
     -h <Endpoint> \
     -p <Port> \
     -T 1800

   For connection parameter details, see Connect to Hologres and develop.

6. While the benchmark runs, toggle automatic throttling and observe CPU utilization in the Hologres Management Console:

   -- Enable automatic throttling
   CALL hg_set_warehouse_adaptive_concurrency_limiting ('init_warehouse', true);
   
   -- Disable automatic throttling
   CALL hg_set_warehouse_adaptive_concurrency_limiting ('init_warehouse', false);

Results:

With automatic throttling enabled, the system builds a short queue (~17 pending queries) and stabilizes CPU at ~80% — significantly reducing the risk of resource exhaustion compared to running at 100% with no throttling.

What's next

• Query queue

• Quick start for compute group instances

• Serverless Computing user guide