Zero-downtime database migrations: the expand and contract pattern

Zero-downtime database migrations change a schema in small steps that never break the code that is currently running. You can get application deploys down to a symlink switch — as in zero-downtime deploys with atomic releases — and still take the site down with one ALTER TABLE. The reason is simple: during a deploy, and after a rollback, old and new code run against the same database. Every schema change has to work for both.

The core rule

At any moment, the database schema must be compatible with the version of the code currently running and the version you might roll back to. A change that breaks either is a breaking change, even if it looks small:

• renaming or dropping a column,
• changing a column’s type or meaning,
• adding a NOT NULL column without a default,
• splitting or merging tables,
• adding a unique constraint to data that may contain duplicates.

The fix is not to avoid these changes. It is to split each one into a sequence of compatible changes.

Expand, migrate, contract

The pattern has three phases, each shipped as its own deploy:

1. Expand. Add the new structure alongside the old one: a new column, a new table, a nullable field. Nothing reads it yet. Old code ignores it.
2. Migrate. Deploy code that writes to both old and new structures, backfill existing rows, then switch reads to the new structure. Verify the data matches.
3. Contract. Once no running or rollback-target code uses the old structure, stop writing it, then remove it in a later deploy.

Each phase is reversible on its own. If something goes wrong in the migrate phase, the old structure is still complete and the previous code still works.

Example: renaming a column

Suppose bookings.ref should become bookings.booking_reference.

1. Expand: add booking_reference as nullable.
2. Dual write: deploy code that writes both ref and booking_reference on every insert and update, still reading ref.
3. Backfill: copy ref into booking_reference for existing rows, in batches.
4. Switch reads: deploy code that reads booking_reference, still writing both.
5. Stop writing the old column: deploy code that writes only booking_reference.
6. Contract: drop ref, and add NOT NULL to the new column if needed.

Six steps for a rename looks excessive until the first rollback goes cleanly. In practice several steps can share a release with unrelated work; what matters is that each is deployed and verified before the next.

Backfilling large tables

Backfills are where load problems appear. A single UPDATE … SET new = old on a large table can hold locks, fill the undo log and push replicas far behind. Instead:

• batch by primary key range — a few thousand rows at a time, ordered by ID,
• pause between batches and back off when replication lag or load rises,
• record progress so the job resumes after a restart instead of starting over,
• make each batch idempotent — running it twice must give the same result,
• verify afterwards by comparing counts or checksums between old and new.

Run backfills as background jobs, not inside the deploy. A deploy should finish in seconds; a backfill may take hours. The replica and indexing considerations in scaling MySQL for transactional platforms apply directly here.

Online schema changes

Even an “expand” step can lock a table if the database rebuilds it. Know which operations your database performs in place and which copy the whole table. Modern MySQL versions can add many columns and indexes without blocking writes, but some type changes and constraint additions still rebuild the table. For changes that would rebuild a large, busy table, tools that build a shadow copy and swap it in — such as gh-ost or pt-online-schema-change for MySQL — avoid long locks.

Two more precautions:

• Set a lock wait timeout for migration sessions, so a migration waiting behind a long transaction fails quickly instead of queuing every other query behind it.
• Run migrations separately from application startup. If every app instance tries to migrate on boot, they race each other.

Constraints and data quality

Adding a constraint to existing data needs its own sequence:

• NOT NULL: add the column nullable, backfill, make the application always write it, then add the constraint.
• Unique: find and resolve duplicates first, prevent new ones in application code, then add the unique index.
• Foreign keys: clean orphaned rows first; on very large tables, consider whether the integrity check belongs in the database or in application logic and periodic verification.

Making it routine

The pattern only works if the team follows it every time. A few habits help:

• review migrations as carefully as code, with a checklist question: does this break the previous version?
• keep migrations forward-only and write a new migration to undo, rather than relying on down scripts nobody tests,
• track pending contract steps — old columns left behind become confusing quickly,
• test against production-sized data before running a migration on a large table,
• use feature flags to switch reads independently of deploys where the switch is risky.

AI coding assistants can draft migrations quickly, but they rarely know which tables are large or busy; that judgement stays with the reviewer, as discussed in AI coding assistants in engineering teams.

Summary

Schema changes break zero-downtime deploys because old and new code share the database. Keep the schema compatible with both by splitting breaking changes into expand, migrate and contract phases; backfill in small, resumable, idempotent batches; use online schema change tools where a table rebuild would lock; and add constraints only after the data and code already satisfy them. It takes more steps than a single ALTER TABLE, and every one of them is a step you can roll back.