The Hot Row Problem: Scaling One Balance to 500 Writes per Second

Part 3 of a 3-part series on concurrency in payment systems.

So far in this series:

• Part 1: read-modify-write on a shared balance causes lost updates: silent, error-free, missing money.
• Part 2: optimistic locking (version column + conditional update + retry) fixes it, and beats pessimistic locking for a high-throughput payment gateway.

Part 2 ended with a warning: optimistic locking assumes conflicts are rare. Here's the interviewer pulling on that thread:

"You onboard a massive merchant, say McDonald's. They receive 500 payments per second, all to one balance row. Your optimistic locking approach turns into a retry storm. The system burns down. What do you do?"

Why optimistic locking collapses under contention

Every optimistic write has a window between the SELECT and the conditional UPDATE. If any other write to the same row commits inside that window, you get "0 rows affected" and have to retry.

A rough back-of-the-envelope model, assuming writes arrive randomly and the read-to-write window is about 5 ms:

At 500 writes/second, more than nine out of ten attempts fail. And it gets worse, because retries are writes too. Each failed attempt re-reads and tries again, adding load to the same row, which raises the conflict rate further. It's a feedback loop: CPU goes to retrying instead of processing payments, latency climbs, and throughput falls.

Pessimistic locking doesn't save you either. It just turns the retry storm into a queue: 500 writers lined up behind one lock.

The real problem isn't the locking strategy. One row is being asked to absorb 500 writes per second. That row is a hotspot, and no lock design changes the fact that every request is fighting over the same thing.

The idea: split the row

If one hot row is the problem, use many cool ones.

Instead of storing McDonald's balance in a single row, store it across N rows (shards). Each shard holds a slice of the balance, and the true balance is the sum of all slices.

Before:                         After (N = 10):
┌───────────┬──────────┐        ┌───────────┬──────────┬──────────┐
│ merchant  │ balance  │        │ merchant  │ shard_id │ balance  │
├───────────┼──────────┤        ├───────────┼──────────┼──────────┤
│ mcd       │ 10,000   │   →    │ mcd       │ 0        │ 1,000    │
└───────────┴──────────┘        │ mcd       │ 1        │ 1,000    │
   500 writes/sec on            │ ...       │ ...      │ ...      │
   ONE row 🔥                   │ mcd       │ 9        │ 1,000    │
                                └───────────┴──────────┴──────────┘
                                   ~50 writes/sec per row

Each write touches one shard, so contention per row drops by a factor of N:

With 100 shards, McDonald's looks like a hundred ordinary small merchants to the database, which is exactly the workload optimistic locking was designed for.

The insight I like most from this problem: we didn't build a better lock. We removed the reason everyone needed the same lock.

The schema

CREATE TABLE balance_shards (
    merchant_id  UUID           NOT NULL,
    shard_id     INT            NOT NULL,
    balance      DECIMAL(18,4)  NOT NULL DEFAULT 0,
    version      INT            NOT NULL DEFAULT 0,
    PRIMARY KEY (merchant_id, shard_id)
);

The composite primary key matters. It makes "update shard 42 of McDonald's" a direct index lookup, and "sum all of McDonald's shards" a tight range scan.

The write path

For each incoming payment, pick one shard and update only that shard:

-- Credit $50 to McDonald's, using shard 42 (chosen by the app)
UPDATE balance_shards
SET balance = balance + 50,
    version = version + 1
WHERE merchant_id = 'mcd' AND shard_id = 42 AND version = 7;

This is exactly the optimistic locking pattern from Part 2, just aimed at one row out of a hundred. On "0 rows affected," re-read that shard and retry. Other shards are untouched.

Choosing the shard:

• Random (random.randint(0, N - 1)) is the simplest option, and it spreads load evenly.
• Hash-based (hash(payment_id) % N) is deterministic. A retry of the same payment hits the same shard, which can make idempotency checks and debugging easier.

Either works. What matters is that writes spread evenly.

Tip: if a payment is a pure credit with no business rule that needs to read the current balance, you don't need the version check at all. SET balance = balance + 50 is already atomic (see Part 1). Sharding then simply spreads the database's internal row-lock contention across N rows. Keep the version check for writes that read before they decide.

The read path

To get the merchant's total balance, add up the shards:

SELECT SUM(balance) FROM balance_shards WHERE merchant_id = 'mcd';

This trades a slightly more expensive read for much cheaper writes. For a high-volume merchant that's a great trade: they receive hundreds of payments per second, but someone looks at their dashboard balance far less often.

Because it's a single SQL statement, databases like PostgreSQL evaluate it against one consistent snapshot, so you won't get a total that includes half of an in-flight write. If the balance is read very frequently, you can also cache it or maintain a periodically refreshed rollup.

Things the slide version usually skips

Balance sharding is easy to sketch on a whiteboard. Here's what comes up when you actually build it.

1. Debits are harder than credits

Credits are easy: add to any shard. But what about a refund or payout that takes money out? An insufficient-funds check now spans N rows. The merchant might have $10,000 in total while the shard you randomly picked holds $3.

Some practical options:

• Try another shard. Debit from any shard that has enough, and fall back to others if one doesn't.
• Lock all shards for the rare big debit. Payouts are infrequent and often batched, so briefly locking all of one merchant's shards (SELECT ... FOR UPDATE across them, in shard order to avoid deadlocks) is often acceptable.
• Separate the flows. High-frequency credits go to shards, and low-frequency debits go through a slower, stricter path.

2. Shards don't need to be balanced

Since only the sum is meaningful, the shards don't need equal amounts. That makes onboarding and growth simpler than it sounds:

• Start sharding a merchant: keep the existing balance in shard 0 and insert shards 1…N−1 with balance 0.
• Increase N: insert more zero-balance rows. The total doesn't change, so no migration is needed.
• Decrease N: move each removed shard's balance into a surviving shard and delete it, inside a single transaction.

3. Shard only the whales

Sharding every merchant would be overkill: more rows, more aggregation and more code paths for no benefit. Most merchants never generate enough contention to need it.

A clean approach is a per-merchant shard_count column that defaults to 1. A merchant with one shard behaves exactly like the original single-row design. Raise it only for the top 1% that actually generate contention, either manually or automatically when retry rates on their row cross a threshold.

Beyond sharding

Balance sharding isn't the only way to remove a hotspot. Two others are worth naming in an interview:

• Batching writes. Put a merchant's payments on a queue and have a single consumer apply them in batches (e.g. one UPDATE per 100 ms with the summed amount). There's no contention, at the cost of a slight delay.
• Append-only ledger. Don't update a balance at all. Insert an immutable entry per transaction, and compute balances from entries (with periodic snapshots). Inserts don't conflict with each other, and you get a full audit trail for free.

That second idea, recording every movement of money rather than just the current balance, is the foundation of how real financial systems prove their numbers are correct. That's the double-entry ledger, and it's a topic for another post.

Recap of the whole series

1. Lost updates happen when a read-modify-write cycle assumes nothing changes between the read and the write. They're silent and inevitable at scale.
2. Pessimistic locking prevents conflicts by making others wait. It's simple, but it chokes under contention or long lock holds.
3. Optimistic locking detects conflicts with a version column and retries. It's the better default, as long as conflicts are rare.
4. When one row is hot enough that conflicts are the norm, stop tuning the lock and remove the hotspot: shard the balance across N rows and sum on read.
5. Apply sharding selectively, only to the merchants who actually need it.

The progression is the real lesson. A junior engineer asks "which lock should I use?" A senior engineer asks "what are the trade-offs between these locks?" A staff engineer asks "why are all these requests fighting over the same row in the first place?"