The graph nobody is watching

#softwareengineering #database #devops #monitoring

If you ask me what part of the system I protect the most, the answer is the database.

I've been writing software alone for twenty-four years, and across every platform I've built, the rule has stayed the same: the web servers can take whatever you throw at them, the batches can be rebuilt, but the database has to stay idle on purpose. Not because I love idle databases, but because the day a database actually starts to struggle is a day with very few good options.

This article is about what "keep the database idle on purpose" actually means in practice, and about one particular kind of graph that, in my experience, almost nobody is watching.

The three layers and what each of them gets

I think of a production system as having three tiers, and each tier gets a different rule.

The web server tier can be horizontally scaled. If load grows, you add machines. If something is wrong, you take a machine out of the pool, and the others handle it. Failures here are visible immediately, and they're cheap to recover from.

The batch server tier can be scaled up or out depending on the work. A batch that's too slow can be split. A batch that crashes can be retried. End users don't see batch servers, so a stuck batch is a problem for me and not for them. Some headroom up here is fine.

The database tier is the one I treat completely differently. The database is not where you absorb load. The database is what you protect from load. The reason is simple: the other tiers can be rebuilt or re-scaled. The database is the irreplaceable record. If it slows down, everything slows down. If it falls over, you don't have many minutes before the rest of the stack notices.

So my rule for the database is: keep it idle. Not idle in the sense of "doing nothing." Idle in the sense of "running well below its capacity, at all times, so that any extra load it picks up has somewhere to go."

For more than a decade I ran a large appliance-grade database where I kept the load average below 1 at all times. Not as a target. As a fact. If the load average went up, that was the signal that something had changed in the application and I needed to find it before the database told me about it.

How I keep it idle

A few habits, repeated for decades.

Aggregate periodically, not on demand. When an application needs a daily total, a monthly summary, a yearly count, the wrong thing is to compute it at the moment of the request. The right thing is to compute it ahead of time, on a schedule, into a summary table the application can read from cheaply. If the summary needs to be refreshed every minute, that's fine — a per-minute aggregation against a well-indexed working set is a small, predictable cost. An on-demand aggregation against the full source table is a large, unpredictable cost, and it scales with data growth in a way you don't want.

Cut joins dynamically. When a query joins many tables but a particular filter condition makes some of those tables redundant, the query construction layer can skip them. The fewer tables in the join, the less work for the planner and the executor. This kind of work is invisible to the application engineer — it lives in the layer that builds the SQL — but it pays for itself many times over the lifetime of the system.

Refuse to optimize the optimization. Once, an infrastructure engineer suggested running an enterprise database optimization dashboard against my database, to surface query-level improvement candidates. The pitch was that with more compute and storage capacity, we could push queries to run much faster. I declined.

The headline reason was that the database wasn't there only to serve analytical queries — its primary job was to keep the user-facing OLTP layer responsive, which meant the spare capacity I was carrying was a buffer for user load, not a budget to be spent on faster reports. The secondary reason was that the optimization process itself would have consumed CPU on the database, and the database is the one place where extra processes are not free. The database I had built was already running below LA 1. There was nothing to optimize at that level. Adding optimization itself would only have added load.

These three habits are not clever. They don't require special tools. They require the willingness to put the database first in design decisions, every time, even when the application engineer's instinct is to do otherwise.

The graph nobody is watching

Here's the part of this that I think is genuinely under-discussed.

I've been looking lately at two production database graphs, from two different services I'm involved with. Both graphs cover the last several months. Both are showing the kind of metric — read-row-count from random access — that tracks how much physical work the database is being asked to do.

Service A's graph is bumpy. Most of the time it sits near zero. Several times a week, there's a spike — 100,000 reads, sometimes more, in a short burst. The spikes are predictable in shape: they happen when a dashboard somewhere runs an on-demand aggregation. The fix would be to move that aggregation off the live database and onto a summary table refreshed periodically, or onto a separate analytics store. The shape is alarming on a single graph, but the architecture explains it.

Service B's graph is the opposite, and that's what makes it the more dangerous of the two. It's not bumpy. It's a slow, steady upward slope. Several months ago the line sat around 12,000. Today it sits around 18,000. Usage is not growing — in fact, the user count for this service has been declining over the same window.

There's no spike to point at. There's no incident to investigate. There's no alert that has fired, because no threshold has been crossed. There's only a slope.

This is what I mean by "the graph nobody is watching." Spikes get attention. Sudden failures get attention. A gradual upward slope, on a metric most teams don't even look at, while usage is flat or declining — this gets no attention at all. And yet it is, in my reading, the more serious signal. Something inside the system is doing more physical work to serve a smaller number of users. The application has degraded silently, in a way the dashboard isn't designed to detect.

The hard part is that the only way to spot this is to look at the graph carefully, over a long enough window that the slope can become visible. A glance at last week's numbers tells you nothing. A glance at last month's numbers tells you very little. The kind of degradation I'm describing only resolves into a recognizable shape when you've been watching the same metric over an extended period — long enough that small monthly differences become a slope.

The asymmetry of upward slopes

Not every upward slope is dangerous. Disk usage climbs because logs accumulate — explainable, dismissable. Connection counts climb because a new client integration came online — explainable, dismissable. Some upward slopes have a story behind them, and the story is fine.

The dangerous upward slopes are the ones without a story. A database doing more work for fewer users has no story that's good. Either the data model has grown in a way the queries weren't designed for, or some piece of code is doing many more operations per request than it used to, or some background job has multiplied without anyone noticing. None of these have an alert attached. All of them are visible only as a slope on a graph that someone has to be looking at.

Twenty-four years of running databases has taught me that the alert thresholds are not the boundary between "fine" and "in trouble." The alert thresholds are the boundary between "I can keep ignoring this" and "I have to act now." There's a whole region below the threshold that contains all the early warnings, and you only see that region if you go looking for it.

Closing

The database is the one component in my stack that I won't let degrade. Everything else exists, in part, to keep the database from being asked to do too much. The web tier absorbs the user. The batch tier absorbs the work that doesn't have to be live. The aggregation layers absorb the queries that would otherwise hit the source tables. The dynamic-join construction absorbs the cost of joins that don't need to happen. All of this exists so that the database can stay below LA 1, all day, every day, for years at a time.

When I look at a system someone else built and the database is the part that surprises me, I read it as a sign that the surrounding layers haven't been doing their job. The database telling you it's tired is a late signal. The graph nobody is watching is an earlier one.

This is not what you should do. This is what twenty-four years has taught one specific person to do.

Built with Claude (Opus).

Earlier in this series: