how to backfill a table without locking it

TL;DR

Update in small batches with keyset pagination, committing after each batch, instead of one giant UPDATE. Small transactions hold locks briefly and let concurrent traffic through, while a single massive transaction locks everything until it finishes and risks bloat if it gets killed halfway.

how to backfill a table without locking it

Use this when

• You must UPDATE or INSERT millions of rows on a live table
• A big UPDATE blocks other queries or times out
• A previous backfill died halfway and left a mess

Not for this skill when

• The table is small (a plain UPDATE finishes in milliseconds)
• The table is empty and you are loading it for the first time
• You are changing the schema, not the data

Steps

1. Estimate the blast radius first. Know how many rows the backfill touches:

SELECT count(*) AS rows_to_backfill
FROM orders
WHERE region_code IS NULL;

Expected output: the row count. If it is under ~100k on a quiet table, skip the batching and just run the UPDATE.

1. Backfill in batches keyed on the primary key, committing each batch:

DO $$
DECLARE
  batch int;
BEGIN
  LOOP
    UPDATE orders
    SET region_code = c.region_code
    FROM customers c
    WHERE orders.customer_id = c.customer_id
      AND orders.region_code IS NULL
      AND orders.order_id > (SELECT COALESCE(max(order_id), 0) FROM backfill_progress)
    -- limit via a subselect on the key range in real code; commit per batch
    GET DIAGNOSTICS batch = ROW_COUNT;
    EXIT WHEN batch = 0;
    COMMIT;
    PERFORM pg_sleep(0.1);  -- breather for concurrent traffic
  END LOOP;
END $$;

Expected output: the loop processes the table in chunks and finishes. Each COMMIT releases locks, so readers and writers only wait for one small batch.

1. Prefer a script loop with LIMIT when the logic is complex. Keyset pagination avoids OFFSET slowdown:

last_id = 0
while True:
    batch = db.query("""
        SELECT order_id FROM orders
        WHERE region_code IS NULL AND order_id > %s
        ORDER BY order_id LIMIT 5000""", (last_id,))
    if not batch:
        break
    db.execute("UPDATE orders SET region_code = ... WHERE order_id = ANY(%s)",
               ([r[0] for r in batch],))
    db.commit()
    last_id = batch[-1][0]

Expected output: steady progress, 5000 rows per commit. If the script dies, rerun it: the WHERE clause skips already-backfilled rows, so it resumes where it stopped.

1. Guard against lock pileups with a lock timeout on the backfill session:

SET lock_timeout = '5s';
-- run the batched backfill in this session

Expected output: if a batch cant get its locks within 5 seconds, it fails fast instead of queueing behind production traffic. The loop retries or you rerun the script.

1. Verify completeness when the loop exits:

SELECT count(*) AS remaining
FROM orders
WHERE region_code IS NULL;

Expected output: 0. Also spot-check a sample of backfilled rows against the source to catch logic errors before they bake in.

Variant phrasings

postgres update millions of rows without locking

The batched pattern from steps 2-3. One huge UPDATE takes an exclusive lock on every row it touches until commit.

backfill killed halfway resume

Keyset on the primary key (step 3) makes the backfill naturally resumable. OFFSET-based batching is not resumable if rows shift.

online backfill large table postgres

Small batches plus short sleeps (step 2) is the standard online pattern. For very hot tables, run during lower traffic too.

Why it happens

An UPDATE locks each row it modifies until the transaction commits, and Postgres cant release those locks early. A single transaction updating millions of rows therefore holds millions of locks for the whole duration, blocking concurrent writers and risking massive table bloat if it aborts. Batching bounds the lock footprint to one small chunk at a time.

Edge cases

• New rows inserted mid-backfill with NULL in the target column get picked up by later batches automatically, since the filter is on the column value, not a snapshot.
• Triggers on the table fire per batch; disable or account for expensive triggers during the backfill.
• Autovacuum may struggle to keep up with the churn; watch table bloat and run VACUUM after if needed.
• If the backfill logic itself is slow (complex join per row), precompute the mapping into a temp table once, then batch-join against it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Ai7djs6BaPH61eT519ZmdQ