Hologres:Manage virtual warehouses

Scale up (vertical scaling)

Scaling up increases the per-cluster CU allocation. The instance must have enough unallocated resources before you scale up a virtual warehouse. Scale out the instance first if needed.

Impact on running queries and writes

Currently executing queries and writes are not affected immediately when a scale-up starts. New resources become available only after provisioning completes.

• Starting from V3.0.28: queries and writes are not interrupted during scale-up. Flink tasks may fail and retry.

• Before V3.0.28: after the instance is scaled out, the system starts new pods, but these pods do not load shard metadata until rebalancing completes. Rebalancing interrupts writes for approximately 15 seconds.

Multi-row DML transactions are interrupted during scale-up regardless of version. This includes transactions with hg_experimental_enable_transaction enabled, native INSERT OVERWRITE statements, and incremental refreshes of Dynamic Tables.

Rebalancing after scale-up

• Starting from V2.2: the system automatically rebalances when a virtual warehouse has empty pods. No manual rebalancing is needed.

• Before V2.2: manually trigger rebalancing after scale-up.

-- Scale up the warehouse
CALL hg_alter_warehouse ('read_warehouse_1', 64);

-- Wait for the warehouse to be running
SELECT wait_until_warehouse_running ('read_warehouse_1');

-- Redistribute shards
SELECT hg_rebalance_warehouse ('read_warehouse_1');

-- Wait for rebalancing to complete
SELECT hg_wait_until_rebalance_warehouse_done ('read_warehouse_1');

Scale down (vertical scaling)

Scaling down reduces the per-cluster CU allocation. The system performs shard rebalancing first to migrate shards off the pods being taken offline, then removes those pods. Released resources return to the instance's unallocated resource pool.

Impact on running queries and writes

• Starting from V3.1 (Beta): major queries and writes are not interrupted. Connections on the pods being taken offline experience a transient disconnection of a few seconds. Flink tasks may fail and retry. If a large query or write is running on the virtual warehouse when the scale-down is triggered and needs to continue for more than 60 minutes, it will error 60 minutes after scale-down starts.

• Before V3.1: queries and writes are interrupted for approximately 15 seconds during rebalancing.

Multi-row DML transactions are interrupted during scale-down regardless of version. This includes transactions with hg_experimental_enable_transaction enabled, native INSERT OVERWRITE statements, and incremental refreshes of Dynamic Tables.

After scale-down, use the freed resources to create new virtual warehouses, scale up other warehouses, or scale in the instance to release them.

Scale out (horizontal scaling)

Scaling out adds more clusters to the virtual warehouse. The same usage notes as scale-up apply.

Scale in (horizontal scaling)

Scaling in reduces the number of clusters in the virtual warehouse. The same usage notes as scale-down apply.

View virtual warehouses

-- View all virtual warehouses in the current instance
SELECT * FROM hologres.hg_warehouses;

-- View the virtual warehouse currently in use
SELECT current_warehouse();

The hologres.hg_warehouses system table contains the following columns:

Stop a virtual warehouse

You cannot stop the default virtual warehouse (init_warehouse) or a primary virtual warehouse whose table group has a secondary virtual warehouse.

Stopping is asynchronous. Use hg_get_warehouse_status to verify the warehouse has stopped.

CALL hg_suspend_warehouse ('<warehouse_name>');

Resume a virtual warehouse

Resuming a virtual warehouse is asynchronous. Use hg_get_warehouse_status to verify the warehouse is running before sending queries.

CALL hg_resume_warehouse ('<warehouse_name>');

Restart a virtual warehouse

Restarting a virtual warehouse is asynchronous. Use hg_get_warehouse_status to verify the warehouse is running before sending queries.

CALL hg_restart_warehouse('<warehouse_name>');

Check virtual warehouse status

All create, scale, stop, resume, and restart operations are asynchronous. Use the following functions to monitor status.

-- Get the current status of a virtual warehouse
select hg_get_warehouse_status('<warehouse_name>');

To block until a virtual warehouse reaches the Running state, use wait_until_warehouse_running:

select wait_until_warehouse_running('<warehouse_name>', <timeout_seconds>);

Example: Scale up warehouse_1 before a data load, then scale it back down.

-- Scale up to 64 CUs
CALL hg_alter_warehouse ('warehouse_1', 64);

-- Wait until running
SELECT wait_until_warehouse_running('warehouse_1');

-- Run the data load
INSERT INTO res_data SELECT * FROM org_data;

-- Scale back down to 32 CUs
CALL hg_alter_warehouse ('warehouse_1', 32);

Rename a virtual warehouse

CALL hg_rename_warehouse('<org_warehouse_name>', '<new_warehouse_name>');

Rebalance a virtual warehouse

Worker nodes in a virtual warehouse evenly load shard metadata under normal conditions. After fast recovery or scale-out, shards may become unevenly distributed. Trigger a rebalance to redistribute shards so each worker node loads an approximately equal number.

• Starting from V2.1: the system automatically triggers rebalancing when a warehouse has empty worker nodes.

• Starting from V2.2: SELECT queries that run during rebalancing are automatically retried, preventing errors such as ERROR: fail to execute query internal error: Get rundown is not allowed in recovering state..

A rebalance operation typically takes 2–3 minutes, depending on the number of table groups. Write operations are interrupted for approximately 15 seconds during rebalancing.

Syntax

SELECT hg_rebalance_warehouse ('<warehouse_name>');

Return values

Rebalancing is balanced when the difference in the number of shards loaded by each worker node is 1 or fewer. For example:

• 2 workers, 2 shards: each worker loads 1 shard.

• 2 workers, 3 shards: one worker loads 1 shard and the other loads 2 shards.

Because rebalancing is asynchronous, monitor progress with:

SELECT hg_get_rebalance_warehouse_status('<warehouse_name>');

DOING means rebalancing is in progress. DONE means it is complete.

Set the statement timeout for a virtual warehouse

Virtual warehouse-level timeout has lower priority than session-level and database-level configurations. To make the warehouse-level setting effective, first reset the database-level configuration.

-- Reset the database-level setting
ALTER DATABASE <db_name> RESET statement_timeout;

-- Set the timeout for the virtual warehouse (value in milliseconds)
CALL hg_alter_warehouse ('<warehouse_name>', <cu>, '{"configs":{"gucs":{"statement_timeout":"600000"}}}');

For more information about statement_timeout and other GUC parameters, see GUC parameters reference.