What student records taught us about expand-and-contract migrations

This one starts in a room with registrars and a whiteboard covered in arrows. They'd built a workable process around a system that fought them at every step, and the workarounds had quietly become the institutional knowledge. Nobody could describe the job without describing the workarounds.

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 clearest thing we saw was how registrars handled a contested enrolment record. On paper it's one step. In practice it's five, three of them over the phone, none of them written down. Which is why nobody could ever explain funding return accuracy to their director.

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

Here it showed up as a queue nobody owned. Some 45,000 enrolments a year went through it, and registrars had learned to check it twice a day because the alternative was a withdrawal was backdated after the census had been filed. A better queue wasn't the answer. Making ownership a property of the enrolment record was.

WHERE IT GOES WRONG

• 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.
• A backfill in a single transaction, holding a lock on the busiest table at midday.

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

WHAT WE TOOK AWAY

The work shipped and funding return accuracy moved, but the thing we're proudest of is smaller than the system: registrars stopped keeping a private spreadsheet. That's usually the honest signal that the model finally matches the job.