On-demand VW Scaling in the Storage and Compute Decoupling: Cost-Effective Adaptation to Flexible and Changing Service Requirements

Background

As a company grows quickly, scaling databases has become a standard way to handle increased business need. From the long-term perspective, the quantitative scale-out method based on workload trends (as shown in Figure 1) can effectively handle high concurrency and reliably cope with continuous business growth.

Figure 1 Traditional scale-out method

However, in a finer time dimension (for example, by week or day), the traditional scale-out method frequently assigns too many resources to handle varying workloads, leading to inefficiency. As shown in Figure 2, the data mid-end team of a company executes ETL jobs from 00:00 to 06:00 every day to import a large amount of data offline. The workload of the service team, however, fluctuates irregularly. Traditional resource overcommitment leads to many unused compute resources, raising enterprise costs.

Figure 2 Job scenarios under different workloads

An ideal database should automatically scale resources up or down based on workload changes, reducing waste and saving costs (as shown in Figure 3). For efficient and cost-effective scaling, set basic resource specifications for normal workloads and expand them during peak hours to maintain stable performance. DWS clusters with decoupled storage and compute support this on-demand scaling, effectively adapting to load fluctuation in the fine-grained time dimension. In such clusters, the system scales resources promptly when needed, ensuring reliability even during sudden workload spikes.

Figure 3 Elastic scaling with workloads

Primary VWs in the Fixed Resource Pool and Elastic VWs in the Elastic Resource Pool

As shown in Figure 4, a DWS cluster with decoupled storage and compute relies on a fixed resource pool and an elastic resource pool.

• Fixed resource pool:

  There are a set of primary VWs that adjust based on workloads in the fixed resource pool. These VWs act as flexible compute groups and can be bound to different workloads for isolation. The primary VW specifications in the MPP architecture control the limit for how much one SQL query can process and the highest queries per second (QPS) the system can handle. This makes the primary VWs ideal for tasks with steady demand and needs for quick responses. DWS allows you to horizontally scale out VWs by adding nodes to handle growing demands. The database administrator should determine the specifications of the primary VWs early, considering expected future workload changes.

• Elastic resource pool

  The elastic resource pool is applicable to load fluctuation scenarios in the fine-grained time dimension and contains one or more elastic VWs. Elastic VWs decouple storage from compute. The storage layer uses a shared structure based on OBS, while the compute layer uses a shared-nothing structure. This allows compute and storage to scale independently. Adding compute nodes avoids redistributing data, and storage can expand as needed, offering unlimited capacity. Elastic VWs share real-time data with primary VWs. This prevents metadata update delays and ensures elastic VWs use the latest metadata for jobs.

Figure 4 Introduction to VWs

Selecting Manual Scaling or Auto Scaling for VWs

DWS offers manual scaling and auto scaling for different on-demand needs.

• Manual scaling:

  Manual scaling works well if you understand how your workloads change over time. It is ideal for situations where the load changes regularly and large queries happen during these changes, like with periodic batch jobs. In this case, you can manually scale in or out resources based on the known load period pattern as required.

  When configuring manual scaling on the console, you can also select user binding mode and proportional offloading mode.

  • User binding mode: When creating an elastic VW, bind a user as shown in ② in Figure 5. For details, see Scenario 1: Manually Creating Elastic VWs in User Binding Mode to Handle ETL Loads.
  • Proportional offloading mode: When creating an elastic VW, bind a main logical cluster, as shown in ① in Figure 5. You do not bind a main logical cluster or user. For details, see Scenario 2: Manually Creating Elastic VWs to Offload Some Jobs of the Primary VW to the Elastic VWs.

    If you bind a main logical cluster, the elastic VWs created are called dedicated elastic VWs. The database kernel sends jobs from the bound main logical cluster to the dedicated elastic VWs. That is, the dedicated elastic VWs only receive jobs from the bound main logical cluster.

    If you do not select any main logical cluster or a user, the elastic VWs created are called public elastic VWs. The database kernel sends jobs from any main logical cluster to the public elastic VWs. That is, the public elastic VWs can receive jobs from all main logical clusters.

  Figure 5 Manually scaling a VW
  
• Auto scaling:

  Auto scaling works well if you are a beginner or are unfamiliar with your workloads. It operates automatically without manual intervention. You only need to pay by resources. It works well when workloads involve frequent complex queries or sudden spikes lasting minutes to hours. The system detects high demand and scales out resources instantly. As demand drops, it scales in resources automatically, maintaining stable performance and efficient resource use.

  For details, see Scenario 3: Automatically Creating Elastic VWs.

Constraints

• The function is available only for decoupled storage-compute clusters of version 9.1.1.100 or later.
• To create elastic VWs, disable fine-grained snapshots first. For details, see Creating and Managing a DWS Snapshot.

Scenario 1: Manually Creating Elastic VWs in User Binding Mode to Handle ETL Loads

As shown in Figure 6, the data middle-end team of the company performs ETL operations from 00:00 to 06:00 every day. user1 and user2 need to import a large amount of offline data. The ETL workload grows in two phases.

DWS uses the elastic VW1 by 00:00 for the initial workload increase. When the workload rises again at 04:00, DWS activates elastic VW2 as planned to handle the second surge.

The data import lasts until 06:00. DWS periodically reclaims the two manual elastic VWs. You only pay extra fees for the period from 00:00 to 06:00, instead of over-allocating resources to the primary VW, reducing costs.

Figure 6 Manually scaling VWs

1. Create a DWS cluster with decoupled storage and compute and create users user1 and user2. For details, see Creating a DWS Storage-Compute Decoupled Cluster.
2. Log in to the DWS console. In the navigation pane, choose Dedicated Clusters > Clusters.
3. In the cluster list, click the name of the target cluster to go to the Cluster Information page.
4. On the upper navigation pane, choose Logical Clusters. On the displayed page, click Add Plan.
5. Set the following parameters to create VW1 and bind it to user1.

   Table 1 Parameter description

   Parameter	Value
   Plan Type	Periodic
   Cluster Name	VW1
   Bind Main Logical Cluster	Deselect
   Bind User	user1
   Nodes	3
   Time Range	Select the ETL execution period, for example, 1 to 3 months.
   Period Type	Every Week
   Time Configuration (UTC)	Create Logical Cluster Before: Select all, 00:00 Delete Logical Cluster After: Select all, 06:00

   Figure 7 Creating VW1 and binding it to a user
   

6. Repeat 5 to create VW2 and bind it to user2. Set Time Configuration (UTC) as follows:
   • Create Logical Cluster Before: Select all, 04:00
   • Delete Logical Cluster After: Select all, 06:00

7. To query the binding relationship between a user and VW, query the nodegroup column of pg_user.
   1. View the elastic VW bound to the user.

      1	SELECT usename, nodegroup from pg_user where usename= 'user1';

   2. View the user bound to the elastic VW.

      1	SELECT usename, nodegroup from pg_user where nodegroup='VW1';

After the preceding steps are performed, the jobs executed by user1 are completed in VW1 from 00:00 to 06:00. At 04:00 to 06:00, the jobs executed by user2 are completed in VW2.

After 06:00, VW1 and VW2 are automatically deleted to reduce costs.

Figure 8 User binding mode

Scenario 2: Manually Creating Elastic VWs to Offload Some Jobs of the Primary VW to the Elastic VWs

The following still uses the data middle-end team as an example to describe the proportional offloading mode. When creating an elastic VW, do not bind it to a user. Instead, bind it to the main logical cluster.

user1 is bound to the primary VW v3_logical, and user2 is bound to the primary VW v3_logical_2. The jobs submitted by the two users include both ETL loads and DML operations.

From 00:00 to 06:00 every day, the ETL load increases. During creating the VW1, set the VW1 to be created at 00:00 every day and deleted at 06:00 every day, and set Bind Main Logical Cluster to v3_logical.

1. Create a DWS cluster with decoupled storage and compute. For details, see Creating a DWS Storage-Compute Decoupled Cluster.
2. Add the second primary VW and name it v3_logical_2.
   1. Log in to the DWS console. In the navigation pane, choose Dedicated Clusters > Clusters.
   2. In the cluster list, click the name of the target cluster to go to the Cluster Information page.
   3. On the upper navigation pane, choose Logical Clusters. On the displayed page, click Add Logical Cluster.
   4. Add the primary VW v3_logical_2. If no node is available, add a node.

3. Create user1 and user2 and bind them to v3_logical and v3_logical_2, respectively.

   1 2	CREATE USER user1 NODE GROUP "v3_logical" PASSWORD '{password}'; CREATE USER user2 NODE GROUP "v3_logical_2" PASSWORD '{password}';

4. Return to the Logical Clusters page and click Add Plan.
5. Create VW1 and bind it to v3_logical. In this case, VW1 is a dedicated elastic VW.

   Table 2 Parameter description

   Parameter	Value
   Plan Type	Periodic
   Cluster Name	VW1
   Bind Main Logical Cluster	v3_logical
   Bind User	Deselect
   Nodes	3
   Time Range	Select the ETL execution period, for example, 1 to 3 months.
   Period Type	Every Week
   Time Configuration (UTC)	Create Logical Cluster Before: Select all, 00:00 Delete Logical Cluster After: Select all, 06:00

   Figure 9 Creating VW1 and binding it to a main logical cluster
   

6. Run the SQL statements above to configure the dedicated offloading strategy. The offloading rate is 60%, that is, the primary VW offloads 60% of the jobs to the dedicated elastic VW1, as shown in Figure 10.

   ALTER NODE GROUP v3_logical SET offloading_strategy TO dedicated; 
   ALTER NODE GROUP v3_logical SET offloading_rate TO 60;

   Table 3 ALTER NODE GROUP parameters

   Parameter	Description	Value Range
   groupname	Node group to be renamed	Valid node group name
   SET PARENT_NAME TO parent_group_name	Name of the classic logical cluster corresponding to the elastic logical cluster. This parameter is available only for elastic concurrency expansion.	To name a logical cluster, enclose the name in quotation marks. The double quotation marks indicate that the cluster name is case sensitive.
   SET [NOT] ELASTIC	This parameter is available only for automatic workload distribution of manual scaling. NOT ELASTIC indicates that the elastic logical cluster does not belong to the elastic resource pool. ELASTIC indicates that the elastic logical cluster belongs to the elastic resource pool.	-
   SET OFFLOADING_STRATEGY TO new_offloading_strategy	This parameter is available only for automatic workload distribution of manual scaling and must be used together with SET OFFLOADING_RATE. This parameter specifies the offloading policy of the primary logical cluster. This parameter is available only for clusters of version 9.1.1.100 or later. You can set an elastic logical cluster to a dedicated elastic logical cluster by specifying PARENT_NAME and ELASTIC. For example, if there is the primary logical cluster v3_logical and the elastic logical cluster computing_group1 in a cluster, you can run ALTER NODE GROUP computing_group1 SET ELASTIC and ALTER NODE GROUP computing_group1 SET PARENT_NAME TO "v3_logical" to set computing_group1 as the dedicated elastic logical cluster of v3_logical. It means that computing_group1 executes only the workloads from the primary logical cluster v3_logical. To set a public elastic logical cluster, you only need to specify ELASTIC. PARENT_NAME is not required. The workloads executed by a public elastic logical cluster can come from any primary logical cluster in a cluster.	None, Dedicated, or Elastic None: The workloads of the primary logic cluster are completed by itself and will not be unloaded. dedicated: The primary logical cluster routes some workloads to dedicated elastic logical clusters. The unloading ratio is controlled by the offloading_rate parameter. elastic: The primary logical cluster routes some loads to dedicated and public elastic logical clusters. The unloading ratio is controlled by the offloading_rate parameter.
   SET OFFLOADING_RATE TO value	This parameter is available only for automatic workload distribution of manual scaling and must be used together with SET OFFLOADING_STRATEGY. This parameter controls the offloading percentage of the primary logical cluster. This parameter is available only for clusters of version 9.1.1.100 or later. SET OFFLOADING_STRATEGY and SET OFFLOADING_RATE are used together to enable automatic workload distribution of manual scaling. For example, there is the primary logical cluster v3_logical, the dedicated elastic logical cluster computing_group1, and the public elastic logical cluster computing_group2 in a cluster. If OFFLOADING_STRATEGY is set to dedicated and OFFLOADING_RATE is set to 40 for v3_logical, 40% of the workloads of v3_logical can be routed to the dedicated elastic logical cluster computing_group1 for execution. If OFFLOADING_STRATEGY is set to elastic and OFFLOADING_RATE is set to 40 for v3_logical, 40% of the workloads of v3_logical can be routed to the dedicated elastic logical cluster computing_group1 and public elastic logical cluster computing_group2 for execution based on a round-robin policy.	0 to 100 The offloading percentage may not reach the value of OFFLOADING_RATE. The reasons are as follows: Automatic workload distribution applies only to the DML workloads of all V3 tables. To ensure the execution performance of simple workloads (the planned cost of workloads is less than vw_offloading_min_cost), the workloads are executed in the primary logical cluster even though they are executed in the elastic logical clusters.

   Figure 10 Dedicated elastic VW1
   

7. Create the elastic VW2 to process jobs of both user1 and user2. Because the job from user1 exceeded the capacity of a single elastic VW and the job from user2 needed to be offloaded to the elastic VW.

   Creating two more VWs would be expensive.

   Select the elastic offloading strategy.

   1. Create elastic VW2 by referring to 4 to 5. However, do not bind the VW to any user or main logical cluster. That is, VW2 is a public elastic VW.
   2. Run the following DDL statements to change the offloading strategy of the primary VWs v3_logical and v3_logical_2 to elastic. That is, jobs of the primary VWs can be offloaded to the dedicated elastic VW and public elastic VW.

      1 2 3 4

      ALTER NODE GROUP v3_logical set offloading_strategy TO elastic; ALTER NODE GROUP v3_logical_2 set offloading_strategy TO elastic; ALTER NODE GROUP v3_logical set offloading_rate TO 60; ALTER NODE GROUP v3_logical_2 set offloading_rate TO 20;

   After the setting, as shown in Figure 11, 40% of user1's jobs run on v3_logical. The rest, 60%, are split evenly: 30% on the dedicated elastic VW1 and 30% on the public elastic VW2. user2 submits 80% of their jobs to v3_logical_2, and 20% to the public elastic VW2.

   That is, the green part in the figure is the public elastic VW, and some jobs of user1 and user2 are offloaded to the VW for execution.

   Figure 11 Public elastic VW2
   

Scenario 3: Automatically Creating Elastic VWs

Service environments often face unpredictable workloads rather than fixed patterns. Manual scaling is not effective for these random loads since users cannot anticipate when to create or reclaim elastic VWs.

To solve this, auto scaling for elastic VWs was introduced. It intelligently manages elastic VWs using a passive trigger system to process random workloads. The system monitors job queues in real-time. During sudden workload spikes (as shown in Figure 12), it creates additional VWs to handle the increased compute demands, ensuring smooth operation and boosting cluster performance. Once the workload returns to normal levels, the system reclaims the excess VWs to avoid unnecessary costs.

The system automatically scales resources without manual input. Auto scaling cuts costs, boosts efficiency by dynamically adjusting resources, improves cluster throughput, and maintains stable performance during workload changes. It is ideal choice to handle unpredictable workloads.

Figure 12 Automatically scaling VWs

As shown in Figure 13, workloads show clear spikes but lack consistent patterns.

The system creates VW1 at t1 and reclaims it at t2.

During the next workload spike, the peak workload increases significantly. Creating VW1 at t3 is not enough, so the system creates VW2 to handle the demand. Once the load drops, the system reclaims VW2 at t5 and VW1 at t6.

This entire process runs automatically. The system directs burst workloads to the elastic VWs without requiring manual intervention.

Figure 13 Significant load fluctuation

• DWS makes an elastic VW with the same number of nodes as the main VW. You cannot change this setting. Creating a VW takes around 4 to 5 minutes. Short-term job queues might delay the process. To avoid issues, keep cluster job queues over 10 minutes.
• The default auto scaling rules are as follows, but you can modify them on the management console. For details, see GUC Parameters Related to Auto Scaling of VWs.
  • The system creates an elastic VW when over 10 jobs queue in the primary VW.
  • The system creates up to five elastic VWs per primary VW.
  • Each elastic VW handles up to 60 jobs at once.
  • If any elastic VWs stay idle for over 5 minutes, the system will automatically reclaim them.

1. Create a DWS cluster with decoupled storage and compute. For details, see Creating a DWS Storage-Compute Decoupled Cluster.
2. Log in to the DWS console. In the navigation pane, choose Dedicated Clusters > Clusters.
3. In the cluster list, click the name of the target cluster to go to the Cluster Information page.
4. On the upper navigation pane, choose Logical Clusters and enable Auto Resiliency Switch.
   Figure 14 Enabling auto resiliency switch
   

5. Run SQL statements to create and set a resource pool, and bind a user to the resource pool, and enable auto scaling for the specified resource pool. In the command, poolg indicates the resource pool name, which can be set as needed.
   The GUC parameter enable_concurrency_scaling controls whether to enable auto scaling for the resource pool.

   1 2 3 4 5 6 7 8

   ---Create a new job resource pool or reuse an existing resource pool. CREATE RESOURCE POOL "poolg" WITH(nodegroup = 'v3_logical'); ---Bind the resource pool to a user. ALTER USER "user1" WITH RESOURCE POOL "poolg"; ---Enable auto scaling for the resource pool. ALTER RESOURCE POOL "poolg" WITH (enable_concurrency_scaling=true);

GUC Parameters Related to Auto Scaling of VWs

You can modify the following GUC parameters related to auto scaling of VWs on the console. For details, see Modifying GUC Parameters of the DWS Cluster.

• concurrency_scaling_max_idle_time: Specifies the maximum idle time of an elastic VW. If the idle time exceeds the value of this parameter, the VW will be automatically reclaimed. The default value is 5 minutes.
• concurrency_scaling_limit_per_main_vw: Specifies the maximum number of elastic VWs that can be automatically created for each primary VW. The default value is 5.
• concurrency_scaling_max_vw_active_statements: Specifies the maximum number of concurrent jobs that can be executed by each elastic VW. The default value is 60.
• concurrency_scaling_max_waiting_statements: Specifies the maximum number of jobs that can be queued in a primary VW. If the number of jobs exceeds the value of this parameter, an elastic VW is automatically created. The default value is 10.

Documentation

• Elastically Adding or Deleting a Logical Cluster