Architecture
Zero-Downtime Database Migrations
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.