Zero-Downtime Database Migrations: A Practical Guide
A schema change shouldn't require a maintenance window. With the right pattern, you can evolve your database while serving live traffic. Here's the expand-and-contract approach we use on every migration.
The Core Problem
The old code and the new code run at the same time during a deploy. Your schema has to be compatible with both, or something breaks mid-rollout.
Expand and Contract
The pattern that makes migrations safe has three phases:
1. Expand
Add the new structure without removing the old:
- Add new columns as nullable, or with defaults
- Add new tables alongside existing ones
- Backfill data in batches, not one giant transaction
2. Migrate
Deploy code that writes to both old and new, reads from new. Both versions of the app stay happy.
3. Contract
Once everything reads and writes the new structure and you've verified it, remove the old columns or tables in a later deploy.
Rules That Keep You Safe
- Never rename a column in place — add new, migrate, drop old
- Never make a column NOT NULL in the same step you add it
- Backfill in small batches to avoid long locks
- Add indexes concurrently so you don't block writes
- Always make migrations reversible
Tooling
- Run migrations as a separate step from app deploy
- Keep migrations in version control alongside code
- Test the migration against a production-sized dataset before shipping
The Result
Done this way, even big schema changes ship during peak traffic without anyone noticing. The maintenance window becomes a relic — and your uptime SLA stays intact.
