Migrations while people are using it

The one that got me

Adding a column with a default and a not null constraint. One line. I had done it many times.

ALTER TABLE students ADD COLUMN status integer NOT NULL DEFAULT 0;

On my machine, on a table of a few thousand rows, instant. In production, on a table several orders of magnitude larger, it took an exclusive lock and held it while it rewrote every row.

For the duration, every read and every write against that table blocked. Not slow. Blocked. Schools were using the system. It was the middle of a working day.

It was not a long outage. It was long enough that people noticed, and long enough that I have never forgotten which operations rewrite a table.

What I did not understand

I thought of migrations in terms of correctness: does the schema end up right. I was not thinking about locks at all, which meant I was not thinking about the only property that determines whether a migration is an outage.

The lock is the whole question. A migration that is instant on a small table and rewrites a large one is not a different migration. It is the same statement with a different cost, and development data is systematically too small to reveal it.

What I check now, before running anything

Three questions, always, in this order.

Does this rewrite the table? Adding a nullable column does not. Changing a column type does. Adding not null to an existing populated column does. If it rewrites, it needs the expand and backfill approach instead.

What lock does it take, and for how long? An access exclusive lock blocks everything, including reads. Taking one briefly is fine. Taking one for the duration of a rewrite is an outage.

How long will it hold on production sized data? Not on my machine. If the answer is unknown, the answer is too long, and it gets tested against a restored copy of production first.

That third question is the one that would have saved me. I had never once tested a migration against production sized data, because everything had always been fast.

The pattern that removed the whole class

Never change something in one step. Expand, migrate, contract.

Expand. Add the new thing alongside the old one. Additive only, nullable, no constraints that existing rows would violate. The old application is unaffected because it does not know the column exists.

Migrate. Deploy code that writes both, then backfill the history in small batches, then switch the reads.

Contract. Remove the old thing, much later, once you have proven nothing reads it.

Six deploys for a rename. That sounds absurd until the first time you do it, and then it becomes the only way that makes sense, because every individual step is safe and reversible on its own.

The reason it is necessary is simple and I had not thought it through: the database and the application deploy at different moments. There is always a window where one version of the code is running against the other version of the schema. Expand and contract exists so that window is harmless in both directions.

The batching detail that matters more than it sounds

Backfilling in one statement is the same mistake as the original one, wearing different clothes. A single update over millions of rows holds one enormous transaction, bloats the table, and blocks writers.

Batch it, with a pause, and watch replication lag rather than the clock.

loop:
  update 1000 rows where the new column is still null
  pause
  if replication lag is rising, pause longer

A backfill taking six hours instead of two is not a problem. A read replica four minutes behind is, because that replica is serving real users stale data and nothing about that looks like a migration problem when it is reported.

The where the new column is still null predicate also makes the whole thing restartable, which matters the first time it fails halfway and you need to just run it again.

Contracting too early, which I have also done

Dropping the old column after the read switch, on the same day, because the deploy had gone well and nothing was using it.

Something was using it. A monthly report, which ran three weeks later and failed.

Reads come from more places than anybody remembers. A scheduled job. An export. An admin script. A dashboard somebody built and did not tell you about.

Now I wait at least a week, confirm from the database’s own statistics rather than from searching the codebase, and then leave the old column in place, unused, for another period before dropping it. Dead columns cost nothing. Dropping one that is still needed costs an afternoon and somebody’s trust.

Across many databases

In a system with a database per tenant, all of the above applies and then multiplies.

The migration must be idempotent and resumable, because running it across many databases will fail partway at some point. It needs a table tracking which tenants are done. It runs in waves, starting with an internal tenant, then a canary, then the rest.

And the application must work while tenants are on mixed schema versions, because between the first and last there is no moment where they all match. Which means expand and contract again, for a different reason, arriving at the same discipline.

What I would tell somebody starting

Test the migration against a restored copy of production, on production sized data, once. It takes an afternoon to set up and it converts migrations from something you hope about into something you know about.

And learn which operations rewrite a table. It is a short list, it does not change often, and not knowing it is how a one line change becomes the reason people could not work that afternoon.