Google improved BigQuery Workload Management

Better Autoscaling and Capacity Management

Photo by Kevin Wang on Unsplash

Scaling and capacity management is a very important feature of (SaaS) Data Warehouse — here Google makes BigQuery now even more powerful.

Google updated the workload management which now provides the following benefits[1][2]:

• The autoscaler now scales up immediately.
• The autoscaler now scales more precisely.
• The autoscaler scales to the nearest multiple of 50 slots, instead of 100.
• You can now purchase capacity commitments, set baseline slots, and set autoscale max slots in incremental steps of 50 slots.
• If one minute or more has passed since the most recent increase in capacity, you can now reduce capacity without resetting the one minute minimum. This allows for multiple consecutive decreases without a one minute delay between them.

The cool thing is that Google made these new features already generally available so you can use them for productive environments.

Using the Slot Recommender in BigQuery

When using Autoscaling here also some best practices you should consider[2]:

• When first using the autoscaler, set the number of autoscaling slots based on past and expected performance. After creating the reservation, monitor the failure rate, performance, and billing, and adjust the number of autoscaling slots as needed.
• The autoscaler has a minimum scaling-down period of 1 minute. Therefore, it’s crucial to set the maximum number of autoscaling slots to balance performance and cost.
• Occasionally, slot usage may exceed the sum of your baseline and scaled slots, but you are not billed for usage beyond this amount.
• The autoscaler is most efficient for heavy, long-running workloads with multiple concurrent queries. Avoid sending queries one at a time, as each query triggers scaling for a 1-minute minimum.

BigQuery now supporting Query Queues

Sources and Further Readings

[1] Google, BigQuery release notes (2024)

[2] Google, Introduction to slots autoscaling (2024)