Sharding strategies for ledger tables
100 million users, one Postgres won't hold them. Pick a shard key.
By Solomon Ajayi · Free to read, no signup
Your ledger grows past what a single Postgres can handle. Time to shard. The shard key choice determines what queries become CHEAP and what becomes EXPENSIVE. Shard by user_id: per-user queries (wallet balance, statement) are co-located in one shard, but cross-user queries (total revenue, top customers) fan out across all shards. Shard by date: time-window queries are local but per-user history fans out. Shard by account: per-account aggregates local but transactions touching multiple accounts hit multiple shards. The right answer depends on your QUERY PATTERN; lesson 41 (partitioning) is the time-shard adjacent. This lesson posts a user-sharded entry and discusses the trade-offs.
Sharding is not really a scaling decision, it is a decision about which queries you want to be fast forever and which you are willing to make slow. Once your ledger outgrows one database, you split it across many by hashing a shard key, and that key choice quietly sorts every future query into cheap or expensive. There is no neutral key; you are picking your favorite access pattern and taxing all the others.
Shard by user id and per-user queries (wallet balance, statement) live entirely in one shard, which is the dominant pattern for consumer fintech, so most queries stay single-database and fast. The cost lands on cross-user questions like total revenue or top customers, which now have to fan out to all shards and aggregate. Shard by date instead and time-window reports go local while a single user's history scatters. The journal entry shape never changes; the shard router in the data layer decides where it lands.
The case that exposes the cost is a P2P transfer where the two users live in different shards. A single-shard transaction cannot span them, so an entry that used to be one atomic write becomes a saga: debit A in its shard, credit B in its shard, and a compensating credit back to A if B fails. The double-entry shape is still two lines, but the storage now needs a multi-step protocol with a compensation path, which is the real price of sharding by user.
Worked example, step by step
User-id-sharded write: deposit on user X lands in shard N
User X has user_id `wlJ6UufvGyoMLGgqDLJ9tUgeIACpA3xD`. Hash that to shard 7 (of 16). Every journal entry referencing user X, including this deposit, lands in shard 7. Wallet balance, statement, comment threads, all in shard 7. Cross-user queries (e.g., total deposits across all users today) fan out to all 16 shards in parallel.
| Account | Debit | Credit |
|---|---|---|
| Bank Account (1200) | ₦5,000.00 | |
| User Wallet (2000) | ₦5,000.00 |
The journal entry is the standard shape. The architectural decision is INVISIBLE at the entry level, shard routing happens in the data-access layer. Each shard has its own connection pool, its own primary, its own replicas.
Cross-shard transaction: user A → user B P2P transfer
Tricky case. User A is in shard 3; user B is in shard 11. A P2P transfer touches BOTH user wallets. Either: (a) keep a single global writer for cross-shard transactions and pay the latency, (b) use a saga pattern (debit A, then credit B, compensate on B failure), or (c) require the journal entry to live in one shard with a 'shadow' entry replicated to the other. Most fintechs do (b) with an outbox-driven saga. This lesson posts the entry as if both lived in the same shard for simplicity.
| Account | Debit | Credit |
|---|---|---|
| User Wallet (2000) | ₦5,000.00 | |
| User Wallet (2000) | ₦5,000.00 |
User A wallet (in shard 3) DOWN ₦5,000 (debit, liability decreasing). User B wallet (in shard 11) UP ₦5,000 (credit, liability increasing). In a single-shard view this is trivial; in a sharded view, it's a saga with a compensating credit-A on failure-to-credit-B. The journal entry SHAPE is the same; the storage gets harder.
Takeaway
Shard your ledger by user_id when your dominant query pattern is per-user (which it always is for B2C fintech). Accept that cross-user queries become fan-out + aggregate operations served by a separate analytical store (Druid, ClickHouse, or a snapshot warehouse). Accept that cross-shard transactions become sagas with compensation paths. Build the shard router cleanly into your data layer so application code never has to know which shard it's hitting. Avoid resharding for as long as possible, pick a high shard count up front (16, 32, 64) so you can grow into them rather than re-sharding mid-life. The pain of resharding is what kills fintechs that grew too fast on a single Postgres.
Practice this on a real ledger
Reading is half of it. Open this lesson in the lab to post the entries yourself against a real Postgres-backed double-entry ledger, with the validation on. Free, your sandbox is yours.