---
title: "Batch a backfill by key, not by OFFSET | Proofline"
description: "A backfill that pages through the rows it still has to fill with OFFSET skips half of them and reports success."
url: https://proofline.sh/learn/postgres-backfill-keyset-pagination/
---

[Learning center](/learn/)

# 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.

Breaking migrations The Proofline team November 22, 2026 3 min read

## 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](https://www.postgresql.org/docs/16/queries-limit.html) 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](/learn/postgres-sync-trigger-old-writer/) 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](/learn/postgres-backfill-reconciliation-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](/learn/data-migration-across-service-versions/).

## 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Backfill in batches so the application never waits on row locks](/learn/postgres-backfill-batch-row-locks/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Find every backfill mismatch with IS DISTINCT FROM](/learn/postgres-backfill-reconciliation-is-distinct-from/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
