Schema migrations fail in production for a short list of recurring reasons: a lock held too long on a hot table, a schema change deployed in lockstep with code that assumes it already happened, or no tested way back when something goes wrong. None of these are solved by picking a fancier migration tool — they are solved by picking the right strategy for the change and the table size involved. This guide covers the strategies themselves: when a straightforward blocking change is fine, when you need expand-contract, and when only an online schema change tool will do.
What changed in 2026
- Online schema change tools became routine, not exotic. gh-ost,
pt-online-schema-change, and Postgres-native pgroll are now common defaults for any migration touching a table over a few million rows, not a last resort.
- Branch-based database testing spread widely. Neon and PlanetScale branching let teams run a migration against a full copy of production data before it ever touches the real database.
- Declarative schema tooling grew alongside imperative migrations. Tools that diff a desired schema state against the live database complement, rather than replace, hand-written migration steps for anything involving data backfills.
- Migration-aware deploy pipelines matured. More CI/CD systems now enforce that a migration runs and is verified as a distinct pipeline stage before the new application version receives traffic.
The four migration strategies
| Strategy |
How it works |
Use when |
| Big-bang (blocking) |
Single ALTER runs directly against the live table |
Table is small, or the change is genuinely instant (e.g., adding a nullable column on Postgres 11+) |
| Expand-contract |
Add new structure, dual-write or backfill, cut over reads, then remove the old structure — across several deploys |
Any change that isn't backward compatible in one step: renames, type changes, NOT NULL additions |
| Online schema change tool |
A copy table is created, rows are copied in batches with change tracking, then an atomic rename swaps it in |
Large or high-traffic tables where even a brief lock is unacceptable |
| Dual-write with backfill |
Application writes to both old and new locations while a background job backfills history |
Splitting a table, changing a primary data store, or introducing a new service of record |
Most production incidents come from applying the big-bang strategy to a change that actually needed expand-contract — the migration itself may run in milliseconds, but the application code deployed alongside it assumes a state that other, still-running instances haven't reached yet.
Migration ordering across a rolling deploy
During a rolling deploy, old and new application code run against the same database at the same time, often for minutes. That means every migration must be compatible with both:
T0: migration runs — adds new column, nullable, no default
T1: rolling deploy begins — some instances run old code, some run new
T2: new code writes to the new column; old code ignores it (safe)
T3: rolling deploy completes — all instances run new code
T4: next deploy: backfill + set NOT NULL, now that old code is gone
Reversing steps 1 and 3 — deploying code that requires the new column before the migration that creates it has finished — is the single most common cause of migration-related outages. The rule: the schema change ships first and alone; the code that depends on it ships in a later, separate deploy.
Common mistakes
Running a schema change and its dependent code in the same deploy. Even if the migration finishes in milliseconds, a rolling deploy means some instances are still on old code when new code starts querying a column or table that isn't guaranteed to exist yet on every replica the old code reads from.
Adding a NOT NULL column with no default directly. On most databases this requires validating every existing row under a lock. Add it nullable, backfill in batches, then add the constraint once the backfill is verified complete.
Assuming a small migration on a small table today stays small. A table with a few thousand rows can grow past the point where a blocking ALTER is safe within a year; revisit the strategy, not just the migration file, as tables grow.
No tested rollback for a destructive change. A DROP COLUMN or a data-changing UPDATE needs a verified restoration path — a backup, a shadow copy, or a reversible expand-contract step — before it runs anywhere near production.
FAQ
Do online schema change tools work with any database?
Mostly by ecosystem — gh-ost and pt-online-schema-change target MySQL, pgroll targets Postgres. Each works by copying data into a shadow table and swapping it in, rather than altering the live table directly.
How long should expand-contract take?
As long as it takes to safely roll out each stage — often days, sometimes longer for high-risk changes, since each stage is typically its own deploy with its own monitoring window before moving to the next.
Can I combine expand-contract with a feature flag?
Yes, and it is a common, safe pattern — gate the code that reads from the new structure behind a flag, verify it against production traffic at low exposure, then ramp up before removing the old structure entirely.
What is the safest way to test a risky migration before running it in production?
Run it against a full, recent copy of production data — a database branch (Neon, PlanetScale) or a restored snapshot — and time it, check for lock waits, and verify the resulting data, not just that the migration command exits successfully.
Where to go next
See zero-downtime deployment in 2026 for coordinating schema changes with the application deploys that depend on them, database sharding explained for 2026 for migrations that span multiple database nodes, and how to pick a database in 2026 if the migration you're avoiding is actually a sign you picked the wrong data store.