DEV Community

晖莫
晖莫

Posted on

The Backfill That Ran Twice and Doubled a Balance

#postgres #data #reliability #migrations

The backfill was 300 lines of a script somebody wrote carefully. At row 1.2 million it hit a deadlock, the process died, and the operator did the obvious thing: ran it again. Every account in the first 1.2 million got its adjustment applied a second time.

Nobody noticed for three days, until support asked why some customers had a credit balance larger than their lifetime spend.

The shape of the bug

The script did this:

for account in accounts_needing_backfill():
    conn.execute(
        "UPDATE accounts SET balance_cents = balance_cents + %s WHERE id = %s",
        (credit_for(account), account.id),
    )
Enter fullscreen mode Exit fullscreen mode

That is an increment, not an assignment. It has no memory of whether it has run before, so running it twice is not a retry — it is a second credit. The script was safe exactly once.

The failure was not the deadlock. The failure was that recovery from the deadlock was "run it again", and the script could not tell the difference between the first run and the second.

A backfill must be a function of the data

The property to aim for: running it N times produces the same rows as running it once. There are three standard ways to get it, and they trade off differently.

Compute the absolute value from the source of truth. If the credit is derivable, set it instead of adding it:

UPDATE accounts a
SET balance_cents = (
    SELECT sum(amount_cents) FROM ledger_entries e WHERE e.account_id = a.id
);
Enter fullscreen mode Exit fullscreen mode

This is the strongest version, because the second run is not just safe, it is corrective — if any earlier run was wrong, this one fixes it. It also fails loudly when the ledger cannot explain the balance, which is the signal you want.

Mark rows in the same transaction as the change.

UPDATE accounts
SET balance_cents = balance_cents + 500,
    backfilled_at = now()
WHERE backfilled_at IS NULL AND id = ANY($1);
Enter fullscreen mode Exit fullscreen mode

The guard column and the mutation commit together, so a crash leaves either both or neither. The next run skips what is done. This costs a column, and the column is worth it.

Record the work in a progress table when you cannot touch the target table's shape: (backfill_name, row_id, applied_at) with a unique key on the first two. Insert-and-update in one transaction; the unique violation is the "already done" signal.

All three share the same idea: the script needs a durable record of what it has done, and that record must move in the same transaction as the effect.

What the operator should have had

Two things would have caught this in minutes instead of three days.

A checksum before and after. Before the backfill, record sum(balance_cents) and the row count. After it, compare against the expected delta. If the delta is double what the business case says, stop. That is one query against a number the finance team already knows.

An idempotency test in the script's own test suite. Run the backfill twice against a fixture and assert the result equals running it once. That test is six lines and it fails on the version above:

def test_backfill_is_idempotent(db):
    run_backfill(db)
    once = db.scalar("SELECT sum(balance_cents) FROM accounts")
    run_backfill(db)
    assert db.scalar("SELECT sum(balance_cents) FROM accounts") == once
Enter fullscreen mode Exit fullscreen mode

The rule

Any script that mutates money must be able to run twice, and the second run must be a no-op. Not "should be safe" — must be, and tested, because the alternative is discovered by a customer.

The general version: a backfill is a migration of data, and a migration you cannot re-run is a migration whose failure mode is a manual investigation. Write the guard first. The increment is the bug; the deadlock was just the witness.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky

Top comments (0)