Architecture
Zero-Downtime Database Migrations
Updated February 18, 2020By the CalliArc team
Key takeaway
Split every breaking schema change into expand, migrate, and contract phases deployed separately, so old and new code can run simultaneously. Never rename or drop a column in the same release that stops using it — that single rule prevents most migration outages.
Schema changes are the most common cause of self-inflicted downtime. The reason is simple: during a deployment, old and new application code run at the same time, and the database has to satisfy both.
Expand, migrate, contract
- Expand — add the new column or table. Nullable, with a default handled in code rather than as a table rewrite. Old code ignores it.
- Migrate — deploy code that writes to both old and new, then backfill existing rows in batches, then switch reads to the new column.
- Contract — once nothing references the old column and you've waited long enough to be confident, drop it.
- Each phase is a separate deployment. Rushing them together is what turns a routine change into an incident.
Operations that will lock your table
- Adding a column with a non-null default on older database versions rewrites the whole table.
- Changing a column type, which often means a full rewrite and a long lock.
- Creating an index without the concurrent option — it blocks writes for the duration.
- Adding a foreign key constraint, which validates every existing row unless you add it as not-valid and validate separately.
- Always check the behaviour for your specific database and version. The same statement blocks in one and doesn't in another.
Backfilling large tables
- Process in batches with a pause between them, and keep each transaction short.
- Make the backfill resumable and idempotent — it will be interrupted.
- Watch replication lag while it runs; an aggressive backfill can push read replicas far behind and degrade the application.
- Run it outside peak hours even though it's online, and have a kill switch.
Discipline that keeps this safe
- Migrations live in version control, run through the deployment pipeline, and are reviewed like code.
- Every migration has a tested rollback, or is deliberately forward-only with a documented reason.
- Test against a production-sized copy — a migration that takes two seconds on a developer machine can take forty minutes on real data.
- Separate schema changes from feature releases, so a rollback of one doesn't force a rollback of the other.