Database Backfill Without Locking: Batched Updates in Postgres

Why a single large UPDATE locks you into trouble

A backfill is a write that populates or rewrites existing rows so they match a new shape: filling a newly added column, normalizing a format, or copying data into a new table. In a monolith, the natural place to put it is the migration file, as one statement like UPDATE orders SET status_code = .... On a large, live table, that single statement causes several problems at once.

• Row locks held until commit. Every row the UPDATE touches stays locked until the transaction commits. Application writes to those rows queue behind it. Reads are not blocked, but a checkout request that updates an order waits for the whole backfill to finish.
• A WAL burst. Every modified row is written to the write-ahead log. Replicas have to replay all of it, so replication lag climbs, and reads routed to replicas return stale data.
• Dead tuples. Postgres implements UPDATE as insert-new-version, mark-old-dead. Rewriting every row can roughly double the table's on-disk footprint until autovacuum catches up.
• A held vacuum horizon. While the transaction is open, vacuum cannot remove dead rows that are newer than its snapshot. This applies to every table, not only the one being backfilled, so bloat grows across the database.
• All-or-nothing failure. If the statement fails at 90%, everything rolls back. You have spent the time and I/O, gained nothing, and left more dead tuples behind.

The alternative is to treat the backfill as a resumable, throttled background job. The job moves through the table in small batches, and each batch runs in its own short transaction. This tutorial is for backend and platform engineers who need to change live tables they cannot take offline.

When to use a batched backfill and when to avoid it

Batch the work when a single statement would hold row locks or generate write-ahead log (WAL) for too long. That applies to large tables, hot tables that take constant writes, clusters with streaming replicas that must stay current, and services where no downtime is acceptable. Batching keeps each transaction short, so locks are released quickly, vacuum can keep up, and replicas receive WAL in small increments instead of one burst.

Skip batching when a simpler option fits:

A lazy backfill alone rarely finishes, because rarely accessed rows are never touched. Pair it with a batched sweep if you need the column complete. A stored generated column is not free: adding one rewrites the table.

Behaviour differs across Postgres versions. One example is whether ADD COLUMN ... DEFAULT rewrites the table or only updates the catalog. Check the documentation for your version before you decide whether a backfill is needed at all.

How it works: expand, backfill, contract

Ship each phase as its own deploy. You can then pause or roll back any phase without touching the others.

1. Expand. Add the new column as nullable with no default, or with a non-volatile one. This is a catalog-only change. Before the DDL, run SET lock_timeout = '3s'. You can also set statement_timeout. Even an instant ALTER TABLE needs an ACCESS EXCLUSIVE lock. If a long-running query holds a conflicting lock, the ALTER waits in the lock queue. Every query that arrives after it also waits, including plain SELECTs. A short lock timeout makes the ALTER fail in seconds instead of stalling traffic. Retry it with backoff.
2. Dual-write. Change application code, or flip a flag ahead of time, so every write updates both the old and new columns. Without this, the app can modify a row after the worker has processed it, and that row ends up stale.
3. Backfill. The worker fills rows in small batches. Each batch commits as its own short transaction.
4. Verify. Confirm that no rows are still unpopulated and that none disagree between the old and new columns.
5. Contract. Add constraints, switch reads to the new column, then drop the old one. Use the same lock_timeout guard on each DDL statement.

Copydeploy:    D1 expand    D2 dual-write  D3 backfill      D4 verify   D5 contract
app:       old only     old + new -------------------------------->  new only
worker:    -            -              [b][b][b]...[b]  checks      -
primary:   ALTER (lock) -              batched UPDATEs  reads       ALTER (lock)
replicas:  replay       replay         replay + lag     -           replay

Every batch generates WAL that the replicas must replay. Watch replica lag while the backfill runs, not just load on the primary.

Copy-- D1 expand: catalog-only change, but it still needs ACCESS EXCLUSIVE.
-- Fail fast instead of queueing behind a long query and blocking traffic.
SET lock_timeout = '3s';
SET statement_timeout = '10s';
ALTER TABLE orders ADD COLUMN total_cents bigint;  -- nullable, no default
-- On lock_timeout error: wait with backoff and rerun the ALTER.

-- D2 dual-write and D3 backfill happen in app code and the worker, not here.

-- D4 verify: both counts must be 0 before contracting.
SELECT count(*) FROM orders WHERE total_cents IS NULL;
SELECT count(*) FROM orders WHERE total_cents IS DISTINCT FROM total * 100;

-- D5 contract, step 1: add NOT NULL without a long ACCESS EXCLUSIVE scan.
SET lock_timeout = '3s';
SET statement_timeout = '10s';
ALTER TABLE orders
  ADD CONSTRAINT orders_total_cents_nn CHECK (total_cents IS NOT NULL) NOT VALID;

-- Validation scans the table under a weaker lock that allows reads and writes,
-- so give it a longer statement timeout.
SET statement_timeout = '0';
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_cents_nn;

-- Postgres 12+ uses the validated CHECK to skip the full-table scan here.
SET statement_timeout = '10s';
ALTER TABLE orders ALTER COLUMN total_cents SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_total_cents_nn;

-- D5 contract, step 2: run only after the deploy that switches reads to total_cents
-- and stops writes to total.
SET lock_timeout = '3s';
ALTER TABLE orders DROP COLUMN total;

Shows the expand, verify, and contract DDL for an orders.total to total_cents migration, with each lock-taking ALTER guarded by lock_timeout so it fails fast and can be retried instead of stalling traffic.

Implementing the batched, resumable backfill job

Run the loop in application code or a background worker, not in the database. Each iteration reads the last processed id from a checkpoint table and issues UPDATE ... WHERE id > $last AND id <= $last + $batch AND new_col IS NULL. In the same short transaction, it advances the checkpoint and commits. Short transactions release row locks quickly and let vacuum reclaim dead tuples between batches.

Use keyset ranges, not OFFSET. With OFFSET, Postgres still reads and discards every skipped row, so each batch costs more than the one before it. An indexed range predicate goes straight to the starting key, so every batch costs roughly the same from the first to the last.

Avoid a DO block or a function. A function runs inside a single transaction, which brings back the long-held locks and table bloat you are trying to avoid. Procedures can COMMIT, but only when called outside an explicit transaction block. They also run as one opaque session that is harder to throttle, observe, and stop from your deploy tooling.

The new_col IS NULL predicate makes each batch idempotent. After a crash or deploy, the worker resumes from the checkpoint, and if it re-runs a range, rows that were already filled are skipped.

Edge cases

• UUIDs, sparse ids, composite keys: Arithmetic ranges don't work here. Walk an index with ORDER BY key LIMIT $batch, then store the last key returned as the checkpoint.
• NULL is a valid result: IS NULL can no longer mark a row as unprocessed. Add a marker column, or rely only on the checkpoint key for progress.

Copyimport time
import psycopg2

JOB = "users_email_normalized"
BATCH = 5000
conn = psycopg2.connect("dbname=app user=backfill host=db.internal")

# One-time setup, run before starting the worker:
#   CREATE TABLE backfill_checkpoint (job text PRIMARY KEY, last_id bigint NOT NULL);
#   INSERT INTO backfill_checkpoint VALUES ('users_email_normalized', 0) ON CONFLICT DO NOTHING;

# Upper bound is fixed at start. New rows already get email_normalized from app code.
with conn, conn.cursor() as cur:
    cur.execute("SELECT coalesce(max(id), 0) FROM users")
    max_id = cur.fetchone()[0]

while True:
    # `with conn` commits on success and rolls back on error: one short transaction per batch.
    with conn, conn.cursor() as cur:
        # FOR UPDATE stops a second worker from processing the same range.
        cur.execute("SELECT last_id FROM backfill_checkpoint WHERE job = %s FOR UPDATE", (JOB,))
        last = cur.fetchone()[0]
        if last >= max_id:
            break
        # Keyset range on the primary key. IS NULL makes a re-run of this range a no-op.
        cur.execute(
            """UPDATE users SET email_normalized = lower(email)
               WHERE id > %s AND id <= %s AND email_normalized IS NULL""",
            (last, last + BATCH),
        )
        updated = cur.rowcount
        # Advance the checkpoint in the same transaction as the data change.
        cur.execute("UPDATE backfill_checkpoint SET last_id = %s WHERE job = %s", (last + BATCH, JOB))
    print(f"ids {last + 1}..{last + BATCH}: {updated} rows")
    time.sleep(0.1)  # throttle so replicas and vacuum keep up

conn.close()

A resumable Python worker that backfills one keyset range per short transaction and advances a checkpoint row in the same commit, so it can resume after a crash or deploy.

Throttling, kill switches, and protecting production

Throttle on feedback, not a fixed sleep

A fixed sleep between batches is tuned for one load level, and production load changes. Before each batch, the job should check whether the database can absorb another one. On the primary, pg_stat_replication reports each replica's replay_lag, and you can compare replay_lsn with pg_current_wal_lsn() to get lag in bytes. Also track how long each batch takes and how many active connections the primary has. Set thresholds for each signal. When a signal crosses its soft threshold, double the delay or halve the batch size. When it crosses a hard limit, pause until it recovers. A batch that suddenly takes three times longer than normal usually means it is competing with real traffic.

A kill switch you can flip without deploying

Before each batch, read a flag from a settings row or your feature-flag store. If the flag says stop, the job finishes its current transaction, saves its checkpoint and exits. Resuming later is safe because the checkpoint is already stored. If you can only stop the job with a deploy, it will keep running during an incident while you wait for CI.

Isolation

Run the backfill on a dedicated worker queue with a concurrency limit of 1. Give it a capped connection budget, such as a single connection. This keeps it from starving other background jobs or draining the application's connection pool.

Watch what the updates write

Postgres can do a HOT (heap-only tuple) update when no indexed column changes and the page has free space. Any other update writes a new entry in every index on the table. Backfilling an indexed column guarantees non-HOT updates. That means more WAL, more replication lag and more vacuum work per row, so plan for smaller batches than a heap-only change would allow.

Copyimport time
import psycopg2

LAG_SOFT, LAG_HARD = 64 * 1024**2, 512 * 1024**2  # WAL bytes not yet replayed
SLOW_BATCH_S = 2.0  # roughly 3x the normal batch time measured on a quiet day
batch, delay = 1000, 0.1

# One connection is the job's entire budget. It runs from its own queue with concurrency 1.
conn = psycopg2.connect("dbname=app user=backfill application_name=orders_backfill",
                        options="-c statement_timeout=5s")

def one(sql, args=()):
    with conn, conn.cursor() as cur:  # each call is its own short transaction
        cur.execute(sql, args)
        return cur.fetchone()

while True:
    stop, last_id = one("SELECT stop, last_id FROM backfill_control WHERE job = 'orders_region'")
    if stop:
        break  # kill switch: the checkpoint was saved with the previous batch
    lag = one("SELECT coalesce(max(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), 0)"
              " FROM pg_stat_replication")[0]
    if lag > LAG_HARD:
        time.sleep(30)  # hard limit: pause until replicas catch up
        continue
    start = time.monotonic()
    with conn, conn.cursor() as cur:  # batch update and checkpoint commit together
        cur.execute("""WITH b AS (SELECT id FROM orders WHERE id > %s ORDER BY id LIMIT %s),
                       u AS (UPDATE orders o SET region_code = upper(o.legacy_region)
                             FROM b WHERE o.id = b.id)
                       SELECT max(id) FROM b""", (last_id, batch))
        new_last = cur.fetchone()[0]
        if new_last is None:
            break  # no rows left; the backfill is done
        cur.execute("UPDATE backfill_control SET last_id = %s WHERE job = 'orders_region'", (new_last,))
    if lag > LAG_SOFT or time.monotonic() - start > SLOW_BATCH_S:
        delay, batch = min(delay * 2, 10), max(batch // 2, 100)  # back off
    else:
        delay, batch = max(delay / 2, 0.1), min(batch + 100, 1000)  # recover slowly
    time.sleep(delay)
conn.close()

This backfill loop checks a kill-switch row and replica lag from pg_stat_replication before every batch, backs off when lag or batch time crosses a threshold, and commits each batch together with its checkpoint over a single dedicated connection.

Verifying completion and contracting safely

Before touching the schema, confirm the job actually finished. Run a count of rows where the new column is still NULL but the source column is not. The batch loop's own bookkeeping can be wrong if a batch was skipped after a restart or a dual-write path missed an edge case. That count must be zero. If the table is large, run the check during a quiet period, since it scans the table.

Next, make the database enforce the invariant. Add a CHECK (new_col IS NOT NULL) constraint marked NOT VALID. This needs only a brief exclusive lock because it skips existing rows and applies only to new writes. Then run VALIDATE CONSTRAINT as a separate statement. Validation scans the table under a weaker lock that still allows reads and writes, so it can take minutes without blocking traffic. On some Postgres versions, a validated CHECK lets ALTER COLUMN ... SET NOT NULL skip its own full scan. Confirm this for your version before you rely on it.

Build any new indexes with CREATE INDEX CONCURRENTLY. It cannot run inside a transaction block, so if your migration tool wraps each migration in a transaction, put this step in its own migration and disable the wrapper for it.

Plan for bloat. Every updated row leaves a dead version behind, so the table can approach double its size until vacuum reclaims the space. Keep enough disk headroom for that growth. Tune autovacuum for this table or run a manual VACUUM once the backfill finishes.

Finally, delete the dual-write code and remove the feature flag.

Copy-- 1. Verify: must return 0 before any contract step.
SELECT count(*) AS remaining
FROM orders
WHERE customer_ref IS NULL
  AND legacy_customer_id IS NOT NULL;

-- 2. Add the invariant without scanning existing rows (brief lock only).
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_ref_not_null
  CHECK (customer_ref IS NOT NULL) NOT VALID;

-- 3. Validate separately: scans the table under a weaker lock
--    that still allows reads and writes.
ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_ref_not_null;

-- 4. Optional: on some versions the validated CHECK lets this skip
--    its full scan. Confirm for your version before relying on it.
ALTER TABLE orders ALTER COLUMN customer_ref SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_customer_ref_not_null;

-- 5. Must run outside a transaction block: give it its own migration
--    with the tool's transaction wrapper disabled.
CREATE INDEX CONCURRENTLY orders_customer_ref_idx
  ON orders (customer_ref);

-- 6. Reclaim dead row versions left by the backfill.
VACUUM (VERBOSE, ANALYZE) orders;

The contract sequence for a backfilled column: verify zero remaining rows, add and validate a NOT VALID CHECK, optionally promote it to NOT NULL, build the index concurrently, then vacuum.

Sizing batches and the impact on cost and engineering time

Illustrative estimate (assumed numbers, not a benchmark). Assume 50M rows, 5,000-row batches, 200 ms of work per batch, and a 100 ms pause between batches:

• Batches = total rows ÷ batch size = 50,000,000 ÷ 5,000 = 10,000
• Runtime = batches × (work + pause) = 10,000 × 0.3 s = 3,000 s ≈ 50 minutes

Put your own row count and measured per-batch timings into the same formula. Work time per batch does not scale linearly with batch size, so measure it for each batch size you try.

No batch size is right for every system. Larger batches finish sooner, but each one holds row locks longer and produces WAL in bursts. Start small, watch replica lag and p99 write latency, and increase the batch size only while both stay within your limits.

The development cost is real. You need dual-write code paths, a feature flag for each phase, a checkpoint table, and a cleanup step that removes all of it. Teams often forget the cleanup, and the flags and dual writes stay in the codebase for months.

The time comes back later. Once you have one backfill job, it becomes a template for the next one. The checkpoint lets a crashed run resume without manual repair. Short transactions mean fewer incidents caused by lock pile-ups, and schema changes ship as normal deploys instead of waiting for a maintenance window.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to database backfill without locking, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.