Large Table Backfill Without Locking: A Postgres Tutorial

Why a single UPDATE or ALTER on a large table hurts production

A lock-free backfill populates or rewrites data in a large table through many small, independently committed batches, so that no statement holds a lock long enough for users to notice. It is not literally free of locks. Each batch still takes row locks, but they last milliseconds instead of hours.

The naive alternatives each cause a different failure.

The table-rewriting ALTER

An ALTER TABLE that rewrites the table needs an ACCESS EXCLUSIVE lock. That lock conflicts with everything, including plain SELECT. If a long-running query or an idle-in-transaction session already holds a weaker lock, the ALTER waits in the lock queue. Every query that arrives after it queues behind the ALTER, even reads that would not conflict with the running query. A table can stop serving traffic before the rewrite has started.

The single giant UPDATE

A single statement such as "UPDATE every row" avoids that table-level lock. It still holds row locks on every row it touches until it commits, so concurrent writes to those rows block.

It also creates a new version of every row. Vacuum cannot reclaim the old versions until the transaction ends, so the table can roughly double in size. The update generates a large burst of WAL. Replicas must replay that WAL, which causes replication lag, and that lag shows up as stale reads.

The statement also cannot be paused or resumed. Cancelling it rolls back every row it changed and still leaves the dead tuples behind.

The core idea

A backfill is a background job, not a migration step. It should run alongside normal traffic. It should be throttled, and it should be safe to stop and restart at any point.

How a lock-free backfill works

The technique replaces one long, blocking operation with several short ones. Each short operation either changes only the catalog or touches a small number of rows in its own transaction.

• Schema changes are catalog-only or fail fast. New columns are added as nullable with no default, so Postgres does not rewrite the table. Every DDL statement runs with lock_timeout, so if it cannot get its lock quickly it gives up and retries instead of queuing application traffic behind it.
• New writes are correct from a fixed point in time. Once the application dual-writes the old and new structures, only historical rows need fixing, and that set no longer grows.
• Historical rows are fixed in small batches. A job walks the primary key with keyset pagination, updates a bounded number of rows per transaction and commits. Row locks last only as long as one batch, and vacuum can reclaim dead tuples between batches.
• Each batch is idempotent and the job is resumable. An IS NULL guard in the UPDATE skips rows that are already done, and a checkpoint table records the last processed key. Stopping and restarting the job at any point is safe.
• The job adapts to the database's health. Before each batch it checks replica lag, lock waits and its own batch latency, and slows down or shrinks the batch when any of them rises. A kill switch lets an operator stop it without a deploy.
• Completion is proven before anything depends on it. Verification queries confirm that no rows are missing values. Only then are constraints enforced, reads switched and the old structure dropped.

The sections below cover each step in that order.

How expand/migrate/contract works for online schema change

Expand/migrate/contract splits one risky change into a sequence of small deploys. Each deploy is safe on its own and can be rolled back without touching data.

1. Expand (deploy 1). Add the new structure: a nullable column with no default, or a new table. In Postgres this changes only the catalog. It needs a brief lock but no table rewrite, so it finishes quickly regardless of table size. Nothing reads or writes the new structure yet.
2. Dual-write (deploy 2). Application code writes both the old and new structures on every insert and update. From this point, new rows are correct and only historical rows are missing values. The set of rows the backfill must fix stops growing, which gives the backfill a fixed end point.
3. Backfill (no deploy). Start the backfill only after deploy 2 is live on every instance. If you start earlier, an old instance can write a row after the backfill has already passed it. Run the backfill as a separate worker or operator-triggered job, not as a step in the deploy pipeline. A multi-hour job inside the pipeline would block releases and could hit deploy timeouts.
4. Switch reads (deploy 3). After verifying the backfill, read from the new structure. Keep dual-writing so you can roll back to reading the old structure.
5. Contract (deploy 4 and later). Stop writing the old structure. Drop it in a later deploy, once no running code references it.

This pattern is for backend and platform engineers running monoliths whose tables are too large or too busy for a blocking migration. It costs extra deploys and temporary dual-write code. In return, no single deploy can lock a hot table for minutes.

CopyDEPLOY PIPELINE (each box is one normal, independently revertible release)
+--------------+   +--------------+   +--------------+   +--------------+
| Deploy 1     |   | Deploy 2     |   | Deploy 3     |   | Deploy 4+    |
| EXPAND       |-->| DUAL-WRITE   |-->| SWITCH READS |-->| CONTRACT     |
|              |   |              |   |              |   |              |
| add nullable |   | write old AND|   | read new     |   | stop old     |
| col or table |   | new on every |   | keep dual-   |   | writes; drop |
| catalog only |   | INSERT/UPDATE|   | writing for  |   | old structure|
| no rewrite   |   |              |   | rollback     |   | later deploy |
+--------------+   +------+-------+   +------^-------+   +--------------+
   after deploy 2 is live |                  | backfill verified
     on every instance    v                  |
                    +-----+------------------+------+
                    | BACKFILL JOB (no deploy)      |
                    | separate worker or operator   |
                    | job, outside the pipeline     |
                    | batches over historical rows  |
                    +---------------+---------------+
                                    |  UPDATE in small batches
                                    v
+-----------------------------------------------------------------------+
| Postgres hot table: old structure + new nullable column or new table  |
+-----------------------------------------------------------------------+

Expand, dual-write, switch-reads and contract each ship as separate deploys, while the backfill runs as an out-of-band job that starts only after dual-write is live everywhere and must be verified before reads switch.

Making DDL fail fast: lock_timeout, statement_timeout and concurrent operations

Every ALTER TABLE needs a brief ACCESS EXCLUSIVE lock. The danger is the wait, not the hold. If a long-running transaction holds a conflicting lock, the DDL waits in the lock queue, and every new read and write on that table queues behind it. A one-millisecond change can still stall the app for minutes.

Set lock_timeout on the migration session, in the range of a few seconds, so the DDL gives up instead of blocking traffic. Set statement_timeout as a ceiling on total runtime. Wrap the step in a retry loop with exponential backoff and jitter. Each failed attempt costs the app only one short stall.

Build indexes with CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block. If it fails, it leaves an index marked invalid. Postgres still maintains that index on writes, but the planner won't use it. Find it by checking pg_index for rows where indisvalid is false. Remove it with DROP INDEX CONCURRENTLY, then retry.

For CHECK and foreign-key constraints, add the constraint with NOT VALID. That enforces it on new writes and skips the scan of existing rows. Afterwards, run VALIDATE CONSTRAINT, which takes a weaker lock that permits normal reads and writes.

These safer operations have costs:

• They take longer than their blocking versions.
• Concurrent builds still wait for existing transactions to finish.
• Under constant long transactions, a step can time out on every retry. In that case, schedule a low-traffic window.

Copy-- Run in psql with autocommit on; CONCURRENTLY cannot run inside BEGIN/COMMIT.
-- Give up quickly on lock waits instead of queuing app traffic behind the DDL.
SET lock_timeout = '3s';
-- Ceiling for short DDL steps; raised per step for long builds below.
SET statement_timeout = '15s';

-- 1. Build the index without blocking writes. Allow a long runtime.
SET statement_timeout = '2h';
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
  ON orders (customer_id);
SET statement_timeout = '15s';

-- 2. If the build failed, find leftover invalid indexes.
--    (An index whose concurrent build is still running also shows as invalid.)
SELECT n.nspname AS schema, c.relname AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;

-- Drop the invalid index without blocking writes, then retry step 1.
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

-- 3. Add the constraint without scanning existing rows.
--    Needs a brief lock; on lock_timeout, the caller retries with backoff and jitter.
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

-- 4. Check existing rows separately under a weaker lock.
--    Normal reads and writes continue while this runs.
SET statement_timeout = '1h';
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;
RESET statement_timeout;
RESET lock_timeout;

A migration session that fails fast on lock waits, builds an index concurrently, finds and drops an invalid index left by a failed build, and adds a foreign key as NOT VALID before validating it in a separate step.

Implementing a batched, idempotent, resumable backfill

Walk the table by primary key with keyset pagination: WHERE id > :last_id ORDER BY id LIMIT :batch_size. Avoid OFFSET. With OFFSET n, Postgres reads and discards n rows on every batch, so the job gets slower as it progresses. A keyset predicate uses the primary key index to jump straight to the next batch.

Run each batch in its own short transaction: select the next keys, update them, commit. Small batches keep row locks brief, limit replication lag, and give autovacuum time to clean up dead tuples between batches. Add AND new_col IS NULL to the update so that rows already done are skipped. That filter is what makes reruns safe.

After each commit, save the last processed key to a checkpoint table in a separate transaction. If the job crashes, it resumes from the checkpoint. At worst it repeats one batch, and the IS NULL guard makes that harmless.

The main hazard is the race with dual-writes. Suppose the backfill reads old_col, then the application writes both columns, and then the backfill writes a value derived from its stale read. The backfill has overwritten fresher data. To prevent this, compute the value inside the UPDATE from the row's current state (SET new_col = f(old_col)) and keep the new_col IS NULL condition in the same statement. Postgres then rechecks the condition against the latest row version and skips any row the application has already written.

Keyset pagination has limits:

• Random UUID keys spread new inserts across the whole key space, including below the cursor.
• Sparse ranges produce empty batches if you iterate fixed id BETWEEN windows. A LIMIT-based cursor avoids this.
• Rows inserted behind the cursor never get revisited. The dual-write path must populate them, and the verification pass described below catches any it missed.

Copy-- Checkpoint lives in its own table so progress survives crashes.
CREATE TABLE IF NOT EXISTS backfill_checkpoint (job text PRIMARY KEY, last_id bigint NOT NULL);
INSERT INTO backfill_checkpoint VALUES ('orders_amount_cents', 0) ON CONFLICT DO NOTHING;

CREATE OR REPLACE PROCEDURE backfill_orders(p_batch int DEFAULT 1000)
LANGUAGE plpgsql AS $$
DECLARE
  v_last bigint;
  v_next bigint;
BEGIN
  SELECT last_id INTO v_last FROM backfill_checkpoint WHERE job = 'orders_amount_cents';
  LOOP
    -- Keyset pagination: jump via the PK index, no OFFSET.
    WITH batch AS (
      SELECT id FROM orders WHERE id > v_last ORDER BY id LIMIT p_batch
    ), upd AS (
      -- Derive the value from the row's current state, and keep the IS NULL guard
      -- in the same statement so rows already written by the app are skipped.
      UPDATE orders o SET amount_cents = round(o.amount * 100)::bigint
      FROM batch b
      WHERE o.id = b.id AND o.amount_cents IS NULL
    )
    SELECT max(id) INTO v_next FROM batch;

    EXIT WHEN v_next IS NULL;  -- no keys left above the cursor
    COMMIT;                    -- short transaction per batch

    -- Record progress in its own transaction; a crash repeats at most one batch.
    UPDATE backfill_checkpoint SET last_id = v_next WHERE job = 'orders_amount_cents';
    COMMIT;

    v_last := v_next;
    PERFORM pg_sleep(0.05);    -- throttle to limit replication lag and let autovacuum keep up
  END LOOP;
END $$;

-- Run outside an explicit transaction block so the procedure can COMMIT.
CALL backfill_orders(1000);

A resumable PL/pgSQL backfill that walks the primary key with keyset pagination, commits each batch, saves a checkpoint in a separate transaction, and uses an in-statement IS NULL guard so it never overwrites values the application has already written.

Throttling with feedback and running the job safely

Instead of a fixed sleep, compute the pause before each batch from live signals:

• Replica lag. Read replay_lag from pg_stat_replication on the primary. Above a threshold, sleep longer or halve the batch size. Lagging replicas serve stale reads and delay failover.
• Lock waits. Count sessions in pg_stat_activity with wait_event_type = 'Lock'. A rising count means your batches are contending with application writes.
• Primary load. Track your own batch latency. If a batch that took 50 ms now takes 500 ms, the database is busy, so back off.

Check a kill switch before every batch. This can be a row in a control table or a feature flag, so an on-call engineer can stop the job without deploying or finding the process. Emit metrics for rows processed, estimated rows remaining, current batch size, sleep duration and error count. Log the cursor position so the job can be resumed.

Every updated row leaves a dead tuple behind, and it generates WAL. Watch n_dead_tup in pg_stat_user_tables. If autovacuum can't keep up, the table bloats. You may need to tune per-table autovacuum settings or pause the job between passes. WAL volume also inflates replica traffic and archive storage, and full-page writes after each checkpoint make it worse.

Copyimport logging, time
import psycopg2

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(message)s")
log = logging.getLogger("backfill")
JOB = "orders_currency_backfill"
conn = psycopg2.connect("postgresql://backfill:CHANGE_ME@db-primary.internal:5432/app")
conn.autocommit = True  # each batch commits on its own; no long transaction
cur = conn.cursor()
cur.execute("SET lock_timeout = '2s'")  # fail a batch rather than queue behind app writes

def scalar(sql, args=()):
    cur.execute(sql, args)
    return cur.fetchone()[0]

last_id = scalar("SELECT last_id FROM backfill_control WHERE job = %s", (JOB,))  # resume point
max_id = scalar("SELECT max(id) FROM orders")
batch, pause, errors = 1000, 0.1, 0
while scalar("SELECT enabled FROM backfill_control WHERE job = %s", (JOB,)):  # kill switch
    lag = scalar("SELECT COALESCE(EXTRACT(EPOCH FROM max(replay_lag)), 0) FROM pg_stat_replication")
    waits = scalar("SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock'")
    hi = scalar("SELECT max(id) FROM (SELECT id FROM orders WHERE id > %s ORDER BY id LIMIT %s) s", (last_id, batch))
    if hi is None:
        log.info("done at id=%s errors=%s", last_id, errors); break
    try:
        t0 = time.monotonic()
        cur.execute("UPDATE orders SET currency = 'USD' WHERE id > %s AND id <= %s AND currency IS NULL", (last_id, hi))
        updated, elapsed = cur.rowcount, time.monotonic() - t0
        cur.execute("UPDATE backfill_control SET last_id = %s WHERE job = %s", (hi, JOB))
        last_id = hi
    except psycopg2.Error as e:
        errors += 1; elapsed = float("inf"); updated = 0
        log.warning("batch failed (%s errors): %s", errors, e)
        if errors > 20: raise
    if lag > 5 or waits > 5 or elapsed > 0.5:  # back off: halve batch, double pause
        batch, pause = max(100, batch // 2), min(30.0, pause * 2)
    else:  # healthy: grow slowly
        batch, pause = min(5000, int(batch * 1.25)), max(0.05, pause * 0.8)
    log.info("last_id=%s updated=%s ~remaining_ids=%s batch=%s pause=%.2fs lag=%.1fs lock_waits=%s errors=%s",
             last_id, updated, max_id - last_id, batch, pause, lag, waits, errors)
    time.sleep(pause)

A resumable Python backfill loop that checks a control-table kill switch before each batch, adapts batch size and sleep to replica lag, lock waits and batch latency, and logs progress, approximate remaining range and error counts.

Verifying completion and running the contract step

Before enforcing anything, prove the backfill finished. First, count rows that still need work, such as rows where new_col IS NULL AND old_col IS NOT NULL. The count must be zero, and it should stay zero across a few checks, because a dual-write bug can quietly create new gaps. Then sample rows and compare old and new values with the same transformation the backfill used. A non-zero count shows the job missed rows. A mismatch shows the transformation itself is wrong.

To enforce the column, add CHECK (new_col IS NOT NULL) NOT VALID, then run VALIDATE CONSTRAINT separately. Validation scans the table under a lock that still allows reads and writes. Postgres can then use the validated constraint to skip the full-table scan when you run SET NOT NULL. After that, switch all reads to the new column. Drop the old column in a later deploy, once no running code references it.

Illustrative estimate. Assume 50M rows. With 5,000-row batches, 200 ms per batch and a 300 ms sleep, the job runs 10,000 batches at 0.5 s each, about 5,000 s or roughly 1.4 hours.

The total runtime is also how long dual writes must stay in place, so a gentler throttle keeps that extra code path live for longer.

Who should use it

This approach is for engineers who own both the application code and the Postgres schema behind it, and who cannot take the table offline.

• Backend and platform engineers on large, hot tables. If a table receives steady reads and writes and a full rewrite would take minutes or longer, batching is the way to change it without an outage.
• Teams that deploy continuously without maintenance windows. Expand/migrate/contract fits a pipeline where every release is small and revertible. Teams that already ship several times a day absorb the extra deploys easily.
• SREs and on-call engineers responsible for replicas and failover. Feedback throttling keeps replica lag bounded, which matters when replicas serve reads or when failover time depends on how far behind they are.
• Teams that already have job infrastructure and metrics. The pattern assumes you can run a long-lived worker, flip a kill switch and watch a dashboard. Without that, the job is harder to operate safely.

It is a poor fit if you cannot change the application to dual-write, for example when a third-party application owns the writes. In that case, logical replication into a new schema or a scheduled window is usually more practical. It is also unnecessary for anyone whose tables are small enough to rewrite in seconds, as the next section explains.

When to use it, when to avoid it, alternatives and cost

That trade-off between throttle speed and how long dual writes stay in place is the core cost of this pattern. The pattern pays off on large, hot tables where no downtime is acceptable and a blocking rewrite would stall writes for minutes or hours. It is not worth it for small tables that rewrite in seconds, for systems that can take a brief maintenance window, or for changes that only touch metadata, such as adding a nullable column. In those cases, the extra deploys add risk without removing any.

What it costs

The cost falls mostly on engineering time. You ship at least three deploys, often spread over days or weeks. Application code has to write both shapes correctly, and every read path has to tolerate partially migrated rows. A longer transition also leaves more room for an unrelated deploy to drop a dual write or add a column that conflicts with the change.

That cost drops after the first migration. A shared batch runner with keyset pagination, throttling and checkpoints means later migrations mostly need only a new query. A standard checklist covering timeouts, verification queries and contract preconditions catches the steps people skip under pressure. Because the jobs are idempotent and resumable, the same tooling can repair corrupted rows during an incident without anyone writing a one-off script at 3 a.m.

How it saves engineering time

The extra deploys are visible up front. The time the pattern saves comes from work that no longer has to happen.

• No maintenance windows to coordinate. A blocking migration on a hot table needs a scheduled window, an announcement, someone awake to run it and someone ready to roll back. A throttled backfill runs during normal hours alongside traffic, and nobody has to be online at an unusual time.
• Failures are retries, not incidents. With lock_timeout and retry loops, a DDL step that cannot get its lock fails in seconds and tries again. Without them, the same step can queue traffic behind it and turn into an outage that needs an incident response and a post-incident review.
• Stopping costs nothing. A single giant UPDATE that is cancelled after running for an hour rolls back everything and has to start over. A checkpointed batch job stops at the kill switch and resumes from the last committed key, so work already done is kept.
• Rollback is an ordinary revert. Each deploy in the sequence can be reverted on its own without restoring data. Engineers use the same release process they use every day instead of a special recovery procedure.
• Problems surface early and small. Metrics for lag, lock waits, rows remaining and errors show trouble after a few batches rather than after the whole table has been touched. Fixing a bad transformation then means correcting the query and rerunning, which the IS NULL guard makes safe.
• The tooling is reused. Once a batch runner, control table and verification queries exist, the next migration reuses them. Engineers write the transformation and the verification query, not a new job from scratch.

Illustrative estimate. Assume a team runs one large-table change per quarter. Assume a blocking migration in a window takes two engineers about four hours of off-hours coordination, execution and monitoring, and that a batched backfill with an existing shared runner takes one engineer about two hours to write the query, start the job and check the dashboard. Under those assumed inputs, each change uses roughly six fewer engineer-hours, before counting any incident avoided. The real figures depend on your team, tooling and how often migrations go wrong.

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 large table 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.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

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.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• 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.