Learning center

Batch a backfill by key, not by OFFSET

A backfill that pages through the rows it still has to fill with OFFSET skips half of them and reports success. Each batch removes its rows from the set it pages through, so the next offset jumps past rows that were never filled. Page by the primary key instead: remember the last key you processed and start the next batch after it.

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
MethodBatch time, first → lastTotalLeft unfilled
OFFSET, unfilled rowsnot timed51 batches500,000
OFFSET, whole table129 → 543 ms35.7 s0
Keyset by id114 → 145 ms15.6 s0

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.

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