Schema migration without downtime

There is one principle that explains practically every database migration incident, and it fits in a single sentence:

During the deploy, both versions of the code run at the same time against the same database.

Always. Rolling update, blue-green, canary: at some point old code and new code are alive simultaneously, and both talk to the same schema.

Therefore, every schema change has to be compatible with the previous version of the code. No exceptions.

What that rules out#

Renaming a column. It breaks the old version the instant it runs.

Dropping a column that is still in use. Same problem.

Adding a NOT NULL column with no default. The old version does not know how to fill it, and its INSERT fails.

Changing a column type in an incompatible way. The old version writes a value that no longer fits.

Adding a constraint the existing data violates. It fails right away or blocks legitimate writes.

The expand-contract pattern#

Also called parallel change. Four steps, and each one is an independent, reversible deploy.

1. 1ExpandNew column, nullable. No code uses it yet.
2. 2Write to bothThe new code writes to the old column and the new one.
3. 3Migrate the historySmall batches, with pauses, fill in the past.
4. 4ContractRead only from the new one. The old goes in a later deploy.

Step 1: Expand#

Add the new structure without touching the old one. New column, nullable, no constraint. Nothing breaks, because no code uses it yet.

Step 2: Write to both#

The new version of the code starts writing to the old column and the new one. Deploy. Now every new piece of data exists in both places, and the old version keeps working by reading the old one.

Step 3: Migrate the history#

A batch process fills the new column for the old records. In small batches, with a pause between them, so it neither locks the table nor floods the replication log.

Step 4: Contract#

Once everything is filled in and no old version is still running, the code starts reading only from the new column. In a later deploy, not the same one, you drop the old column.

Four deploys instead of one. It looks bureaucratic. It is what turns a migration into a Tuesday afternoon task instead of a middle-of-the-night window with the whole team awake.

And each step is reversible on its own, which is the property that matters most when something goes wrong at eleven at night.

What goes wrong at volume#

A migration that runs in milliseconds on an empty table can lock the table for twenty minutes in production, with ten million rows.

The classic cases, on Postgres:

CREATE INDEX locks writes. Use CREATE INDEX CONCURRENTLY. It is slower, it does not lock, and it can fail leaving an invalid index you have to drop by hand. Learn that behaviour before deploy night.

ALTER TABLE that rewrites the table. Changing a column type usually rewrites everything, with an exclusive lock. On modern versions, adding a column with a default no longer rewrites, but check your version.

Adding a foreign key validates the whole table under a lock. The way out is to add it as NOT VALID and then run VALIDATE CONSTRAINT, which uses a weaker lock.

A mass UPDATE generates an enormous amount of WAL, delays replicas and can fill the disk. Always in batches, with pauses, watching the lag.

The lock that waits. On Postgres, an ALTER that needs an exclusive lock joins the queue, and while it waits, it blocks every query arriving after it. One long transaction holding a light lock can, indirectly, lock the whole table. Use a short lock_timeout and retry, instead of leaving the command hanging.

Test the migration at volume#

This is what almost nobody does and what prevents the most surprises.

Run the migration on a copy with a volume close to production, even just once, before the big deploy. Time it. If it takes twenty minutes, you have just discovered you need a different approach, and you discovered it on a Tuesday, not in the middle of the night.

And test the migration backwards too. Plenty of tools generate the rollback script automatically and nobody ever runs it. On the day you need it, it does not work.

Data migration versus schema migration#

It is worth separating the two.

A schema change is fast or slow depending on the command, and is reversible in most cases.

A data change (recomputing a field, normalising values, reprocessing history) is always slow and frequently irreversible. Treat it as a stateful batch process: record what has already been processed, allow resuming, and make the batches idempotent.

The table rename case#

The same principle, one level up. You want to rename users to accounts.

Expand: create the new table and a trigger replicating writes from one to the other. Or a view with the old name pointing at the new table, if your database allows writing through views.

Migrate: copy the history in batches.

Contract: move the reads, then the writes, then drop the old table.

The same choreography, more moving parts. And the question worth asking first: does this rename deliver enough value to justify four deploys? Sometimes the honest answer is no.

In summary#

Three rules that avoid most incidents:

1. Never rename. Add, migrate, drop.
2. Every migration is compatible with the previous version of the code.
3. Test at volume first.

And a fourth one, which is more cultural: the migration is not an appendix to the task. It is part of the design, and it deserves the same care as the code.