How to Backfill Large Table Batches in Postgres Without Downtime

What is a batched backfill, and why does a single UPDATE break production?

A batched backfill is the data-copy step of a schema change. After you add a new column or table, existing rows still need values, so a background job fills them in a few thousand rows at a time, committing after each batch. Each batch is a short, independent transaction instead of one statement that touches the whole table.

The obvious approach is a single UPDATE inside a migration. In Postgres that causes several problems on a table with tens of millions of rows:

• Row locks last for the whole statement. The UPDATE runs as one transaction and keeps a lock on every row it modifies until it commits. Any application write to those rows waits, and that can mean minutes of blocked requests.
• Every row gets a new version. Under MVCC, an update writes a new tuple and leaves the old one as dead. The table can roughly double in size. Indexes often grow too, because each new tuple needs index entries unless the update qualifies as a HOT update.
• Vacuum is held back. While the transaction is open, vacuum cannot remove dead tuples newer than its snapshot. This applies across the database, not just to the table being updated.
• WAL arrives in one burst. Replicas have to replay all of it, and replica lag climbs.
• Deploys depend on data volume. If the migration hits a timeout or fails late, the rollback discards all the work, and the deploy fails with it.

This pattern is for engineers running a monolith on a single Postgres database where tables are large enough that one statement runs for minutes. If a table is small enough that the update finishes in a second or two, a plain migration is simpler and works fine.

Copy-- Anti-pattern: one statement, one transaction, every row in the table.
-- Row locks are held until COMMIT, every row gets a new tuple version,
-- and the whole WAL volume is generated before anything is released.
UPDATE orders
SET    total_cents = (total * 100)::bigint
WHERE  total_cents IS NULL;

-- Batched alternative: a background job runs this repeatedly,
-- advancing the id range each time. Each run is its own short
-- transaction (autocommit), so locks are released and vacuum can
-- make progress between batches.
UPDATE orders
SET    total_cents = (total * 100)::bigint
WHERE  id >= 1000000          -- batch start, advanced by the job
  AND  id <  1005000          -- batch size of 5,000 rows
  AND  total_cents IS NULL;   -- skip rows already filled, so reruns are safe

-- Watch dead tuples accumulate on the table while either approach runs.
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  relname = 'orders';

-- Watch replica lag from the primary (Postgres 10+).
SELECT application_name,
       replay_lag
FROM   pg_stat_replication;

A single full-table UPDATE compared with one key-range batch of a backfill, plus catalog queries for watching dead tuples and replica lag while it runs.

How does a backfill fit into an expand-contract migration?

The backfill is one step in a longer sequence. Expand-contract splits a breaking schema change into small, backward-compatible steps:

1. Expand. Add the new column as nullable with no default, or create the new table. In Postgres, adding a nullable column without a default only changes the catalog, so it does not rewrite the table.
2. Dual-write. Deploy application code that writes both the old and new representations on every insert and update. From this point on, new rows are correct.
3. Backfill. Fill in the historical rows in batches. This is the only step whose duration grows with table size.
4. Verify. Confirm that no rows are missing the new value and that the old and new values agree.
5. Switch reads. Point queries at the new column while still dual-writing, so you can switch back.
6. Contract. Stop writing the old column, drop it, and add the constraints you deferred, such as NOT NULL.

Each step ships as its own deploy because each one can be rolled back on its own. If switching reads exposes a bug, you revert that one change. The data is still intact in both places, and you don't have to undo a schema change under pressure.

The cost is coordination. One logical change now takes five or six deploys, and they can span days or weeks while the backfill runs and you wait for confidence before contracting. Someone has to own the sequence and track which step each environment is on. Leftover dual-write code also needs a ticket to remove it.

This still saves engineering time. A single migration that locks a large table can stall the deploy pipeline, time out, and leave you doing an emergency rollback during an incident. Several boring deploys cost less than one failed deploy.

Copy +-------------+    +-------------+    +-------------+    +-------------+
 | 1 EXPAND    |--->| 2 DUAL-WRITE|--->| 3 BACKFILL  |--->| 4 VERIFY    |
 | add column  |    | app writes  |    | historical  |    | no missing  |
 | nullable,   |    | old + new   |    | rows in     |    | values;     |
 | no default  |    | new rows ok |    | batches     |    | old == new  |
 +-------------+    +-------------+    +-------------+    +------+------+
                                                                 |
        +--------------------------------------------------------+
        v
 +------+------+    +-------------+    +------------------------------------+
 | 5 SWITCH    |--->| 6 CONTRACT  |    | Each box = one deploy that can be  |
 | reads -> new|    | stop old    |    | reverted on its own. Old column    |
 | col; still  |    | writes, drop|    | stays intact until step 6, so a    |
 | dual-writing|    | add NOT NULL|    | bad read switch is a code revert,  |
 +-------------+    +-------------+    | not a schema rollback.             |
                                       +------------------------------------+

 |<----------- one logical change: days to weeks, one owner ----------->|
  vs. one locking migration: pipeline stall, timeout, emergency rollback

An expand-contract migration ships as six separately revertible deploys, with the batched backfill as the only step whose duration grows with table size.

How do you implement a resumable, keyset-paginated backfill job?

The job is a loop: read the last processed id from a checkpoint table, pick the next batch, update it, and record progress. To pick the batch, select the next N ids in primary key order (WHERE id > $last ORDER BY id LIMIT N), then update WHERE id IN (...) AND new_col IS NULL. A simpler variant updates a bounded range (id > $last AND id <= $last + N) and skips the select step.

The new_col IS NULL filter makes the update idempotent. Rows that a previous run or the application's dual writes already filled are left untouched, so re-running a batch does no harm. Write the batch update and the checkpoint row in the same short transaction. That way the checkpoint never records progress that was rolled back, and a crashed or paused job resumes from the last committed id instead of starting over. Short transactions also release row locks quickly and avoid holding back vacuum.

Avoid OFFSET. Postgres has to read and discard every skipped row, so each batch costs more than the last, and the final batches are the most expensive.

Keyset pagination has its own limits. With sparse integer keys, bounded ranges produce uneven batches. With random UUIDs, you cannot step through the keyspace in predictable increments. Extra predicates also matter. If you add a condition such as AND status = 'active' and no index supports it, Postgres may scan many rows to find N matches, which turns each batch into a partial table scan.

Copy-- One-time setup: one checkpoint row per backfill job.
CREATE TABLE IF NOT EXISTS backfill_checkpoint (
  job_name text PRIMARY KEY,
  last_id  bigint NOT NULL
);
INSERT INTO backfill_checkpoint (job_name, last_id)
VALUES ('orders_amount_cents', 0)
ON CONFLICT (job_name) DO NOTHING;

-- One batch. The worker runs this repeatedly and stops when it returns zero rows.
BEGIN;
WITH ckpt AS (
  -- Lock the checkpoint row so two workers cannot process the same batch.
  SELECT last_id FROM backfill_checkpoint
  WHERE job_name = 'orders_amount_cents'
  FOR UPDATE
), batch AS (
  -- Keyset step: an index seek on the primary key, with no OFFSET.
  SELECT o.id FROM orders o, ckpt
  WHERE o.id > ckpt.last_id
  ORDER BY o.id
  LIMIT 5000
), upd AS (
  -- The IS NULL filter keeps the update idempotent. Rows already
  -- filled by an earlier run or by dual writes are skipped.
  UPDATE orders o
  SET amount_cents = o.amount * 100
  FROM batch b
  WHERE o.id = b.id AND o.amount_cents IS NULL
)
-- Advance to the highest id selected, not the highest id updated,
-- so skipped rows are not revisited. This commits atomically with the update.
UPDATE backfill_checkpoint
SET last_id = (SELECT max(id) FROM batch)
WHERE job_name = 'orders_amount_cents'
  AND EXISTS (SELECT 1 FROM batch)
RETURNING last_id;
COMMIT;

One resumable backfill batch: it reads and locks the checkpoint, selects the next 5,000 ids by keyset, fills only rows that are still NULL, and advances the checkpoint in the same short transaction.

Why run the backfill outside the deploy pipeline?

Schema changes and data changes have different failure profiles. Adding a nullable column or creating an index concurrently either completes in seconds or fails cleanly, and a deploy pipeline is built for that. A backfill over hundreds of millions of rows can run for hours. If it runs as a migration step, a deploy blocks on it, a pipeline timeout can kill it halfway, and a rollback becomes unclear because the code may roll back while half the rows are already rewritten. Keep the pipeline for fast, transactional DDL. Run the backfill as a background job or a one-off worker that reads its checkpoint, processes batches, and exits cleanly when told to.

That worker needs a kill switch you can flip without a deploy, such as a feature flag or a row in a control table that the job checks before each batch. When replica lag spikes or an incident starts, you want the backfill paused within one batch.

Guard each session so a stuck batch fails fast. Set lock_timeout to a few seconds so an UPDATE waiting on a row lock gives up instead of queuing behind a long transaction while other writers queue behind it. Set statement_timeout a little above your expected batch duration so a batch that hits a bad plan is cancelled rather than holding locks. Treat both errors as retryable and back off before the next attempt.

The trade-off is visibility. A failed deploy is loud. A paused job is silent. Give the job an owner, emit progress metrics such as the last processed key and rows per minute, and alert when progress stalls. Otherwise a job that died quietly can leave the contract step blocked for weeks.

Copyimport time
import psycopg2
from psycopg2 import errors

JOB, BATCH = "orders_total_cents", 5000
conn = psycopg2.connect("host=db.internal dbname=app user=backfill_worker")

with conn.cursor() as cur:
    # Session guards: give up on a row lock after 3s; cancel a batch that
    # runs far past its normal ~2s duration instead of letting it hold locks.
    cur.execute("SET lock_timeout = '3s'")
    cur.execute("SET statement_timeout = '15s'")
    cur.execute("SELECT max(id) FROM orders")
    max_id = cur.fetchone()[0] or 0
conn.commit()

backoff = 1
while True:
    try:
        with conn.cursor() as cur:
            # Kill switch and checkpoint live in a control table, so you can
            # pause the job with one UPDATE, without a deploy.
            cur.execute("SELECT paused, last_id FROM backfill_control WHERE job = %s", (JOB,))
            paused, last_id = cur.fetchone()
            if paused or last_id >= max_id:
                break  # exit cleanly; a rerun resumes from last_id
            hi = last_id + BATCH
            cur.execute("UPDATE orders SET total_cents = round(total * 100)::bigint "
                        "WHERE id > %s AND id <= %s AND total_cents IS NULL", (last_id, hi))
            # Checkpoint commits with the batch; alert if updated_at goes stale.
            cur.execute("UPDATE backfill_control SET last_id = %s, updated_at = now() "
                        "WHERE job = %s", (hi, JOB))
        conn.commit()
        print(f"job={JOB} last_id={hi} rows={cur.rowcount}")  # feed into metrics
        backoff = 1
    except (errors.LockNotAvailable, errors.QueryCanceled):
        # Lock or statement timeout: treat as retryable and back off.
        conn.rollback()
        time.sleep(backoff)
        backoff = min(backoff * 2, 60)
conn.rollback()
conn.close()

This Python backfill worker sets lock_timeout and statement_timeout for its session. Before each batch it checks a pause flag and its checkpoint in a control table, and it retries timed-out batches with exponential backoff.

How do you choose a batch size and throttle the job?

Start with a small batch, such as 1,000 rows, and time each one. Adjust the size toward a target duration per batch, for example 100–500 ms. A short target keeps row locks brief and limits how long a conflicting write can wait. Sleep between batches so autovacuum, replication and normal traffic can catch up. Before each batch, check replica lag, lock waits and database CPU. If any of them is past its threshold, back off by sleeping longer or halving the batch size.

Illustrative estimate (assumed inputs, not measured):

• 50,000,000 rows at 5,000 rows per batch is 10,000 batches.
• Each batch takes 200 ms, followed by a 100 ms sleep, so one cycle is 300 ms.
• 10,000 × 0.3 s = 3,000 s, or about 50 minutes.

Every updated row leaves a dead tuple behind. If autovacuum falls behind, the table and its indexes bloat, and queries get slower while the backfill is still running. Watch dead-tuple counts alongside lag.

Lag-based throttling has a failure mode of its own. Under sustained write load, the thresholds may never clear, and the job stalls indefinitely. Decide a policy in advance. Options include running the job only during off-peak hours, setting a minimum throughput floor, or pausing it and escalating to the job's owner.

How do you handle concurrent writes during the backfill?

The backfill only handles rows that already exist. Every row inserted or updated while it runs must be written in the new shape by the application itself. Deploy the dual-write code first and confirm it is live on every instance before starting the job. If you reverse the order, rows written in the gap will have empty new columns, and nothing will revisit them.

With both writers touching the same rows, the backfill must never overwrite a value the application set. Make each batch's UPDATE conditional. The simplest guard is to fill only NULLs: add AND new_column IS NULL to the WHERE clause, so a row the application already populated is skipped. When NULL is a legitimate value, or when the derived value can go stale, compare an updated_at timestamp or a version column instead. Read the version when you compute the batch, and write only if it is unchanged. This works like optimistic locking. A row modified mid-batch is left to the application's newer write.

Dual writes cover only the code paths you changed. Before you start the job, check for these:

• Raw SQL in admin tools or scheduled jobs.
• Bulk import scripts that bypass the ORM.
• Another service writing to the same tables.

Any one of them can keep producing rows in the old shape after the backfill finishes.

Fill-only-NULL logic also cannot repair its own mistakes. If a transformation bug writes wrong values, those rows are no longer NULL, and rerunning the job skips them. A fix needs a second pass with different selection criteria, such as rows where the new column disagrees with a recomputed value or rows touched within the affected ID or time range.

How do you verify and add constraints before contracting?

Start with a count that must reach zero: rows where the old column holds a value and the new one is still NULL. Run it against a replica or during a quiet period, because it is a full scan. A zero count shows coverage. It does not show correctness, so also pull a random sample of a few thousand rows and compare the old value, transformed in application code, with the stored new value. Any mismatch points to a bug in the transform or a write path that skipped dual-writing.

Next, switch reads to the new column behind a feature flag. Leave dual-writes running so you can turn the flag off without losing data. Drop the old column only after the flag has been on through a full traffic cycle, including batch jobs and reports that run weekly or monthly.

Add constraints in two steps. ALTER TABLE ... ADD CONSTRAINT ... CHECK (new_col IS NOT NULL) NOT VALID returns almost immediately because it checks only new writes. A later VALIDATE CONSTRAINT scans existing rows, but it holds a lock that still allows reads and writes. Once the constraint is validated, current Postgres versions can use it to skip the scan for SET NOT NULL.

Indexes follow the same pattern. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so turn off your migration tool's automatic transaction wrapping for that step. If the build fails, it leaves an INVALID index behind; drop it and retry.

Skipping these steps undoes the work. A plain ADD CONSTRAINT or a non-concurrent index build takes a lock that blocks writes, or reads and writes, while it scans the whole table. That brings back the outage the batched backfill was meant to prevent.

What are the alternatives to a batched backfill?

The batched job is not always worth building. Three other approaches cover most cases, and the deciding factors are table size, write rate and how long you can tolerate two code paths.

A single statement is fine if you test it against production-sized data first. The risk is that a table you remember as small has grown since.

Copying into a new table means keeping it in sync with triggers or dual writes until the swap. You also have to recreate indexes, foreign keys, grants and sequence ownership. Use it for reshaping a table, such as changing the primary key type or partitioning.

A lazy backfill never reaches rows nobody reads. You still need a final sweep before you can add NOT NULL, and that sweep is usually the batched job anyway. Treat lazy backfill as a way to shrink the batched job, not to replace it.

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 backfill large table batches, 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.