The three-hour deploy that taught us to fear big-bang migrations

We estimated a schema migration for a client's core orders table at twenty minutes of downtime, scheduled for a low-traffic window. It took just under three hours, and the only reason it ended in a working system rather than a much worse outcome was a rollback plan we almost skipped writing because we were confident the migration would go smoothly.

What actually happened

The migration added a non-nullable column with a computed default across a table with 40 million rows. In our staging environment, at roughly 2 million rows, this ran in under a minute. At production scale, the single transaction locking the table for the backfill ran far longer than expected, and every write to the orders table queued behind it.

Why staging did not catch this

Staging data was a two-year-old snapshot, twenty times smaller than production, and the migration's runtime scaled non-linearly with row count in a way that a 20x smaller test simply could not reveal. This is the single most common way we now see teams get burned by database migrations.

The rollback that saved us

We had, almost as an afterthought, written a reversible migration and tested the rollback path in staging. When it became clear the forward migration would not complete in the maintenance window, we rolled back cleanly in under four minutes and re-planned the migration as a batched, non-locking backfill instead.

• Test migrations against a production-scale row count, or a representative sample at real scale, not just a small staging snapshot.

• Every migration needs a tested rollback path, even ones you are confident about — especially the ones you are confident about.

• Batch large backfills instead of running them as a single locking transaction, regardless of how well it performs in staging.