The skipped half#
The orders table has 1,000,000 rows with an empty state. We ran this on PostgreSQL 16.15. The job selects the next 10,000 unfilled rows by offset:
UPDATE orders SET state = upper(status)
WHERE id IN (
SELECT id FROM orders
WHERE state IS NULL
ORDER BY id
OFFSET $1 LIMIT 10000
);
-- the job adds 10,000 to $1 after each batch, and stops when a batch updates no row
It stopped after 51 statements. Then we counted:
SELECT count(*) FROM orders WHERE state IS NULL;
-- 500000
The first batch filled rows 1 to 10,000. The second asked for the unfilled rows after skipping 10,000 of them, which meant rows 20,001 to 30,000. Rows 10,001 to 20,000 were never touched. Every batch skipped one batch, and the job ended when the offset passed the end of the shrinking set.
Offset over the whole table is slow instead#
Paging the whole table by offset, without the state IS NULL filter, fills every row. It gets slower as it goes, because PostgreSQL has to read and discard every row before the offset, as the LIMIT and OFFSET documentation warns. It also shifts if rows are deleted during the run: the old release deleting 100 early orders moves 100 rows past the next batch.
Keyset batches#
The keyset version starts each batch after the last key of the previous one:
WITH batch AS (
SELECT id FROM orders WHERE id > $1 ORDER BY id LIMIT 10000
)
UPDATE orders o SET state = upper(o.status)
FROM batch WHERE o.id = batch.id
RETURNING o.id;
-- the job sets $1 to the largest id returned, and stops when none is returned
| Method | Batch time, first → last | Total | Left unfilled |
|---|---|---|---|
OFFSET, unfilled rows | not timed | 51 batches | 500,000 |
OFFSET, whole table | 129 → 543 ms | 35.7 s | 0 |
Keyset by id | 114 → 145 ms | 15.6 s | 0 |
The median batch took 371 ms with OFFSET over the table and 152 ms by key.
The keyset batch costs about the same at the end of the table as at the start because it uses the primary key index to find its first row. A deletion or insertion elsewhere cannot move it. Rows the old release inserts after the job passes their key are a separate problem: a trigger has to fill them, as use a trigger when the old release must keep filling the new column shows.
The check#
Count the rows the backfill was meant to change after it reports completion, and compare the count with zero, not with the job's own tally. A job that counts its updates reported 500,000 rows here and looked finished. Find every backfill mismatch with IS DISTINCT FROM gives a count that also finds wrong values. More backfill labs are in moving data while two application releases share one database.