Distributed SQL Databases: Spanner, CockroachDB and NewSQL

A distributed SQL database (sometimes called NewSQL) offers what a traditional relational database does, with SQL, transactions and strong consistency, while scaling horizontally across many machines and surviving node and zone failures. Google Spanner pioneered the approach, and systems such as CockroachDB, YugabyteDB and TiDB follow similar designs: split data into ranges, replicate each range with Raft or Paxos, and coordinate transactions across ranges with careful use of time.

The architecture

• Range partitioning: tables (and indexes) are stored as sorted key-value data split into ranges of a bounded size. Ranges split as they grow and move between nodes to balance load.
• Consensus per range: each range is replicated, typically three or five ways across zones, and a Raft group agrees on every write, so a range survives the loss of a minority of replicas without losing committed data.
• Leaseholders: one replica holds a lease to serve consistent reads without a round of consensus, which keeps reads fast.
• A stateless SQL layer on every node plans queries and routes them to the right ranges.

Transactions across ranges

A transaction that touches rows in several ranges must commit atomically across Raft groups. Distributed SQL databases use a variant of two-phase commit where the participants are themselves fault-tolerant Raft groups, avoiding the classic blocking problem of 2PC with a single coordinator (see two-phase commit). A transaction record stored in one range acts as the commit point, and writes are provisional "intents" until it commits.

Concurrency control is usually multi-version (MVCC) with serializable or snapshot isolation. Conflicts are detected at commit, and conflicting transactions are retried, so applications must be prepared to retry. See isolation levels.

The role of time

To give external consistency (if transaction A commits before B starts, B sees A), transactions need timestamps that respect real time across machines. Spanner uses TrueTime, an API backed by GPS and atomic clocks that returns a time interval with bounded uncertainty, and waits out that uncertainty ("commit wait") before acknowledging a commit. Systems without specialised hardware use hybrid logical clocks combined with a maximum allowed clock offset, and handle uncertainty by restarting reads that might have missed a write. See clocks and ordering.

Latency and data placement

Strong consistency across regions always costs write latency, because a majority must hear about each write. See geo-replication.

When to choose distributed SQL

• Choose it when you need relational transactions and strong consistency at a scale or availability level a single primary can't provide, such as financial ledgers, inventory, or global user accounts.
• Think twice when one well-sized PostgreSQL or MySQL instance with replicas fits: it's simpler, cheaper and faster per query.
• Choose something else for very high-volume, simple-key workloads that tolerate eventual consistency, where a Dynamo-style store is cheaper.
• Design schemas for distribution: avoid sequential primary keys that create a hot range (use UUIDs or hashed keys), and keep frequently joined data in the same ranges or region.