How to Backfill a PostgreSQL Column in Batches

When a production PostgreSQL table needs a new value for existing rows, update a limited set of rows at a time and commit each batch. This update of existing records is called a backfill. Run each batch in a transaction, a unit of database work that PostgreSQL commits or rolls back as a whole. Short transactions release row locks sooner, limit the work lost if a batch fails, and let you track progress as the migration runs.

Choose an ordering key with a database index, such as the table’s primary key, and advance a checkpoint after each batch commits. PostgreSQL still locks the rows it updates and records each change in its write-ahead log (WAL). Smaller batches limit how long each batch holds locks. Monitor the workload to keep it within production limits.

Prepare writes before the backfill

Add the new column as nullable, then make sure new or changed rows receive its value before the backfill starts. Otherwise, production writes can create new rows that the backfill never sees or change rows after the migration has passed them.

For a new column derived from an existing email value, the schema change could look like this:

ALTER TABLE customers
ADD COLUMN normalized_email text;

Deploy code that writes normalized_email for new customers and updates it whenever email changes. Then backfill older rows. After the backfill, check that no rows remain incomplete before enforcing a NOT NULL constraint.

An UPDATE takes a table-level lock that allows ordinary reads and writes, plus row locks on the rows it changes. Those row locks remain until the transaction ends. PostgreSQL uses multiversion concurrency control (MVCC) so concurrent transactions can see different versions of a row. The MVCC documentation describes this behavior. A large update does not block every write to the table, but it can hold many row locks for a long time, generate substantial WAL, and leave obsolete row versions for PostgreSQL’s vacuum cleanup.

Update rows in an ordered batch

Use a stable key with a database index to select each batch. The example below advances through customers.id, updating only rows whose new column remains null:

WITH batch AS (
    SELECT id
    FROM customers
    WHERE id > :last_id
      AND normalized_email IS NULL
    ORDER BY id
    LIMIT :batch_size
),
updated AS (
    UPDATE customers AS c
    SET normalized_email = lower(c.email)
    FROM batch AS b
    WHERE c.id = b.id
      AND c.normalized_email IS NULL
    RETURNING c.id
)
SELECT
    (SELECT max(id) FROM batch) AS last_scanned_id,
    (SELECT count(*) FROM updated) AS updated_count;

Run each execution as its own transaction. When the client uses autocommit, it commits each statement as it finishes. Store last_scanned_id as the checkpoint only after that commit succeeds. If the statement fails, keep the previous checkpoint and retry; the IS NULL condition makes already-completed rows safe to encounter again.

Ordering by the primary key makes progress predictable and lets the next batch start after the previous key instead of repeatedly searching from the beginning. This keyset approach is preferable to repeatedly using OFFSET, which has to step past earlier rows as the offset grows. The large-table backfill guidance also recommends chunked work rather than a single update across the table.

The checkpoint marks the last key examined, which might differ from the last row changed because some rows in the range might already have a value. If the table continues receiving writes, keep the write path populating the new column and run a final completeness check after the backfill. A checkpoint alone does not catch a row that becomes incomplete behind the scan.

Choose batch size from production behavior

There is no batch size that fits every table and workload. Start with a conservative size, run a batch, and observe its duration, database load, delay on database copies (replica lag), and effect on application latency. Increase the size only while those measures remain acceptable; reduce it when batches hold locks too long or compete with production traffic.

Each batch creates WAL and updated row versions. PostgreSQL keeps old versions until no transaction needs them; vacuum then cleans them up. A sustained backfill can therefore increase WAL volume and vacuum work. If a replication slot or a change-data-capture (CDC) consumer is involved, watch its lag and retained WAL as the backfill runs; the CDC backfill discussion describes why those consumers need separate attention.

Avoid wrapping the entire migration in one transaction. A long transaction keeps its locks until it ends and delays cleanup of row versions it can still see. If it fails, all its work rolls back. Instead, let the client commit each batch and record its checkpoint between batches.

Handle contention and verify completion

Set a lock_timeout appropriate to the production workload so PostgreSQL aborts a statement that waits too long for a needed lock. PostgreSQL aborts a statement that waits longer than this setting; the client should retry the batch after a delay, without advancing the checkpoint. Keep the timeout local to the backfill transaction where practical, so it does not change unrelated sessions.

Before starting, inspect long-running transactions: an open transaction can hold locks or keep using an old snapshot, its view of row versions from an earlier point in time, even when its session is idle. During the migration, inspect pg_stat_activity for the backfill’s transaction age and wait events, and compare batch results with application latency and replica lag. If a batch repeatedly waits or harms production traffic, lower its size or pause the backfill while you investigate the blocker.

After the scan, verify completeness directly:

SELECT count(*) AS rows_missing_normalized_email
FROM customers
WHERE normalized_email IS NULL;

A zero count means every row currently has the value. If writes can still create nulls, keep the write path in place and repeat the check before enforcing the constraint. Schedule any constraint change separately, since schema changes have their own locking behavior.

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