More databases should be single-threaded

Summary

Last night, I caused something on X by saying that (most) transactional databases should be single-threaded and aggressively sharded: I have a lot more to say than would fit in a tweet, and the tweet also got more attention than I thought, so here's a lengthy blog post.(There's no solution that solves every problem. But I hope I can convince you that aggressively sharded, single-threaded databases would suit you best more often than you'd think, even if you're "just" building a generic B2B SaaS app.)The Status QuoIn essence, a traditional SQL database can write and read to rows in a table. It can also lock those rows, meaning that other writers may have to wait for the lock to be freed.More concretely, Postgres has three different transaction modes:READ COMMITTED (default): Read statements will read the current database state. Write statements don't abort.If you read a row and then another transaction changes it, any future reads will return the new value.REPEATABLE READ: Read statements see a snapshot of the DB taken at the start of the transaction. Write statements abort the transaction if that particular row changed since the snapshot.SERIALIZABLE: Like REPEATABLE READ, but write statements abort the transaction if any dependent rows changed since the snapshot.Ensures a perfect ordering without serialization anomalies, but there is considerable overhead and retries are more common.This has a lot of problems, though:When starting out:No one gets this right. IME most people think any transaction will make the entire block serializable!No one tells you when you get it wrong! There is no compile error, warning, or linter that gives you certainty. Like any parallel system, mistakes are hard to find.At scale:All three transaction modes use hard locks on writes! These don't block reads, but they do block other writes. If transaction A writes row 1 and then row 2, and transaction B does the same in reverse, chances are good you'll run into a deadlock.Under constant high ...