Ten million rows, and nobody notices

The one-statement version

The obvious way to fill a new column is one UPDATE over the whole table. It takes ROW EXCLUSIVE, which conflicts with no read and no ordinary write, so on the face of it it blocks nobody.

What it does hold is a single transaction, for as long as it runs. Nothing that transaction has touched can be vacuumed while it is open, every replica has to keep its replay slot for it, and if it fails at the ninety-ninth percent the whole thing rolls back and you start again.

ALTER TABLE orders ADD COLUMN memo varchar(64);
UPDATE orders SET memo = note;

Blocked-writes seconds is not the only cost a migration has. A backfill that blocks nothing and runs for six hours is still an incident, it is just a slower one.

What the batch size is actually deciding

Splitting the same work into batches does not reduce the work. Every row still has to be written, every index still has to be maintained, and the total number of bytes is identical.

Write it so you can run it twice

A backfill will be interrupted. Something will time out, somebody will deploy, the script will be killed. The question is only whether re-running it is safe.

The shape that is safe is one that selects the rows still needing work rather than counting through them: WHERE memo IS NULL rather than WHERE id BETWEEN 400000 AND 500000. Run it twice and the second run does nothing; resume it half-way and it picks up exactly where it stopped.

The version that counts through offsets has to be resumed by hand, from a number somebody wrote down, and it is wrong the moment a row is inserted underneath it.

Every good backfill is idempotent for the same reason every good deploy is: not because it is elegant, but because you are going to have to run it again.

MongoDBThe same job, and the same two settings. What changes is that there is no column to be null: a document either has the field or it does not, and the readers have to handle both until the job finishes.