Database migrations that don't wake you up at 2am

I have been paged for a migration exactly twice in my career, and both times the migration itself was one line. That is the thing about database migrations: they look like the most boring part of shipping a feature, right up until one of them takes down the app during a normal Tuesday deploy. The code review focused on the new API endpoint. Nobody looked twice at the ALTER TABLE in the same pull request.

The first time, it was a NOT NULL column with no default on a table with a few million rows. Here is roughly what it looked like:

-- risky: locks the table for the full rewrite
ALTER TABLE users ADD COLUMN plan_tier text NOT NULL DEFAULT 'free';

On newer Postgres (11+) adding a column with a constant default is actually cheap, Postgres just stores the default in the catalog and does not rewrite existing rows. But the moment you add NOT NULL without a default, or use a default that is not a simple constant, Postgres has to rewrite every row to fill in the value and validate the constraint, and it holds an ACCESS EXCLUSIVE lock on the table while it does. That lock blocks every read and write against users, including the login query running for every other request on the site. On a small table you would not even notice. On a few million rows, that rewrite took long enough that connections backed up, the pool exhausted, and half the site returned 500s until the migration finished.

The fix was to split one migration into three boring, individually safe steps instead of one risky one.

-- step 1: add the column, nullable, no rewrite needed
ALTER TABLE users ADD COLUMN plan_tier text;

-- step 2: backfill in small batches, not one giant UPDATE
UPDATE users SET plan_tier = 'free'
WHERE id IN (
  SELECT id FROM users WHERE plan_tier IS NULL LIMIT 5000
);
-- repeat until zero rows match, with a short sleep between batches

-- step 3: once every row is backfilled, enforce the constraint
ALTER TABLE users ALTER COLUMN plan_tier SET NOT NULL;

Step 1 is instant, it is just a catalog change. Step 2 does the actual work of filling in five million rows, but in chunks of five thousand at a time instead of one statement touching the whole table. Each batch takes a short lock, finishes fast, and lets other queries interleave between batches. If a batch is slow or the database is under load, you slow down or pause and nobody notices. Step 3 is the one that used to be dangerous, but by the time you run it there are no NULL values left, so Postgres only has to check a constraint against the current data (and modern Postgres can even validate NOT NULL from an existing CHECK constraint without a full scan). None of the three steps holds a lock long enough to matter.

The general pattern is: split anything that touches every row into an additive step that ships immediately, and a cleanup step that ships later, once the world has caught up. Adding a column, backfilling it, and only then adding the constraint is one version of this. Renaming a column is another: add the new column, write to both old and new for a while, backfill, switch reads over, then drop the old column in a separate migration once you are sure nothing still depends on it. It feels slower than just doing the rename, but “slower and boring” beats “fast and an incident.”

The second page I got was not from a lock, it was from a migration that could not be undone. Someone dropped a column that turned out to still be read by a background job that had not been redeployed yet. There was no rollback, because “add the column back” does not get you the data back. The rule I follow now is that every migration needs a rollback plan before it ships, and the plan has to match what the migration actually does. Additive changes (new column, new index, new table) roll back by just not using the new thing, so they are cheap to reverse. Destructive changes (dropping a column, dropping a table, renaming something in place) do not have a cheap rollback, which is exactly why they belong in their own migration, run well after the code that depended on the old shape is fully gone from production. If you cannot describe how you would undo a migration in one sentence, that is a sign it is not ready to ship yet.

The other habit that has saved me more than once is testing migrations against a copy of production-sized data, not the tiny seed data in the dev database. A migration that runs in eleven milliseconds against two hundred rows can run for twenty minutes against twenty million, and the failure modes are completely different at that size, lock contention, disk I/O, autovacuum getting in the way. Restoring a recent production dump into a staging database and running the migration there, with EXPLAIN or just a stopwatch, catches almost everything that would otherwise be a surprise. It is a bit of setup work, but it is a lot cheaper than finding out live.

Which gets at the actual point: a migration is production code, and it deserves the same scrutiny as the endpoint it supports. It runs against live data, it can lock tables, and it usually cannot be undone by just reverting a git commit. I now ask the same questions of a migration that I would ask of any other change to a hot path: what does this lock, for how long, what happens if it fails halfway through, and can I undo it. Most migrations are fine. The ones that are not fine are never the ones anybody thought to double check.