Batching Large PostgreSQL Updates to Reduce Locking Risk

When a PostgreSQL migration changes many rows in a live table, one large UPDATE holds locks for a long time and competes with writes from the application. Smaller transactions update fewer rows at a time, so each transaction holds its row locks for less time.

Batching does not remove table-level locks. Every UPDATE takes a ROW EXCLUSIVE table lock, which is compatible with ordinary reads and writes. Each committed batch releases its row locks sooner, so other transactions spend less time waiting to change the same rows. PostgreSQL lists table bloat, replication lag, and lock contention as possible effects of large updates, and recommends considering smaller batches when these occur. Check the documentation for your PostgreSQL version before running a migration.

Update a limited set of rows per transaction

PostgreSQL does not support a LIMIT clause directly on UPDATE. One approach uses a common table expression, a named query inside a statement, to select a limited set of rows and then update them in that same statement:

WITH batch AS (
    SELECT w.ctid
    FROM public.work_item AS w
    WHERE w.status = 'active'
      AND w.num_retries > 10
    ORDER BY w.retry_timestamp, w.id
    LIMIT 1000
    FOR UPDATE
)
UPDATE public.work_item AS w
SET status = 'failed'
FROM batch AS b
WHERE w.ctid = b.ctid
RETURNING w.id;

Here, ctid is PostgreSQL’s physical identifier for a row. The statement uses it to match the selected rows to the update. It is suitable for this repeated, single-statement pattern, but it is not a durable checkpoint to store between runs. PostgreSQL documents a similar approach in its UPDATE guidance.

The 1000 rows in this example illustrate the pattern; they are not a recommended batch size. PostgreSQL’s example uses 5,000 rows, but that is not a universal target. The cost depends on how wide the rows are, which indexes the table has, how much traffic runs at the same time, and how much capacity the replica has. Start with a small batch in an environment that reflects your production setup. Adjust the size based on transaction duration, application latency, lock waits, and replication lag.

Run the statement repeatedly and commit after each batch. If a migration framework wraps the entire loop in one transaction, the row locks remain held until that transaction ends, defeating much of the benefit. A separately managed worker or migration step can commit each batch independently.

For a job that can resume after a pause, make the condition that determines which rows need updating explicit, and choose an order that moves through those rows predictably. An indexed key, such as a primary key, can help the job find the next rows efficiently. If the ordering column has duplicate values, add a unique tie-breaker. Check that changes made by the application while the job runs will not cause its condition to skip rows that still need updating. The right key and condition depend on the table’s data and update rules.

Make the backfill safe to run alongside application changes

Keep a large data rewrite separate from schema changes that require stronger locks. A backfill updates existing rows to match a new schema. One migration sequence adds a schema element that works with the existing code, deploys code that handles both old and new data, updates the rows in batches, checks the new values, switches application reads or writes, and removes the old structure later. This expand-and-contract approach depends on how the application behaves, so check that every deployed version can operate safely during the transition.

Before running the backfill, inspect its query plan, the steps PostgreSQL chooses to find and update rows. A small batch limit does not guarantee that each batch is cheap. If PostgreSQL scans much of the table to find rows that meet the condition, each batch can still use substantial resources. EXPLAIN shows the planned scan and join steps. EXPLAIN ANALYZE runs the statement. For a statement that changes data, PostgreSQL documents running it inside a transaction and then rolling the transaction back. The test still does the work, so use care on a busy database. See PostgreSQL’s EXPLAIN documentation.

Set a migration-specific lock_timeout to limit how long a batch waits to acquire a lock. A statement_timeout instead limits the total time a statement runs. Configure either setting for the migration session or transaction according to service needs; a timeout does not remove the underlying contention. If a batch times out or hits a deadlock, roll it back and retry under a deliberate policy rather than immediately repeating it in a tight loop. PostgreSQL explains these settings in its client connection defaults.

Watch for lock waits, replication lag, and cleanup work

While the backfill runs, monitor application latency and errors along with PostgreSQL activity. The pg_stat_activity view shows active queries, transaction age, and wait events. Use pg_blocking_pids() to find sessions that are blocking a lock request. The pg_locks view shows lock state, but a row-level wait often appears as a wait on the transaction ID of the session holding the row lock, not as a tuple-lock entry. PostgreSQL explains this detail in its pg_locks documentation.

Also monitor replica lag, available disk space, increases in table and index size, and autovacuum activity. PostgreSQL uses multiversion concurrency control, which keeps older versions of rows until nothing needs them. Vacuum makes space from deleted or outdated rows available for reuse, but ordinary VACUUM generally does not shrink the table file on disk. VACUUM FULL rewrites the table and takes an ACCESS EXCLUSIVE lock. That lock blocks ordinary reads and writes. A large update needs disk space for both the writes and the cleanup afterward. See the routine vacuuming guidance.

Give the migration a clear way to pause or slow down. If lock waits, application latency, or replica lag exceed the service’s limits, stop or slow the backfill and investigate before resuming. Adding workers increases write pressure and contention, so it does not automatically make the job safer. Start with one worker. Add more only when monitoring shows that the database has capacity.

If multiple workers do run, SKIP LOCKED lets each worker move past rows another transaction has locked. However, a worker that finds no available rows has not proved the migration is complete; other workers may hold the remaining rows. Finish with a pass that does not skip locked rows, or use another completeness check. PostgreSQL calls out this caveat in its UPDATE documentation.

Handle schema operations as separate steps

Batching the backfill does not make related schema changes nonblocking. The lock required by ALTER TABLE depends on the specific subcommand, and some forms require ACCESS EXCLUSIVE, which conflicts with ordinary reads and writes. Review the lock requirements for each planned change in the ALTER TABLE documentation.

You can stage some constraint changes. PostgreSQL lets you add a constraint as NOT VALID, which skips checking existing rows when you add it. You can validate the constraint in a later step. Validation checks existing rows while allowing concurrent updates, but it still takes a table lock. Similarly, CREATE INDEX CONCURRENTLY allows writes while PostgreSQL builds the index, but takes longer and does more work than a regular index build. It cannot run inside a transaction block. Plan each step separately and account for its lock and resource demands.

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