Reducing Lock Contention With PostgreSQL Batch Updates

When you backfill a large PostgreSQL table, you update values in existing rows. Change a limited number of rows that meet the update condition in each transaction, then commit and repeat. A row lock is PostgreSQL’s protection on a particular row while a transaction changes it. Committing each batch releases its locks sooner than keeping one transaction open for the entire backfill. It also gives you clear points to pause or resume the backfill and check its progress. PostgreSQL documents a batching pattern for large updates. In some workloads, smaller batches can help manage lock contention, table bloat, and replication lag (PostgreSQL’s UPDATE documentation).

Batching does not prevent contention, which happens when the backfill and other transactions need to change the same rows. It limits how many rows a transaction locks at once and how long it holds those locks. The pattern below uses a primary key, a column whose value uniquely identifies each row, to select rows in order. It also uses a condition to identify rows that still need updating.

Why one large update can cause trouble

A single UPDATE that changes many rows holds its row locks until its transaction ends. While the update runs, transactions that need to update, delete, or lock those same rows can wait. PostgreSQL also lists table bloat and increased replication lag as possible effects of large updates. A regular UPDATE takes a table-level ROW EXCLUSIVE lock, but this lock does not lock the whole table against other transactions. Contention often centers on the specific rows being changed (PostgreSQL’s explanation of explicit locking).

With one long transaction, you cannot commit part of the backfill and keep the rest in that same transaction. Instead, limit the rows in each update and commit it before starting the next one. PostgreSQL’s UPDATE command has no LIMIT clause, so its documented approach selects a limited set of rows in a common table expression, a named query result, then updates those rows.

Select and update one batch

This example fills normalized_email for rows that have an email but still lack the derived value. It assumes id uniquely identifies each row:

WITH batch AS (
    SELECT id
    FROM users
    WHERE normalized_email IS NULL
      AND email IS NOT NULL
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1000
)
UPDATE users AS u
SET normalized_email = lower(u.email)
FROM batch AS b
WHERE u.id = b.id
  AND u.normalized_email IS NULL
RETURNING u.id;

Run the statement repeatedly and commit after each execution. If your client uses autocommit, each execution commits as its own transaction. Otherwise, issue an explicit COMMIT after each batch. The LIMIT sets the maximum number of selected rows, while ORDER BY id gives the worker a consistent order. FOR UPDATE locks selected rows for the transaction. SKIP LOCKED tells the worker to skip rows another transaction currently holds instead of waiting for them. RETURNING reports the IDs changed in that batch.

You set the LIMIT value when the statement runs; that value does not determine an ideal batch size. Start with a low value, then adjust it based on how long each batch takes and how it affects production traffic. PostgreSQL does not prescribe a universal batch size.

Resume safely and confirm completion

The normalized_email IS NULL condition lets the query resume safely. After the update, that condition is false for the row, so a rerun skips it. The assignment calculates the value from the row’s current email, making a retry safe for this example. If the source column can change during the backfill, decide whether each row should use its latest value or a fixed snapshot. The final condition in the UPDATE also checks that the row still meets the condition when the update runs.

A batch returning no rows does not prove that no eligible rows remain, because another transaction might hold every remaining eligible row. PostgreSQL warns that skipped rows produce an inconsistent view and recommends a final update without LIMIT or SKIP LOCKED to catch rows the batch queries overlooked (the documented batching pattern). If application writes can make rows eligible during the backfill, define which rows belong to the job and run a catch-up pass after the main scan.

For a single worker, omit SKIP LOCKED so the query waits for a selected row instead of skipping it. You can also save the last-processed key and select later keys on the next run. This saved key is a checkpoint. Advance it only after the batch commits: a crash before then can cause the query to repeat work, which the eligibility condition handles. If you advance the checkpoint before the data commits, the query can skip unfinished rows. A primary key provides a lasting way to identify rows, unlike physical row locations such as ctid (PostgreSQL’s guidance on updating data).

Monitor batches and lock waits

Track how many IDs each batch returns and how long each execution takes. Those measurements show whether the job is progressing and whether a smaller limit would create shorter transactions. PostgreSQL exposes lock requests and granted locks through pg_locks, but row-level locks usually do not appear there as direct entries. A session waiting on a row lock typically appears as waiting for the transaction ID of the session holding that lock. Check for ungranted lock requests and identify the transaction holding them using PostgreSQL’s lock-monitoring guidance.

Updates also leave old versions of rows that PostgreSQL later removes through vacuuming. PostgreSQL notes that vacuuming between batches can make sense in some cases, but you do not need to run vacuum manually after every batch. Consider how often the table changes and how much vacuuming normally occurs when planning the backfill.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com