Migrations Are the Riskiest Routine Operation You’ll Run
Unlike most deploys, a database migration can be genuinely difficult or impossible to cleanly roll back once it’s touched production data, especially destructive changes like dropping a column. Treating migrations with more caution than a typical code deploy isn’t excessive — it’s proportional to the actual risk.
Backward-Compatible Migrations Enable Zero-Downtime Deploys
Adding a new required column and deploying application code that depends on it simultaneously creates a window where old application instances (still running during a rolling deploy) fail because the column they don’t expect now exists, or new instances fail because the column isn’t there yet. The standard fix is splitting changes into backward-compatible steps: add the column as nullable first, deploy code that can handle both its presence and absence, backfill data, then finally enforce constraints once all instances are updated.
The Expand-Contract Pattern
For more invasive changes — renaming a column, changing a type — the expand-contract pattern is the reliable approach: expand the schema to support both old and new forms simultaneously, migrate application code and data to the new form gradually, then contract by removing the old form only once nothing depends on it anymore. This takes more deploys than a direct change, but it eliminates the risky “everything must switch atomically” moment entirely.
Large Table Migrations Need Special Care
Running an ALTER TABLE on a table with hundreds of millions of rows can lock the table for an extended period on some databases, effectively causing an outage. Tools like gh-ost or pt-online-schema-change for MySQL, or built-in online DDL support in Postgres for many operation types, perform these changes without blocking normal reads and writes.
Always Test Migrations Against Production-Scale Data
A migration that runs instantly against a development database with a thousand rows can take hours — or lock a table unacceptably — against a production table with hundreds of millions of rows. Testing against a realistically-sized dataset, or at minimum estimating execution time based on row count, avoids nasty surprises during an actual production run.
Have a Rollback Plan Before You Need One
Not every migration can be cleanly reversed, but knowing in advance whether yours can — and having a tested plan if it can’t — beats discovering the answer during an active incident. For genuinely irreversible changes, taking a backup or snapshot immediately before running is cheap insurance.
Practical Checklist
- Split risky changes into backward-compatible, multi-step migrations rather than one atomic change.
- Use online schema change tools for large tables to avoid blocking locks.
- Test migrations against production-scale data before running them for real.
- Know your rollback plan, and have a fresh backup, before running anything irreversible.