A schema migration dropped an index nobody remembered was load-bearing. Postgres started sequential-scanning a 40-million-row table, and the whole orders service crawled.

At 11:15 on January 21st, a migration meant to rename a column on the orders table quietly dropped and recreated it without its composite index. p95 latency on order-lookup went from 45ms to 6.8 seconds in under two minutes.

Timeline

11:15:00 — migration 0187_rename_customer_ref runs during the deploy window; it uses a generic "add column, backfill, drop old column" pattern that doesn't explicitly recreate indexes on the old column. 11:16:40 — order-lookup p95 crosses 2s, our SLO alert for the orders service fires. 11:18:00 — on-call Priya confirms via HelixQL that latency correlates exactly with the migration completion timestamp, not with any code deploy. 11:21:00 — EXPLAIN ANALYZE on the slow query confirms a sequential scan on a 40M-row table. 11:24:00 — team decides against a rollback (data was already backfilled) and instead runs CREATE INDEX CONCURRENTLY. 11:47:00 — index build completes; p95 drops to 52ms within 90 seconds as query plans flip back to index scans.

Root cause

Our migration tooling recreates columns but does not carry forward indexes automatically, and the migration's own review checklist didn't have an explicit "list indexes on this table before and after" step. The 26-minute gap between detection and full recovery was the CONCURRENTLY index build itself, which we chose over a lock-and-block rebuild specifically to avoid a second outage.

The fix

The query that nailed the correlation to the exact migration, not the deploy:

from traces
| where service == "orders-api" and span.name == "orders.lookup_by_customer"
| summarize p50=p50(duration), p95=p95(duration), p99=p99(duration) by bin(time, 1m)
| where p95 > 500ms

Cross-referencing that jump against the migration log's exact commit timestamp (11:15:42) versus the last code deploy (10:40:00, unrelated) told us within three minutes this was a schema problem, not an application one — which saved us from wasting time bisecting application code.

What changed

Migrations that touch indexed columns on tables over 1M rows now require an explicit index-diff in the PR description and a dry run against a production-sized snapshot in staging. We also added a HelixQL alert that fires when a table's sequential-scan ratio jumps more than 10x in a 5-minute window, independent of latency thresholds.

  • Require an explicit before/after index diff on any migration touching a table over 1M rows.
  • Dry-run schema migrations against a production-sized data snapshot, not a small staging table.
  • Alert on sequential-scan ratio jumps, which catch this class of bug before latency does.
  • Default to CREATE INDEX CONCURRENTLY in the runbook so responders don't have to decide under pressure.