SQL 118 🛢️ Distributed SQL Engines (CockroachDB, YugabyteDB, Spanner)
Distributed SQL engines exist to answer a question that traditional relational databases answer poorly: how do you run ACID transactions with strong consistency across multiple machines, multiple data centers, or multiple continents without sacrificing the relational model? The three systems most commonly discussed in this category—Google Spanner, CockroachDB, and YugabyteDB—share the same architectural lineage. Each partitions data into ranges or tablets, replicates each partition using a consensus protocol (Paxos in Spanner, Raft in the other two), and layers a SQL engine on top. Where they diverge is in how they handle time, and that divergence determines their latency characteristics, their deployment flexibility, and their cost.
This chapter covers the architectural foundations of all three, the specific mechanism Spanner uses to achieve external consistency, and the trade-offs that CockroachDB and YugabyteDB make to deliver similar guarantees without Google’s hardware.
Key point: Spanner’s external consistency comes from TrueTime, a clock with a bounded uncertainty interval backed by GPS and atomic clocks. CockroachDB and YugabyteDB replace TrueTime with hybrid-logical clocks and uncertainty intervals, which preserves correctness but introduces a different latency profile and a reliance on bounded clock skew from NTP.
Why distributed SQL engines exist
The single-node ceiling problem. A traditional PostgreSQL or MySQL deployment runs on one primary. Read replicas scale reads, but writes still go through a single node, and that node’s capacity is finite. Sharding at the application layer solves the capacity problem by splitting data across independent databases, but it pushes the complexity of cross-shard joins, distributed transactions, and rebalancing onto the application team . Distributed SQL engines exist to provide horizontal write scalability without requiring the application to manage sharding.
The geo-distribution problem. An application with users in North America, Europe, and Asia needs low-latency access to data from all three regions. A single primary in one region means writes from the other two regions cross an ocean. Multi-region read replicas help reads but introduce replication lag, which means reads can return stale data. Distributed SQL engines replicate data across regions and route reads and writes to the nearest replica while maintaining a consistent global view . The consistency guarantee is what distinguishes them from NoSQL systems that accept eventual consistency.
The consistency cost problem. Strong consistency across regions is expensive. The CAP theorem says that during a network partition, a system must choose between consistency and availability. Distributed SQL engines choose consistency. When a partition separates a quorum of replicas from the rest, the isolated minority stops accepting writes rather than diverging . This is the correct choice for financial ledgers, inventory systems, and identity management, where stale or conflicting data is worse than temporary unavailability. The trade-off is that the architecture is not appropriate for every workload; applications that tolerate eventual consistency can use cheaper, more available systems.
a. The Shared Architecture: Sharding, Raft, and SQL
All three engines follow the same fundamental design. Data is split into partitions—called ranges in CockroachDB, tablets in YugabyteDB, and splits in Spanner. Each partition is replicated across multiple nodes, typically three or five, and each replica set runs a consensus protocol to agree on the order of writes . A leader is elected for each partition, and all writes for that partition go through the leader. Followers replicate the leader’s log and can serve reads under certain conditions.
CockroachDB uses Raft for replication and a leaseholder mechanism to serve consistent reads. The leaseholder is typically co-located with the Raft leader, which reduces the latency of proposing changes because the node that coordinates reads is also the node that coordinates writes . The SQL layer is a gateway that parses queries, determines which ranges are involved, and constructs a distributed plan that runs operations near the data where possible .
YugabyteDB splits into two layers. The query layer handles YSQL (PostgreSQL-compatible) and YCQL (Cassandra-compatible) requests and routes them to the correct tablets. The storage layer, built on DocDB (a document store on top of RocksDB), manages replication and consistency. Each tablet has its own Raft group with a leader and followers . The master servers act as catalog managers and cluster orchestrators, storing the shard-to-node mapping.
Spanner uses Paxos rather than Raft for replication, but the structure is the same: a leader per split, a replicated log, and a majority quorum required for writes. Spanner’s SQL engine supports standard relational features, including joins and secondary indexes, and the system is designed to run across Google’s global infrastructure .
b. Spanner and TrueTime: The Hardware Advantage
Spanner’s defining feature is external consistency, which it achieves through TrueTime. TrueTime is a distributed clock API that returns a time interval [earliest, latest] rather than a single timestamp. The interval width, called epsilon (ε), is bounded by the hardware: Google’s data centers are equipped with GPS receivers and atomic clocks that keep the uncertainty small, typically in the single-digit milliseconds .
The mechanism that uses TrueTime is called commit-wait. When a transaction is ready to commit, it is assigned a timestamp s equal to the latest possible time at which it could have occurred (TT.now().latest). Before releasing locks and acknowledging the commit, Spanner waits until TT.after(s) returns true—meaning the timestamp s is definitely in the past according to every server’s clock. This wait is typically a few milliseconds, equal to the epsilon .
The guarantee this provides is external consistency: if transaction T1 commits before transaction T2 starts, then T2 will observe T1’s effects. In a banking ledger, this means a withdrawal from one account cannot observe a stale balance that predates a deposit from another account, regardless of which continents the two transactions originate from . The cost is the commit-wait latency, which is proportional to epsilon. A smaller epsilon means a shorter wait, which is why Google invests in the atomic-clock fleet. Without that hardware, the same software cannot achieve the same bound .
c. CockroachDB and YugabyteDB: Software-Defined Time
CockroachDB and YugabyteDB cannot rely on GPS and atomic clocks. They run on commodity hardware in any cloud or on-premises environment, and their clocks are synchronized by NTP, which provides no provable bound on skew. Both systems use hybrid-logical clocks (HLCs) instead of TrueTime. An HLC combines a physical clock reading with a logical counter, producing a timestamp that tracks causal order and stays close to wall-clock time, but has no guaranteed uncertainty bound .
The absence of a bound is handled through uncertainty intervals and read restarts. When a transaction reads a value whose timestamp falls within the uncertainty window—typically the maximum clock offset configured for the cluster, 500ms by default in CockroachDB—it cannot be certain whether that value is committed or whether a concurrent transaction with an earlier timestamp might still commit. The read is restarted at a higher timestamp to eliminate the ambiguity . This preserves serializability, which CockroachDB provides by default, but it can add tail latency under clock skew.
The trade-off relative to Spanner is explicit. Spanner’s commit-wait is a fixed cost paid on every write, bounded by hardware. CockroachDB’s read restarts are a variable cost paid only when clock uncertainty affects a read, but the uncertainty window is wider (500ms versus single-digit milliseconds) and depends on NTP behaving correctly. CockroachDB provides single-key linearizability and serializable transactions, but it is explicit that it does not provide the full external consistency guarantee that Spanner’s TrueTime enables without those restarts .
YugabyteDB follows a similar model, using HLCs and Raft per tablet. Its distinguishing feature is the YSQL layer, which reuses a significant portion of PostgreSQL’s query layer code, making it more compatible with existing PostgreSQL applications than CockroachDB’s implementation . Both systems are open source and run anywhere, which is their primary advantage over Spanner’s managed, Google-only deployment.
Complete Example Session
-- ============================================
-- PART 1: RANGE/TABLET SHARDING
-- ============================================
-- All three engines split data into partitions
-- Each partition is replicated independently
-- CockroachDB: ranges (default 512 MiB)
-- YugabyteDB: tablets
-- Spanner: splits
-- ============================================
-- PART 2: RAFT/PaxOS PER PARTITION
-- ============================================
-- CockroachDB and YugabyteDB: Raft
-- Leader per range/tablet
-- Majority quorum required for writes
-- Followers replicate the log
-- Spanner: Paxos
-- Same structure, different protocol
-- ============================================
-- PART 3: COCKROACHDB LEASEHOLDER
-- ============================================
-- The leaseholder serves consistent reads
-- It is typically co-located with the Raft leader
-- This avoids a network hop for reads that
-- need the latest committed value
-- ============================================
-- PART 4: SPANNER TRUETIME COMMIT-WAIT
-- ============================================
-- Transaction picks timestamp s = TT.now().latest
-- Replicates via Paxos to majority
-- Waits until TT.after(s) returns true
-- Then releases locks and acknowledges commit
-- Wait duration is bounded by epsilon
-- ============================================
-- PART 5: COCKROACHDB UNCERTAINTY INTERVAL
-- ============================================
-- HLC timestamp has no provable bound
-- Read encounters value inside uncertainty window
-- (default max_offset = 500ms)
-- Read restarts at higher timestamp
-- Preserves serializability, adds tail latency
-- ============================================
-- PART 6: YUGABYTE YSQL LAYER
-- ============================================
-- YSQL reuses PostgreSQL query layer code
-- Higher PostgreSQL compatibility than CockroachDB
-- YCQL provides Cassandra-compatible API
-- Both route to the same DocDB storage layer
-- ============================================
-- PART 7: ISOLATION LEVELS
-- ============================================
-- CockroachDB: SERIALIZABLE by default
-- Spanner: SERIALIZABLE (external consistency)
-- YugabyteDB: SERIALIZABLE or SNAPSHOT
-- All three default to strong isolation
-- ============================================
-- PART 8: DEPLOYMENT MODEL
-- ============================================
-- Spanner: Google Cloud managed only
-- CockroachDB: self-hosted, any cloud, on-prem
-- YugabyteDB: self-hosted, any cloud, on-prem
-- ============================================
-- PART 9: LATENCY PROFILE
-- ============================================
-- Spanner: commit-wait ~2*epsilon (few ms)
-- CockroachDB: read restarts under clock skew
-- (tail latency, not fixed cost)
-- Both pay consensus round-trip for writes
-- ============================================
-- PART 10: SELECTION CRITERIA
-- ============================================
-- Need external consistency + managed service
-- → Spanner
-- Need open source + portability + serializable
-- → CockroachDB or YugabyteDB
-- Need PostgreSQL compatibility
-- → YugabyteDB (YSQL) or CockroachDB (wire protocol)The ten parts trace the shared architecture through the specific mechanisms that distinguish the three engines. Parts 4 and 5 show the central divergence: Spanner’s hardware-bounded commit-wait versus CockroachDB’s software-defined read restarts.
Quick Reference
Architecture Comparison
| Feature | Spanner | CockroachDB | YugabyteDB |
|---|---|---|---|
| Consensus | Paxos | Raft | Raft |
| Time source | TrueTime (GPS + atomic) | HLC + NTP | HLC + NTP |
| Uncertainty bound | Provable, ~ms | Configured, 500ms default | Configured |
| Consistency guarantee | External consistency | Serializable, single-key linearizable | Serializable |
| Deployment | Google Cloud managed | Self-hosted, any cloud | Self-hosted, any cloud |
| SQL compatibility | Standard SQL | PostgreSQL wire protocol | YSQL (PostgreSQL), YCQL |
Time Mechanism Comparison
| Mechanism | Spanner | CockroachDB |
|---|---|---|
| Clock type | TrueTime interval | Hybrid-logical clock |
| Bound | Hardware-proven | NTP-dependent |
| Cost | Commit-wait (~2ε) | Read restarts under skew |
| Latency profile | Fixed per write | Variable, tail-heavy |
When to Choose
| Requirement | Recommendation |
|---|---|
| External consistency, managed | Spanner |
| Open source, portable | CockroachDB or YugabyteDB |
| PostgreSQL compatibility | YugabyteDB (YSQL) |
| PostgreSQL wire protocol | CockroachDB |
| Avoid vendor lock-in | CockroachDB or YugabyteDB |
Best Practices
✅ Do This:
-- Use SERIALIZABLE isolation (the default in CockroachDB)
-- Do not weaken it without understanding the consequences -- ✅-- Design primary keys to avoid hotspots
-- Use UUIDs or hash-sharded indexes -- ✅-- Co-locate related data where the engine supports it
-- (CockroachDB: interleaved tables or regional tables) -- ✅-- Monitor clock skew in CockroachDB/YugabyteDB
-- NTP failures can cause read restarts and latency spikes -- ✅❌ Don’t Do This:
-- Assume CockroachDB matches Spanner's external consistency
-- without read restarts -- ❌-- Use a monotonically increasing integer primary key
-- without hash sharding (creates a hotspot on one range) -- ❌-- Deploy multi-region without understanding the consensus
-- round-trip cost for writes -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Write latency spikes | Clock skew exceeds configured bound | Monitor NTP; increase max_offset with caution |
| Hotspot on one range | Sequential primary key | Use UUID or hash-sharded index |
| Unexpected read restarts | HLC uncertainty window | Check clock synchronization |
| Cross-region write latency | Consensus round-trip | Co-locate data with writers |
| Compatibility gaps | YSQL not 100% PostgreSQL | Test queries before migration |
Real-World Examples
1. CockroachDB Default Isolation
-- SERIALIZABLE is the default; no clause needed
BEGIN; SELECT * FROM accounts WHERE id = 1; COMMIT;2. Spanner External Consistency
-- Every transaction gets a TrueTime timestamp
-- Commit-wait guarantees ordering3. YugabyteDB YSQL Connection
-- Connect via PostgreSQL wire protocol
-- YSQL reuses PostgreSQL query layer4. Hash-Sharded Index in CockroachDB
CREATE INDEX ON orders (customer_id) USING HASH;5. Raft Leader per Range
-- SHOW RANGES FROM TABLE orders; -- CockroachDB
-- Shows range boundaries and leaseholders6. YugabyteDB Tablet Leader
# yb-admin list_tablets7. Spanner Commit Timestamp
-- Every transaction has a commit timestamp
-- Read at a timestamp for snapshot consistency8. CockroachDB Uncertainty Check
-- SET CLUSTER SETTING kv.closed_timestamp.target_duration = '3s';9. Regional Tables in CockroachDB
CREATE TABLE users (...) LOCALITY REGIONAL BY ROW;10. Multi-Region Deployment
Spanner: Google-managed global replication
CockroachDB: self-managed multi-region
YugabyteDB: self-managed multi-regionVisual
Shared Architecture
┌──────────────────────────────────────────────────────────────┐
│ SQL LAYER │
│ Parses queries, plans distributed execution │
│ │
│ ┌─────────────────┐ │
│ │ Range/Tablet 1 │ Leader + Followers (Raft/Paxos) │
│ │ Range/Tablet 2 │ Leader + Followers │
│ │ Range/Tablet 3 │ Leader + Followers │
│ └─────────────────┘ │
│ │
│ Each partition replicated independently. │
│ Majority quorum required for writes. │
│ Leaders serve consistent reads (or leaseholders). │
│ │
└──────────────────────────────────────────────────────────────┘Spanner TrueTime Commit-Wait
┌──────────────────────────────────────────────────────────────┐
│ T1 COMMIT │
│ │
│ ┌─────────────────┐ │
│ │ Pick timestamp │ │
│ │ s = TT.now() │ │
│ │ .latest │ │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Replicate via │ │
│ │ Paxos to │ │
│ │ majority │ │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Wait until │ Commit-wait │
│ │ TT.after(s) │ Duration ≈ 2ε │
│ │ is true │ (few milliseconds) │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Release locks │ │
│ │ Acknowledge │ │
│ └─────────────────┘ │
│ │
│ s is provably in the past before locks release. │
│ T2 starting after sees T1's effects. │
│ External consistency. │
│ │
└──────────────────────────────────────────────────────────────┘CockroachDB Uncertainty Interval
┌──────────────────────────────────────────────────────────────┐
│ READ AT TIMESTAMP t │
│ │
│ ┌─────────────────┐ │
│ │ Value found at │────► Outside uncertainty? │
│ │ timestamp t' │ │
│ └────────┬────────┘ │
│ │ │
│ ┌─────┴─────┐ │
│ ▼ ▼ │
│ ┌──────┐ ┌──────┐ │
│ │ YES │ │ NO │ t' is within [t, t + max_offset] │
│ │ │ │ │ │
│ │Return│ │Restart│ Read again at higher timestamp │
│ │value │ │read │ │
│ └──────┘ └──────┘ │
│ │
│ Preserves serializability. │
│ Tail latency under clock skew. │
│ No hardware bound. │
│ │
└──────────────────────────────────────────────────────────────┘Deployment Model
┌──────────────────────────────────────────────────────────────┐
│ SPANNER COCKROACHDB / YUGABYTE │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Google Cloud │ │ Self-hosted │ │
│ │ managed only │ │ any cloud │ │
│ │ │ │ on-prem │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ GPS + atomic │ │ NTP-synced │ │
│ │ clocks │ │ commodity │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Hardware bound │ │ Software bound │ │
│ │ on uncertainty │ │ (configurable) │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ External consistency Serializable, with │
│ without read restarts read restarts under skew │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Shared architecture | Partitioned data, consensus per partition, SQL layer |
| Spanner consensus | Paxos |
| CockroachDB/YugabyteDB consensus | Raft |
| Spanner time | TrueTime (GPS + atomic clocks, bounded ε) |
| CockroachDB/YugabyteDB time | HLC + NTP (no provable bound) |
| Spanner consistency | External consistency |
| CockroachDB consistency | Serializable, single-key linearizable |
| Spanner latency cost | Commit-wait (~2ε, fixed) |
| CockroachDB latency cost | Read restarts under skew (variable) |
| Deployment | Spanner managed; CockroachDB/YugabyteDB self-hosted |
Key takeaways:
- The architecture is shared; the time mechanism is the differentiator. All three partition data, replicate with consensus, and run SQL on top. Spanner’s TrueTime gives it a provable bound on clock uncertainty; CockroachDB and YugabyteDB use HLCs and rely on NTP, with no provable bound .
- Spanner’s commit-wait is a hardware-dependent guarantee. The wait duration equals the epsilon of the TrueTime interval, which is small because of GPS and atomic clocks. Without that hardware, the same software cannot achieve the same bound, and the latency cost becomes unpredictable .
- CockroachDB and YugabyteDB trade fixed cost for variable cost. Spanner pays a small, bounded commit-wait on every write. CockroachDB and YugabyteDB avoid that wait but pay read restarts when clock uncertainty affects a read. The restarts preserve correctness but add tail latency under skew .
- Deployment flexibility is the open-source advantage. Spanner runs only on Google Cloud. CockroachDB and YugabyteDB run anywhere, which matters for organizations that need multi-cloud, on-premises, or hybrid deployments .
- PostgreSQL compatibility differs. YugabyteDB’s YSQL layer reuses PostgreSQL’s query layer code, giving it higher compatibility than CockroachDB’s PostgreSQL wire protocol implementation. Applications with complex PostgreSQL-specific features should be tested against both .
Remember: Distributed SQL engines solve the problem of running ACID transactions across multiple machines, but they solve it with different assumptions about time. Spanner assumes it can bound clock uncertainty with hardware, and it pays for that assumption with the capital cost of GPS and atomic clocks. CockroachDB and YugabyteDB assume they cannot, and they pay for that assumption with read restarts and a reliance on NTP. Neither approach is universally better. The choice depends on whether the workload requires Spanner’s external consistency guarantee, whether the organization can accept the read-restart latency profile, and whether vendor lock-in to Google Cloud is acceptable. For most applications, the answer is that CockroachDB or YugabyteDB provides sufficient consistency at a lower operational cost. For the small set of workloads where cross-region transactions must serialize with no staleness window—ledgers, inventory, identity—Spanner’s hardware advantage is real and difficult to replicate in software.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!