Learning center

Backfill in batches so the application never waits on row locks

A backfill written as one UPDATE locks each row it changes until the statement commits. Any request from the running release that updates one of those rows waits for the whole backfill. In our lab, one statement over a million rows made an application update wait 3,664 ms. Batches of 1,000 rows, each committed on its own, kept the longest wait to 12 ms.

The lab#

The orders table holds 1,000,000 rows with a status and an empty state. A thread acting as release 2026.11.3 updated the status of a random order every 50 ms and timed each update. A second session ran the backfill three ways. We ran this on PostgreSQL 16.15; the timings depend on the machine and only compare the three runs.

-- one statement
UPDATE orders SET state = upper(status) WHERE state IS NULL;

-- batches, one transaction each (the job runs in autocommit)
UPDATE orders SET state = upper(status)
WHERE id > $1 AND id <= $1 + 1000 AND state IS NULL;
BackfillTookLongest application updateMedian
One UPDATE4.4 s3,664 ms2.9 ms
Batches of 1,0005.4 s12 ms1.4 ms
Batches of 10,0004.1 s28 ms2.8 ms

The median hides the problem. Most application updates touched a row the single statement had not reached yet, or did so before it started. The one that touched an early row waited nearly the whole run.

Why the wait lasts until commit#

UPDATE takes a row-level lock on each row it modifies and keeps it until the transaction ends, as the explicit locking chapter describes. A concurrent UPDATE or SELECT ... FOR UPDATE of the same row waits for that end. Plain reads do not wait, so a backfill can look harmless in a read-mostly test. On a table of 100 million rows, the same statement holds its first lock for minutes, and the requests waiting behind it hold their own connections from the pool.

A batch commits its locks away when it finishes. The longest wait an application write can see is one batch's duration.

Choosing the size#

Smaller batches add per-statement overhead: 1,000-row batches took 23% longer in total than one statement. Larger ones lengthen the worst wait. Pick the size from the wait you can accept, not from total time. Measure one batch's duration on production-sized data, and size it so a batch finishes well inside the application's own request timeout. Then add a pause between batches if replicas or vacuum need time to keep up.

Run the job with a lock_timeout as well. If an application transaction holds a row the batch needs, the batch gives up and retries instead of joining a queue. Why a fast migration blocked every query shows that queue for table locks.

The rehearsal#

Rehearse the backfill against a copy at production size while a script repeats the old release's update path and records its latency. Pass when the longest update during the backfill stays under the request timeout you set for the service. Batching by id ranges assumes the key is dense enough that a range holds about one batch of rows; batch a backfill by key, not by OFFSET covers how to walk the table. The full sequence is in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading