Cruxly › System design › Databases: Choosing One

Distributed SQL: Spanner, CockroachDB, Aurora and friends

SQL and ACID across machines and regions: what you get, and the latency, clock and operations bill

Distributed SQL keeps the relational contract (SQL, transactions, serializable isolation) while spreading data over many machines. It does that with consensus per shard, an atomic commit protocol across shards, and a clock strategy to order transactions. Each piece costs round trips or waiting. Choose it when you need cross-key transactions beyond one primary, or writes that survive the loss of a region. For most systems a single primary with replicas is cheaper and faster.

Why single-node ACID does not just scale out

On one machine, ACID is cheap because everything is shared: one write-ahead log, one lock table, one clock. A commit is one log flush. Split the data over several machines and each of those shared things disappears. You need three replacements, and each one costs something.

That is the whole lesson in a table. Everything below is how Spanner and CockroachDB implement these three rows, how Aurora avoids two of them by keeping a single writer, and how sharded SQL (Vitess, Citus) pushes the problem back onto the application.

Spanner's paper says Bigtable is hard to use for applications that want strong consistency across wide-area replication, and that many teams chose Megastore despite its poor write throughput. The point of "NewSQL" is to take that burden away from application code. It does not make the physics go away.

I will not re-teach isolation levels, storage engines or the choose-a-database framework here. This lesson assumes you already know that serializable isolation is a correctness contract and asks what delivering it over a network costs.

:::key The price list Replication costs a majority round trip per write. Atomic commit across shards costs extra rounds. Ordering without a shared clock costs waiting or retries. Know which of the three a design choice is paying. :::

Ranges replicated by consensus

Both systems cut the data into contiguous slices and replicate each slice on its own.

• CockroachDB stores one sorted key-value space and splits it into ranges. The default maximum range size is 512 MiB (range_max_bytes = 536870912, current stable docs). Each range is a Raft group.
• Spanner splits data into tablets, each replicated by a Paxos group with a long-lived leader (OSDI 2012 paper).

The number to hold in your head: a big cluster is thousands of small consensus groups, not one. A node is a replica in many groups and the leader of some of them.

The write path. A write goes to the range's leader (in CockroachDB the leaseholder, which is kept on the Raft leader under the default leader-lease scheme). The leader appends it to its log and sends it to the other replicas in parallel. It commits when a majority has it: two of three, or three of five. Latency is therefore the round trip to the replica that completes the majority (the nearest follower, with three replicas), not the farthest one. Spanner's paper shows this: write latency stayed near 14 ms for 1, 3 and 5 replicas because Paxos runs in parallel (2012 numbers, a test cluster with sub-millisecond network distance).

Failure. If the leader's node dies, the followers elect a new leader. CockroachDB's docs say detection, election and lease acquisition "should complete within a few seconds". In Spanner's paper, leader leases last 10 seconds, and after a zone was killed every group had a leader again about 10 seconds later. Ranges whose leader survived never noticed.

Non-voting replicas. CockroachDB (non-voting replicas arrived in v21.1) can place replicas that follow the log and serve follower reads but do not vote, so they add almost nothing to write latency. Spanner has the same idea: witness replicas vote but hold no readable copy, and read-only replicas serve reads but do not vote. These are the tools for multi-region placement later.

:::key Consensus is per range Losing a node means electing new leaders for the ranges it led. It is not a cluster-wide election. A cluster-wide Raft group is the wrong picture and a common interview slip. :::

Transactions that cross ranges

If a transaction touches keys in one range, one consensus group decides it. Spanner's paper says exactly this: a single-group transaction skips two-phase commit. If it touches two ranges there is no shared log, so the shards need an atomic commit protocol on top of their own consensus.

Spanner. The client buffers its writes and then starts two-phase commit between the Paxos group leaders. It picks one group as coordinator. Each other participant leader takes write locks, logs a prepare record through Paxos, and reports a prepare timestamp. The coordinator picks the commit timestamp, logs the commit record through Paxos, and the participants apply it and release locks. Prepare and commit are each a consensus round, in sequence, plus a commit wait that overlaps the commit round (next section).

The paper measured how this scales with participants, on a cluster across 3 zones with network distance under 1 ms (2012 numbers):

Read it the way the authors did: fine up to dozens of participants, noticeably worse at a hundred. Over wide-area links each of those rounds is a cross-region round trip, so real numbers are higher.

CockroachDB avoids writing a separate prepare record per shard. A write creates a write intent in the range, which is a replicated provisional value that also acts as an exclusive lock. A transaction record in the range of the first write holds the state: PENDING, STAGING, COMMITTED or ABORTED. At commit the coordinator writes the record as STAGING, listing the writes still in flight, in parallel with those writes. A transaction is committed once its record is STAGING and every listed write has replicated, so the client gets its answer after one round of waiting. Moving the record to COMMITTED and resolving the intents happens afterwards, asynchronously. A transaction that meets a STAGING record runs a recovery check on the listed writes. This is the "parallel commits" design: the client waits for the writes and the record in parallel instead of prepare-then-commit in sequence.

Conflicts. Concurrent transactions hit the same intents. Under serializable isolation, CockroachDB may push a transaction's timestamp and must re-check what it read, or return a retryable error (code 40001). The client must retry. The server retries automatically only for individual statements (implicit transactions) whose results still fit in the results buffer, 16 KiB by default; once results start streaming it cannot, so write transactions as retry-safe units.

:::key Keep transactions local Design so the common transaction touches one range, or at least one region. Every extra range adds a participant, and every extra region adds a wide-area round trip. :::

Clocks: TrueTime and hybrid logical clocks

Serializable is about order. If transaction A commits and then B starts, B must see A. External consistency (Spanner's term; the paper calls it equivalent to linearizability, and it is commonly equated with strict serializability) means that if T1 commits before T2 starts, T1's timestamp is smaller. Without a shared clock you have to engineer that.

Spanner: TrueTime and commit wait. TrueTime returns an interval [earliest, latest] that is guaranteed to contain true time. It is backed by GPS receivers and atomic clocks. The 2012 paper describes the uncertainty (epsilon) as a sawtooth varying from about 1 to 7 ms over each poll interval, with a 30 second poll interval and an assumed 200 microseconds per second of drift. These are 2012 numbers; I have not verified current ones, so treat them as the order of magnitude.

The coordinator picks a commit timestamp s no earlier than TT.now().latest, then waits until TT.after(s) is true before making the commit visible. Once s is certainly in the past everywhere, any transaction that starts afterwards gets a later timestamp. The paper says the expected wait is at least twice epsilon and is overlapped with the Paxos round. In its one-replica test, commit wait was about 5 ms and Paxos about 9 ms of a 14 ms write.

CockroachDB: hybrid logical clocks. No atomic clocks are required. Each timestamp is a physical part close to wall time plus a logical counter, and nodes exchange timestamps in every message so clocks never run behind what they have seen. In return you configure a maximum clock offset (--max-offset, default 500 ms; the multi-region docs recommend 250 ms for new clusters). A node that finds itself out of sync with at least half the others by 80% of that bound crashes on purpose.

Because the offset is a bound and not an exact value, a read can find a value with a higher timestamp inside its uncertainty window and cannot tell whether it happened before the read. CockroachDB then pushes the reading transaction's timestamp past that value and, under serializable isolation, re-checks its earlier reads. The docs say serializability holds whatever the skew; skew beyond the bound can break single-key linearizability between transactions. Jepsen's 2016 report on a CockroachDB beta found that it offers serializability, not strict serializability (single keys looked linearizable, the whole database did not), and that its tests only injected offsets below the configured bound. It also found serializability bugs, which were fixed in later betas.

:::key TrueTime does not remove uncertainty It makes the uncertainty small and explicit, then waits it out. While the bound holds, a bigger epsilon means slower commits, not wrong ones. :::

Read paths: leaseholders, followers and stale reads

Writes need consensus. Reads mostly should not. Both systems have a cheap read path and a fresh read path, and you choose per query.

Strong reads. In CockroachDB, a normal SELECT goes through the range's leaseholder. The lease means that replica is the only one allowed to serve reads, so it can answer without a consensus round. The answer is current. The cost is distance: if the leaseholder is in another region, so is the round trip.

Stale reads. Every range tracks a closed timestamp: a promise that no new write will appear at or below it, advanced continuously and lagging current time by a target interval. Any replica can serve a read whose time is at or below it. In SQL this is AS OF SYSTEM TIME; the function follower_read_timestamp() returns a time far enough back that the nearest replica can usually answer. Bounded staleness reads let the system pick the freshest timestamp the local replica can serve, and come with their own restrictions (for example, single-statement use). Limits from the docs: follower reads work only in read-only transactions, a timestamp that is too recent falls back to the leaseholder, and long-running write transactions can stall an exact-staleness read behind their intents.

Spanner has the same shape. Its read-only transactions and snapshot reads took about 1.2 to 1.4 ms in the paper's benchmark against about 14 ms for a write (2012, microbenchmark). In the current docs, a read-only replica can serve stale reads without a round trip to the leader region, assuming staleness of at least 15 seconds.

:::key Stale is a number "Follower reads" is only an acceptable answer if you also say how stale: seconds in the Spanner read-only replica case, the closed-timestamp lag in CockroachDB. Name the bound. :::

Multi-region placement and survival goals

This is why people buy distributed SQL. Plain async replicas lose recent writes when a region dies. Consensus across regions does not, but it puts the speed of light into your write path.

CockroachDB survival goals. The default is zone failure: the database stays fully available for reads and writes if a zone is lost, but several zones in one region failing may not be survivable. The region goal keeps the database available if a whole region is lost. It needs at least three database regions (with three regions the docs show 5 replicas per range), and the docs state the cost: write latency rises by at least the round trip to the nearest other region; read performance is unaffected.

Assumption for illustration: if the nearest other region is 70 ms away round trip, every region-survivable write takes at least about 70 ms, plus the rest. That time is the speed of light, not a tuning problem.

Table localities decide where a table's data is fast:

• Regional by table: fast in the table's home region, slower elsewhere.
• Regional by row: each row has a home region in a hidden crdb_region column, which defaults to the region of the gateway node that inserted it. The table and its indexes are partitioned by it. Good for per-user data where users live near one region.
• Global: low-latency reads from every region, higher write latency, for read-mostly reference data. It uses non-blocking transactions with a timestamp in the future plus commit wait, so write latency depends on --max-offset.

Spanner placement. A regional configuration has three read-write replicas in three zones and a 99.99% availability SLA. Dual-region and multi-region configurations are rated 99.999% (Google's SLA page; check it for the exact regions and conditions). Writes always go to the leader replica first, in the leader region. Witness replicas vote on writes but hold no readable copy, so they make a quorum cheaper. Read-only replicas serve reads close to users without voting.

Schema design for locality. Put the region (or tenant) into the primary key or the row's home region, keep a user's rows together, and avoid transactions and foreign keys that join data homed in different regions. A model that is global by accident pays wide-area latency on the hot path.

:::key Survival is a latency decision Pick zone survival unless a business reason demands region survival, because the second one makes every write wait for another region. :::

Aurora, and sharded SQL as the third option

Not every scalable relational system is distributed SQL. Aurora and sharded SQL are the two alternatives you must be able to place.

Aurora (SIGMOD 2017 paper). Aurora keeps MySQL or PostgreSQL's engine and replaces storage with a shared, replicated volume. Each 10 GB segment is kept as six copies in three Availability Zones, two per zone. A write needs four of six, a read quorum is three of six, so a whole zone can go and writes continue; a zone plus one more node can go and reads continue. The paper reports a 10 GB segment can be repaired in about 10 seconds on a 10 Gbps link.

"The log is the database": the engine sends only redo log records over the network, and storage nodes build pages from them in the background. A transaction commits asynchronously: the commit completes once the volume durable point has advanced past its log position. Normal reads go to a single segment known to be up to date, so no quorum read is needed. There is one writer and up to 15 read replicas sharing the same volume. The paper reports replica lag between about 2.6 and 5.4 ms at 1,000 to 10,000 writes per second in a SysBench write-only test (versus under a second up to minutes for MySQL replication there), and a 5x write throughput claim over MySQL 5.7 on its largest instance, which are AWS's own benchmark numbers.

Because there is one writer, Aurora needs no cross-shard commit and no clock tricks. The classic (provisioned) design does not scale writes beyond one instance and is not a multi-writer, multi-region design. AWS also sells Aurora Global Database (cross-region replicas) and Aurora DSQL, a separate distributed SQL service, which this lesson does not cover. It sits between "big Postgres" and distributed SQL: much faster failover and replicas, same single-writer limit.

Sharded SQL. Vitess (MySQL) and Citus (Postgres extension) keep ordinary database engines as shards and put routing on top.

• Vitess maps a column value to a keyspace ID through a vindex; each shard covers a range of IDs, and the primary vindex is effectively the sharding key. Distributed transactions come in modes: single-shard, best-effort multi-shard, and TwoPC. The docs warn that TwoPC gives atomicity but not full isolation (a cross-shard query can see a partial commit) and that it adds commit latency, and recommend a VSchema that minimises cross-shard updates.
• Citus distributes tables by a distribution column, co-locates related tables (for example by tenant id) so joins stay local, and copies small shared tables to every node as reference tables.

:::key Aurora is the cheap upgrade If the question is "Postgres is too small and failover is too slow", Aurora-style shared storage answers it. If the question is "writes exceed one machine" or "survive a region loss with no data loss", it does not. :::

When to choose it, and what to say in the interview

Choose distributed SQL when:

• You need serializable transactions across keys and writes exceed what one primary can do, and the data does not split cleanly by a single shard key.
• You need to survive the loss of a region without losing committed writes, which async replicas cannot give.
• You can design the schema so most transactions stay in one range and one region.
• The team can run it, or you buy it managed.

It is overkill when:

• One region, moderate load: a primary with replicas is far cheaper, and a commit needs only a local log flush instead of a consensus round.
• Writes concentrate on a hot key range (a counter, a monotonically increasing key). One range has one leader, so a hot range is a hot node.
• The workload is mostly read-heavy and fits replicas and a cache.
• Migration risk outweighs the benefit. Figma's engineering post says they looked at CockroachDB, TiDB, Spanner and Vitess and kept Postgres, mainly because of migration risk, lost in-house expertise and limited runway.

Costs to name: write latency (consensus plus commit wait or uncertainty), retries under contention, wide-area round trips, cross-region traffic, and an unfamiliar operations surface. Say them before the interviewer does.

Common wrong statements.

A five-beat answer:

1. State the workload: write rate, cross-key transactions, regions, loss tolerance.
2. Say the default is one primary with replicas, and what breaks it.
3. If it breaks, name the option: shared storage, sharded SQL, or distributed SQL.
4. State the price: commit latency in numbers, retries, hot ranges.
5. Say what schema choices keep transactions local.

How to say it in the interview

"Distributed SQL gives me serializable transactions across shards and regions, but I pay for it on every write: a consensus round per shard, a two-phase commit when a transaction touches several shards, and clock uncertainty. If one primary with replicas fits the load, I'd stay there. I'd pick Spanner or CockroachDB for multi-region writes that must survive a region loss, and I'd design the schema so most transactions stay in one shard and one region."

Checklist

• Named the three replacements for single-node ACID: consensus, atomic commit and clock ordering
• Said consensus is per range or tablet, not per cluster
• Explained why a cross-range transaction costs more than a single-range one
• Contrasted TrueTime with hybrid logical clocks
• Gave a staleness bound whenever I proposed follower or stale reads
• Priced region survival in write latency before recommending it
• Placed Aurora and sharded SQL correctly against distributed SQL
• Stated when a single primary is enough and distributed SQL is overkill

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB