A Missing Database Index That Went Unnoticed for a Year

A weekly reporting job that joined an events table against a campaigns table on campaign_id started timing out. It had been running fine, if slowly, for over a year, and nobody had flagged the gradual slowdown because it ran unattended overnight and nobody was watching its duration — only its success or failure. The day it finally crossed the job's timeout threshold and failed outright was the first time anyone looked at it in twelve months.

What had actually been happening the whole time

The events table had no index on campaign_id. At launch, with a few thousand rows, a full table scan on the join was fast enough that nobody noticed — it probably ran in under a second. A year later, with the table at around forty million rows and growing by roughly a hundred thousand rows a day, that same full table scan had degraded linearly with table size, and nobody had a duration metric on the job to see the slope happening, only a pass/fail status that stayed green right up until it finally crossed the timeout and went red.

A missing index doesn't announce itself with an error. It announces itself as a slope on a graph nobody was plotting.

The fix and why it was almost the easy part

Adding the index itself was a one-line migration. The actual work was doing it safely on a forty-million-row production table without locking writes during business hours — we used a concurrent index build (CREATE INDEX CONCURRENTLY on Postgres) specifically to avoid a write lock, and ran it during a low-traffic window anyway out of caution. Query time on the reporting job dropped from a multi-minute timeout to under two seconds.

• Added job duration as a tracked metric with alerting on a relative increase, not just pass/fail status — a job that silently gets 3x slower over six months should trigger a look before it hits a hard timeout.
• Ran a query plan audit across other long-running scheduled jobs touching large tables, and found two more missing indexes on tables that were still small enough not to have caused a timeout yet.
• Added index coverage to the review checklist for any new foreign-key-style join column introduced in a migration, rather than relying on someone remembering to think about it at write time.
• Documented table growth rate for the handful of tables most likely to hit this pattern again, so a slow slope is visible before it becomes an incident.

What made this one sting was how avoidable it was in hindsight — the missing index was there from day one, visible to anyone who ran EXPLAIN on the query at any point in the year. Nobody did, because the job wasn't failing. A query that's getting slower every month but still technically succeeding is one of the easiest problems to miss entirely, because success and health aren't the same thing, and most monitoring only checks the first.