A cleanup migration dropped an index nobody remembered was load-bearing. Checkout latency went from 90ms to 12 seconds in under a minute.

A cleanup migration dropped an index nobody remembered was load-bearing. Checkout latency went from 90ms to 12 seconds in under a minute.

A Routine Migration, Or So We Thought

The migration was labeled "remove unused indexes" and had been reviewed and merged two weeks earlier as part of a database cleanup pass. It ran during a low-traffic maintenance window, exactly as planned. Checkout looked fine for six hours. Then evening traffic ramped up and the query planner, faced with real load for the first time since the index dropped, started falling back to a sequential scan on the orders table for every cart lookup.

Finding It in the Query Plan, Not the Code

Nothing in the application had changed, so the code-review instinct to check recent deploys led nowhere. What found it was comparing query plan telemetry before and after the maintenance window:

from traces
| where service == "checkout-api" and span.name == "db.query"
| where span.attributes["db.statement"] contains "orders"
| summarize p95(duration), count() by bin(15m)
| sort by bin(15m) asc

p95 duration on that exact query pattern held at 4ms for months, then jumped to 1.8 seconds precisely at the maintenance window boundary, six hours before anyone noticed. The index that had been dropped was on orders(customer_id, status) — unused by any query in the last 30 days of the analysis window the DBA team had used to decide it was safe, but essential to a monthly billing reconciliation job that only ran on the first of the month and happened to also be the exact pattern the cart lookup used under a different query path the analysis hadn't caught.

The Real Lesson Was About the Analysis Window

Thirty days of query logs felt thorough. It wasn't enough to catch a monthly job, and it definitely wasn't enough to catch a query pattern that only manifested under peak evening load with a specific cart-lookup code path that ran less frequently than the main checkout flow. We rebuilt the "unused index" analysis to require a full 90-day window and to explicitly flag any index whose usage was bursty rather than steady, since bursty-but-rare is exactly the pattern that slips through short lookback windows.

  • "Unused in 30 days" is not the same as "safe to drop" — monthly and quarterly jobs need longer lookback windows.
  • Query plan telemetry catches this class of regression faster than code review ever will, since nothing in the code changed.
  • Maintenance windows hide the blast radius of a bad change until real traffic returns.
  • Restoring the index took four minutes once found; finding it took thirty-six.