Database migrations without downtime: expand, migrate, contract

In the days before 1 August 2012, Knight Capital, one of the largest market makers in US equities, installed new trading code on its order-routing servers. The new version reached seven of the eight. The eighth kept the old version. The new code reused a flag that, in the old code, switched on a function retired years earlier. When trading opened on 1 August, the eighth server read the flag the old way. In 45 minutes it sent millions of orders into the market, and Knight lost more than $460 million.

The cause wasn't a bug in either version. Each one was correct on its own. The failure happened because, for a while, both versions ran at the same time and gave the same shared thing a different meaning.

Knight's shared thing was a flag in its order messages. In most applications it's the database. Every deployment has a window in which the old code and the new code run against the same database, and every schema change has to be safe for both of them. Google's Site Reliability Engineering book estimates that roughly 70% of outages are caused by changes to a live system. Database changes are among the riskiest of those, because they can't simply be rolled back.

Why old and new code always overlap

It's tempting to think of a deployment as an instant switch. It almost never is:

• Rolling deployments (Kubernetes, most container platforms, several application servers behind a load balancer) replace instances one by one, so both versions serve traffic for minutes.
• Serverless platforms keep the previous version's functions running until their requests finish, and often build the new version while the old one serves everything.
• Migrations usually run before the new code is live. A typical pipeline runs php artisan migrate, payload migrate or prisma migrate deploy as a build or release step, and only then switches traffic. For that whole time, the old code is running against the new schema.
• Caches and browsers keep serving pages and scripts built by the old version.

So the real question for every migration is: will the code that's currently running still work after this change?

The pattern: expand, migrate, contract

The safe way to change a schema is to split every breaking change into steps that are each compatible with the code around them:

1. Expand. Add what the new code needs: new tables, new nullable columns, new indexes. Nothing is removed or renamed, so the old code doesn't notice.
2. Deploy the new code. It uses the new structures. If data has to move, it writes to both the old and the new place, or a backfill copies existing rows across.
3. Contract. Once nothing reads the old structures any more, and only then, a second migration removes them.

A rename becomes add, copy and drop. A new required column becomes a nullable column, a backfill, and then the constraint. It's more steps, and every step is safe to run while the site serves traffic.

I used exactly this on a site I maintain, when its article topics were replaced by categories and tags. The migration tool generated a single migration that created the new tables and dropped the old ones in one go. Running it as generated would have broken the live site for the length of the deployment, because the code that was serving pages still read the old tables. Split into two, the first migration only added, so the running site never noticed it. The second removed the old tables after the new code was live and nothing read them any more.

What breaks the running code

Most changes are safe in only one direction. These are the usual suspects:

• Dropping or renaming a column or table that the running code still reads or writes.
• Adding a NOT NULL column without a default. The old code doesn't know about the column, so its inserts start failing.
• Changing a column's type or meaning while the old code still writes the old format.
• Making a constraint stricter (a new unique index, a foreign key) when existing rows or the old code's writes don't satisfy it.

The other risk is locking. Some schema changes lock the table while they run, which on a large table means every query waits:

• PostgreSQL adds a column with a constant default instantly since version 11. Creating an index normally blocks writes, while CREATE INDEX CONCURRENTLY doesn't, but it can't run inside a transaction, which many migration tools use by default.
• MySQL 8 can add columns instantly (ALGORITHM=INSTANT), but many other changes still copy the whole table. Tools such as gh-ost or pt-online-schema-change exist for exactly those.

Your migration tool doesn't know your deployment

Migration generators compare your models with the database and produce the SQL that gets from one to the other. They know nothing about the code that's running while the SQL executes. A few things to watch:

• Renames look like drop-and-create. Drizzle's generator asks interactively whether a table or column was renamed or created, and a wrong answer loses the data. Prisma generates a drop and an add and warns about data loss. Rails and Laravel make you write renames explicitly.
• The generated order isn't always safe. In the migration from my example, the generated SQL dropped a table with CASCADE before dropping the foreign keys that pointed at it, so the next statement would have failed. Generated migrations deserve a review like any other code.
• Tooling can enforce the rules. In Rails, the strong_migrations gem refuses known-dangerous operations unless you confirm them. Other ecosystems have linters for SQL migrations. Where nothing like that exists, the review is the safeguard.

By environment

Where the application runs changes when migrations run and how long the overlap lasts:

• A single server with a deploy script. The simplest case: migrate, then restart. The overlap is short, but it isn't zero: requests in flight during the restart still run the old code.
• Rolling deployments. The overlap lasts as long as the rollout. Expand and contract is the only safe approach. A migration that runs as a separate job before the rollout keeps it from running once per instance.
• Blue-green deployments. Two full environments share one database, so the old environment has to keep working against the new schema until traffic has switched and the old one is shut down.
• Serverless and build-time migrations. The migration often runs during the build, while the previous version serves all traffic, and a failed build leaves the migration applied with the old code still live. Additive migrations make that harmless.

Rollback is a code operation

Rolling back code takes seconds. Rolling back a migration often isn't possible at all: a dropped column takes its data with it, and a "down" migration usually restores the structure, not the contents. That's the strongest argument for expand and contract. If each step only adds, the previous version of the code still works against the new schema, so rolling back is just deploying the old code again. The destructive step comes last, when nothing depends on what it removes, and when you're confident that you won't need to go back.

Treat every deploy as two versions

Knight Capital didn't lose $460 million because its new code was wrong, or because its old code was wrong. It lost the money in the 45 minutes when both ran at once and disagreed about something they shared. A database is the most common shared thing in software, and every deployment opens that window. Schema changes that are safe for both versions, made in steps, and with the destructive step last, keep the window harmless.

Sources: SEC order in the Knight Capital case; Site Reliability Engineering, Introduction (Google).