The number worth staring at in that fintech story is not the eighteen hours. It's the eight. After the weekend where a single UPDATE on a 1.2 billion row transactions table filled the primary's disk to 95 %, pushed the replica four hours behind and then spent four more hours rolling back to change exactly zero rows, the same team came back and did the same work in about eight hours with batch size 1000, a 20 ms sleep between batches, the last processed id parked in Redis and the throttle reacting to replication lag. Nobody got paged. Here's my claim: the loop that made that possible is the least interesting thing they built.

CREATE TABLE backfill_state (
  name TEXT PRIMARY KEY,
  last_id BIGINT NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO backfill_state (name, last_id)
VALUES ('transactions_usd', 12345)
ON CONFLICT (name) DO UPDATE
  SET last_id = EXCLUDED.last_id, updated_at = NOW();

Look at the measured numbers and you'll see why speed was never the argument. On 100K rows, the single UPDATE finished in 122 ms. The same work split into 100 batches of 1000 took 688 ms of wall time, of which only 176 ms was actual query time, averaging 1.77 ms per batch. The chunked version is roughly five times slower and that is the point of it. You are not buying throughput, you are buying transactions that hold locks for two milliseconds instead of twenty minutes, so the WAL can truncate and the replica can breathe. Extrapolate the fast version linearly to a billion rows and you get about 1200 seconds of one uninterruptible transaction, with the delightful property that aborting it is more expensive than letting it run.

Keyset pagination gets quoted the most, and it deserves the citation. Even at a toy scale of 100K rows under PHP 8.3.6 against SQLite, OFFSET 0 cost 0.6 ms while OFFSET 80000 cost 2.0 ms, because the engine walks and throws away everything it skips. The keyset reads sat between 0.2 ms and 0.5 ms no matter where in the table they landed. At 900 million rows deep, that gap stops being a factor of three and starts being minutes per batch. Fine. But WHERE id > :last_id ORDER BY id LIMIT :batch_size is one line, and the trap around it is narrow: your key has to be indexed and monotonic, and if it isn't unique you need something like (created_at, id) to keep the ordering stable. If OFFSET is the hardest problem in your backfill, you have already solved the hard ones.

The honest objection first, because it's a good one. Most tables are not billion-row tables. Writing a checkpointed, throttled, adaptively paced framework to touch 40,000 rows is theatre; the plain UPDATE commits before your deploy hook has finished printing. And for structural changes, gh-ost, pt-online-schema-change, pg_repack and pg_squeeze have been chunking and throttling and swapping tables for years, better than the script you'll write on a Tuesday. Both true. Where I still land differently is on the shape of the risk: guessing wrong toward chunking costs you an afternoon, and guessing wrong the other way costs you a rollback you cannot cancel, on a Saturday, on the primary.

Which brings me to the part that has nothing to do with SQL. A safe backfill is a sequence of releases. First you ship application code that writes both total_cents and total_usd_cents so every new row arrives already correct. Then, and only then, you run the job that fills the historical rows where total_usd_cents IS NULL. Later, in a separate release, you flip reads over. That predicate is also what makes reruns free: a row already processed simply doesn't match. Three deploys and a job that runs for eight hours in between cannot live inside a migration file that your deploy pipeline waits on. It needs to be a worker, or a queued job with retries under Symfony Messenger or Laravel Queue, with a name, a runbook and somebody who knows how to stop it.

Two things I'd push harder than the playbook does. One: the done-signal, SELECT COUNT(*) FROM transactions WHERE total_usd_cents IS NULL reaching zero, is a snapshot and rows keep arriving, so check it twice before you declare victory and start deleting columns. Two, and this is the one that actually scares me, chunking protects the database and does nothing at all for correctness. If compute_usd rounds the wrong way or picks the wrong day's rate, you now own 1.2 billion rows of confidently wrong money, and unless you kept the originals somewhere you have nothing to diff against. Test the conversion on a sample until it's boring, keep the source values, and put a pause flag in Redis so an operator can freeze the job during an unrelated incident without killing the process. Our Go-writing neighbours will show you a worker pool for this. A PHP CLI script with usleep and a flag is genuinely fine, because the bottleneck was never the language, it was the disk under the WAL.

So where's your line? Mine is somewhere around a few million rows, past which I won't approve a bare UPDATE in a migration, but I've never written that number down and I suspect neither have you. Do you gate it in review, or do you have a base class everyone inherits from? And the follow-up I'm genuinely undecided on: for a table this size, do you actually split the id space across four parallel workers, or is one worker with an adaptive sleep the version that lets you sleep too?