agent ran the backfill in one giant transaction and the database ran out of disk on the WAL

TL;DR

One giant transaction forces the database to keep every change (and its WAL) until commit, so a big backfill can fill the disk and wedge the database. Free space or add disk to unwedge it, then rewrite the backfill as small batches that commit per batch with a checkpoint of the last processed key, and resume from the checkpoint. The checkpoint makes the retry safe and the batches keep WAL bounded.

The query

agent ran the backfill in one giant transaction and the database ran out of disk on the WAL

Use this when

• Backfill in one transaction filled the disk with WAL
• Database out of disk during a data migration
• Need to resume a backfill after a disk-full failure
• An agent's backfill never commits until the end

Not for

• Disk full from logs or backups (free that space; not a backfill problem)
• A backfill that is slow but committing (a performance problem)
• WAL growth on a replica (check replication lag, not the backfill)

Steps

1. Unwedge the database

Stop the backfill. Free space safely: remove old WAL segments only if archiving is keeping up (never delete WAL the database still needs), clear temp files, or attach more disk. On postgres, check pg_wal size; the aborted transaction's WAL becomes reclaimable once the transaction ends.

Expected output: the database accepts writes again and disk has headroom. Do not restart the same giant transaction.

2. Find the resume point

Determine how far the backfill got before it died: query the target for the max migrated key or timestamp. The failed transaction rolled back, so the answer may be "nowhere": that is fine, the checkpoint design below handles it.

Expected output: the last successfully committed key, or confirmation that nothing committed.

3. Rewrite the backfill as committed batches

Process rows in keyset order, committing after each batch, and record the last key of each batch in a checkpoint row or file:

# pseudo: migrate in key order, commit per batch, checkpoint the key
last_key [your value]  # None on first run
while True:
    batch = fetch_next_batch(last_key, size=1000)
    if not batch:
        break
    migrate_batch(batch)
    commit()
    save_checkpoint(batch[-1].key)

Expected output: each batch commits independently, so WAL stays bounded and a failure loses at most one batch.

4. Resume from the checkpoint and monitor disk

Run the batched backfill from the checkpoint. Watch disk usage as it runs: it should stay flat, not climb.

Expected output: the backfill completes, disk stays healthy, and the checkpoint reads "done".

5. Make batched-with-checkpoint the agent's default

The agent's backfill template always includes batch size, per-batch commit, and a checkpoint. Single-transaction backfills are banned above a trivial row count.

Expected output: the next large backfill is resumable and disk-safe by construction.

Tool compatibility

postgres, mysql (innodb), and cockroachdb. WAL/redo/ballast concepts differ in name; the batching principle is universal.

Variant phrasings

backfill one giant transaction out of disk WAL

Steps 1 through 3: unwedge, then rewrite as committed batches.

postgres pg_wal full during backfill

Step 1: the aborted transaction's WAL is reclaimable once it ends; then step 3 keeps it bounded.

resume backfill after disk full

Steps 2 and 4: find the last committed key and resume the batched version from there.

Why it happens

A transaction's changes must be durable-or-undoable until commit, so the database retains every row version and its WAL for the whole backfill. Committing per batch lets the database reclaim that space continuously. The giant transaction also holds locks and blocks vacuum, compounding the problem: batching fixes all three at once.

Edge cases

• Killing the backfill mid-transaction rolls it back, which itself writes WAL and takes time on huge transactions: be patient, and monitor rather than killing the rollback.
• Checkpoint in the same database (a small table) so the checkpoint and the data share fate; a checkpoint file on the agent's disk dies with the sandbox.
• Batch size is a tradeoff: too small is slow, too large re-creates the WAL problem. Start at 1000 rows and watch disk.

Provenance

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

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

Thread query: agent ran the backfill in one giant transaction and the database ran out of disk on the WAL

TL;DR: One giant transaction forces the database to keep every change (and its WAL) until commit, so a big backfill can fill the disk and wedge the database. Free space or add disk to unwedge it, then rewrite the backfill as small batches that commit per batch with a checkpoint of the last processed key, and resume from the checkpoint. The checkpoint makes the retry safe and the batches keep WAL bounded.