When the Root Cause Hides in the Index
Two incidents, one avoidable cause
In recent weeks our team sat on two almost identical business-impacting incidents. In both, a planned change rebuilt an index in the wrong order, and the database lost the index its busiest queries depended on.
Both incidents were avoidable. Both were detected early. And in both, the monitoring pointed everyone at the wrong place for far too long. Different tools, suggesting they might have had a possible root cause.
This post explains why that happens, and what we built in Dynatrace to close the gap: a workflow that cross-checks the evidence the way an experienced DBA would.
What everyone saw: symptoms, everywhere
The two incidents happened at different customers, running different tooling. Each tool did its job and raised something early. None of them named the cause.
Here is what the alerts and dashboards showed:
Frontends slowed and then timed out. User-facing response times climbed, then requests started failing.
App servers ran out of pooled connections. Each query held its connection longer, a backlog built, and the pools drained.
Exceptions piled up. Timeouts, pool exhaustion errors and retries filled the logs across several services.
A ticket changed status. One tool noticed a change ticket moving and flagged it as a possible factor, but couldn’t say how it related to the slowdown.
Every one of those signals was true. Each was a downstream effect of one missing index. Teams chased the application tier, the connection pools and the network before anyone looked at the database’s query plans.
Why the tools missed it
Most monitoring tools are built to spot symptoms, not to reason about database changes. Three gaps made these incidents hard to diagnose.
They don’t watch index health. Out-of-the-box monitoring rarely tracks index bloat, missing indexes or a sudden change in how queries are executed. When an index disappears or becomes unusable, the first visible effect is slower queries. By then the alert says “slow”, not “index”.
They don’t understand the order of a change. A change ticket can be approved, executed and closed with every step present but in the wrong sequence. For example, the old index is dropped before its replacement is built and validated. No tool we saw checks whether a change was done in a safe order.
They judge each signal on its own. A CPU threshold, a latency threshold and a ticket status are each evaluated in isolation. Nobody joins them up. Worse, a metric that is always high, such as long-standing bloat on a big table, looks alarming but is often unrelated to today’s problem.
What a good DBA does instead
When a senior DBA is pulled into an incident like this, they don’t start with the frontend. They ask a short set of questions, in roughly this order:
What changed recently? Which tickets moved to In Progress or Done in the last few minutes or hours, and did any of them touch the database?
Are queries slower than normal for this time of day? Not “are they slow”, but slower than their usual baseline.
Is it all queries, or only the slowest? A broad slowdown points to a resource problem. A slowdown only in the slowest queries points to plans, locks or indexes.
Is CPU unusual, or just busy as usual? High CPU on a database that always runs hot means little.
Has index or table health changed? Is bloat new or growing, or has it been there for months?
Those five answers, put side by side, usually point straight at the cause. The problem is that answering them by hand, across several tools, under pressure, takes time. That time is exactly when the business is hurting.
The improvement: a DBA’s cross-check, built into Dynatrace
We built a Dynatrace workflow that starts whenever Davis raises a database-related problem. It doesn’t add more alerts. It takes the problem Dynatrace has already detected and runs the DBA’s checks against it automatically.
Davis raises a problem. The workflow starts and captures the problem ID.
Four checks run side by side. Recent Jira changes, query latency, CPU and index bloat, each against its own baseline.
A correlation decision. Change timing and database evidence are weighed together.
One Dynatrace event on the problem. The likely cause, plus the evidence behind it.
In more detail:
Recent changes. The workflow asks Jira for issues that moved to In Progress or Done in the last 15 minutes, and keeps only those whose summary or description is database related.
Database evidence. It reads PostgreSQL telemetry collected by the Dynatrace PostgreSQL custom SQL extension, and compares query latency, CPU and bloat with their own baselines.
One answer. It combines change timing with the database evidence and writes a Dynatrace event carrying the problem ID and every result, including where data was missing.
The on-call team now sees, next to the symptoms, the change most likely behind them and the database evidence that supports it.
How it decides: baselines, not thresholds
Every database signal is compared with its own recent history before it counts as evidence. A value that is high but normal for this database is not treated as a cause.
Query latency compares the last 15 minutes with the previous day, looking at both typical (median, P50) and slowest (P95) query times. It lands in one of four states:
Broad slowdown: P50 and P95 are both at least 2× baseline. A database-wide problem and a strong signal.
Tail slowdown: P95 is at least 2× baseline, P50 is not. Only the slowest queries hurt, so check plans, locks and indexes.
Normal: neither is clearly above baseline. No latency evidence.
Insufficient data: too few queries to judge. Reported, not guessed.
A tail slowdown is the classic fingerprint of a lost or unusable index. Most queries are unaffected, but the ones that relied on the index fall back to scanning whole tables.
CPU uses the same current-versus-baseline comparison, so a database that always runs hot doesn’t raise a false alarm.
Bloat is judged on change, not just size:
High and increasing: high and has grown materially. The strongest bloat signal.
Increasing: clearly above its baseline. A likely sign of recent change.
High but stable: high, but it was high yesterday too. Not enough on its own to blame.
Normal: within the expected range.
The workflow then combines the evidence. Slower queries together with a CPU spike or affected indexes, close to a database-related change, give the strongest correlation. Slower queries alone, or a CPU spike alone, give weaker and more specific states.
What it is, and what it isn’t
The workflow is a diagnostic assistant, not an autopilot. It never touches the database. A finding such as “investigate bloat” or “consider VACUUM” is guidance for a person to act on. Any automated fix would need its own approval and safeguards.
It also doesn’t replace Dynatrace’s own analysis. It builds on the problems Davis already raises and adds the context Davis can’t see: change tickets and database internals.
We’re already extending it. Next on the list:
More DBA signals: lock waits, long-running transactions, dead tuples and autovacuum activity, connection saturation.
Better change context: a wider look-back window anchored to when the problem started, and Jira components mapped to the affected database.
Scored confidence: a High, Medium or Low rating with the reasons, rather than a simple match.
Change-order checks: spotting the risky patterns, such as an index dropped before its replacement is ready, that caused both incidents.
The takeaway
In both incidents, the information needed to find the cause existed from the first minutes. It sat in a change ticket, in query timings and in index health, but in different places, judged by different rules.
The fix wasn’t another alert. It was joining up the evidence and comparing it against what’s normal, the way a DBA would. Now that reasoning runs automatically the moment Dynatrace raises a problem. The next time an index goes missing, the first event the team sees should point at the database, not at the frontend or connection pools. If you would like a copy of the workflow, a demo of the workflow - we are here to help : hello@visibilityplatforms.com
Comments