Say you’re building a marketplace. People list things, other people buy them, and you keep a cut of every sale.
Somewhere underneath that, something’s got to keep track of the money. That something is a ledger, and a ledger is simpler than it sounds. Money doesn’t appear out of nowhere and doesn’t vanish, it only moves, so every time it moves you write it down twice: one line saying it left here, one line saying it arrived there. The two lines add up to zero. That’s the whole rule, it’s called double-entry bookkeeping, and it’s about seven hundred years old.
For your marketplace you’d put that in a relational database, say Postgres, and it comes out as two tables. One called accounts, a row per person, with their balance on it. One called entries, two rows per movement of money, saying what left where and what arrived where. Somebody buys a chair for a hundred: ninety goes to the seller, ten goes to you, and that’s two movements, so four rows in entries say so.
So that’s the thing I built, on my laptop, with ten thousand accounts in it. Then I pointed sixteen connections at it and had them move money around at random, as fast as they could go.
9,000 transfers a second.1
That’s nine a millisecond, which is more than any marketplace I’m ever going to build. You’d have to sell thousands of chairs a second, all day, to trouble it. Fine so far.
Now think about your cut. Every sale on the marketplace pays it into the same account, because there’s only one of you. A million sellers and ten million buyers, and exactly one row in that accounts table is on the receiving end of all of it. So I changed the benchmark to match: one side of every transfer is now that account, the other side is a random customer, and everything else stays where it was.
1,091 transfers a second.
Same machine, same code, same sixteen connections, same number of rows written. The only thing that changed is which row the money lands on, and it cost me eight ninths of the throughput.
My first thought was that I’d written the ledger badly, and I had.
It was doing what most first ledgers do: read the balance, work out the new number in application code, write it back, all inside a transaction with the row locked. That’s two round trips while you’re holding a lock. Forget the lock and it’s also quietly wrong, because if two transfers read the balance before either writes, one of them vanishes without an error. Anyone who’s been bitten by that will tell you the same fix: do the arithmetic in the database, in one statement.
So I did that.
1,218 a second.
Twelve percent. I’d fixed something real, but it clearly wasn’t what was costing me the other eight thousand.
My second thought was that eight cores probably want more than sixteen connections to keep them busy. So I ran it again with more, and with fewer.
Four connections did 1,364 a second. Sixteen did 1,200. Sixty-four did 1,108. And one connection, on its own, did 1,244.
One connection beats sixty-four. Each of those sixty-four also waits 58 milliseconds per transfer, against eight tenths of a millisecond for the connection working alone, so they’re each waiting seventy times longer to do less work in total.
What’s going on is the lock. When a transaction updates a row it takes a lock on that row, and it doesn’t let go until the transaction commits, which is later than it sounds: not when the statement finishes, but when the whole transaction is over and the commit is on the disk. Everyone else who wants that row waits, and when their turn comes they do the same thing to everybody behind them.
You’d think a row could take a few writes at once, given how little each one does. It can’t. It takes them one at a time, and each one keeps it until its commit, which turns out to be the slow part.
Before Postgres tells you a transaction succeeded, it writes the change into its log, asks the disk to confirm the log is really on the disk, and waits for the answer, because that confirmation is the entire promise a commit makes. On this machine, that’s about a millisecond.
So the row hands out about a thousand turns a second, and a turn is one commit. One connection can take all thousand. Sixty-four connections have to share the same thousand, and each of them spends most of its time in the queue, which is exactly where the 58 milliseconds comes from: sixty-four turns at roughly nine tenths of a millisecond each, and you can work that out on paper before you run anything.
That explains why more connections don’t help. Why sixty-four do worse than one, I can only partly explain. A connection working alone finds the row free every time. One that’s been queueing has to be woken up first, and because the row changed while it waited, Postgres finds the new version and checks the WHERE clause against it again before it does its update. That makes every turn a little longer than it is for one connection on its own. A few connections still beat one, because a lone connection leaves the row idle while it sends its next transaction, which is how four got to 1,364. Why it keeps sliding after four, when four already pay for every handoff, I didn’t measure.
Add connections and watch them queue for the same row:
The queue also explains why every number I’d measured on that account was really the same number in a different hat. 1,091, then 1,218, then 1,244, then 1,108: four runs, all within fifteen percent of each other, all of them measuring how many times a second one row can be committed.
So what runs out is turns, and one row only has about a thousand of them a second.
Once you’re counting turns, every fix is a way of asking that one row for fewer of them. I tried three, and I’ll go from worst to best.
The first is to stop having one row. Keep sixty-four rows that are all the fee account, pick one at random for each transfer, and your balance is the sum of the sixty-four. 8,741 a second, eight times what it was doing, near enough back to where the ledger started. Not sixty-four times, sadly, because sixty-four queues don’t make the laptop sixty-four times bigger. They stop that row being the thing everyone waits for, and then you wait for everything else, which around here runs out at about nine thousand.
The second is to stop writing the balance. Insert the two entries and update nothing at all. The balance becomes whatever the entries add up to, which is what a ledger always claimed it was. 9,612 a second, which is faster than the very first run, the one with ten thousand accounts and no contention anywhere.
That first run was writing two balances on every transfer and still did 9,000, so writing balances was never the expensive part. Writing the one on the fee row was. What had been costing me eight thousand transfers a second was making everybody agree on one number, one at a time, in order.
You pay for it later, when somebody wants their balance and you’ve got to add up every entry that ever touched their account. Monzo wrote about hitting that wall: their balance reads sat at four to five hundred milliseconds at the ninety-ninth percentile until they started storing the sum of older entries in blocks, so a read only had to add up what came after. The balance comes back in the end, just not as one row that every transfer has to wait for.
The third is to stop treating one transfer as one transaction, which both of the other fixes still did. Batch a hundred transfers into one: a single update to the fee account for the whole batch, one insert carrying all two hundred entries, one update per customer.
Ten transfers per batch gets 9,500 a second. Fifty gets 41,000. A hundred gets 60,000.
So 1,200 became 60,000, and nothing got faster. Same row, same laptop, same lock, held a little longer each time. A turn used to buy one transfer and now it buys a hundred, and everything else a transaction costs gets shared by a hundred transfers too, which is how it gets past the nine thousand.
What you pay is time. A transfer has to wait for its batch, and with real traffic you’d close each batch on a timer, so this only works if a couple of hundred milliseconds between taking someone’s money and it showing up in the ledger is acceptable. In payments it usually is, given that the networks underneath settle in days. Uber did the same thing on their own system, three or four operations a second on one account going to more than thirty, by gathering them into windows of 250 milliseconds.
One account in every transfer is the extreme case. So where does the cost actually start? I went back to the random version and changed one number, how many accounts the connections were allowed to pick from.
Ten thousand accounts gives 9,000 transfers a second. A thousand accounts gives about the same. A hundred drops to 6,800, fifty to 4,700, twenty to 2,800, ten to 1,450, five to 1,250. Two accounts gives 1,187.
Most of the damage happens between a hundred accounts and ten. By five there’s nothing left to lose, and squeezing down to two costs nothing more.
Those runs picked both sides of every transfer from the pool, which is harsher than a real product, where one side is usually a customer. So the curve is about how quickly sharing rows starts to cost, and at a hundred accounts it’s already a quarter. A small payment product has a handful of accounts that a lot of its transfers touch: a fee account, a float account for the money you’re holding before it’s paid out, one per currency you take, a couple more for money in transit. The fee account alone is on every sale, which puts you back on the 1,091, and you can be there on launch day.
TigerBeetle is a database built specifically for this problem, and their homepage puts a number on the thing I’d just measured. Traditional SQL databases hold their locks across the network, they say, and even modest contention caps write throughput somewhere between a hundred and a thousand a second, which they call a hard asymptote that no amount of horizontal scaling will get you past.
I measured 1,200, a shade above the top of their range, and their own sentence says why: they’re talking about locks held across a network. My Postgres is a socket away on the same machine, so the network adds almost nothing to a turn. Joran Greef, who built TigerBeetle, does the same arithmetic on a podcast with a ten millisecond round trip and lands on a hundred.
Which puts my first fix in a better light than it deserved. Deleting the read before the write only bought me twelve percent because my read cost about a tenth of a millisecond. Delete it when the round trip is ten milliseconds, like Greef’s, and you’re taking all ten out of the middle of a held lock, every time.
There’s a disagreement in the other direction too. Paul Gross built a ledger like this one and benchmarked it, and going from fifty accounts to ten cost him twenty-nine percent of his throughput where the same change cost me seventy. His version does the whole transfer in one call to a database function, so its locks are never held across a round trip, where my runs with fewer accounts used the first version, which sends each statement separately with the locks held. And his ran on a different laptop, with twenty connections to my sixteen. That’s enough difference that the two numbers were never going to match. If the number matters to you, run it on your own setup.
Keep the like count for a video in one row and you’ve built the same queue. So has the shop with one row for the stock level of the thing everybody wants, and the airline with one row for the last seats on a flight. Lots of people want the same row at the same moment, and they all queue for it.
In every one of those, adding connections feels like it should help, and it doesn’t. I had plenty of connections. What I was short of was turns, and the row only hands those out one at a time, at the speed a commit reaches the disk. Sharding the row, dropping the balance and batching the transfers are all ways of asking that row for fewer of them.
If you find yourself here, with throughput flat and latency climbing as the load goes up, count turns before you tune anything. Batch if a couple of hundred milliseconds of delay is acceptable, because it’s worth fifty times the number you started with and seven times what sharding gets you, and the work lives in your application rather than your schema. Shard the row if that delay isn’t acceptable, since sixty-four rows buy back most of it and cost you nothing but a rule for adding up the balance. Give up the balance column only if you’re ready to pay for it on the read side. And don’t raise the connection pool, whatever else you do, because the pool was never the thing running out.
Sixty-four connections lose to one because they’re all waiting on the same row, and taking a cut of every sale puts one row on the end of every sale. That row gets about a thousand commits a second however big the machine is, so what’s left for you to decide is how often you make it commit.
Postgres 17 in Docker on a MacBook Air, eight cores, fsync on. Ten thousand accounts, all funded so nothing gets declined. A transfer is one transaction: two balance updates, two entry rows, commit. The load comes from pgbench, which ships with Postgres, so the whole thing runs from one command. Every number here is a twenty-second run at sixteen connections unless it says otherwise. ↩