NewSQL and Distributed SQL

How Spanner, CockroachDB and TiDB keep SQL and ACID transactions while spreading data over many machines: ranges, consensus, distributed commits and the clock problem.

Overview

A classic SQL database such as PostgreSQL or MySQL keeps all writes on one primary machine. That gives full SQL and ACID transactions, but writes and storage are capped by what one machine can do, and losing that machine means a failover. NoSQL stores removed the cap by spreading data over many machines, but most gave up joins, multi-row transactions or strong reads to do it.

NewSQL, now usually called distributed SQL, tries to keep both. The data is split into many small key ranges; each range is copied to several machines with a consensus protocol (Paxos or Raft); and a transaction layer on top gives SQL with ACID transactions across ranges. Google's Spanner started the category; CockroachDB and TiDB are widely used open-source designs built on the same ideas.

The hard part is time. To give every transaction a place in one global order, the machines must agree on timestamps even though their clocks drift. Each system answers that differently, and the answer explains its latency and its guarantees.

What you will be able to do

  • Draw the common architecture: SQL layer, transaction layer, ranges replicated by consensus, and a local storage engine.
  • Explain how a transaction that touches several ranges commits, and what it costs.
  • Compare TrueTime commit wait, hybrid logical clocks and a timestamp oracle.
  • Explain follower reads and multi-region table placement, and what each trades away.
  • Decide between PostgreSQL, a NoSQL store and distributed SQL for a given workload.

Key concepts

  • Distributed SQL
  • Ranges and Raft groups
  • Leaseholders
  • Two-phase commit over consensus
  • TrueTime and commit wait
  • Hybrid logical clocks
  • Timestamp oracle
  • Follower reads
  • Multi-region tables

Interview questions

  • How does Spanner give globally consistent transactions?
  • How does CockroachDB stay consistent without atomic clocks?
  • Walk through a write in a distributed SQL database.
  • Compare Spanner, CockroachDB and TiDB.
  • When would you choose distributed SQL, and when not?
  • What is a follower read, and when is it safe?

Sign up free to read the full topic

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio