Technology · Distributed SQL
Spanner and CockroachDB
Globally distributed SQL databases providing serializable ACID transactions across multiple regions. Raft-replicated ranges, TrueTime vs hybrid logical clocks, and external consistency.
12 min read
Google Cloud Spanner and CockroachDB belong to the NewSQL (Distributed SQL) family. They combine the relational schema, SQL expressive power, and serializable ACID transactions of traditional relational databases with the horizontal scalability and multi-region survivability of NoSQL systems.
Unlike traditional primary-replica relational databases that suffer from replication lag or single-primary write bottlenecks, distributed SQL divides tables into ordered chunks of key-value pairs called ranges, replicates each range via consensus quorums, and coordinates distributed transactions without sacrificing consistency.
When to reach for them
- Global multi-region ACID consistency: Financial ledgers, multi-region payment systems, or identity registries requiring strict serializability across continents.
- Horizontal scale without sharding logic in application code: Workloads outgrowing a single large PostgreSQL instance where manual application-level sharding would be too complex or fragile.
- High availability through regional outages: Surviving the loss of an entire cloud data center or region with zero data loss (RPO = 0) and automatic sub-minute failover (RTO < 30s).
- Relational querying with global unique constraints: Needing secondary indexes, joins, and foreign keys across geographically sharded rows.
If your system fits comfortably on a single primary PostgreSQL instance (up to tens of thousands of writes/s), standard PostgreSQL is far simpler, cheaper, and faster for local queries.
Architecture: ranges and consensus groups
In both Spanner and CockroachDB, all relational tables, secondary indexes, and system catalogs are mapped into a single monolithic, sorted key-value keyspace.
- Continuous ranges: The keyspace is divided into continuous chunks called ranges in CockroachDB (default 64 MB) or splits in Spanner (default 4 GB).
- Independent consensus per range: Each range forms its own independent consensus group (Paxos in Spanner, Multi-Raft in CockroachDB) spread across separate availability zones or regions.
- Leaseholders and leaders: One replica in the consensus group acts as the leaseholder/leader, serving all strong reads and coordinating writes for that range without contacting peer replicas on local reads.
- Range splits: When a range grows beyond its size threshold, it automatically splits in two, updating the cluster routing range descriptor table.
Ordering time: TrueTime vs Hybrid Logical Clocks (HLC)
Serializable transactions across independent ranges require total ordering of events. In distributed systems with independent physical clocks that can disagree by milliseconds, and occasionally by much more, ordering distributed transactions without central lock bottlenecks is the defining challenge.
Google Spanner (TrueTime API)
- Hardware-backed atomic and GPS synchronized clocks
- Clock uncertainty bounded to under 7 ms
- True external consistency (linearizability) across global regions
- Requires specialized proprietary Google data center hardware
- Closed-source proprietary cloud service
Pick it when: Global workloads hosted natively on Google Cloud Platform.
CockroachDB (Hybrid Logical Clocks)
- Runs on standard commodity cloud VMs (AWS, GCP, bare metal)
- Combines physical NTP clock with Lamport logical counters
- Open-core software
- Cannot establish absolute physical time ordering across unrelated nodes
- Requires wait-and-retry restart cycles if clock skew exceeds threshold (default 500 ms)
Pick it when: Cloud-agnostic, on-premise, or multi-cloud deployments.
How TrueTime guarantees external consistency
Spanner's TrueTime API represents the current time as an interval $[t.earliest, t.latest]$ where the true time is guaranteed to lie within uncertainty $\epsilon$ (typically $\le 7$ ms). When committing transaction $T_1$, Spanner assigns it a commit timestamp $s = t.latest$, and intentionally waits out the clock uncertainty (holding locks for $2\epsilon$) before releasing locks. This guarantees that any subsequent transaction $T_2$ started after $T_1$ completes will receive a timestamp strictly greater than $s$, achieving external consistency (strict serializability) without central coordination.
Distributed transactions: 2PC across consensus groups
When a single transaction mutates keys in different ranges (for example, deducting funds from an account in Range 1 and crediting an account in Range 3), consensus within a single range is insufficient:
- The client's gateway node becomes the transaction coordinator.
- Two-Phase Locking (2PL): Coordinator acquires read and write locks across all involved range leaseholders.
- Two-Phase Commit (2PC) over Paxos/Raft:
- Prepare Phase: The coordinator asks each participating range's consensus group to vote. Each range commits a prepared intent record in its consensus log.
- Commit Phase: Once all participants acknowledge the prepare, the coordinator commits the transaction record to its own consensus group and notifies participants to resolve intents.
- Range leaseholder optimizations: Because 2PC is executed across range leaders (which are each backed by consensus), a leader crash does not stall the commit; the range elects a new leader that reads the consensus state.
Multi-region latency costs
Consensus requires acknowledging writes across a majority quorum. You cannot violate the speed of light:
- Single-region quorum writeacross local availability zones
- 5–15 ms
- Cross-continent quorum writespeed-of-light transatlantic/transpacific network roundtrip
- 50–150 ms
- Local leaseholder readserved directly from range leaseholder memory
- < 2 ms
- Stale follower readbounded staleness read from local replica without quorum
- < 1 ms
- Range size
- 512 MiB default in CockroachDB; Spanner splits by size and load
Where they fit vs Postgres and DynamoDB
Spanner / CockroachDB
- Globally distributed serializable ACID transactions
- Automatic sharding and range rebalancing
- Zero RPO multi-region disaster recovery
- Higher write latency (cross-region quorum roundtrips)
- Expensive operational/cloud costs
- Complex query optimization
Pick it when: Global financial ledgers, mission-critical multi-region enterprise databases.
PostgreSQL
- Sub-millisecond local latency
- Unmatched SQL feature richness and extension ecosystem
- Low hardware cost
- Single-primary write bottleneck
- Manual sharding
- Replication lag on read replicas
Pick it when: 95% of standard applications operating within one or two cloud regions.
DynamoDB
- Predictable single-digit ms latency at any scale
- Serverless zero-ops pricing
- Simple key-value partition scaling
- No joins or complex multi-table relational queries
- Eventual consistency default
- 400 KB item limit
Pick it when: High-volume key-value or document lookups with known access patterns.
Where it bites
- Sequential primary key hotspotting: If a table uses an auto-incrementing integer or sequential timestamp as its primary key, all consecutive inserts fall into the exact same range (the highest range). A single range leader handles 100% of the write load. Always use UUIDv4, hashed keys, or hash-sharded indexes to distribute inserts across all ranges.
- Cross-region distributed transaction amplification: A multi-statement transaction updating five rows located on ranges in five different global regions requires multiple sequential cross-region network roundtrips, causing P99 transaction latencies to exceed 500 ms.
- Clock desynchronization crashes: In CockroachDB, if NTP drifts beyond the maximum clock offset limit (e.g. 500 ms), the node voluntarily terminates itself to protect data integrity against stale reads.
Try it
Multi-region Failover HLD
Design a multi-region active-active database failover topology with zero data loss.