A migration that worked cleanly against a staging copy of the database locked a production table for eleven minutes on a fleet platform's busiest hour. We'd tested the migration. We hadn't tested it under the conditions it would actually run in.
What staging didn't have
Staging had a realistic schema and a realistic row count, and it had none of production's concurrent write load. The migration added an index, which staging did in under a second and production did while a hundred vehicles were actively reporting position updates every few seconds.
Running it twice, properly
We now run every non-trivial migration against a copy of production taken close to peak load, replaying a sample of the write traffic from that period concurrently with the migration, rather than just against a quiet staging snapshot. This caught the same class of lock contention on two later migrations before either reached production.
Rules we now follow for schema changes
- Assume any migration that isn't provably online will lock something, and prove it either way before it runs against live data.
- Add columns and indexes concurrently where the database supports it, and treat "concurrently" as a requirement to justify skipping, not a default to opt into.
- Schedule migrations against the load curve, not the calendar — the quietest hour for a taxi platform is not the same hour as for an office SaaS product, and we've had to relearn this per client.
A migration tested against a quiet copy of the database has been tested against a system that doesn't exist.
The eleven minutes, in context
Eleven minutes of a locked table on a dispatch platform means eleven minutes of rides not being assigned. Nobody lost data and the incident review still ran long, because the gap between "tested" and "tested realistically" was the entire story.
What we'd say to a team about to run a big migration
Ask what the busiest concurrent write pattern on the affected table looks like, and replay it, even approximately, before the migration goes anywhere near production. It is more setup than most teams do for a routine schema change, and it is the setup that would have caught this one.