The Expand and Contract Pattern
Applying breaking schema changes (renaming a column, splitting tables) directly on a live database is unsafe in a single deployment, because during a rolling deploy old and new application versions run side by side against the same schema. The industry standard is the Expand and Contract (Parallel Run) pattern executed in 4 distinct phases:
- Phase 1 (Expand): Add the new column or table alongside the old one. The database now contains both.
- Phase 2 (Dual Write): Deploy an application release that reads from the old column, but writes mutations to both the old and new columns simultaneously. A background backfill job migrates historical rows.
- Phase 3 (Switch Read): Once backfill and validation pass, deploy an update that switches reads to the new column while still dual-writing, so rolling back remains possible.
- Phase 4 (Contract): Stop writing the old column, then drop it after a soak period.
Worked example: renaming users.first_name to given_name
| Step | Change | Reads | Writes | Safe to roll back? |
|---|---|---|---|---|
| 1. Expand | ALTER TABLE users ADD COLUMN given_name text (nullable, no default) | old | old | Yes |
| 2. Dual write | Application release writes both columns | old | old + new | Yes |
| 3. Backfill | Batched job copies old to new where new is null | old | old + new | Yes |
| 4. Verify | Count mismatches, for example WHERE given_name IS DISTINCT FROM first_name | old | old + new | Yes |
| 5. Switch reads | Release reads the new column, still dual writes | new | old + new | Yes |
| 6. Stop old writes | Release writes only the new column | new | new | Only back to step 5 |
| 7. Contract | Drop first_name after a soak period | new | new | No (restore from backup) |
Backfill arithmetic
A table with 400 million rows, updated in batches of 5,000 rows every 100 ms, processes about 50,000 rows per second and finishes in roughly 2.2 hours. Keep each batch in its own short transaction so replication lag and lock hold times stay low. Throttle when replica lag exceeds a threshold, which is what gh-ost and pt-online-schema-change do. Make the job idempotent (WHERE given_name IS NULL AND id BETWEEN x AND y) so it can be restarted safely.
Engine-specific facts that matter
- PostgreSQL 11 and later:
ADD COLUMN ... DEFAULTwith a non-volatile default is a metadata-only change. Before version 11 it rewrote the whole table. UseCREATE INDEX CONCURRENTLYto build indexes without blocking writes. - Adding NOT NULL safely in PostgreSQL: add
CHECK (col IS NOT NULL) NOT VALID, thenVALIDATE CONSTRAINT, which does not block writes. PostgreSQL 12 and later can then setNOT NULLwithout a full scan. - MySQL 8.0: many
ADD COLUMNoperations supportALGORITHM=INSTANT. For operations that do not, gh-ost and pt-online-schema-change copy into a shadow table and swap it in. - Always: set
lock_timeout(PostgreSQL) orlock_wait_timeout(MySQL) to a few seconds so a blocked DDL fails fast instead of stalling all traffic behind it.
Failure modes
- Dual-write divergence: if the two writes are not in the same transaction, a crash between them leaves the columns inconsistent. Put both writes in one transaction, or rely on the backfill and verification steps to repair differences.
- ORM caches the schema: some ORMs select every column by name. Dropping a column breaks old pods even though they never used it. Remove the column from the model first, then drop it.
- Replica lag during a large backfill can make read replicas serve stale data, or trigger automatic failover. Throttle on lag.