12 min read

Zero-downtime schema migrations

Senior SQL Reliability3 November 2026

A developer renames users.phone to users.mobile, the migration runs in the deploy, and for the next four minutes half the API returns 500s. Nothing was wrong with the SQL. The problem was timing: a rolling deploy replaces servers a few at a time, so for those four minutes some servers ran the old code, which still read phone, against a database where phone no longer existed.

Every schema change in a system that does not stop for deploys has to work with two versions of the code at once: the one being replaced and the one replacing it. The pattern that makes that routine is called expand and contract (also parallel change). Change the schema in small steps, each of which is compatible with both the code before it and the code after it, and never in one step that breaks either.

Your answer

You need to rename orders.amount to orders.amount_paise on a 400 million row table that takes 3,000 writes a second, without downtime. What steps would you deploy, in what order?

Why a schema change breaks a running system#

Two separate things go wrong, and both need handling.

Old and new code overlap. A rolling or canary deploy runs both versions side by side for minutes. A blue-green deploy runs both against the same database while traffic switches. If a rollback is needed, the old code runs again against whatever schema the new deploy left behind. So at every moment, the schema must work for the version that might be running, in both directions.

DDL takes locks. Most schema changes need a lock on the table, and some hold it for as long as it takes to rewrite every row. In Postgres, ALTER TABLE takes an ACCESS EXCLUSIVE lock, which blocks reads as well as writes. Even a change that is instant once it has the lock must wait for that lock, behind any long-running query, and while it waits, every new query on the table queues behind it. One slow analytics query plus one harmless-looking ALTER is a classic way to take a site down.

1/4 Holds a light lock on the table.

So the two rules: never deploy a schema change that breaks the code version still running, and never run DDL without a lock_timeout (Postgres) or lock_wait_timeout (MySQL) short enough that waiting for the lock cannot become an outage.