Handling Balances and Transactions: Patterns and Anti-Patterns

Almost every business system has to answer one question correctly: how much is in this account right now? The account might be a ledger account, a customer receivable, a wallet or a loyalty-point balance. The obvious design is a balance column that every transaction updates. It works in a demo. Under real load, audit requirements and integrations that retry, it tends to produce lost updates, unexplained differences and slow reports.
This article describes a different approach, taken from how Odoo 19 handles accounting on PostgreSQL. Odoo does not treat a mutable "current balance" as the authoritative record. It stores balanced journal lines and the matching events between them. It keeps a few line-level values that are read constantly, such as the open residual. It calculates every broader balance with set-based queries.

Core rule: write immutable ledger facts and read balances as indexed projections. Keep a stored balance only as a cache you can rebuild.[1]

1. Foundation: Ledger Model

Why: A single mutable balance row becomes a contention hotspot. It also can't answer historical questions such as "balance on 30 June" or "balance by partner".[1]
Benefit: You get a full audit trail, and any filter (account, date, partner, currency) can produce a balance.
Impact: Writes become cheap inserts, and reads become aggregates that indexes keep fast.

CREATE TABLE ledger_entry ( id bigserial PRIMARY KEY, company_id bigint NOT NULL, external_system text, external_event_id text, state text NOT NULL DEFAULT 'draft' ); CREATE TABLE ledger_line ( id bigserial PRIMARY KEY, entry_id bigint NOT NULL REFERENCES ledger_entry(id), account_id bigint NOT NULL, partner_id bigint, posted_at date NOT NULL, amount numeric(18,2) NOT NULL, -- signed: debit +, credit - amount_residual numeric(18,2) NOT NULL DEFAULT 0, reconciled boolean NOT NULL DEFAULT false, state text NOT NULL DEFAULT 'draft' ); 

Example: A 500.00 sale inserts two rows, receivable +500 and revenue -500. No balance column is updated.

Choose one canonical amount. If debit, credit and balance are maintained independently, they can drift. Store the signed amount and derive the others.[1]

SELECT GREATEST(amount,0) AS debit, GREATEST(-amount,0) AS credit FROM ledger_line; 

2. Write Patterns

Atomic, balanced posting

Why: An entry spans several lines, and a partial write leaves the books wrong.
Benefit: Other sessions never see half-finished work, and a failure rolls everything back.
Impact: The ledger stays consistent.[1]

BEGIN; INSERT INTO ledger_line(entry_id, account_id, posted_at, amount) VALUES (1001, 10, CURRENT_DATE, 500.00), (1001, 20, CURRENT_DATE, -500.00); SELECT entry_id FROM ledger_line WHERE entry_id = 1001 GROUP BY entry_id HAVING ROUND(SUM(amount),2) <> 0; -- row returned means ROLLBACK COMMIT; 

When validating many entries, use WHERE entry_id = ANY(...) in one grouped query. Don't load lines into the application.[1]

Database constraints for local rules

Why: Application checks can be bypassed by scripts or other services.
Benefit: Invalid rows can't exist.
Impact: Fewer integrity incidents. Rules spanning rows, such as entry balance, still need the grouped check above.[1]

ALTER TABLE ledger_line ADD CONSTRAINT chk_acc CHECK (account_id IS NOT NULL); 

Idempotent posting

Why: Integrations retry, and "search then insert" races between two workers.
Benefit: The database decides the winner.
Impact: No duplicates after timeouts or message replays.[1]

ALTER TABLE ledger_entry ADD CONSTRAINT uq_event UNIQUE (company_id, external_system, external_event_id); INSERT INTO ledger_entry(company_id, external_system, external_event_id) VALUES (1,'ERP','INV-778') ON CONFLICT (company_id, external_system, external_event_id) DO NOTHING RETURNING id; -- no row means already posted 

Reverse and repost

Why: Editing posted history breaks audit trails and period closing.
Benefit: Every correction is visible.
Impact: The ledger stays append-only.[1]

INSERT INTO ledger_line(entry_id, account_id, posted_at, amount, state) SELECT %(new_entry)s, account_id, CURRENT_DATE, -amount, 'posted' FROM ledger_line WHERE entry_id = %(old_entry)s; 

Batch writes

Why: Per-record loops repeat ORM, access-control, recompute and query overhead.
Benefit: Overhead is paid once per batch.
Impact: Much faster imports and integrations.[1]

Line.create(vals_list) # good lines.write({'custom_state': 'processed'}) # good for v in vals_list: Line.create(v) # avoid 

Atomic counters, only when unavoidable

Why: Wallet and quota use cases sometimes need a mutable balance.
Benefit: The check and the update happen in one statement.
Impact: There is no lost update, but you lose history unless you also append a ledger row.[1]

UPDATE wallet_account SET balance = balance - %(amt)s WHERE id = %(id)s AND balance >= %(amt)s RETURNING balance; -- no row means insufficient funds or no account 

3. Read Patterns

Grouped balance

Why: One query per account (N+1) grows with the number of accounts.
Benefit: The query count stays roughly constant.
Impact: Dashboards and reports stay fast as data grows.[1]

SELECT account_id, SUM(amount) FILTER (WHERE amount>0) AS debit, -SUM(amount) FILTER (WHERE amount<0) AS credit, SUM(amount) AS balance FROM ledger_line WHERE account_id = ANY(%(ids)s) AND state='posted' AND posted_at BETWEEN %(from)s AND %(to)s GROUP BY account_id; 

Accounts with no rows are absent from the result, so default them to zero in the caller.

Running balance with a window

Why: A running balance depends on the filter and order, so it can't be a global stored value.
Benefit: It is correct for any filter and costs one query.
Impact: Statements are accurate, provided the window runs before pagination.[1]

WITH opening AS ( SELECT COALESCE(SUM(amount),0) AS amt FROM ledger_line WHERE account_id=%(acc)s AND state='posted' AND posted_at < %(from)s ), period AS ( SELECT id, posted_at, amount, SUM(amount) OVER (ORDER BY posted_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run FROM ledger_line WHERE account_id=%(acc)s AND state='posted' AND posted_at BETWEEN %(from)s AND %(to)s ) SELECT p.*, o.amt + p.run AS running_balance FROM period p CROSS JOIN opening o ORDER BY posted_at, id; 

The explicit ROWS frame avoids default peer-row behavior, and id makes the order deterministic.

Open items and partner balance

Why: Payments can be partial, multiple or in foreign currency, and overwriting the invoice destroys history.
Benefit: The original fact stays intact, matching is reversible, and aging is cheap.
Impact: Unreconciling means deleting a match row and recomputing the residual.[1]

CREATE TABLE ledger_match ( id bigserial PRIMARY KEY, debit_line_id bigint REFERENCES ledger_line(id), credit_line_id bigint REFERENCES ledger_line(id), amount numeric(18,2) NOT NULL ); SELECT partner_id, SUM(amount_residual) AS due FROM ledger_line WHERE reconciled IS NOT TRUE AND state='posted' GROUP BY partner_id; 

The residual is the line amount, minus what is matched as debit, plus what is matched as credit. Recompute it in batches. Example: an invoice of 1000 with payments of 300 and 200 leaves a residual of 500 and reconciled = false.

Indexes that match the queries

Why: Balance queries filter by account and date, and by open status.
Benefit: Scans touch only the needed rows, and a partial index stays small.
Impact: Reads get faster, but every index slows writes, so add only the dominant ones.[1]

CREATE INDEX idx_acct_date ON ledger_line(account_id, posted_at); CREATE INDEX idx_open ON ledger_line(account_id, partner_id) WHERE reconciled IS NOT TRUE; 

Use the same predicate in the query (reconciled IS NOT TRUE), or the planner may skip the partial index.

Rebuildable snapshots

Why: Some fixed aggregates, such as daily balances, are too costly to recompute repeatedly.
Benefit: Hot reports read quickly.
Impact: Backdated entries, reversals and migrations must update or rebuild the affected dates. Add a snapshot only after profiling.[1]

CREATE TABLE account_daily_balance ( account_id bigint, balance_date date, balance numeric NOT NULL DEFAULT 0, PRIMARY KEY (account_id, balance_date)); INSERT INTO account_daily_balance VALUES (%s,%s,%s) ON CONFLICT (account_id, balance_date) DO UPDATE SET balance = account_daily_balance.balance + EXCLUDED.balance; 

For analytics, use a materialized view refreshed asynchronously, not on every post.[1]

4. Anti-Patterns

All rows reflect the anti-pattern list in the source document.[1]

Concurrency example. Two workers each read a balance of 100 and add 50. The stored result is 150, not 200. The atomic statement SET balance = balance + 50 avoids this because PostgreSQL serializes the row update.

5. Production Checklist

• Each entry's signed total rounds to zero.
• Posting is batched, atomic and idempotent.
• Posted and locked-period rows are protected.
• Multi-currency and partial-reconciliation rounding are tested.
• EXPLAIN (ANALYZE, BUFFERS) runs on production-shaped data.
• Account/date and open-item queries use the intended indexes.
• Concurrent posting and reconciliation are load-tested.
• Snapshots can be rebuilt from the ledger.

The SQL is simplified and untested against your schema. The table definitions, reversal insert and idempotent insert are my own illustrative examples.