Distributed SQL databases, compared
The previous post in this series covered single-node relational databases — Postgres, MySQL, Oracle, SQL Server — and the tradeoffs they make around concurrency on one machine. Those systems scale up: bigger box, more cores, more memory, faster disks. Eventually that runs out. Distributed SQL databases — CockroachDB, Google Spanner, YugabyteDB, TiDB, and newer entrants like Aurora Limitless — scale out instead: more machines, each holding a slice of the data, all still speaking SQL and still promising ACID transactions.
That promise is much harder to keep once data lives on multiple machines. This post spends real time on the mechanics of how these systems actually achieve horizontal scale, because that mechanism is where every meaningful tradeoff between them originates.
How horizontal scalability actually works
Splitting a single-node database across many machines requires solving four problems simultaneously:
- Where does each piece of data live.
- How many copies of it exist.
- How do those copies stay in agreement.
- How do transactions that touch data on multiple machines still behave atomically.
Every distributed SQL database solves these in roughly the same shape, even though the details differ.
- Partitioning (sharding) the data
Tables are split into contiguous chunks — usually called ranges or tablets — typically by sorting rows on their primary key and cutting the sorted key space into pieces of a bounded size (commonly tens of megabytes to a few hundred). This is range partitioning. Some systems instead hash the key to pick a shard, which spreads load more evenly but destroys the ability to do efficient range scans. Most modern distributed SQL engines default to range partitioning specifically so that ordered queries stay efficient.
2. Replicating each shard
Each range is copied to multiple nodes — typically three or five — so the system survives a machine failure without losing data. This is where distributed SQL borrows directly from distributed systems theory rather than classical database theory: each range and its replicas form an independent consensus group.
3. Keeping replicas in agreement via consensus
Every range’s replicas run a consensus protocol — almost universally a variant of Raft or Paxos — to agree on the order of writes to that range. One replica is elected leader for the range; writes go through the leader, get replicated to a majority of followers, and are only considered committed once a majority acknowledges. This majority requirement is exactly why these systems tolerate losing a minority of nodes without losing data or availability for that range.
4. Coordinating transactions that span multiple ranges
A single SQL transaction routinely touches rows that live on different ranges, on different nodes. That requires a second layer of coordination on top of per-range consensus — typically a variant of two-phase commit (2PC), where a transaction coordinator gets every participating range to independently prepare and then commit. Google’s original Percolator paper (which underlies Spanner’s transaction model) popularized doing this using MVCC-style timestamps rather than heavyweight locking, which is now the dominant pattern across the whole category.
5. Ordering events across machines with no shared clock
Because there’s no single physical clock shared by every node, the system needs some way to decide the global order of transactions that happened on different machines — otherwise “what does the database look like right now” has no consistent answer. This is arguably the single hardest problem in the whole stack, and it’s where the different systems diverge the most (covered in detail below).
6. Rebalancing automatically as load changes
As ranges grow past their size threshold, or as some ranges get hotter than others, the system splits and moves ranges between nodes automatically, without operator intervention or downtime. This elastic rebalancing is what lets you add a node to a live cluster and have it start absorbing load within minutes — the single-node equivalent would be re-provisioning a bigger machine and migrating everything onto it.
Walking a real transaction through the stack
The six steps above are abstract. Here’s what they look like for an actual write — a funds transfer that debits an account on Range A and credits one on Range B, modeled on how CockroachDB’s transaction protocol (write intents plus a transaction record, its production implementation of the Percolator pattern) actually executes this. Step through it:
Replication factor: how many nodes can you lose?
Every consensus group needs a majority of its replicas to agree before a write is durable. That single fact determines both how many node failures a cluster survives and how many nodes have to acknowledge every write.
The clock problem, and three different answers to it
Deciding whether transaction X happened before transaction Y matters enormously for consistency, but machines’ clocks drift relative to each other by milliseconds or more. The systems below take genuinely different approaches to this, and it’s one of the most consequential architectural decisions any of them make.
TrueTime — bounded-uncertainty atomic clocks
Google Spanner
Spanner’s defining innovation is TrueTime: every node has access to GPS and atomic clock hardware, giving it a time reading with a known, bounded uncertainty window (typically a few milliseconds). Instead of pretending clocks are perfectly synchronized, Spanner’s transaction protocol explicitly waits out that uncertainty window before committing, guaranteeing that if transaction Y starts after transaction X commits in real time, every node will agree Y comes after X. This is what lets Spanner offer external consistency — the strongest consistency guarantee in this category — but it requires specialized hardware most operators can only get by running on Google Cloud.
The numbers behind this: Spanner’s TrueTime uncertainty bound (ε) runs under roughly 4ms at the 99th percentile in Google’s own infrastructure, and the resulting commit wait — the time a write transaction has to sit idle before it’s safe to acknowledge — averages around 5–8ms per write. A single-region Postgres commit, by contrast, can complete in under a millisecond. That gap is the literal, measurable price of external consistency.
Hybrid Logical Clocks — software-only ordering
CockroachDB & YugabyteDB
Both use a Hybrid Logical Clock (HLC): a timestamp that combines each node’s physical clock with a logical counter, so causally-related events stay correctly ordered even when physical clocks disagree slightly. This needs no special hardware and runs on any commodity server or any cloud, which is a major reason both databases can be self-hosted anywhere. The cost is a slightly weaker default consistency guarantee than TrueTime provides, plus a dependency on bounded clock skew between nodes (enforced via NTP) that operators have to actually monitor.
Get Bhavesh Anand (bhaveshAn)’s stories in your inbox
Join Medium for free to get updates from this writer.
CockroachDB enforces this with a hard --max-offset setting, 500ms by default: if a node detects its clock has drifted beyond that threshold relative to its peers, it self-terminates rather than risk serving an inconsistent read. That's a deliberate, aggressive tradeoff — correctness over uptime — and it's the single most common cause of unexpected node restarts operators report in production.
Percolator-derived timestamp oracle
TiDB
TiDB’s transaction layer, TiKV, is a direct implementation of Google’s Percolator model: a centralized Placement Driver component hands out globally increasing timestamps to every transaction, which sidesteps the clock-synchronization problem entirely by not relying on physical clocks for ordering at all. The tradeoff is that the timestamp oracle is a potential bottleneck and a component that itself needs to be highly available, since every transaction in the cluster depends on it.
What horizontal scale actually buys you — and costs
Once partitioning, replication, and transaction coordination are all in place, you get real horizontal write scalability: adding nodes increases both storage capacity and write throughput, not just read capacity the way single-node read replicas do. That is the entire point, and it’s genuinely difficult to get elsewhere.
What it costs is latency on multi-range transactions. A transaction that touches only one range is nearly as fast as a single-node database. A transaction spanning multiple ranges — especially ranges on nodes in different geographic regions — pays the cost of consensus round-trips and two-phase commit coordination. Schema design in distributed SQL is largely an exercise in keeping transactions range-local wherever possible, which is a genuinely different skill from single-node schema design.
What actually breaks in production
The architecture holds up well in steady state. It’s worth being specific about the failure modes senior engineers actually run into operating these systems, since the marketing material tends to skip this part.
Failure mode · clock skew
A cluster that self-terminates to protect correctness
Because HLC-based systems depend on bounded clock skew, a bad NTP source or a VM live-migration event that stalls a clock can push a node’s offset past its configured limit. CockroachDB’s response is deliberate: the node kills itself rather than risk serving a stale or inconsistent read. In a cluster where several nodes share the same misconfigured time source, this can cascade — multiple nodes hit the threshold within the same window, quorum is lost on the ranges they held, and the outage looks like a network partition even though the network is fine.
Failure mode · leaseholder unavailability
One stuck node can stall queries cluster-wide
Each range has a single “leaseholder” that serves reads and coordinates writes for it. If that node becomes unresponsive — a disk stall, a deadlock, a severed network link — rather than crashing outright, in-flight requests routed to it can hang until the lease-transfer timeout expires rather than failing fast. In documented worst cases, a query touching ranges whose leaseholders all sat on one faulty node has taken up to 60 seconds to return before the cluster elected new leaseholders elsewhere. The lesson: “the node is technically still up” is not the same as “the node is fine,” and health checks that only ping the process miss this failure mode entirely.
Real incident · September 2018
Two independent bugs, one fsync storm
A documented production outage on a CockroachDB registration cluster traced back to a bug in the vendored etcd/raft library: the MustSync flag, which tells the storage layer whether a Raft state change needs a synchronous disk write, was being set on every heartbeat regardless of whether the commit index had actually advanced. The fsync rate became proportional to heartbeat traffic rather than to real commits. The cluster's I/O budget, already running near half capacity under normal load, was consumed by heartbeat-induced fsyncs rather than actual writes, and the cluster lost availability. The fix (CockroachDB PR #30443, pulling in etcd-io/etcd#10106) corrected the MustSync logic so fsyncs only happen when there's real state to persist.
The throughline across all three: distributed consensus doesn’t just distribute your data, it distributes your failure modes. A clock, a disk, and a heartbeat interval are all things a single-node database never has to reason about as sources of a correctness-driven outage — in a consensus-based system, each one is a first-class failure surface.
Correction: an earlier version of this section described the 2018 incident mechanism less precisely. Thanks to Franck Pachot for the correction.
Where each system sits on the tradeoff spectrum
Raft-based, open source, HLC ordering
CockroachDB
Built explicitly to survive datacenter failures with strong consistency by default, using Raft for per-range consensus and HLCs for ordering. Strongly PostgreSQL-wire-compatible, which eases migration. The tradeoff is that its strong default consistency mode carries real latency cost on multi-region deployments unless you deliberately relax isolation or use its geo-partitioning features to keep data (and transactions) local to a region.
Paxos-based, TrueTime, fully managed
Google Spanner
The strongest consistency guarantee in the category, proven at extreme scale inside Google itself for over a decade before being offered externally. The tradeoff is availability of the underlying hardware: TrueTime is a Google Cloud-only capability, so Spanner is not something you self-host, and you’re fully dependent on Google Cloud’s operational model and pricing.
Raft-based, PostgreSQL-compatible, open source
YugabyteDB
Similar architecture to CockroachDB — Raft consensus, HLC ordering — but built specifically for closer PostgreSQL compatibility, including support for many Postgres extensions. It also offers a distinct storage layer optimized for lower read latency than CockroachDB in some workloads. The tradeoff is a smaller operational community than CockroachDB and a somewhat more complex deployment model given its dual API surface (YSQL and YCQL).
YugabyteDB’s durable_wal_write flag defaults to false — WAL writes sync to disk on a time/size interval rather than on every write, on the explicit assumption that a 3-way replication factor already protects against single-node data loss. This is the inverse tradeoff from CockroachDB's heartbeat-fsync bug above: it's not that one system persists more metadata than the other, but that they make different default choices about when a write needs to touch disk versus when replication alone is trusted to provide durability.
Percolator-derived, MySQL-compatible, open source
TiDB
The only major system here that speaks the MySQL wire protocol rather than PostgreSQL’s, which makes it the natural migration target for MySQL-based applications outgrowing a single node. Its separated storage (TiKV) and compute (TiDB) layers scale independently, which is architecturally distinct from the other three. The tradeoff is the centralized timestamp oracle’s role as a potential single point of contention at very high transaction rates, though it is itself made highly available via Raft.
Tradeoff matrix
The bottom line
Horizontal scalability in SQL databases is not a feature you turn on — it’s a completely different architecture built from range partitioning, per-range consensus, cross-range transaction coordination, and some answer to the clock-ordering problem. Every one of those four layers has real cost, and the systems above differ mainly in how they pay it: Spanner buys the cleanest answer to clock ordering with specialized hardware and cloud lock-in; CockroachDB and YugabyteDB buy portability with a slightly weaker (but still strong) default guarantee; TiDB sidesteps clocks entirely at the cost of a centralized coordination component.
The single practical rule that falls out of all of this: distributed SQL gives you real horizontal write scale, but only for transactions that stay close to their data. Schema and query design that respects range locality is what actually determines whether you get the scalability you’re paying the architectural complexity for.
