What distributed SQL means

Distributed SQL claims you can have a relational database with transactions and joins, spread across many machines, without sharding it yourself. That claim deserves a careful look, because half of it is true and the other half has a price.

The three things it promises

It survives machines dying. Data is replicated to several nodes, and losing one doesn't lose the data or stop writes.

It grows by adding nodes. You don't choose a shard key or rewrite queries, which is the work the senior course "Database sharding: horizontal" spent seven lessons on.

It still speaks SQL, with transactions across the whole dataset. That's what separates distributed SQL from the previous generation of distributed stores, which asked you to give up joins and multi-row transactions in exchange for scale.

How it's built

The table is cut into contiguous ranges of the primary key, each up to about 512 MB in CockroachDB. Each range is replicated to three nodes by default, and the three replicas run a consensus protocol, Raft, to agree on every write.

A write is acknowledged when a majority of the replicas have it. Two of three is a majority, so one node can be slow or dead without stopping writes.

flowchart TB
  T["a table"] --> R1["range: keys aaa to fff"]
  T --> R2["range: keys ggg to mmm"]
  T --> R3["range: keys nnn to zzz"]
  R1 --> N1["replica on node 1"]
  R1 --> N2["replica on node 2"]
  R1 --> N3["replica on node 3"]
  N1 --> C["a write needs 2 of the 3 to agree"]
  N2 --> C

Notice what that consensus costs. A write isn't done when one machine has it. It's done when a majority have it, which means at least one network round trip to another node, every time.

What it costs

Single-row writes are slower than in Postgres, sometimes several times slower, because of that consensus round trip.

Transactions that touch rows on different ranges are much slower, because the coordination now spans several groups of nodes.

The minimum sensible deployment is three nodes, so the cheapest useful CockroachDB cluster costs more than this application's entire server.

The honest framing

Distributed SQL trades single-node write latency for survival and horizontal growth.

If you need neither, you pay the tax and get nothing back. That's the situation this application is in, and it's why this course will end by recommending against it.

This isn't a criticism of the technology. It's the reasoning used throughout this series: judge the tool against the problem you have.

Key takeaways

• Distributed SQL offers survival, horizontal growth and real SQL transactions at the same time.
• Tables are cut into key ranges, and each range is replicated to three nodes that agree through Raft.
• A majority acknowledges each write, so every write includes a network round trip.
• Single-row writes are slower than in Postgres, and cross-range transactions are much slower.
• You pay the tax whether or not you need the survival and the growth.