Migrating a Database Schema Without Downtime

Changing the shape of a database that is serving traffic feels risky because the naive version is risky: drop the old column, deploy the new code, hope the timing works out. Done in stages, the same change carries almost no risk. The pattern is expand, migrate, contract.

The problem with doing it in one step

If you rename a column and deploy the code that uses the new name together, there is always a window where the running app expects one and the database has the other. Requests in that window fail. On a busy system the window is never zero.

Expand: add the new thing, keep the old

First migration: add the new column, table or structure, alongside the old one. Nothing reads it yet. This is a safe, additive change that can go out on its own with no code change.

Then deploy code that writes to both the old and the new location on every change, but still reads from the old one. Now every new write is in both places and the system is still behaving exactly as before.

Backfill the existing data

Run a background job that copies the old data into the new shape, in batches, slowly enough not to strain the database. Let it finish. Now old and new are in sync for every row, not just the ones written since the deploy.

Switch reads

Deploy code that reads from the new location. Keep writing to both for now. If something is wrong with the new shape, you find out here, with the old data still intact and one deploy to roll back.

Contract: remove the old thing

Once reads have been on the new location for long enough that you trust it, deploy code that stops writing to the old location. Then, and only then, a final migration drops the old column or table.

Each step is independently safe

The whole point is that no single deploy has a moment where the code and the schema disagree. Every step is either purely additive or a read switch with a fallback. If you need to stop halfway, you can, and the system keeps working.

Practical notes

Add indexes without locking the table where your database offers it. Keep backfill jobs gentle and resumable. Test the sequence against a copy of production data, because the row that breaks the migration is always the weird one from four years ago. And write the steps down before you start, so a colleague can pick it up if you are away when step three is due.

Common questions

Why not just take a short maintenance window?

Sometimes that is the right call for a small system. But a window that was meant to be ten minutes has a habit of becoming two hours, and staged migrations remove the risk entirely.

Does this work for adding an index?

Add indexes concurrently where your database supports it, so the table is not locked while the index builds. On a large table a plain index build can block writes for a long time.

What is the most common mistake?

Deploying the schema change and the code that depends on it at the same time. There is always a moment where one is ahead of the other, and that moment breaks.