Ledger

Replace this file with a real project.

The problem

The first version stored account balances as a column and updated them inside the same transaction as the transfer. It worked until it didn’t: a partial failure during a batch payout left four accounts with balances that disagreed with their transaction history, and there was no way to tell which number was right.

What I chose, and why

Balances became a derived value — the sum of entries against an account — never written directly. Every movement of money is two entries that must sum to zero, enforced by a database constraint rather than application code.

This is slower to read, and that’s the trade. A balance query became an aggregate over an index instead of a single-row lookup, which at our volume cost about 3ms. In exchange, the system cannot represent an inconsistent state: if the entries are there, the balance is correct by construction.

Reads that needed to be fast got a rolling snapshot — a checkpoint row every 10,000 entries, so a balance query sums the checkpoint plus whatever came after it. The snapshot is a cache, and it’s rebuildable from the entries at any time.

The hard part

Concurrent transfers against the same account. Two payouts drawing from one balance could both read “sufficient funds” and both succeed, overdrawing it.

Optimistic locking on a balance row was out — there was no balance row anymore. What worked was a per-account advisory lock taken in a deterministic order (sorted by account ID) for the accounts involved in a transfer. The ordering is what prevents deadlock when two transfers touch the same pair of accounts in opposite directions.

-- Locks are taken in sorted order so that A→B and B→A can't deadlock.
SELECT pg_advisory_xact_lock(hashtext(account_id))
FROM unnest($1::text[]) AS account_id
ORDER BY account_id;

Writing a test that reliably reproduced the deadlock before the fix took longer than the fix itself.

What I’d change

I’d make the checkpoint interval adaptive. A fixed 10,000 entries is far too frequent for a dormant account and not frequent enough for the handful of hot ones, and tuning it globally means picking the wrong number for almost every account.

I’d also push back harder on supporting multiple currencies in v1. We built the column, never used it, and every query has carried an unnecessary predicate for a year.