Zero-Downtime Database Migrations: Expand and Contract in Practice

Why migrations cause outages

Most deployment outages that are not caused by bugs are caused by schema changes. Two things go wrong. First, some schema operations take locks that block reads or writes on a busy table, sometimes for minutes. Second, during a rolling deployment, old and new versions of the application run at the same time, and a schema change that suits only the new version breaks the old one.

The fix for both is the same discipline: break every change into small steps, each compatible with the code running before and after it. This is usually called expand and contract, or parallel change.

The expand and contract pattern

Take a common example: renaming a column from fullname to display_name on a large users table. A single rename would break every running instance that still reads fullname. Instead, the change happens across several releases:

• Expand: add the new nullable column display_name
• Dual write: deploy code that writes both columns, still reading fullname
• Backfill: copy existing values into display_name in small batches
• Switch reads: deploy code that reads display_name, still writing both
• Stop old writes: deploy code that only uses display_name
• Contract: drop the fullname column in a later release

Why so many steps are worth it

Each step is independently deployable and reversible. If the release that switches reads has a bug, you roll back the application, and both columns are still populated. Nothing about the rollback touches the schema, which is the slow and risky part. Feature flags make the read switch even safer, because you can turn it on for a fraction of traffic first. Our feature flags explainer covers the technique.

The cost is calendar time, not much engineering effort. Many teams batch the contract steps of several changes into a periodic cleanup release.

Know which operations lock

In PostgreSQL, most ALTER TABLE operations take an ACCESS EXCLUSIVE lock, which blocks all reads and writes on the table while held. Many of these are instant metadata changes, so the lock is brief. The danger is twofold: operations that rewrite or scan the whole table while holding the lock, and lock queuing. If a long-running query holds a weaker lock, your ALTER waits, and every query arriving after it queues behind the ALTER. A ten-second wait becomes a ten-second outage.

Protect yourself by setting a short lock_timeout, such as a few seconds, at the start of each migration and retrying if it fails. A failed migration you can retry is far better than a stalled production database.

Lock-safe recipes for PostgreSQL

These patterns avoid long locks for the most common changes:

• Add a column: nullable columns are instant; since PostgreSQL 11, a column with a constant default is also added without rewriting the table
• Add NOT NULL: add a CHECK (col IS NOT NULL) constraint with NOT VALID, run VALIDATE CONSTRAINT separately, then SET NOT NULL, which can use the validated constraint to skip a full scan
• Add a foreign key: create it with NOT VALID, then validate it in a separate step
• Add an index: use CREATE INDEX CONCURRENTLY, outside a transaction block
• Change a column type: usually rewrites the table, so add a new column and use expand and contract instead

Backfilling large tables

A single UPDATE across millions of rows holds row locks for a long time, bloats the table and can flood replicas with changes. Backfill in batches instead: process a few thousand rows at a time, ordered by primary key, commit each batch and pause briefly between them. Make the job resumable by recording the last processed ID, and idempotent so rerunning it is harmless.

Watch replication lag and database load while it runs, and slow down if either climbs. Run backfills as a background job, not inside the migration that deploys with your application.

Deploy order and rolling releases

The rule is simple: every migration must be compatible with the code currently running, and every code release must be compatible with the schema before and after its migration. In practice, run expanding migrations before deploying the code that uses them, and run contracting migrations only after no running code references the old structure.

Watch out for ORMs that cache a table's column list. Some will fail when a column they expect disappears, even if the code no longer uses it. Tell the ORM to ignore a column in one release before dropping it in the next.

Data migrations are different from schema migrations

Schema migrations change structure; data migrations change content, such as splitting a name field or recalculating stored totals. Keep them separate. Schema migrations run with each deploy and should be fast. Data migrations run as background jobs that can be paused, resumed and monitored, with progress logged and counts verified at the end. Write a quick check comparing old and new values on a sample before trusting the result.

Dropping columns and tables safely

Dropping is the only step you cannot undo, so be patient. Before the contract step, confirm that no running code, scheduled job, report, view, trigger or analytics pipeline still reads the column. Search the codebase and query logs, and check with teams that run reports on the database. Take a backup or export of the data you are about to drop, then drop it in a release of its own, so if something unexpected breaks, the cause is obvious.

MySQL and other databases

The pattern is the same elsewhere; the lock rules differ. MySQL's InnoDB supports many online DDL operations, with INSTANT and INPLACE algorithms for some changes, and tools such as gh-ost and pt-online-schema-change copy a table in the background and swap it in for changes that would otherwise block. Our PostgreSQL vs MySQL comparison covers broader differences.

Test against realistic data

A migration that runs in a second on a development database can take an hour on production. Test migrations against a recent, anonymized copy of production data, time them and check their locks. Compare the schema before and after with a schema dump and a diff, for example using our text diff checker, so unexpected changes stand out in review.

• Review every migration for locks, rewrites and long scans
• Separate schema migrations from data backfills
• Keep migrations small: one logical change each
• Have a written plan for each step's rollback

Make it routine

Zero-downtime migrations are less about clever tricks and more about habit: small steps, compatible releases, timeouts and batches. Once a team works this way, schema changes stop being scheduled for nights and weekends. Nexzem's DevOps automation work includes migration pipelines with these safeguards built in, and our blue-green deployment explainer covers a related release technique.