---
title: "Backfill in batches so the application never waits on row locks | Proofline"
description: "A backfill written as one UPDATE locks each row it changes until the statement commits."
url: https://proofline.sh/learn/postgres-backfill-batch-row-locks/
---

[Learning center](/learn/)

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

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

## 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;
```

| Backfill | Took | Longest application update | Median |
| --- | --- | --- | --- |
| One `UPDATE` | 4.4 s | 3,664 ms | 2.9 ms |
| Batches of 1,000 | 5.4 s | 12 ms | 1.4 ms |
| Batches of 10,000 | 4.1 s | 28 ms | 2.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](https://www.postgresql.org/docs/16/explicit-locking.html#LOCKING-ROWS) 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](/learn/postgres-migration-lock-queue/) 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](/learn/postgres-backfill-keyset-pagination/) covers how to walk the table. The full sequence is 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

-   ### [Batch a backfill by key, not by OFFSET](/learn/postgres-backfill-keyset-pagination/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Why a fast PostgreSQL migration blocked every query](/learn/postgres-migration-lock-queue/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
