← All posts·SQL Development

Building a Concurrent Ledger in Postgres

Giovanni Martinez·October 13, 2026·22 min read
postgresqlsql developmenttransactionsadvisory locksconcurrencysecurity definerrunning balancedata modeling


A ledger is only as honest as the gap between its reads and its writes. Two withdrawals hit the same account at the same instant. Each one reads the balance, sees enough money, and writes its debit. Both succeed, and the account is overdrawn.

You need a rule: when two requests touch the same account, one of them waits. But you don't want a global lock that serializes the whole system. A transfer against account A should never block an unrelated transfer against account B.

Postgres gives you exactly this granularity, as long as you model the ledger correctly and reach for the right lock. In this post the right lock is a transaction-scoped advisory lock keyed on the account id, wrapped in a SECURITY DEFINER function that becomes the only sanctioned way to write a ledger entry. That combination makes same-account inserts serialize while different-account inserts run in parallel — and it takes the raw INSERT privilege away from callers entirely. We will also store a running balance per entry so reading an account's balance stays O(1) even when the table grows into the billions of rows.

The data model

In finance there is usually a rule: you never update an existing record. To correct a mistake, you add a new one — but you NEVER update. A ledger is append-only. You never update or delete an entry; you only insert new ones. Balances are derived from the entries. Here is the core schema:

CREATE TABLE accounts (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_name   text NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE TABLE ledger (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id   bigint NOT NULL REFERENCES accounts(id),
    -- Per-account sequence number: entry 1, 2, 3, ... for THIS account. Assigned
    -- by the posting function as MAX(seq) + 1 under the advisory lock. This is
    -- the ordering key — "latest entry" means the max seq for the account.
    seq          bigint NOT NULL,
    amount       numeric(18,2) NOT NULL CHECK (amount > 0),
    -- Running balance AFTER this entry is applied. Written explicitly by the
    -- posting function as: previous balance + amount (credit) or - amount (debit).
    total_amount numeric(18,2) NOT NULL,
    entry_type   varchar(50) NOT NULL CHECK (entry_type IN ('debit', 'credit')),
    notes        text,
    -- An audit timestamp only. clock_timestamp() (not now(), which is frozen at
    -- transaction start) records roughly when the row was inserted. Ordering does
    -- NOT depend on it — the per-account seq above is the order key.
    created_at   timestamptz NOT NULL DEFAULT clock_timestamp(),
    -- Backstop: makes the per-account sequence correct even if a writer somehow
    -- skips the advisory lock. A duplicate (account_id, seq) fails loudly here
    -- instead of silently corrupting the order.
    UNIQUE (account_id, seq)
);
-- Supports the "latest entry for this account" lookup that reads the balance:
-- grab the row with the highest seq for the account.
CREATE INDEX idx_ledger_account ON ledger (account_id, seq DESC);

The important column is total_amount. It is not a generated column — it is the account's running balance after this entry is applied. amount is always positive (it is the magnitude of the movement); the direction comes from entry_type. So each new entry sets total_amount = previous_balance + amount for a credit, or previous_balance - amount for a debit. The current balance of an account is simply the total_amount of its most recent entry.

Why a running balance instead of SUM()

The obvious alternative is to store each entry's signed delta and compute the balance on demand with SUM(total_amount) WHERE account_id = X. That is correct, and the WHERE clause means it only sums one account's rows, not the whole table. But the cost of that sum grows with the number of entries that account has. On a ledger you check the balance before every single write, so on a hot account with millions of entries — in a table heading toward billions of rows — every write pays to re-read and re-add that account's entire history. That cost only ever climbs.

The running balance makes a balance read O(1): grab the latest entry for the account and read its total_amount. It never gets slower, no matter how large the account or the table grows:

SELECT COALESCE(
  (SELECT total_amount
   FROM ledger
   WHERE account_id = 42
   ORDER BY seq DESC
   LIMIT 1),
  0
) AS balance;

Two things to note in that query. First, new accounts with no entries yet start at a balance of 0 — that is the COALESCE fallback. An account opens empty and only gets money once a credit entry is posted to it; a brand-new account cannot be debited until it has been funded. Second, the ordering is seq DESC — the per-account sequence number, the way real ledgers order entries. seq is a logical clock: entry 1, 2, 3, ... for each account, assigned by the posting function as MAX(seq) + 1 while it holds the account's advisory lock. Because it is a counter and not a wall-clock value, it has none of a timestamp's failure modes — no microsecond ties, no clock skew, no now()-is-frozen-at-transaction-start trap. "Latest entry" is simply the row with the highest seq for the account.

Why not just order by the global id? It would actually give correct ordering here — identity values are assigned under the lock too, so id order within an account matches the serialization order. But id is a global counter shared across every account (account 42 might have ids 312, 580, 904 while its seq runs 1, 2, 3), so it carries no per-account meaning. seq gives each account a clean 1..N numbering, which is a real business fact: "entry 47 on account 42", the thing a statement or audit refers to. We keep id as a surrogate primary key, but seq is the order key.

And created_at? It is an audit timestamp only. It uses clock_timestamp() rather than now() so it at least records roughly when the row was inserted (now() is frozen at transaction start and would not), but ordering never depends on it. Keep created_at for "when did this happen" reporting; never for "which entry is latest".

There is a tradeoff worth stating plainly. A running balance is a denormalized cache: its correctness depends entirely on every writer reading the true latest balance and no two writers for the same account interleaving. With SUM, a missed lock corrupts one decision; with a running balance, a missed lock writes a wrong total_amount that every later entry builds on. That is why the per-account lock below — and the discipline of routing every write through one function — is not optional here. It is load-bearing.

The naive approach that gets it wrong

Assume account 42 has already been funded — say a credit of 1000, so its latest total_amount is 1000. A withdrawal needs to read the current balance, then insert a debit if there are sufficient funds:

BEGIN;
-- Read the latest running balance (0 if the account has no entries yet).
SELECT COALESCE(
  (SELECT total_amount FROM ledger
   WHERE account_id = 42
   ORDER BY seq DESC
   LIMIT 1),
  0
) AS balance;
-- application checks: balance >= 100?  (balance is 1000, so yes)
-- (and we would also need the next seq: MAX(seq) + 1 for account 42)
-- New balance = 1000 - 100 = 900, written into the row.
INSERT INTO ledger (account_id, seq, amount, total_amount, entry_type)
VALUES (42, 5, 100, 900, 'debit');  -- seq hand-computed, nothing guards it
COMMIT;

Under concurrency this is broken. Two withdrawals against account 42 both read the same latest balance before either inserts. Both see enough funds. Both compute their new balance from the same stale number and insert. Now the account has two entries claiming to be the balance after a withdrawal, both computed from 1000 — the running balance is corrupt and the account may have gone negative. This is a classic read-then-write race, and READ COMMITTED (the Postgres default) does nothing to stop it — the two SELECTs do not block each other at all.

The seq makes the race even more visible: both withdrawals read the same MAX(seq) and compute the same next value, so they would both try to write seq = 5 for account 42. The UNIQUE (account_id, seq) constraint would reject the second one — which is the backstop doing its job, but failing an insert is not a design, it is a symptom. We want the writers to serialize so the collision never happens in the first place.

The fix: an advisory lock keyed on the account

The trick is to serialize on the account id itself. Postgres advisory locks let you lock an arbitrary 64-bit key — it does not have to correspond to any row or table. We use the account id as the key. Any transaction that wants to write to account 42 first calls pg_advisory_xact_lock(...) with that account's key; the first caller holds the lock, and every other caller for the same account waits until that transaction ends.

The xact variant is the important one: pg_advisory_xact_lock takes a lock scoped to the transaction and released automatically on COMMIT or ROLLBACK. You never have to remember to unlock it, and a crashed or rolled-back transaction cannot leak it. (The session-scoped pg_advisory_lock does not auto-release, which is a footgun in a connection pool — avoid it for this.)

One more thing about the key. Advisory lock keys share a single global space for the whole database — there is no built-in partitioning by table or feature. So if you lock on a bare account id like 42, that lock is just "the advisory lock numbered 42", and anything else in your system that locks on 42 for an unrelated reason collides with it. Picture a background job that locks on invoice 42 with pg_advisory_xact_lock(42): to Postgres that is the same key as your account 42 lock, so a ledger write would needlessly block on an invoice job. No corruption — they still serialize — but it is mystery contention between features that should be independent.

The two-argument form fixes that by giving you a namespace. pg_advisory_xact_lock(key1, key2) locks on the pair, and two locks conflict only if both numbers match. So you dedicate key1 to "which domain": (1001, 42) means "accounts domain, account 42" and (1002, 42) means "invoices domain, invoice 42" — different pairs, different locks, no collision, even though both carry a 42.

That 1001 is arbitrary. Postgres attaches no meaning to it, nothing registers or validates it; it is a convention you invent. The only rules are that you always use the same constant for every account lock (if one code path used 1001 and another 2002 for the same account, they would not serialize against each other — which is one more reason to route every write through the one function), and that different domains pick different constants. Since nothing enforces this, keep the mapping written down — a comment or a constants file, say 1001 = accounts, 1002 = invoices, 1003 = report generation — so two subsystems never reuse the same key1 and start blocking each other by accident.

In short: key1 labels the category of thing you are locking and key2 is the id within it, and together they make account 42's lock genuinely distinct from every other 42 in the database — pg_advisory_xact_lock(1001, 42).

Here is the read-check-write sequence, now correct:

BEGIN;
-- Grab a transaction-scoped advisory lock keyed on the account id (1001 = the
-- "accounts" namespace). Any other transaction that calls this with 42 now
-- waits here until we commit.
SELECT pg_advisory_xact_lock(1001, 42);
-- Now it is safe to read the latest balance and the next sequence number — no
-- other writer holding the lock for account 42 can be between their read and
-- their insert.
SELECT COALESCE(
  (SELECT total_amount FROM ledger
   WHERE account_id = 42
   ORDER BY seq DESC
   LIMIT 1),
  0
) AS balance;
-- application checks: balance >= 100?
-- Next per-account sequence number, safe to compute under the lock.
SELECT COALESCE(MAX(seq), 0) + 1 FROM ledger WHERE account_id = 42;
-- New balance = balance - 100, written into total_amount; seq is MAX(seq)+1.
INSERT INTO ledger (account_id, seq, amount, total_amount, entry_type)
VALUES (42, 5, 100, 900, 'debit');
COMMIT;

A second transaction that calls pg_advisory_xact_lock(1001, 42) blocks until this one commits or rolls back — then it proceeds and reads a balance that already reflects the first transaction's insert. The race is gone.

Note that the advisory lock does not touch the accounts or ledger rows at all — it is a lock on a key, not a row. It works purely because every writer agrees to take the same key before writing. That agreement is the whole mechanism, which is why the next section makes the function the only writer.

Why different accounts still run in parallel

This is the part that makes advisory locks the right tool. The lock is keyed. pg_advisory_xact_lock(1001, 42) and pg_advisory_xact_lock(1001, 99) are two completely independent locks. When transaction 1 holds the lock for account 42 and transaction 2 holds the lock for account 99, neither waits for the other — different keys never conflict. They commit whenever they finish.

So two transactions on account 42 serialize: the first to grab the lock for 42 blocks the second on that same key. But a third transaction for account 99 grabs the lock for 99 — a different key — and sails right through. That is exactly the behavior you want.

Transaction ATransaction BOutcome
Entry on account 42Entry on account 42B waits for A to commit, then runs
Entry on account 42Entry on account 99Run concurrently, neither blocks

Make the function the only way in

The advisory lock only works if every writer takes it. If any code path can INSERT INTO ledger directly without first calling pg_advisory_xact_lock, your invariant is broken — that writer will not serialize against the others, because the advisory lock guards a key by convention, not the table itself. The naive insert we started with is still perfectly legal SQL, and that is the real hole.

The fix is to make direct inserts impossible and route every write through one function. A SECURITY DEFINER function is how you do that in Postgres. It runs with the privileges of the function's owner, not the caller. So you:

  • Own the accounts and ledger tables as, say, the ledger_admin role.
  • Write a SECURITY DEFINER function owned by ledger_admin that takes the advisory lock and does the insert.
  • REVOKE INSERT on ledger from the application role, and GRANT EXECUTE on the function instead.

Now the application role literally cannot insert a ledger row except by calling the function — and the function always takes the account's advisory lock first.

CREATE OR REPLACE FUNCTION post_ledger_entry(
    p_account_id bigint,
    p_amount     numeric,   -- always positive; direction comes from entry_type
    p_entry_type text
)
RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
-- Pin search_path so the function body cannot be hijacked by a caller who
-- created a malicious "accounts" or "ledger" table in a schema earlier on
-- their path. This is mandatory for SECURITY DEFINER functions.
SET search_path = public, pg_temp
AS $$
DECLARE
    v_id      bigint;
    v_seq     bigint;
    v_balance numeric;
    v_new     numeric;
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'amount must be positive, got %', p_amount;
    END IF;
    IF p_entry_type NOT IN ('credit', 'debit') THEN
        RAISE EXCEPTION 'entry type must be credit or debit, got %', p_entry_type;
    END IF;
    -- Serialize on this account with a transaction-scoped advisory lock
    -- (1001 = "accounts" namespace). Any concurrent call for the same account
    -- waits here; a different account uses a different key and runs in parallel.
    PERFORM pg_advisory_xact_lock(1001, p_account_id);
    -- Confirm the account exists (the advisory lock does not check this).
    PERFORM 1 FROM accounts WHERE id = p_account_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'no such account: %', p_account_id;
    END IF;
    -- Read the current running balance (0 for a brand-new account) and the
    -- next per-account sequence number in the same pass. Both are safe here
    -- because we hold the account's advisory lock: no concurrent writer for
    -- this account can be between its read and its insert, so no two writers
    -- can read the same MAX(seq) and both write MAX(seq) + 1.
    v_balance := COALESCE(
        (SELECT total_amount FROM ledger
         WHERE account_id = p_account_id
         ORDER BY seq DESC
         LIMIT 1),
        0
    );
    SELECT COALESCE(MAX(seq), 0) + 1
    INTO v_seq
    FROM ledger
    WHERE account_id = p_account_id;
    -- Apply the entry: credit adds, debit subtracts.
    v_new := CASE p_entry_type
                 WHEN 'credit' THEN v_balance + p_amount
                 WHEN 'debit'  THEN v_balance - p_amount
             END;
    IF p_entry_type = 'debit' AND v_new < 0 THEN
        RAISE EXCEPTION 'insufficient funds: account % has %, cannot debit %',
            p_account_id, v_balance, p_amount;
    END IF;
    INSERT INTO ledger (account_id, seq, amount, total_amount, entry_type)
    VALUES (p_account_id, v_seq, p_amount, v_new, p_entry_type)
    RETURNING id INTO v_id;
    RETURN v_id;
END;
$$;
-- Lock down the table and expose only the function. Revoke from PUBLIC too,
-- and take away UPDATE/DELETE so the ledger really is append-only.
REVOKE INSERT, UPDATE, DELETE ON ledger FROM PUBLIC, app_role;
-- Functions are executable by PUBLIC by default; narrow that to the app role.
REVOKE EXECUTE ON FUNCTION post_ledger_entry(bigint, numeric, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION post_ledger_entry(bigint, numeric, text) TO app_role;

Notice that the balance and the sequence number are computed in the same locked region. MAX(seq) + 1 is a read-then-write exactly like the balance check: two concurrent writers could both read the same MAX(seq) and both try to write N + 1. It is safe for the same reason the balance is safe — the advisory lock serializes writers for the account, so the second one does not run its MAX(seq) until the first has committed and sees N, writing N + 1. The COALESCE(MAX(seq), 0) + 1 handles the brand-new account: no rows yet means MAX is NULL, so the first entry gets seq = 1. And UNIQUE (account_id, seq) is the backstop: if a writer ever skipped the lock, the duplicate seq fails the insert instead of silently corrupting the order. The lock makes the sequence correct; the constraint makes a lock-bypass fail loudly rather than corrupt.

Two things about that function that are not optional:

  • SET search_path — a SECURITY DEFINER function that does not pin its search_path is a well-known privilege-escalation vector. A caller who controls their own search_path could plant a ledger table in a schema that resolves first and trick the elevated function into writing there. Pinning it to public, pg_temp closes that. Always do this.
  • The advisory lock lives inside the function, so it is impossible to forget. Every caller that inserts an entry takes the account's lock, because taking it and inserting are the same operation now.

The application no longer writes SQL inserts at all. It calls:

SELECT post_ledger_entry(42, 100, 'debit');

Seeing it happen

Open two psql sessions. In session 1:

BEGIN;
SELECT pg_advisory_xact_lock(1001, 42);
-- do not commit yet

In session 2, take the same lock:

BEGIN;
SELECT pg_advisory_xact_lock(1001, 42);
-- this blocks — the cursor just hangs

Session 2 is now waiting. Confirm it from a third session — advisory waits show up as their own wait event:

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock' AND wait_event = 'advisory';
-- Or inspect the advisory locks directly:
SELECT pid, classid, objid, granted
FROM pg_locks
WHERE locktype = 'advisory'
ORDER BY granted DESC;

You will see session 2's backend waiting on the advisory lock, and in pg_locks one row with granted = true (session 1) and one with granted = false (session 2). Commit session 1 and session 2 unblocks instantly. Repeat with account 99 in session 2 — it never blocks, because (1001, 99) is a different key.

A transfer is two entries, one transaction

A real transfer debits one account and credits another. Both entries must succeed or neither does, so they live in one transaction and take both accounts' locks. Acquire them in a deterministic order — lowest account id first — so two opposing transfers (42 to 99 and 99 to 42 at the same time) cannot each grab one lock and wait forever on the other:

CREATE OR REPLACE FUNCTION transfer(
    p_from   bigint,
    p_to     bigint,
    p_amount numeric   -- always positive
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
    v_exists       bigint;
    v_from_balance numeric;
    v_to_balance   numeric;
    v_from_seq     bigint;
    v_to_seq       bigint;
BEGIN
    IF p_amount <= 0 THEN
        RAISE EXCEPTION 'amount must be positive, got %', p_amount;
    END IF;
    IF p_from = p_to THEN
        RAISE EXCEPTION 'cannot transfer an account to itself: %', p_from;
    END IF;
    -- Take both accounts' advisory locks in a fixed order (lowest id first) to
    -- prevent deadlocks between opposing transfers. least/greatest guarantees
    -- every caller reaches for the same key first.
    PERFORM pg_advisory_xact_lock(1001, least(p_from, p_to));
    PERFORM pg_advisory_xact_lock(1001, greatest(p_from, p_to));
    -- Confirm both accounts exist (the advisory locks do not check this).
    SELECT count(*) INTO v_exists
    FROM accounts WHERE id IN (p_from, p_to);
    IF v_exists <> 2 THEN
        RAISE EXCEPTION 'both accounts must exist: % and %', p_from, p_to;
    END IF;
    -- Read each account's current running balance (0 if it has no entries) and
    -- its next per-account sequence number. Safe because we hold both locks.
    v_from_balance := COALESCE(
        (SELECT total_amount FROM ledger WHERE account_id = p_from
         ORDER BY seq DESC LIMIT 1), 0);
    v_to_balance := COALESCE(
        (SELECT total_amount FROM ledger WHERE account_id = p_to
         ORDER BY seq DESC LIMIT 1), 0);
    SELECT COALESCE(MAX(seq), 0) + 1 INTO v_from_seq
    FROM ledger WHERE account_id = p_from;
    SELECT COALESCE(MAX(seq), 0) + 1 INTO v_to_seq
    FROM ledger WHERE account_id = p_to;
    IF v_from_balance - p_amount < 0 THEN
        RAISE EXCEPTION 'insufficient funds: account % has %', p_from, v_from_balance;
    END IF;
    -- Debit the source: new running balance = from balance - amount.
    INSERT INTO ledger (account_id, seq, amount, total_amount, entry_type)
    VALUES (p_from, v_from_seq, p_amount, v_from_balance - p_amount, 'debit');
    -- Credit the destination: new running balance = to balance + amount.
    INSERT INTO ledger (account_id, seq, amount, total_amount, entry_type)
    VALUES (p_to, v_to_seq, p_amount, v_to_balance + p_amount, 'credit');
END;
$$;

The least/greatest ordering matters. Without it, a transfer from 42 to 99 and a simultaneous transfer from 99 to 42 could each grab one lock and then wait forever for the other — a deadlock. Postgres would detect it and kill one transaction, but you would rather not pay that cost. Ordering the two pg_advisory_xact_lock calls means both transactions reach for key 42 first, so one simply waits for the other. No cycle, no deadlock.

Because the lock ordering is explicit — two separate pg_advisory_xact_lock calls, lowest key first — it does not depend on the query planner or on row order the way a SELECT ... FOR UPDATE would. The key you pass is the key you lock, in the order you write the calls.

Why not just use SERIALIZABLE?

You could set the transaction to SERIALIZABLE and let Postgres detect the conflict for you. That works and it is correct. But it is optimistic: it lets both transactions run and then aborts one with a serialization failure at commit, which means your application must catch that error and retry. For a hot account with a lot of contention, that is a lot of wasted work and retry loops.

The advisory lock is pessimistic: the second transaction waits instead of doing work it will have to throw away. For a ledger, where same-account contention is expected and correctness is non-negotiable, the explicit lock is usually the simpler and more predictable choice. Reach for SERIALIZABLE when the conflicts are rare and you would rather not manage lock keys yourself.

An advisory lock has a nice property for a ledger: you do not need a row to exist to lock its key. pg_advisory_xact_lock(1001, 42) serializes on account 42 even before the account's first entry is written, and it never touches the accounts or ledger rows, so it does not interfere with any FOR UPDATE row locks other queries might legitimately take. The main thing to remember is the one this whole post hinges on — the lock guards a key, not a row, and it only stops concurrent inserts because every writer agrees to take it first. That agreement is what the SECURITY DEFINER function enforces.

The takeaway

A ledger is append-only entries plus a balance. Store the balance as a running total on each entry (total_amount = previous balance plus or minus amount) so reading it stays O(1) even at billions of rows — never re-SUM an account's whole history on every write. Then serialize writes with a transaction-scoped advisory lock keyed on the account id, and put that lock inside a SECURITY DEFINER function that is the only way to insert an entry — revoke direct INSERT and grant EXECUTE instead. That way the lock can never be skipped, which matters more with a running balance than with a SUM: a missed lock does not just spoil one decision, it corrupts the stored balance that every later entry builds on. Advisory locks give you the exact concurrency shape you want: the same account serializes, different accounts run in parallel — no table locks, no global bottleneck. Remember that the advisory lock guards a key, not the ledger inserts; the insert is safe only because every writer takes that key first. And do not forget to pin search_path on any SECURITY DEFINER function.

Vacuum & Analyze — bi-weekly PostgreSQL internals, no spam.