Renaming a column or altering a table locking mechanism on a database serving thousands of concurrent requests will lock tables, stall connection pools, and result in immediate downtime.
To achieve zero-downtime deployments, engineering teams must abandon single-step destructive migrations in favor of the Expand and Contract methodology.
The Expand and Contract Lifecycle#
Every schema alteration is broken into discrete, backward-compatible phases:
- Expand Phase: Add the new column or table without modifying existing fields. Both old and new fields coexist.
- Dual-Writing Phase: Deploy application code that reads from the old column but writes simultaneously to both old and new columns.
- Backfill Phase: Run asynchronous background jobs to backfill historical records from the old column to the new column.
- Cutover Phase: Update application code to read exclusively from the new column.
- Contract Phase: Safely remove the legacy column in a subsequent release after verifying zero references in production logs.
Avoiding Heavy Table Locks#
In MySQL 8 and PostgreSQL, altering large tables can trigger metadata locks. Always use non-blocking algorithmic flags (ALGORITHM=INPLACE, LOCK=NONE) or tools like gh-ost / pt-online-schema-change when migrating tables exceeding tens of millions of rows.
Engineering Takeaways#
Never perform destructive schema alterations in a single deployment. Multi-phase migrations ensure seamless continuous delivery without customer-facing maintenance windows.