The incident that taught us expand-and-contract migrations

The alert fired at a bad hour, as they do. The system wasn't down. That was the problem. It was up and confidently serving wrong answers, which is a lot worse than an outage because nobody comes looking.

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.

The trigger was boring. A suspended seller kept transacting through a second account. The system had no way to represent that, so it picked one, and trust and safety reviewers spent the next day proving it wrong with exported CSVs.

-- 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.

By the time anyone looked, close to 80,000 listings a month had gone through the affected path. Only a slice of it was wrong, and we couldn't tell which slice without a full replay. The replay was the one thing we'd never tested.

WHERE IT GOES WRONG

• 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.
• 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.

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

WHAT CHANGED AFTERWARDS

Two action items survived: the two that removed a decision. Everything on the list that asked someone to be more careful was quietly dead within a quarter, which is roughly what we expected when we wrote it.