NewSQL / Global SQL (Spanner, CockroachDB)

1. What Is It?

NewSQL databases are a class of relational database systems designed to provide the horizontal scalability of NoSQL while preserving the full ACID guarantees and SQL interface of traditional relational databases. They solve a problem that neither classical SQL nor NoSQL handles well: globally distributed, strongly consistent, SQL-queryable data at internet scale.

Without NewSQL, engineers face an impossible tradeoff: use PostgreSQL/MySQL for ACID correctness but hit scalability walls at ~10–50K writes/sec on a single node, or use Cassandra/DynamoDB for horizontal scale but sacrifice strong consistency and lose SQL expressiveness. NewSQL systems (Spanner, CockroachDB) collapse this tradeoff — you get distributed writes that scale linearly across nodes and regions, serializable transactions, and a familiar SQL interface. The cost is higher write latency (due to cross-replica consensus) and significantly higher operational/financial complexity.

2. How It Works

Core Mechanism

NewSQL systems achieve distributed ACID by combining three key primitives:

1. Sharding (Range-based): Data is divided into ranges (called "splits" in Spanner, "ranges" in CockroachDB). Each range is ~64–256 MB and is independently replicated.
2. Consensus Replication (Raft/Paxos): Each range has its own consensus group — a set of replicas (typically 3 or 5) that use the Raft algorithm to agree on every write before it's applied. Raft ensures all replicas apply writes in the same order by electing a leader that coordinates replication. A write must be acknowledged by a quorum (a majority of replicas — e.g. 2 of 3) before it's considered committed. This is the source of durability and partition tolerance.
3. Global Timestamp Ordering: For cross-shard transactions to be serializable, every transaction needs a globally meaningful timestamp.
   • Spanner: Uses TrueTime — GPS receivers and atomic clocks in every Google datacenter give a bounded time uncertainty of 1–7ms. Spanner issues a commit timestamp and then performs a "commit wait" (pausing until the TrueTime uncertainty window has passed), guaranteeing that no future transaction can have an earlier timestamp. This achieves external consistency — equivalent to strict serializability, the multi-operation transactional generalization of linearizability — meaning every transaction appears to take effect at a single instant in time, and all observers agree on the ordering of operations.
   • CockroachDB: Uses a hybrid logical clock (HLC) — combines physical wall time with a logical counter. When clocks drift, CockroachDB uses transaction restarts to resolve conflicts. Achieves serializable isolation without atomic clocks.

Write Path (CockroachDB Example)

1. Client sends SQL INSERT to any node (gateway node)
2. Gateway node determines which range owns the row (via shard key)
3. Gateway routes write to that range's Raft leader
4. Raft leader writes to its WAL, sends AppendEntries to followers
5. Quorum (2 of 3) acknowledges → leader commits
6. Leader sends commit timestamp back through gateway to client
7. Any conflicting concurrent transactions are resolved via MVCC

Multi-Shard Transaction (Parallel Commits):
1. Write intents are placed on all shards simultaneously
2. Transaction record is written to mark the transaction as "staging"
3. Once all intents are placed, transaction is committed atomically
4. Old approach required 2 round trips; Parallel Commits does it in 1

Mermaid: Architecture Diagram

-+ResetFullscreen

Comparison: How Spanner vs. CockroachDB Achieve Consistency

-+ResetFullscreen

3. Variants & Comparisons

NewSQL Systems

Consistency Strength Hierarchy

Linearizability (strongest)
  └─ Spanner (external consistency via TrueTime)

Serializability
  └─ CockroachDB (SSI via HLC)
  └─ PostgreSQL (single node)

Snapshot Isolation
  └─ TiDB, MySQL (InnoDB default)

Read Committed
  └─ MySQL default, many OLTP systems

Eventual Consistency (weakest)
  └─ DynamoDB, Cassandra, MongoDB (replica reads)

4. When to Use It (and When NOT To)

Use NewSQL When:

• Global ACID transactions are non-negotiable: Financial systems where a debit in region A and a credit in region B must be atomic. NewSQL is the only class of database that handles this correctly across regions without application-level saga patterns.
• You've outgrown single-node SQL: Your PostgreSQL write throughput is saturated (~10–50K writes/sec sustained) and your dataset can't fit in one machine. NewSQL scales writes horizontally while keeping SQL.
• Multi-region active-active with strong consistency: Active-active where any region can accept writes and all regions read consistent data. Cassandra/DynamoDB are AP (eventual consistency); NewSQL is CP but with multi-region active writes.
• Regulatory data residency + global queries: Spanner lets you pin certain data to specific regions (for compliance) while still running global queries with ACID.

Do NOT Use NewSQL When:

• Write latency < 5ms is required: NewSQL requires cross-replica Raft consensus on every write. Single-region: 1–5ms. Multi-region: 50–200ms. Redis (0.1ms) or single-node PostgreSQL (~0.5ms) are orders of magnitude faster for write latency.
• Read-heavy workloads with no strong consistency need: If 95% of your traffic is reads and eventual consistency is acceptable, Cassandra or DynamoDB at a fraction of the cost.
• Simple CRUD app at moderate scale: PostgreSQL + read replicas handles 99% of apps. NewSQL is expensive (Spanner charges per node-hour + I/O; CockroachDB requires 3+ nodes per region) and adds operational complexity.
• Budget-constrained startups: Spanner is one of the most expensive managed databases. CockroachDB's enterprise licensing adds cost. Start with PostgreSQL and migrate if you hit walls.
• Analytical queries (OLAP): NewSQL is optimized for OLTP (short transactions, row-level). For analytical queries (full-table scans, aggregations), use BigQuery, Snowflake, or a columnar store.

Decision Triggers

5. Real-World Usage

Google F1 → Spanner (AdWords)

Google replaced MySQL for its AdWords advertising database with F1, a distributed SQL layer built on top of Spanner. MySQL had grown beyond 100 TB serving 100+ applications; manual sharding was operationally untenable and cross-shard transactions were impossible. F1 on Spanner gave Google ACID transactions across datacenters, enabling correct billing and budget enforcement globally. The F1 paper (VLDB 2013) became one of the seminal database systems papers.

Netflix → CockroachDB (380+ Clusters)

Netflix runs 380+ CockroachDB clusters for various microservices, making it one of the largest documented CockroachDB deployments. The key driver: Netflix operates globally and needs services that survive regional failures while maintaining consistency. CockroachDB's Raft-based replication means a cluster can survive the loss of one region (with 3+ regions) and resume writes without manual failover — a critical property for Netflix's 24/7 reliability requirements.

6. Interview Cheat Sheet

5 Sentences to Show Deep Understanding

1. "NewSQL solves the distributed ACID problem by combining range-based sharding with Raft consensus per shard and a global timestamp ordering mechanism — Spanner uses TrueTime (GPS/atomic clocks with commit wait), CockroachDB uses Hybrid Logical Clocks with transaction restarts on clock skew."

2. "The fundamental cost of NewSQL is write latency: every write requires a Raft quorum round-trip — 1–5ms single-region, 50–200ms multi-region — so if your application requires sub-millisecond writes, NewSQL is the wrong tool."

3. "Spanner achieves linearizability (the strictest consistency model) through TrueTime's commit wait — it literally waits until the uncertainty window for the commit timestamp has passed before declaring the transaction committed, ensuring no future transaction can observe it 'in the past'."

4. "CockroachDB is PostgreSQL wire-protocol compatible, meaning you can point most PostgreSQL drivers at a CockroachDB cluster and run SQL queries — but the underlying engine is completely different: Raft consensus, MVCC, and distributed range management instead of a single WAL."

5. "The key question when evaluating NewSQL vs. NoSQL is: do you need cross-entity ACID transactions? If yes, NewSQL. If eventual consistency is acceptable and you can design around limited access patterns, DynamoDB/Cassandra is cheaper and faster."

Common Follow-Up Questions

Q: Spanner is described as CA in the CAP theorem sense — is that accurate? A: Google itself argues Spanner is "effectively CA" in practice because TrueTime's uncertainty interval (1–7ms) is small enough that the commit wait overhead is negligible. In theory, during a true network partition, Spanner would become unavailable rather than return inconsistent data — making it CP. The "effectively CA" claim is a practical statement about Google's datacenter connectivity, not a theoretical one.

Q: What's the difference between serializable and linearizable? A: Serializable is a transaction isolation level: a group of transactions appears to execute in some serial order, but that order need not match real-world time. Linearizable additionally requires that the serial order matches real-world wall-clock time — if transaction A commits before transaction B starts (in real time), A must appear before B in the serialization order. Spanner provides linearizability; CockroachDB provides serializability (strictly weaker, but sufficient for most correctness requirements).

Q: How does CockroachDB handle clock skew without atomic clocks? A: Via Hybrid Logical Clocks (HLC): each node tracks a max observed timestamp across all messages it sees. If a transaction's timestamp conflicts with another due to clock skew, CockroachDB restarts the transaction with a higher timestamp. This adds latency on conflict but maintains correctness. The maximum clock skew tolerance is configurable (default 500ms).

Q: When would you pick CockroachDB over Spanner? A: (1) You're not on Google Cloud; (2) you need PostgreSQL wire protocol compatibility; (3) you want self-hosted option; (4) cost sensitivity (Spanner is significantly more expensive). Spanner wins on maximum consistency guarantees (external consistency vs. serializable) and operational simplicity as a managed service.

Connections to Other Building Blocks

• Consensus (Raft/Paxos): NewSQL is built on top of Raft. Understanding Raft's leader election, log replication, and quorum writes is prerequisite to understanding NewSQL write paths.
• Sharding & Partitioning: NewSQL auto-shards by range and rebalances automatically. The same hot partition problems apply — a single high-traffic key range will be a bottleneck.
• Write-Ahead Log (WAL): Each Raft replica maintains a WAL. The WAL is the source of truth for replaying transactions after a crash.
• SQL Databases: NewSQL is what you reach for when you've exhausted single-node SQL scaling options but need ACID guarantees NoSQL can't provide.
• Replication Strategies: NewSQL uses synchronous multi-replica replication (not async leader-follower like PostgreSQL replication). This is why writes are slower but reads are always consistent.