Backfill in batches, not in one UPDATE

Sixty million rows in orders, a new nullable column total_cents from expand-and-contract, and a Friday afternoon. The question is how to fill it.

The naive answer is one statement. UPDATE orders SET total_cents = ROUND(total * 100). On a test database it works. On production it holds row locks on the whole table for the whole run, writes one giant chunk of WAL, and the replica falls minutes behind. On MySQL the binlog gets the same present. One project I worked on learned this on that Friday. Since then I backfill only in batches.

The pattern is a loop over primary key ranges:

$lastId = 0;
do {
    $count = $db->executeStatement(
        'UPDATE orders SET total_cents = ROUND(total * 100)
         WHERE id > ? AND id <= ? AND total_cents IS NULL',
        [$lastId, $lastId + 5000]
    );
    $lastId += 5000;
    usleep(200_000);
} while ($lastId < $maxId);

Each batch is a short transaction. Locks live for milliseconds. Replication chews small pieces. The usleep is throttling, and the number is tuned by watching replica lag, not by feeling.

Store $lastId somewhere persistent after each batch. The script will die. Deploy, OOM, someone closes the terminal. With a checkpoint you continue from where you stopped. Without it you start from zero and pretend that was the plan. The IS NULL condition makes the whole thing idempotent, which is the same insurance from the other side.

While the backfill runs, the application must dual-write: every new or updated row fills both columns. Otherwise you chase a moving target. Reads fall back to the old column when the new one is null.

Only when the backfill is done and verified, add NOT NULL. On Postgres, add CHECK (total_cents IS NOT NULL) NOT VALID first, validate it separately, then set the constraint. Validation scans without blocking writes.

Nobody remembers that a backfill took three days. Everybody remembers who took the site down.