Photo by Kevin Ache on Unsplash
Why Database Migrations Fail at 3 AM
Database schema migrations are one of those things that work perfectly in development and spectacularly fail in production. The gap between a successful test run on a local database with 10,000 rows and a production deployment against billions of rows is where most operational lessons are learned the hard way.
The problem isn’t that migrations are inherently difficult. It’s that production databases operate under constraints that never appear in testing: concurrent traffic, replication topologies, resource contention, and sheer data volume. When a migration that took 200 milliseconds locally takes six hours in production and blocks every write for the entire duration, you’ve discovered the difference between a schema change and a production schema change.
Lock Escalation and Table-Level Blocking
Most relational databases acquire locks during schema changes. Adding a column with a default value, creating an index, or altering a constraint can trigger a table-level lock that blocks all writes until the operation completes. On a small table, this happens in milliseconds. On a table with hundreds of millions of rows, it can take hours.
The issue is that many DDL operations require rewriting the entire table. Postgres, for example, historically rewrote the whole table when adding a column with a non-null default value. Even when the database can avoid a full rewrite, it still needs to scan the table to validate constraints or build indexes, and that scan holds locks.
The result is a maintenance window that extends far beyond what was planned. Writes queue up, connection pools saturate, timeouts cascade through upstream services, and eventually the migration gets rolled back, leaving the database in an ambiguous state.
Replication Lag and Topology Complexity
Production databases rarely run as single instances. Most architectures include read replicas, and those replicas need to apply the same migration. If the migration takes 30 minutes on the primary, replicas fall 30 minutes behind during that window. Applications that read from replicas start seeing stale data, and if those reads inform writes, you can end up with subtle correctness violations.
Worse, some replication topologies apply DDL statements serially while continuing to apply DML changes in parallel. This can cause replicas to drift or encounter conflicts that halt replication entirely. A migration that adds a unique constraint might succeed on the primary but fail on a replica if the data drifted during the migration window.
Multi-region deployments compound this. If you’re replicating across data centers with non-negligible latency, a slow migration can cause one region to fall so far behind that it triggers failover logic or manual intervention.
Resource Exhaustion and IO Saturation
Schema changes are resource-intensive. Building an index requires sorting potentially billions of rows, which means heavy CPU and disk usage. Rewriting a table requires allocating new storage and then swapping it in atomically. On a system already near capacity, this can push disk IO, memory, or CPU over the edge.
Databases often prioritize foreground queries over background maintenance operations, but a migration isn’t background maintenance from the database’s perspective. It’s a foreground transaction that must complete. If the system runs out of temp space during an index build, the migration fails. If memory pressure causes the OS to start swapping, the whole database slows to a crawl.
Production databases also contend with autovacuum, backups, and monitoring queries. A migration that expects to monopolize IO might instead compete with a scheduled backup, turning a two-hour window into a six-hour window.
Why Online Schema Change Tools Exist
The industry response to these failure modes has been online schema change tools: pt-online-schema-change for MySQL, pg_osc for Postgres, and similar utilities. These tools work by creating a shadow table, copying data in small batches, keeping the shadow table in sync with ongoing writes using triggers, and then swapping the tables atomically.
The trade-off is complexity. Online schema change tools add operational overhead, require monitoring, and can fail in ways that leave the database with orphaned triggers or shadow tables. They also don’t eliminate resource contention; they just spread it out over a longer time window.
Still, they work because they avoid the single biggest migration failure mode: holding exclusive locks for extended periods. By copying data incrementally and only locking during the final swap, they allow migrations to run alongside production traffic without blocking writes for hours.
What Production-Ready Migrations Look Like
Successful production migrations share a few characteristics. They’re decomposed into small, incremental steps that can each be verified and rolled back independently. They avoid operations that require table rewrites or long-lived locks. They account for replication lag and validate that replicas converged after each step.
They also include monitoring and escape hatches. A migration should expose metrics about its progress, resource usage, and impact on query latency. It should be pausable if it starts degrading production traffic. And it should have a rollback plan that doesn’t require restoring from backup.
None of this is exotic. It’s just the reality of operating databases at scale, where schema evolution is an operational problem as much as a logical one.