Zero-downtime database migrations: the expand and contract pattern

A schema change looks like one step: rename a column, ship the code that uses the new name. In production it is two systems changing at different moments, and the gap between them is where outages live.

Why one-step migrations break

During a deploy, old and new versions of your application run at the same time. Even a few seconds of overlap is enough: instances behind a load balancer update one by one, background workers finish their current jobs on old code, and a rollback means old code meets the new schema.

If the schema change is only compatible with one version of the code, some requests will fail while the other version is still serving traffic.

The expand and contract pattern

Split the change into steps that are each safe on their own.

1. Expand. Add the new structure without removing the old. A new nullable column, a new table, a new index. The current code does not know it exists and keeps working.

2. Write to both. Deploy code that writes to the old and the new location. Reads still come from the old one, so behaviour is unchanged.

3. Backfill. Copy existing data into the new structure, in small batches, outside peak hours, throttled so the database stays healthy. Verify the counts and spot-check values.

4. Switch reads. Deploy code that reads from the new location. Keep dual writes for a while, so a rollback to the previous version still finds current data.

5. Contract. Once nothing reads or writes the old structure, and you have waited long enough to be confident you will not roll back, remove it.

Each step is a separate deploy. It feels slower, and it is dramatically safer.

Things that quietly cause downtime

• Adding a column with a default on a large table can rewrite the whole table and hold locks on some database versions. Check how your version behaves, and add the column first, set defaults separately.
• Creating an index normally can block writes. Most databases offer a concurrent or online option; use it.
• Adding a NOT NULL or foreign key constraint validates every row. Add it in a way that validates without a long exclusive lock where your database supports that, after backfilling.
• Long-running backfill transactions hold locks and bloat logs. Keep batches small.
• Renames. Treat a rename as add, copy, switch, remove — never as a single statement.

Plan the rollback before you start

For each step, answer: if this goes wrong, what do we do? Expand steps are usually trivial to undo. A contract step destroys data, so it needs a backup and a deliberate pause before it runs. Rehearse the whole sequence on a copy of production-sized data, because a migration that takes two seconds on a laptop can take an hour on the real table.

Automate, but keep a human at the dangerous step

Run migrations from your pipeline so they are versioned, reviewed and repeatable. Require explicit approval for destructive contract steps. And monitor lock waits, replication lag and error rates while a migration runs, with a clear point at which you stop.

What to take away

Treat schema and code as two things that change independently. Make every step backwards compatible, backfill gently, and remove old structures last. The result is migrations nobody notices — which is the goal.