Balance Derivation at Scale
Prerequisite: The Ledger, Representing Money
Your omnibus wallet has 5 million entries. A customer calls asking about their balance, but the query behind that balance check is scanning every entry the account has ever received. On Postgres without a covering index, that scan takes 420ms. With a covering index, it drops to 0.84ms — a 500x improvement that still grows linearly with the account’s history. The customer account with 200 entries is fine. The omnibus wallet that pools funds for 50,000 users is not.
The balance derivation problem is not “how do you compute a balance.” It’s how you compute a balance when one account has 200 entries and another has 5 million, and both need to answer in under 50ms.
The Naive SUM and Its Ceiling
The correct way to compute a balance is SELECT SUM(amount) FROM entries WHERE account_id = ?. This is always right — the result reflects every entry ever posted. No cache, no staleness, no invalidation.
SELECT SUM(amount)
FROM entries
WHERE account_id = $1;On a table with 5 million rows and no index optimization, Postgres runs a parallel sequential scan that completes in roughly 420ms. Adding a covering index on (account_id, amount) enables an Index Only Scan — Postgres reads the amounts directly from the index without touching the heap. The same query drops to 0.84ms.
| Table size | No index | Covering index |
|---|---|---|
| 5M rows | ~420ms | ~0.84ms |
The covering index is the first optimization you should apply, and for most accounts it’s the last one you’ll need. But even with the index, cost is O(entries for this account). The omnibus wallet with 5 million entries still scans 5 million index entries. The covering index does not change the complexity — it reduces the constant factor.
See The Ledger. A balance is always derived from entries, never stored independently. Every pattern below is a way to derive faster — not a way to replace derivation with storage.
Snapshots: O(n) Becomes O(recent)
Instead of summing every entry since account creation, compute the sum at a known point in time and store it. To read the current balance, load the most recent snapshot and sum only the entries posted since then.
SELECT s.balance + COALESCE(SUM(e.amount), 0)
FROM balance_snapshots s
LEFT JOIN entries e
ON e.account_id = s.account_id
AND e.booking_time > s.snapshot_time
WHERE s.account_id = $1
ORDER BY s.snapshot_time DESC
LIMIT 1;An account with 5 million total entries but only 300 since the last snapshot scans 300 entries plus one snapshot row. O(n) becomes O(recent).
The tradeoff is snapshot cadence. Snapshot every hour and you scan up to an hour of entries. Snapshot every day and a high-traffic account accumulates thousands of entries between snapshots, eroding the performance gain. Snapshot on every Nth entry and write cost becomes variable.
| Snapshot frequency | Read cost | Staleness risk |
|---|---|---|
| Every hour | Up to 1 hour of entries | Low for most accounts |
| Every day | Up to 1 day of entries | High-traffic accounts accumulate thousands |
| Every Nth entry | Bounded by N | Write cost becomes variable |
Concurrent writes during snapshot computation require care. The snapshot must be consistent with a specific point in the entry sequence. Timestamp-bounded snapshots (SUM(entries WHERE booking_time <= T)) work with Postgres MVCC — new entries with a later booking time don’t affect the computation. Sequence-bounded snapshots (SUM(entries WHERE seq <= N)) require monotonic entry IDs.
The snapshot is a performance optimization, not a source of truth. If a snapshot is suspect — after a partial write failure, a bug in the computation job, or a reconciliation discrepancy — discard it and rebuild from entries. The entries always win.
Running Balance: O(1) Reads, Row Contention on Writes
Maintain a balance column on the accounts table. Update it atomically with every entry write inside the same database transaction.
BEGIN;
INSERT INTO entries (account_id, amount, ...) VALUES ($1, $2, ...);
UPDATE accounts SET balance = balance + $2 WHERE id = $1;
COMMIT;Read cost: O(1). Write cost: O(1) additional per entry. The balance is always consistent with the entries because both writes happen in the same transaction.
The handbook warns against stored balances, and the warning is correct for systems that update the balance outside the entry transaction. But when the entry insert and balance update share a transaction boundary with serializable isolation, divergence between the stored balance and the derived balance is structurally impossible.
Formance uses this pattern — an immutable event log (the write model) alongside a materialized running balance (the read model) updated within the same write. The CQRS architecture ensures the running balance is a projection of the event log, not an independent record.
The failure mode is write contention. Every entry to the same account serializes on the balance row. A customer account receiving a few transactions per day never notices. An omnibus wallet receiving thousands of writes per second creates a queue — each write waits for the previous one to release the row lock. Under heavy contention, throughput drops and latency spikes.
Hybrid Sync/Async: Modern Treasury at 4,800 Entries/Second
Modern Treasury runs Postgres with three specialized balance caches:
| Cache | Purpose |
|---|---|
| Total Balance Cache | Running sum of all entries — O(1) read |
| Effective Time Balance Cache | Historical balances supporting backdated entries and time-travel queries |
| Resulting Balance Cache | Balance snapshot at the time each entry was written — for audit trail |
The key architectural decision: callers choose synchronous or asynchronous processing per entry. Entries requiring immediate balance locks — a payment that must check available funds before executing — take exclusive Postgres row-level locks and process serially. High-volume entries that don’t need real-time balance feedback route through an SQS queue and process in batches.
The async path targets processing all queued entries within 60 seconds. Actual p90: 1 second.
| Metric | Value |
|---|---|
| Write throughput | 1,200 transactions/sec (4,800 entries/sec) |
| Read throughput | 3,600 reads/sec |
| Write latency (p90) | 297ms |
| Read latency (p90) | 89ms |
Modern Treasury scales horizontally with a cell-based architecture. Each cell is a complete instance with its own database. Customer traffic routes to specific cells, and cells scale independently.
The hybrid model acknowledges that not every entry needs the same consistency guarantee. “Can this account spend right now?” requires synchronous locking. “Record this settlement entry” does not. Splitting the write path by consistency requirement is the insight — the caller knows which one they need.
Pre-Computed Deltas: Fragment at 14,578 Entries/Second
Fragment takes a different approach. Instead of storing snapshots at intervals or maintaining a single running balance, they pre-compute balance deltas at four temporal granularities: hour, day, month, and year. Every entry’s balance impact is decomposed and stored as deltas at each level.
The write path has two tiers:
- Synchronous. Update the transactional (current) balance in DynamoDB using conditional writes. This is the double-spend check — the conditional write fails if the balance would go negative.
- Asynchronous. Cascade balance delta updates through DynamoDB Streams → EventBridge → SQS. At each cascade level, batch and coalesce updates to reduce write volume.
A single ledger entry triggers approximately 80 balance updates — 4 granularity levels × 5 hierarchy depth levels (sub-accounts rolling up to parent accounts) × 4 lines per entry. This 80x write amplification is the cost of fixed-time reads at any temporal granularity.
The read path splits by consistency requirement:
| Balance type | How computed | Latency (p95) | Consistency |
|---|---|---|---|
| Transactional (current) | Pre-computed at write time | 33ms | Strongly consistent |
| Historical (point-in-time) | Sum relevant year/month/day/hour deltas | ~130ms | Eventually consistent (~10s p95 staleness) |
“Can this account spend right now?” reads the transactional balance — 33ms, strongly consistent. “What was the balance last Tuesday?” sums the relevant deltas — 130ms, eventually consistent with approximately 10 seconds of staleness.
| Metric | Value |
|---|---|
| Write throughput | 14,578 entries/sec |
| Read throughput | 7,489 reads/sec |
| Write latency (p95) | 69ms |
| Read latency (p95) | 33ms |
| Total requests in load test | 19.6M over 15 minutes |
Fragment chose DynamoDB over Postgres for specific reasons. DynamoDB provides single-digit-millisecond reads at any scale (no query planner, no joins), conditional writes for double-spend prevention (compare-and-swap semantics), and DynamoDB Streams for change data capture that powers the async cascade. The tradeoff: no SQL, no ad-hoc queries, no JOINs. Reporting and analytics require a separate data path.
The two-tier consistency model means “can this account spend right now?” and “what was the balance last Tuesday?” have different correctness guarantees. If your system conflates these two questions into a single balance query, the 10-second staleness window on historical balances will produce wrong answers for real-time spending decisions.
Purpose-Built: TigerBeetle at 42,000 TPS
TigerBeetle reframes the problem. Instead of optimizing balance queries on a general-purpose database, it eliminates the query entirely.
TigerBeetle is a purpose-built financial transactions database written in Zig. All account balances live in an in-memory hash map. A balance check is two hash-map lookups. A transfer is two lookups, a constraint check, and two updates — all in memory, all O(1). The hot-account problem disappears because there are no rows to lock. A single-threaded state machine processes all operations sequentially, eliminating concurrency control overhead entirely.
Consensus uses Viewstamped Replication. TigerBeetle has been Jepsen-tested to Strong Serializable — the strongest consistency guarantee available.
| Metric | TigerBeetle | Postgres (batched) | Ratio |
|---|---|---|---|
| Throughput (moderate contention) | 42K TPS | 15K TPS | 2.8x |
| Throughput (heavy contention) | Stable | Degrades | 4.6x |
The 4.6x difference under heavy contention is the critical number. Postgres degrades because row-level locking serializes writes to hot accounts. TigerBeetle is stable because its single-threaded state machine processes every operation in sequence regardless of which account it touches — there is no per-account contention.
The deployment model is a co-processor, not a replacement. TigerBeetle sits alongside Postgres. Postgres handles users, metadata, application state, and ad-hoc queries. TigerBeetle handles the ledger — entries, balances, and transfers. You query Postgres for reporting. You query TigerBeetle for “can this account spend right now?”
The tradeoff is flexibility. TigerBeetle has no SQL. You cannot run arbitrary aggregations, historical queries across custom time ranges, or ad-hoc analysis. The balance is always correct and always fast, but you can only ask the questions TigerBeetle was designed to answer. Everything else requires a separate data path.
Square’s Books: Immutable Entries on Spanner
Square built a dedicated accounting database service called Books on Google Cloud Spanner. The data model is immutable double-entry — entries are appended, never updated. Consistency is enforced by construction, and Spanner’s TrueTime provides globally distributed strong consistency.
Square has not published detailed balance derivation benchmarks, but the architecture implies balance is derived from entries — the immutable model precludes running balance updates in the same way a mutable balance column would work. The significance is that a company processing billions of transactions chose Spanner’s append-only model over a running balance or pre-computed delta approach.
Google’s own prescriptive guidance for payment systems on Spanner — “Hot Rows, Cool Solutions” — recommends the append-only ledger model specifically to avoid hot-row contention. Balance is computed on demand or periodically, never updated in place. Spanner’s 2025 release added optimistic concurrency control mode and schema recommendations for hotspot-prone keys.
Choosing Your Approach
The approaches form a progression. Each one trades write complexity for read performance, and the right choice depends on your scale, your consistency requirements, and your operational tolerance.
| Approach | Read cost | Write overhead | Consistency | When to use |
|---|---|---|---|---|
| SUM + covering index | O(n) per account | None | Always correct | < 100K entries per account, < 100 reads/sec |
| Periodic snapshots | O(recent) | Snapshot computation jobs | Correct if snapshot valid | < 1M entries per account, tolerance for snapshot cadence |
| Running balance | O(1) | 1 additional UPDATE per entry | Strong (same txn) | Low-to-moderate write contention |
| Hybrid sync/async | O(1) | 1 sync or batched async | Sync: strong. Async: seconds | Moderate contention, mixed consistency needs |
| Pre-computed deltas | O(1) current, O(granularity) historical | 80x write amplification | Current: strong. Historical: ~10s eventual | High throughput, need both real-time and historical |
| Purpose-built (TigerBeetle) | O(1) | None (in-memory) | Strong Serializable | Extreme throughput, tolerance for co-processor model |
Start with the simplest approach that meets your latency and throughput requirements. A SUM with a covering index handles more traffic than most early-stage fintech products will ever see. Add snapshots when per-account entry counts push index scans past your latency budget. Move to a running balance when read latency must be O(1). Split the write path when hot-account contention degrades your throughput. Consider pre-computed deltas when you need both real-time and historical balances at high throughput. Evaluate a purpose-built database when the general-purpose database is the bottleneck, not your application code.
Every approach preserves the fundamental invariant: the entries are the source of truth. Snapshots, running balances, pre-computed deltas, and in-memory hash maps are all caches of a derivation. If any of them diverges from the entries, the entries win.