The arithmetic of expand-and-contract migrations

The question is never whether something is worth doing in the abstract. It's whether it's worth doing at your volume, with your failure rate, valuing your team's time properly. So let's do the sum.

A schema change and a code change can't be simultaneous, because a deploy isn't atomic. Accept that and the technique falls out: add the new shape, write both, move readers, then remove the old one. Four boring steps instead of one exciting one.

THERE IS ALWAYS A MOMENT WITH BOTH

During any deploy, old code and new code are both running. Any migration that assumes otherwise has a window where one of them is talking to a schema it doesn't understand. On a quiet system you might not notice. On a busy one, that window is thousands of requests.

Renaming a column is the classic. It's one line in a migration file and it breaks every instance still running the previous release. The fix isn't care, it's sequence: add the new column, write to both, backfill, switch reads, then drop.

EACH STEP DEPLOYS ALONE

The discipline is that every step has to be safe with either version of the code running. That means separate deploys, and it means the backfill is its own step you can watch. It feels slow. It's slow in the way that a checklist is slow.

The step people skip is the last one. Dropping the old column is unglamorous and nobody's asking for it, so it doesn't happen, and a year later the table has three columns for the same thing and no way to know which is authoritative. Put a date on it in the same pull request.

At about 9,000 referrals a month with a two percent exception rate, care coordinators were absorbing about nine hours of manual reconciliation a week. That's the number the build had to beat, and it's a lower bar than anyone in the room expected.

-- 1. expand: additive only, old code unaffected
ALTER TABLE tender ADD COLUMN accepted_at timestamptz;

-- 2. dual-write happens in application code, deployed separately

-- 3. backfill in bounded, resumable batches
UPDATE tender SET accepted_at = accepted_date::timestamptz
WHERE accepted_at IS NULL AND id > $cursor
ORDER BY id LIMIT 5000;

-- 4. contract: only after every reader is on the new column
ALTER TABLE tender DROP COLUMN accepted_date;

BACKFILLS ARE THEIR OWN PROBLEM

A backfill that updates ten million rows in one transaction will lock something important. Do it in batches, with a bound on how long each batch runs, and make it resumable. It'll be interrupted, and restarting from the beginning isn't acceptable at that size.

Have it report progress somewhere a human can see. A backfill that's silently been stalled for six hours is a common and thoroughly avoidable way to lose an afternoon.

The interesting term wasn't engineering time. It was the cost of a referral sat unrouted for eleven days because its owner had left landing once in the wrong quarter, which the client could size to the pound and we couldn't size at all.

WHERE IT GOES WRONG

• A backfill in a single transaction, holding a lock on the busiest table at midday.
• Skipping the contract step, leaving three columns for one concept and no authoritative answer.
• A backfill with no progress output, stalled for six hours before anyone noticed.
• Renaming a column in one migration, breaking every instance still on the previous release.

Expand, dual-write, backfill, contract. Four deploys, and put a date on the fourth.

WHERE THE MODEL BREAKS

Do the numbers before the meeting, not during it. A decision that survives arithmetic tends to survive the next reorg too, because the reasoning outlives the people who made it.