Deep Dive into CockroachDB Architecture: Distributed SQL, Distributed Execution, and Storage Internals

Building a distributed SQL database from scratch is one of the most ambitious engineering undertakings in computer science. It sits at the intersection of operating system scheduling, compiler design, fault-tolerant consensus, distributed systems, and low-level disk I/O.

In this technical exploration, based on insights shared by Ben Darnell (Co-founder and Chief Architect of CockroachDB), we unpack the architectural mechanics behind CockroachDB: why manual sharding fails at scale, how distributed execution differs from distributed storage, why serializable isolation is feasible as a default in distributed environments, the evolution from RocksDB to Pebble, and how CockroachDB solves clock synchronization without proprietary atomic hardware.

---

1. The Distributed SQL Imperative: Beyond Manual Sharding

For decades, scaling relational workloads meant running monolithic MySQL or PostgreSQL instances. When workloads outgrew single-node vertical scaling, teams resorted to application-level manual sharding.

+-------------------------------------------------------------+
|                      Application Layer                      |
| (Sharding Logic, Routing, Cross-Shard Aggregations, Sagas)  |
+---------------+---------------+--------------+--------------+
                |               |              |               
        +-------v-------+ +-----v------+ +-----v------+        
        | MySQL Shard 1 | | MySQL Shard 2| | MySQL Shard 3|        
        +---------------+ +------------+ +------------+        

The Operational and Consistency Cost of Sharding

1. Compromised Transactional Guarantees: Cross-shard ACID transactions require distributed two-phase commit (2PC) bolted on at the application layer or via external middleware, often resulting in brittle partial failures or performance bottlenecks.
2. No Native Cross-Shard Secondary Indexes: Secondary indexes must either be duplicated per shard (scatter-gather reads across all shards) or indexed externally, breaking referential integrity.
3. Operational Overhead: Schema migrations, shard splits, rebalancing, and failover management must be manually choreographed across dozens or hundreds of independent instances.

While non-relational systems (NoSQL) like BigTable and DynamoDB solved transparent horizontal scaling and high availability, they pushed complex transactional consistency, secondary indexing, and schema enforcement back onto application developers.

CockroachDB was designed to deliver the relational guarantees of SQL alongside the horizontal scalability and automated operational characteristics of NoSQL.

---

2. Distributed Storage vs. Distributed Execution

Many modern distributed databases decouple compute and storage, providing distributed storage beneath a single-node query engine (e.g., standard engines writing to distributed shared block stores). CockroachDB differentiates itself by implementing both distributed storage and truly distributed execution.

+-------------------------------------------------------------------------+
|                        Distributed SQL Query                            |
|                  SELECT region, SUM(amount) ...                         |
+-------------------------------------------------------------------------+
                                     |
                  +------------------v------------------+
                  |      Distributed Query Planner      |
                  |  (Cost-Based Optimizer / Topologies)| 
                  +------------------+------------------+
                                     |
     +-------------------------------+-------------------------------+
     |                               |                               |
+----v---------------+      +--------v-----------+      +------------v------+
| Node 1 (Storage)   |      | Node 2 (Storage)   |      | Node 3 (Storage)  |
| Local Scan + Sum   |      | Local Scan + Sum   |      | Local Scan + Sum  |
+----+---------------+      +--------+-----------+      +------------+------+
     |                               |                               |
     +-------------------------------+-------------------------------+
                                     |
                                     v
                      +------------------------------+
                      | Coordinator Node             |
                      | Collects Partial Sums        |
                      | Final Aggregation & Return   |
                      +------------------------------+

The Need for a Custom Cost-Based Optimizer (CBO)

Traditional query planners optimize execution paths assuming local memory and disk I/O costs. In a distributed environment, the dominant bottleneck is network latency and cross-node data transfer.

CockroachDB built its query optimizer from scratch to be topology-aware:

• Logical Awareness: Inspects indexes, predicates, and schema constraints.
• Physical Awareness: Understands how ranges (shards of the key-value space) are mapped across physical nodes and data centers.

Vectorized & Distributed Query Execution

When executing a complex aggregation (e.g., GROUP BY with SUM()):

1. The planner compiles the query into a directed acyclic graph (DAG) of execution stages.
2. Instead of shipping raw table rows across the network to a central coordinator, the planner splits the computation.
3. Sub-plans run locally on every node hosting the relevant data ranges, computing intermediate partial sums.
4. Only the aggregated intermediate results are sent over the network to the coordinator node for the final reduction, drastically reducing network bandwidth consumption.

---

3. The Isolation Paradox: Why Default to Serializable?

Under the ANSI SQL-92 standard, SERIALIZABLE is specified as the default isolation level. However, most commercial databases defaulted to weaker isolation levels (e.g., READ COMMITTED or REPEATABLE READ) due to the performance penalties associated with lock-based serializability on single-node engines.

CockroachDB inverted this convention by adopting Serializable Isolation by default.

+---------------------------------------------------------------------+
| Isolation Level Comparison in a Distributed Environment              |
+-------------------+-------------------------------------------------+
| Metric            | Observation                                     |
+-------------------+-------------------------------------------------+
| Throughput        | Roughly equivalent between Read Committed and   |
|                   | Serializable (dominated by network round trips) |
+-------------------+-------------------------------------------------+
| Latency Median    | Very close across isolation levels              |
+-------------------+-------------------------------------------------+
| Latency Variance  | Higher tail latency in Serializable due to      |
| (P99 / Tail)      | conflict detection and transaction retries      |
+-------------------+-------------------------------------------------+

Why Weaker Isolation Does Not Equal Massive Speedups in Distributed Systems

1. Network vs. Locking Overhead: In a single-node database, lock acquisition and lock contention on CPU/memory directly impact throughput. In a distributed database, transaction latency is dominated by network round trips (consensus rounds via Raft, distributed commit protocols).
2. Implementing Weaker Isolation Still Requires Coordination: Implementing snapshot isolation or repeatable read across a cluster still requires snapshot timestamps and distributed commit validation. Weakening the isolation level does not eliminate network round trips.
3. The Correctness Trade-Off: Non-serializable anomalies (e.g., write skew, phantom reads) lead to silent data corruption at scale. CockroachDB’s design ethos was to guarantee correctness first, optimizing the execution engine rather than cutting correctness corners.

Latency Variance and Retries

The primary cost of Serializable isolation is not degraded peak throughput, but elevated latency variance (tail latency). When two distributed transactions experience serializability conflicts, one must abort, roll back, and retry. Consequently, a small fraction of transactions take longer to finish, which applications must handle gracefully using automated retry logic.

---

4. Storage Engine Evolution: From RocksDB to Pebble

CockroachDB represents its entire relational data model on top of an ordered transactional Key-Value (KV) engine.

+------------------------------------------------------------+
| SQL Layer (Tables, Rows, Types, Constraints, Schemas)      |
+------------------------------------------------------------+
                             |
                             v
+------------------------------------------------------------+
| Distributed KV Layer (Raft, Ranges, Range Addressing, MVCC)|
+------------------------------------------------------------+
                             |
                             v
+------------------------------------------------------------+
| Local Storage Engine (LSM-Tree: Pebble)                    |
+------------------------------------------------------------+

Why RocksDB Was Replaced

CockroachDB was originally built using RocksDB (a C++ Log-Structured Merge-tree engine started by Meta). Over time, three major frictions emerged:

1. CGo Overhead & Build Complexities: CockroachDB is written in Go. Crossing the Go-to-C++ barrier via CGo introduced significant CPU overhead on high-frequency read/write paths, inflated build times, and complicated cross-platform profiling.
2. Feature Interoperability & Code Quality: As an open-source project with diverse external contributions, RocksDB accumulated features that did not always work harmoniously together. For example, specific combinations of range deletions (DeleteRange) and compaction filters occasionally triggered severe performance degradation under CockroachDB’s access patterns.
3. Operational Control: Deeply tuning an external C++ engine to CockroachDB’s exact distributed lifecycle was unsustainable.

Pebble: Purpose-Built Pure-Go Storage Engine

Cockroach Labs engineered Pebble, a high-performance, pure-Go LSM engine modeled after LevelDB and RocksDB, but strictly constrained to the subset of features and access patterns required by CockroachDB.

• Zero CGo Overhead: High-throughput internal key scans remain inside the Go runtime.
• Controlled Surface Area: Avoids unneeded features, allowing aggressive optimization of critical paths like range deletions, iterator seeks, and sstable ingestions.
• Multi-Year Migration: The team ran Pebble and RocksDB in parallel with feature flags across several major releases before completely removing RocksDB from the code base.

---

5. SQL Mapping to Ordered Key-Value Storage

CockroachDB stores relational tables inside an ordered byte-array KV store using deterministic, order-preserving encodings.

Key Encoding

Keys are structured to ensure that sequential scans in SQL map directly to contiguous ranges in the KV store:

$\text{Key} = \langle \text{TableID}, \text{IndexID}, \text{ColumnValues...} \rangle$

• Integers are encoded using big-endian byte sequences so that lexical comparison matches numerical ordering.
• Floating-point numbers and arbitrary-precision decimals use specialized encodings that preserve IEEE 754 and decimal collation orders when compared lexicographically.

Value Encoding: The Shift to Column Families

[Early CockroachDB Implementation]
Row: ID=1, Name="Alice", Balance=100, Bio="..."
-> Key(1, /id)      => Val(1)
-> Key(1, /name)    => Val("Alice")
-> Key(1, /balance) => Val(100)
-> Key(1, /bio)     => Val("...")
(4 distinct KV pairs per row)

[Modern CockroachDB Implementation]
Row: ID=1, Name="Alice", Balance=100, Bio="..."
-> Key(Table1, IndexPrimaryKey, 1) => Val(FamilyDefault: {Name, Balance, Bio})
(Single KV pair per row by default; custom families can split heavy blobs)

1. Original Model: Each column was written as an independent KV pair. While this allowed updating a single column without rewriting others, it caused massive write amplification and multiplied LSM metadata overhead.
2. Modern Column Family Model: A single row defaults to a single KV entry containing all column values packed together. Users can optionally declare explicit Column Families (e.g., placing frequently updated scalar values in one family and a rarely modified, large JSON blob in another), minimizing I/O during targeted updates.

---

6. Distributed Time: CockroachDB vs. Google Spanner

Google Spanner relies on TrueTime, an API backed by specialized physical hardware (GPS receivers and atomic clocks) deployed in every Google datacenter.

+------------------------------------------------------------------------+
| Clock Synchronization Trade-off Matrix                                 |
+----------------------+-------------------------------------------------+
| Mechanism            | Architectural Design                            |
+----------------------+-------------------------------------------------+
| Google Spanner       | Commit-Wait (Write Delay):                      |
| (TrueTime)           | Hardware-bounded clock uncertainty (e.g., 7ms). |
|                      | Every write sleeps for 7ms before committing to |
|                      | ensure no future transaction reads stale data.  |
+----------------------+-------------------------------------------------+
| CockroachDB          | Hybrid Logical Clocks (HLC) + Read-Side Checks: |
| (Commodity NTP/PTP)  | Unbounded commodity clock uncertainty (up to    |
|                      | 100ms+). Writes do NOT wait. Clock skew is      |
|                      | detected during read operations.                |
+----------------------+-------------------------------------------------+

CockroachDB’s Commodity Hardware Solution

Because CockroachDB must run anywhere—on AWS, GCP, Azure, bare metal, or hybrid private clouds—it cannot depend on atomic clocks. Standard NTP-synchronized servers can drift anywhere from a few milliseconds to over 100 milliseconds.

Instead of applying a costly commit-wait sleep (e.g., 100ms) to every write:

1. Writes Execute Immediately: Transactions write values timestamped by their local Hybrid Logical Clock (combining physical time and Lamport logical clocks).
2. Read-Side Uncertainty Handling: When a read encounters a value whose timestamp falls within the uncertainty interval of the reading node’s clock, the reader cannot determine causal order with certainty.
3. Resolution: The reading transaction either pauses briefly for the uncertainty window to pass or aborts and retries at a higher timestamp, preventing stale reads and preserving serializability without specialized hardware.

---

7. Multi-Version Concurrency Control (MVCC) and Time Travel Queries

CockroachDB’s underlying engine stores data with MVCC timestamps. Modifying a row does not overwrite the old bytes in place; it writes a new version of the row with an incremented HLC timestamp.

-- Historical read as of a specific point in time
SELECT * FROM accounts 
AS OF SYSTEM TIME '2024-03-16 09:00:00+00'
WHERE account_id = 42;

Strategic Applications of AS OF SYSTEM TIME

1. Disaster Recovery & Forensic Auditing: Immediate retrieval of accidentally dropped records or mutated tables without taking snapshots offline or restoring backups.
2. Lock-Free Analytical Reporting: Analytical queries reading slightly in the past (e.g., AS OF SYSTEM TIME INTERVAL '-10s') read completely static versions of data. They do not conflict with concurrent read-write transactions.
3. Bypassing Clock Uncertainty: Because historical reads target a finalized timestamp well behind current wall-clock uncertainty windows, they avoid read-side clock uncertainty pauses and transaction restarts, delivering fast, deterministic read latency.

---

8. Summary of Key Architectural Takeaways

• Unified System Architecture: CockroachDB bridges distributed transactions and relational semantics by building a distributed execution engine and cost-based optimizer directly atop a sharded, ordered key-value store.
• Cost of Serializability: In distributed systems, serialization performance is dominated by network round-trips rather than isolation level overhead. The trade-off manifests primarily as P99 latency variance (due to conflict retries) rather than diminished throughput.
• Storage Engine Independence: Pure-language storage engines like Pebble eliminate cross-runtime overhead (such as CGo boundaries) and grant complete control over edge-case compactions and range deletions.
• TrueTime Alternative: Rather than delaying writes to satisfy hardware-based uncertainty bounds, CockroachDB uses Hybrid Logical Clocks and shifts uncertainty detection onto read conflicts, enabling standard commodity cloud deployment.