Why auto-scaling analytical databases aren’t so magical

This blog post was authored by Steve Sarsfield.

There is a new feature in analytical databases that seems to be all the rage, particular in cloud data warehouse – Autoscaling. Autoscaling’s promise is that if you have a particularly hard analytical workload, autoscaling will spin up new storage and compute to get the job done. If I have a heavy workload, new resources will easily spin up to meet the level of service that you need, and then automatically scale back down when they are not needed.

In the cloud databases I’ve evaluated, autoscaling seems to be implemented instead of tuning. Since the database automatically scales, they will tell you that tuning (or maintenance) becomes less critical. The old tried and true way of handling increased or chaotic workloads is having an enterprise architect look at the workload and design an optimal strategy for delivering it. This involves designing the right schemas, tuning individual queries with features like indexes, materialized views and projections. The architect might leverage a system that can sense long-running queries and assign difference resources (memory, cpu, etc) so that long-running queries don’t notably impact daily analytics. The newer autoscaling databases don’t offer much in tuning. After all, you can just autoscale when things get slow.

The Daily Scenario

To understand the difference, let’s look at what happens when an analyst formulates a particularly gnarly query that begins to chew up resources and database time? This may be a mistake, but often an analyst is exploring the data without really understanding schemas, optimizations or how the query will impact the system. An analyst has an idea and wants to know something new, but since it’s a new idea, the query might be full of JOINs and other nasties that impede performance.

In an autoscaling scenario, the database would ask for more resources to come online. It simply spins up extra nodes, at additional cost, to meet the needs of the query. Many of the license agreements for the auto-tune have you on the hook for this additional cost for at least 30 days. Even if you realize your mistake, the resources are locked in for that time.

In a tuning scenario, the resources and costs would stay the same, but the new query would be demoted as a second class citizen, asked to complete as a background task, slowly completing when the database was not too busy.

Making the choice

If given a hard choice, I think most companies would want to keep the tuning scenario for the best fiscal responsibility. If the analyst finds something good that needs to be part of the daily analytics, it’s possible that in the tuning scenario, resources would never need to be increased. The enterprise architect would create some optimizations to boost the query. In the autoscaling scenario, the new query would require additional cost and additional resources.

Of course, if tuning runs its course, then it’s OK to bring in new nodes. In any sitation, this should be easy. It should also be easy to leverage any on-premise nodes, cloud nodes or even Hadoop nodes if there are spare cycles. The questions become: Should this be automatic? Should it be at the exclusion of tuning, also called “maintenance” by some vendors?

The future should consider both

Analytical systems should devise ways to optimize query costs across both tuning and autoscaling. If enterprise architects could easily see the impact of how these two processes work together to provide the ultimate, low-cost analytics. Yes, you do want to be able to scale at-will, but you also should consider tuning and the differences in costs.