Building a Load-Balancing Client to Mitigate PostgreSQL Row-Level Locking Contention
Last updated: March 4, 2026
This article explains how to design and build a load-balancing client for Ledger to reduce contention caused by PostgreSQL row-level locking. It introduces the problem, explains why load-balancing helps, and walks through the main implementation options.
1. The Problem: Row-Level Locking Contention
In a double-entry ledger backed by PostgreSQL, each transaction that moves funds locks the rows of the accounts involved (source and destination). When many transactions share the same source account (for example a single @world or “mint” account), they serialize: only one transaction can hold the lock on that account at a time. The others wait in line.
What happens with a single source account
Every transaction that debits from
@worldmust acquire a lock on the@worldaccount row.PostgreSQL ensures consistency by allowing only one writer per row at a time.
Result: throughput is limited to roughly one transaction at a time for that account, regardless of how many clients or threads you use.
┌─────────────────────────────────────────────────────────────────────────┐
│ SINGLE SOURCE ACCOUNT — SERIALIZED EXECUTION │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Client (many workers) PostgreSQL │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ Worker 1 │─────────────────▶│ │ │
│ │ Worker 2 │────── wait ─────▶│ ROW LOCK on │ │
│ │ Worker 3 │────── wait ─────▶│ @world │ │
│ │ Worker N │────── wait ─────▶│ │ │
│ └─────────────────────┘ └─────────────────────┘ │
│ │
│ • Only one transaction can hold the lock on @world at a time │
│ • All other transactions block until the lock is released │
│ • Effective throughput ≈ 1 transaction at a time │
│ │
└─────────────────────────────────────────────────────────────────────────┘
So the bottleneck is not necessarily the ledger API or the network, but lock contention on the same account row in the database.
2. Why Load-Balancing?
The idea is to spread transactions across multiple source accounts instead of a single one. Each account has its own row in the database, so each account can be locked independently. That allows parallel processing: as many concurrent transactions as you have distinct source accounts (up to the limits of your DB and API).
Trade-offs at a glance
Approach | Throughput | Trade-off |
Single source account | Serialized (effectively 1 at a time) | Simple, but bottleneck |
Pool of N source accounts | Up to N concurrent transactions | Needs routing logic |
Very large pool (e.g. thousands) | Diminishing gains | More accounts to manage, more DB bloat |
Practical recommendation: Start with a small pool (e.g. 5–20 source accounts) and scale up only if metrics show you need it.
Conceptual view: pool of source accounts
┌─────────────────────────────────────────────────────────────────────────┐
│ POOL OF SOURCE ACCOUNTS — PARALLEL EXECUTION │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Client (N workers) PostgreSQL │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ Worker 1 → account-1│─── lock(1) ─────▶│ Row 1 (account-1) │ │
│ │ Worker 2 → account-2│─── lock(2) ─────▶│ Row 2 (account-2) │ │
│ │ Worker 3 → account-3│─── lock(3) ─────▶│ Row 3 (account-3) │ │
│ │ ... │ ... │ ... │ │
│ │ Worker N → account-N│─── lock(N) ─────▶│ Row N (account-N) │ │
│ └─────────────────────┘ └─────────────────────┘ │
│ │
│ • N different rows → N locks can be held at once │
│ • No contention between workers using different accounts │
│ • Throughput scales with pool size (up to DB/API limits) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
To implement this, you need:
A pool of source accounts (e.g.
world:<id>).A strategy to assign each transaction to one of these accounts (routing).
Optionally, batching and/or multi-posting to reduce HTTP and round-trips.
The following sections describe the main options you can implement in your client.
3. Option 1: Individual Transactions with Dedicated Accounts
In this model, the client has N workers (e.g. goroutines or threads). Each worker is bound to one source account and sends one transaction per HTTP request. Transactions from the same worker are serialized (same account → same row lock), but different workers use different accounts, so up to N transactions can be in flight at once.
Schema
┌─────────────────────────────────────────────────────────────────────────┐
│ INDIVIDUAL MODE — DEDICATED ACCOUNTS (per-worker) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Client (N workers) Server / Ledger API │
│ ┌─────────────────────┐ │
│ │ Worker 1 (account-1)│──── POST /transactions ────▶ Process tx │
│ │ Worker 2 (account-2)│──── POST /transactions ────▶ Process tx │
│ │ Worker 3 (account-3)│──── POST /transactions ────▶ Process tx │
│ │ ... │ ... │
│ │ Worker N (account-N)│──── POST /transactions ────▶ Process tx │
│ └─────────────────────┘ │
│ │
│ • N concurrent HTTP requests (N = pool size) │
│ • Each worker uses a fixed (dedicated) source account │
│ • Row locking is per account; within one worker, txs are serialized │
│ • Good for: predictable, even distribution and simple logic │
│ │
└─────────────────────────────────────────────────────────────────────────┘
How to build it
Maintain a pool of source accounts (e.g. created once per run:
world:<id>).Create N workers; assign worker index
ito account indexi % pool_size.Each worker loops: take a transaction (e.g. from a queue), send one
POST /transactionswith that worker’s source account, then proceed to the next.No shared state between workers except the job queue; no need to “pick” an account per transaction.
4. Option 2: Individual Transactions with Dynamic Load-Balancing
Here, workers do not own a fixed account. For each transaction, a worker chooses a source account from the pool using a dynamic strategy so that busier accounts get fewer new transactions. That improves utilization when some transactions are slower (e.g. one account’s row is hot or one request is slow).
You need:
A pool of source accounts.
A per-account “in-flight” counter: how many transactions are currently sent but not yet confirmed.
A selection strategy: e.g. “least-active” (pick account with smallest in-flight count) or “power-of-two” (pick two random accounts, then the one with smaller in-flight count).
Strategy A: Least-active (check all accounts)
For each transaction, scan all accounts and choose the one with the lowest in-flight count, then increment that account’s in-flight count. When the request completes (success or failure), decrement the count.
┌─────────────────────────────────────────────────────────────────────────┐
│ DYNAMIC LB: LEAST-ACTIVE (scan all accounts) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Account pool: [1] in-flight:0 [2] in-flight:1 [3] in-flight:0 │
│ [4] in-flight:2 [5] in-flight:0 │
│ │
│ Worker needs account → Look at ALL accounts → Pick lowest (1, 3, or 5)│
│ │
│ ✓ Always picks an account with minimum current load │
│ ✗ O(pool size) per transaction; needs a lock during selection │
│ ✓ Best when pool size is small (e.g. 5) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
Strategy B: Power-of-two choices
For each transaction, pick two random accounts and choose the one with the lower in-flight count, then increment that account’s in-flight count. On completion, decrement.
┌─────────────────────────────────────────────────────────────────────────┐
│ DYNAMIC LB: POWER-OF-TWO (two random, pick lesser) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Account pool: [1] fly:0 [2] fly:1 [3] fly:0 [4] fly:2 [5] fly:0 │
│ │
│ Worker needs account → Pick 2 random (e.g. [2] and [4]) │
│ → Compare: fly:1 vs fly:2 │
│ → Choose [2] (lower in-flight) │
│ │
│ ✓ O(1) — constant time regardless of pool size │
│ ✓ Only two atomic reads (no global mutex for selection) │
│ ✓ Statistically near-optimal load in practice │
│ ✗ May not pick the single least-loaded account │
│ ✓ Best for larger pools and high throughput │
│ │
└─────────────────────────────────────────────────────────────────────────┘
In-flight lifecycle
In-flight means “request sent, response not yet received.” The counter is per account and shared across all workers. Each worker sends transactions sequentially (one at a time), but multiple workers can pick the same account before any of them complete. So the in-flight count for an account can be 0, 1, 2, … up to the number of workers—not always 0 or 1.
Per worker: at most one transaction in flight (sequential: assign → send → wait → release → next).
Per account: as many in-flight as there are workers that currently have an outstanding request using that account. When a worker’s request completes, it decrements that account’s counter; when another worker picks the same account, it increments again.
Example: 5 workers, 3 accounts. Workers A and B both pick account 2 (it had the lowest count when they looked). Account 2’s in-flight is 2 until one of them completes. So in-flight > 1 is normal under concurrency.
The counter is incremented when a worker assigns an account and sends the request, and decremented when that request completes (success or error).
┌──────────────────────────────────────────────────────────────────────────┐
│ IN-FLIGHT TRACKING (per account) │
├──────────────────────────────────────────────────────────────────────────┤
│ │
│ One worker timeline (sequential): │
│ │
│ SelectAccount() HTTP request / wait OnResponse(). │
│ │ │ │ │
│ in-flight++ (blocking) in-flight-- │
│ │◀────────────────── request in progress ────────────▶│ │
│ t0 t1 │
│ │
│ Per account: in-flight = number of workers with a request in progress │
│ for that account → can be 2, 3, … (not only 0 or 1). │
│ │
│ • Account is “active” for the whole duration [t0, t1] per worker │
│ • Slow requests keep the count high → other workers avoid that account │
│ • Fast accounts get more traffic automatically │
│ │
└──────────────────────────────────────────────────────────────────────────┘
How to build it
Pool of source accounts + an array of atomic in-flight counters (one per account).
Least-active: Under a mutex (or equivalent), find index
iwith minimuminFlight[i], theninFlight[i]++, release mutex, send request withaccounts[i]; on response (in a finally/defer),inFlight[i]--.Power-of-two: Pick two distinct random indices
i,j; ifinFlight[i] <= inFlight[j]then useielse usej; atomically increment the chosen index; send request; on response, decrement that index.Optionally expose these counters (e.g. for a simple live dashboard) to observe that slower accounts get fewer new transactions.
5. Option 3: Bulk API (batched transactions)
Instead of one HTTP request per transaction, you batch many transactions into a single bulk request. The server processes the batch (in parallel or sequentially, depending on the API). Source accounts are still chosen from your pool (e.g. round-robin within the batch) so that row locks are spread across accounts and you avoid a single-account bottleneck.
Schema
┌─────────────────────────────────────────────────────────────────────────┐
│ BULK MODE (batched requests) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Client (1 or more senders) Server / Ledger API │
│ ┌─────────────┐ ┌───────────────────┐ │
│ │ │──── Batch 1 (e.g. 100 txs) ──▶│ │ │
│ │ Sender(s) │ wait... │ Process batch: │ │
│ │ │◀──────── response ────────────│ • parallel: │ │
│ │ │──── Batch 2 (100 txs) ───────▶│ concurrent txs │ │
│ │ │ wait... │ • atomic: │ │
│ │ │◀──────── response ────────────│ all-or-nothing │ │
│ └─────────────┘ └───────────────────┘ │
│ │
│ • Each batch = one HTTP request; many transactions inside │
│ • Source accounts: round-robin (or other strategy) within the batch │
│ • parallel: server processes txs concurrently; continue on failure │
│ • atomic: entire batch commits or rolls back │
│ │
└─────────────────────────────────────────────────────────────────────────┘
Optional: parallel bulk senders
You can have multiple “bulk senders” (e.g. several threads) each sending batches to the same API. That adds client-side concurrency on top of the bulk API.
Single sender: Batch1 → wait → Batch2 → wait → Batch3
Multiple senders: Batch1 ─┬─▶ Server processes in parallel
Batch2 ─┤
Batch3 ─┘
How to build it
Build a bulk payload: list of transactions; for each transaction, assign a source account from the pool (e.g. round-robin: next account in the list).
Send one bulk request (e.g.
POST /bulkor equivalent) with that payload.Respect the API’s options for parallel vs atomic processing and continue-on-failure if supported.
Optionally run several bulk senders in parallel, each building and sending its own batches.
6. Option 4: Multi-Post Mode (one transaction, many postings)
⚠Here, one logical transaction contains multiple postings, each from a different source account (from your pool) to (possibly different) destinations. The ledger commits them atomically: all postings succeed or all fail. This uses one HTTP request for many debits from different source accounts, so you get both fewer requests and parallelism at the DB level (multiple rows locked in one transaction).
┌─────────────────────────────────────────────────────────────────────────┐
│ MULTI-POST MODE (one tx, N postings from N sources) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Client Server / Ledger API │
│ ┌─────────────┐ ┌──────────────────────┐ │
│ │ │── Tx with N postings ───▶│ Posting 1 (src-1) │ │
│ │ Single │ (N = pool size) │ Posting 2 (src-2) │ │
│ │ thread │ │ ... │ │
│ │ │◀───────── done ──────────│ Posting N (src-N) │ │
│ │ │ │ → Single atomic tx │ │
│ └─────────────┘ └──────────────────────┘ │
│ │
│ • One HTTP request = one transaction with N postings │
│ • Each posting uses a different source account from the pool │
│ • All postings commit or roll back together │
│ • Good for: atomic multi-party transfers, high throughput per request │
│ │
└─────────────────────────────────────────────────────────────────────────┘
How to build it
For each “multi-post” transaction, take N postings (N = pool size or a chosen slice).
Assign posting
ito source accountpool[i](or round-robin from a shared index).Send one
POST /transactionswith all N postings in a single transaction payload.Repeat for the next N postings until the workload is done.
8. Summary: Choosing an Option
Option | Best for | Throughput | Complexity |
1. Dedicated accounts | Simple, predictable load | Good (N-way parallelism) | Low |
2. Dynamic LB | Uneven or slow requests, bursty load | Good, adapts to load | Medium (in-flight tracking) |
3. Bulk | High volume, fewer round-trips | High | Medium (batching logic) |
4. Multi-post | Atomic multi-party moves, fewer requests | High per request | Medium |
Practical tips
Pool size: Start with 5–20 source accounts; increase only if metrics show lock contention.
Account naming: Use a deterministic pattern (e.g.
world:<id>) so you can query and reconcile balances per run.Verification: After a run, aggregate balances for all pool accounts and check that total outflow matches (successful txs × amount).
Timeouts and failures: Use timeouts and optional max-failure limits so one slow or failing account doesn’t stall the whole run; with dynamic LB, in-flight counts will naturally steer traffic away from slow accounts.
By combining a pool of source accounts with one or more of these options, you can build a load-balancing client that significantly reduces PostgreSQL row-level locking contention and increases throughput while keeping the implementation manageable.