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