Batched Backfills: Updating Millions of Rows Safely

The one thing to understand first

Running UPDATE huge_table SET col = ... in a single statement looks simple but is operationally hostile:

• It holds row locks on every updated row until commit — blocking concurrent writers for the whole duration.
• It generates one enormous transaction's worth of WAL, which can stall replication and fill disk.
• It creates millions of dead tuples at once, causing a bloat spike that vacuum must chase.
• If it fails near the end, the entire change rolls back — hours of work lost.
• It holds back the vacuum horizon for its entire (long) lifetime.

A backfill should be many small committed batches, not one giant transaction — each batch releases its locks, bounds its WAL, and lets vacuum and replicas catch up. The fix is to do the same work as many short, idempotent, resumable chunks.

Keyset batching (the right way)

Drive batches by the primary key so each chunk is an indexed range — never OFFSET, which re-scans more rows every iteration:

-- Pseudocode loop in your migration runner
last_id := 0;
LOOP
  UPDATE big_table
  SET    token = gen_random_uuid()
  WHERE  id > last_id
  AND    id <= last_id + 10000
  AND    token IS NULL;          -- idempotent: only unprocessed rows

  -- record the new high-water mark
  last_id := last_id + 10000;
  COMMIT;                        -- each batch is durable & releases locks
  EXIT WHEN last_id >= (SELECT max(id) FROM big_table);
  PERFORM pg_sleep(0.1);         -- throttle: let replicas/vacuum catch up
END LOOP;

Each batch is a short transaction: locks are released at its commit, WAL is bounded, and dead tuples from earlier batches can be vacuumed while later batches run.

Make it idempotent and resumable

Always include a predicate that excludes already-processed rows (token IS NULL, or a migrated_at timestamp). Then a failed or interrupted backfill can simply be re-run — it picks up where it left off and never double-processes a row. Store the high-water key if you cannot derive "unprocessed" from the data itself.

Layer 3 — Watch it happen on your own database

-- While the backfill runs, watch the two things it can break:
-- 1) Replication lag — pause the job if replay_lag grows
SELECT application_name, state,
       replay_lag, write_lag
FROM   pg_stat_replication;

-- 2) Dead-tuple / bloat accumulation on the target table
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables WHERE relname = 'big_table';

-- 3) Progress: how much is left to do
SELECT count(*) AS remaining FROM big_table WHERE token IS NULL;

These three queries are your dashboard. replay_lag climbing means your throttle is too aggressive — slow down or raise the sleep. n_dead_tup growing faster than autovacuum can clear it signals a bloat runaway. And the remaining count both tracks progress and proves the job is idempotent (it only ever shrinks, and a re-run resumes from where it stopped).

Layer 4 — The levers this hands you

• Batch size: start at 1,000–10,000 rows — big enough to amortise per-statement overhead, small enough that each transaction is sub-second and locks little. Drive batches by indexed primary key, never OFFSET.
• Idempotency: include a predicate that excludes processed rows (token IS NULL or a migrated_at timestamp) so the job is resumable after any failure.
• Throttle: a brief pg_sleep between batches lets replicas apply WAL and autovacuum keep up; pause if pg_stat_replication.replay_lag grows.
• Bloat control: let autovacuum run between batches, or run manual VACUUM periodically; temporarily lower autovacuum_vacuum_scale_factor, and lower fillfactor beforehand if the backfill can stay HOT.
• It is the "migrate" phase of expand/contract: verify counts before switching reads and contracting.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle DBAs batch big DML too, but for partly different reasons:

• No UNDO/rollback-segment limit driving the chunking. In Oracle, a giant UPDATE risks ORA-01555 "snapshot too old" and UNDO exhaustion. In Postgres the pressure is different — dead-tuple bloat and the vacuum horizon — but the remedy (commit in batches) is the same.
• Long transactions block vacuum, not just UNDO. A long-lived Postgres backfill holds back the global xmin horizon, stalling vacuum cluster-wide — arguably a sharper penalty than Oracle's.
• **No DBMS_PARALLEL_EXECUTE.** Oracle ships a built-in chunked-update package; in Postgres you write the keyset loop yourself (or use a tool), giving full control over throttle and resumability.
• WAL ≈ redo. A single huge transaction floods WAL/redo and can stall standbys on either engine — batching bounds the volume per commit.

Key takeaway

Replace one giant UPDATE with a keyset-driven loop: chunk by indexed primary key, commit per batch, and include an idempotent predicate so the job is resumable. Each short transaction releases locks, bounds WAL, and lets autovacuum and replicas keep pace. Watch pg_stat_replication.replay_lag and n_dead_tup, throttle with pg_sleep, and verify the remaining count hits zero before advancing the expand/contract migration.