Update a Huge Table in Safe Chunks

Rewrite millions of rows without a giant transaction by chunking on a key, committing per batch, tracking progress, and resuming after interruption.

~40 mindatabasepostgresoperations

Task

Run a bulk UPDATE over millions of rows in bounded batches that keep locks short, WAL manageable, and progress resumable.

When to use this

A backfill needs to set a region column on 40 million orders. One UPDATE statement would lock the table for an hour, balloon WAL, and block replication.

Prerequisites

A unique or near-unique key to chunk on, such as an id
Disk headroom for WAL during the operation
A maintenance window soft enough to tolerate elevated I/O

Steps

1. Count the work and choose a batch size.

SELECT count(*) FROM orders WHERE region IS NULL;

Expected output: A total such as 40 million rows divided into batches of 50 thousand means 800 commits, each holding locks for well under a second.

2. Update one bounded batch in its own transaction.

UPDATE orders
SET region = compute_region(country)
WHERE id IN (
  SELECT id FROM orders
  WHERE region IS NULL
  ORDER BY id
  LIMIT 50000
  FOR UPDATE SKIP LOCKED
);

Expected output: One batch commits quickly; the FOR UPDATE SKIP LOCKED clause lets a parallel worker take the next rows without collision.

3. Loop the batch until no rows remain.

while :; do n=$(psql -Atc "UPDATE ... WHERE id IN (SELECT id FROM orders WHERE region IS NULL ORDER BY id LIMIT 50000 FOR UPDATE SKIP LOCKED)"); [ "$n" = "0" ] && break; sleep 1; done

Expected output: The loop prints rows-per-batch, decays to zero, and exits; the sleep between batches leaves replication room to catch up.

4. Watch replication lag while it runs.

SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;

Expected output: Lag stays within the tolerated threshold because each commit is small; if it spikes, lengthen the sleep rather than shrinking the batch.

5. Verify the final state and add the constraint.

SELECT count(*) FROM orders WHERE region IS NULL; -- then: ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

Expected output: Zero remaining rows, and the NOT NULL constraint now validates instantly because every row already satisfies it.

Troubleshooting

Problem

Later batches get slower

Solution

The WHERE region IS NULL predicate rescans the table each round; switch to keyset chunking on id ranges so each batch reads an indexed slice.

Problem

Replication lag keeps climbing

Solution

Insert a sleep proportional to observed lag, or pause the loop until replay catches up; sustained lag starves failover and backups.

Problem

A batch fails halfway

Solution

The transaction rolled back, so no partial batch exists; rerun the loop and it resumes from the rows still NULL, which is why the predicate-based checkpoint matters.

Limitations

Chunking trades total runtime for safety; the whole job runs longer than a single statement would.
Readers may see the new and old values coexisting until the final batch completes, so downstream jobs must tolerate the intermediate state.

Small transactions age well, giant ones do not

One UPDATE over forty million rows is simple to write and terrible to run: an hour of lock retention, a WAL spike that stalls replication, and a failure at 95 percent that rolls everything back. The same work in fifty-thousand-row batches commits in under a second each, stays resumable through the WHERE predicate, and throttles itself to the pace replication can absorb. Boring, observable, and restartable beats fast and catastrophic.

UPDATE orders
SET region = compute_region(country)
WHERE id IN (
  SELECT id FROM orders
  WHERE region IS NULL
  ORDER BY id
  LIMIT 50000
  FOR UPDATE SKIP LOCKED
);

The predicate is the checkpoint

Chunking on the unfinished predicate, region IS NULL, makes the job idempotent and resumable for free: any crash leaves the remaining rows as the exact to-do list. Keyset ranges on id are faster per batch, but then progress must be tracked separately, so pick the simpler scheme unless the table is enormous.

Pace to the slowest replica

The batch size sets lock time; the sleep between batches sets replication pressure. Watch pg_wal_lsn_diff during the run and stretch the sleep when lag grows. A backfill that outruns replication converts a maintenance task into an incident by delaying failover exactly when you are doing risky work.