| name | Safe Schema Migration |
| description | Use when planning an online schema change, zero-downtime migration, or rolling back a deploy that changed a table. Triggers on 'migration', 'ALTER TABLE', 'zero-downtime', 'expand-contract', 'backfill'. |
Safe Schema Migration
When to invoke
- "Plan the migration for adding column X."
- "Can we rename this column without downtime?"
- "How do we drop this table safely?"
Expand / migrate / contract pattern
Every schema change that touches live traffic goes in three deploys, never one.
- Expand - add the new shape alongside the old (new column nullable, new table, new index). No reads/writes use it yet.
- Migrate - dual-write to old and new, backfill historical rows, switch reads to the new shape behind a flag.
- Contract - remove the old shape only after the new shape has been authoritative for at least one release cycle.
Rules of thumb
- Additive is always safe: new nullable column, new index (CONCURRENTLY / ONLINE), new table.
- Destructive is never one deploy: drop column, rename column, change type, drop table, NOT NULL add.
- Backfills run in batches with a LIMIT, sleep between batches, idempotent. Never
UPDATE whole_table SET … in one shot.
- Index builds:
CREATE INDEX CONCURRENTLY (Postgres), ONLINE=ON (MySQL 8 / SQL Server). Watch for lock escalation.
- Renames: do NOT rename in place. Add new column → dual-write → backfill → switch reads → drop old.
Pre-flight checklist
Red flags - do not ship
- Single
ALTER TABLE that takes a full lock on a large table.
- Migration coupled to application deploy (cannot roll back independently).
- Irreversible step with no backup.
- Backfill that rewrites every row in one transaction.
References
