Safe Backfills and Large Data Updates

Scenario

Example app: after adding users.full_name, the team must copy name into it for 50 million rows. Someone runs one UPDATE in a console on a Friday afternoon.

What you see in production

The single UPDATE ran for 70 minutes in one transaction: replicas fell 40 minutes behind, the table doubled in size (every row got a new version), autovacuum couldn't keep up, and rows locked by the update blocked user profile saves. Cancelling it rolled everything back — another 50 minutes.

The naive code

UPDATE users SET full_name = name WHERE full_name IS NULL;    -- 50M rows, one transaction

for user in User.objects.filter(full_name__isnull=True):       # loads everything, one UPDATE per row
    user.full_name = user.name
    user.save()

Reproduce it

A batched backfill: walk the primary key in ranges, commit each batch, and pause when replica lag rises:

TOTAL, BATCH, LAG_LIMIT = 50_000, 10_000, 2.0
replica_lag = [0.3, 0.8, 2.6, 1.1, 0.4, 0.2]      # sampled lag (s) as batches run
checkpoint = 0                                    # last processed id, persisted for resume
batch_no = 0
while checkpoint < TOTAL:
    lag = replica_lag[batch_no % len(replica_lag)]
    batch_no += 1
    if lag > LAG_LIMIT:
        print(f"replica lag {lag}s > {LAG_LIMIT}s -> sleep and retry")
        continue
    upper = min(checkpoint + BATCH, TOTAL)
    # UPDATE users SET full_name = name WHERE id > :checkpoint AND id <= :upper AND full_name IS NULL
    print(f"batch ids ({checkpoint}, {upper}] committed, lag {lag}s")
    checkpoint = upper
print("done; safe to re-run: WHERE full_name IS NULL makes it idempotent")

batch ids (0, 10000] committed, lag 0.3s
batch ids (10000, 20000] committed, lag 0.8s
replica lag 2.6s > 2.0s -> sleep and retry
batch ids (20000, 30000] committed, lag 1.1s
batch ids (30000, 40000] committed, lag 0.4s
batch ids (40000, 50000] committed, lag 0.2s
done; safe to re-run: WHERE full_name IS NULL makes it idempotent

Root cause

One huge transaction holds locks for its whole duration, generates a massive burst of WAL/binlog that replicas must replay, creates millions of dead row versions at once, and can only be undone by an equally long rollback. Row-by-row ORM loops are the opposite extreme — millions of round trips.

The fix

Backfill in small, committed, idempotent batches keyed by primary key ranges, with throttling and a checkpoint:

UPDATE users SET full_name = name
WHERE id > $1 AND id <= $1 + 10000 AND full_name IS NULL;   -- uses the PK index; idempotent

def backfill(conn, batch=10_000):
    last = load_checkpoint() or 0
    max_id = conn.scalar(text("SELECT max(id) FROM users"))
    while last < max_id:
        with conn.begin():
            conn.execute(text("UPDATE users SET full_name = name "
                              "WHERE id > :a AND id <= :b AND full_name IS NULL"), {"a": last, "b": last + batch})
        last += batch
        save_checkpoint(last)
        while replica_lag_seconds() > 2:
            time.sleep(5)

for (long from = checkpoint(); from < maxId; from += BATCH) {
    tx.execute(status -> jdbc.update(
        "UPDATE users SET full_name = name WHERE id > ? AND id <= ? AND full_name IS NULL", from, from + BATCH));
    saveCheckpoint(from + BATCH);
    throttle.awaitReplicaLagBelow(Duration.ofSeconds(2));
}

User::whereNull('full_name')->chunkById(10000, function ($users) {
    User::whereIn('id', $users->pluck('id'))->update(['full_name' => DB::raw('name')]);
    usleep(200_000);
});

Trade-offs

• Batches mean the backfill takes longer in wall-clock time — run it as a background job with progress reporting, not in a migration that blocks deploys.
• Choose batch size by measuring: each batch should take well under a second.
• Concurrent writes during the backfill must also populate the new column (dual write), or the backfill will miss rows changed after it passed them.

Detect it in production

• Replica lag, WAL generation rate and dead tuples during backfills.
• Progress metrics (rows remaining) and a dashboard showing the checkpoint.

Key points

• Never update millions of rows in one transaction.
• Batch by primary key, commit each batch, make it idempotent and resumable.
• Throttle on replica lag and run backfills as monitored background jobs.