Distributed SQL: TiDB, TiKV & CockroachDB Architecture Study

1. Context & Architecture Reality

Project Identity, Version & License Baseline

SYSTEM ARCHITECTURE BLUEPRINTDISTRIBUTED SYSTEMS

⤢Expand

ARCHITECTURAL FLOW · STEP 1 OF 3

1. Placement Driver (PD) / TSO & DISTRIBUTED TRANSACTIONAL KV ENGINE (TiKV / Pebble)

Establishes the ingress and structural boundary across Placement Driver (PD) / TSO & DISTRIBUTED TRANSACTIONAL KV ENGINE (TiKV / Pebble): Allocates monotonic timestamps, Region rebalancing; Continuous sorted byte array: Key-Value pairs with MVCC timestamps; Local storage: RocksDB / Pebble LSM engines on NVMe drives Full horizontal scaling: Add TiDB for compute or TiKV for storage independently!.

Architectural Invariant: Split is local and fast: Zero heavy data copying required!.

ARCHITECTURAL FLOW · STEP 2 OF 3

2. Phase 1: Prewrite (start_ts) & Phase 2: Commit (commit_ts)

Coordinates core data-plane execution and state transitions through Phase 1: Prewrite (start_ts) & Phase 2: Commit (commit_ts): 1. Designate Row 1 as Primary Lock; 2. Write Secondary Locks pointing to Primary; 1. Commit Primary Lock atomically.

Architectural Invariant: If client dies during Phase 2, concurrent readers inspect the Primary Lock. If Primary is committed, readers roll secondaries forward. Zero coordinator bottleneck!.

ARCHITECTURAL FLOW · STEP 3 OF 3

3. Decoupled Compute and Storage Layers & Multi-Raft Range Partitioning (Regions)

Enforces durability, state convergence, and output verification across Decoupled Compute and Storage Layers, Multi-Raft Range Partitioning (Regions), Google Percolator 2PC Transaction Protocol, Distributed Time: TrueTime vs Hybrid Logical Clocks within the Distributed SQL Architecture: TiDB, TiKV and Multi-Raft Consensus architecture.

Architectural Invariant: Maintains deterministic distributed systems invariants across Decoupled Compute and Storage Layers & Multi-Raft Range Partitioning (Regions).

For thirty years, database design was split into two hostile camps:

• Relational Databases (RDBMS): PostgreSQL, MySQL, and Oracle offered ANSI SQL, rich joins, secondary indexes, and strong ACID guarantees. However, they were architected for single-node vertical scaling. When data exceeded a single physical server, teams were forced into complex manual sharding, losing distributed joins, multi-table transactions, and cross-shard referential integrity.
• NoSQL Databases: Cassandra, MongoDB, and DynamoDB scaled horizontally across hundreds of commodity nodes with automated sharding and high availability. However, they abandoned relational features, transactions, and foreign keys, pushing transaction orchestration and conflict resolution onto application developers.

In 2012, Google published Spanner, followed by F1 and Percolator, proving that horizontal scalability and strong ACID consistency were not mutually exclusive.

This inspired the Distributed SQL (NewSQL) movement, led by TiDB (PingCAP) and CockroachDB. These systems present a standard relational SQL interface to applications while decoupling the stateless compute layer from a distributed, auto-sharding transactional key-value storage engine powered by Multi-Raft.

+--------------------------------------------------------------------------------------------------+
|                            DISTRIBUTED SQL LAYERED ARCHITECTURE                                  |
|                                                                                                  |
|   Client Applications (MySQL / PostgreSQL Driver)                                                |
|        |                                                                                         |
|        v                                                                                         |
|   +-----------------------------------------------------------------------------------------+    |
|   | STATELESS COMPUTE LAYER (TiDB / Cockroach SQL)                                          |    |
|   | - SQL Parser, Cost-Based Optimizer, Distributed Execution Engine                        |    |
|   | - Coprocessor Pushdown (Filters & Aggregations pushed to storage nodes)                 |    |
|   +-----------------------------------------------------------------------------------------+    |
|        |                                                                                         |
|        +-------------------+ (Allocates monotonic timestamps / manages Region balance)           |
|        |                   v                                                                     |
|        |        +-------------------------------------------------------------+                  |
|        |        | PLACEMENT DRIVER (PD) / TSO (3-node Raft Consensus Cluster) |                  |
|        |        +-------------------------------------------------------------+                  |
|        v                                                                                         |
|   +-----------------------------------------------------------------------------------------+    |
|   | DISTRIBUTED STORAGE LAYER (TiKV / Cockroach KV Nodes)                                   |    |
|   |                                                                                         |    |
|   |   Node 1                   Node 2                   Node 3                              |    |
|   |   +---------------------+  +---------------------+  +---------------------+             |    |
|   |   | Region 1 (Leader)   |  | Region 1 (Follower) |  | Region 1 (Follower) | [Raft Grp 1]|    |
|   |   | Region 2 (Follower) |  | Region 2 (Leader)   |  | Region 2 (Follower) | [Raft Grp 2]|    |
|   |   | Region 3 (Follower) |  | Region 3 (Follower) |  | Region 3 (Leader)   | [Raft Grp 3]|    |
|   |   +---------------------+  +---------------------+  +---------------------+             |    |
|   |   (Local Storage Engine: RocksDB / Pebble LSM-Tree on NVMe)                             |    |
|   +-----------------------------------------------------------------------------------------+    |
+--------------------------------------------------------------------------------------------------+

2. Core Problem & Failure Modes

Operating a distributed, transactional relational database creates four acute failure modes:

1. The Sequential Monotonic Key Hotspot: Distributed SQL maps relational tables into a single globally sorted byte-ordered key-value space: table_id + index_id + row_id. If a table uses an AUTO_INCREMENT integer or timestamp as its primary key, every newly inserted row has a key lexicographically greater than previous rows. Consequently, $100\%$ of writes land in the single Region covering the high end of the keyspace, choking a single node’s NVMe drive while dozens of cluster nodes sit idle.

2. Distributed Deadlocks in Two-Phase Commit: When Transaction 1 (updating rows across Region 1 and Region 2) and Transaction 2 (updating rows across Region 2 and Region 1) run concurrently, both transactions can acquire locks on one key and block waiting for the other. Without active distributed deadlock detection (Wait-For-Graph cycle checking), both transactions stall indefinitely.

3. Stranded Secondary Locks from Client Crashes: Under the Percolator 2PC protocol, a client prewrites locks on multiple storage nodes. If the client machine crashes or suffers a power cut after prewriting secondary locks but before committing the Primary Lock, those keys remain locked. Subsequent readers and writers encountering the orphaned locks will stall until decentralized lock resolution cleans them up.

4. The False Horizon of Clock Skew (Stale Reads): CockroachDB uses Hybrid Logical Clocks. If an NTP daemon on Node 5 loses synchronization and drifts by $400\text{ ms}$ (exceeding max_offset), the node can read a snapshot that misses writes that causally preceded it, violating serializability. CockroachDB mitigates this by having nodes crash themselves (node panic) if clock drift exceeds configured tolerances.

3. Technical Deep Dive

Multi-Raft Range Partitioning & Region Splitting

Traditional distributed consensus engines (like etcd) run a single Raft consensus group. Every log entry passes through one leader, capping write throughput at approximately $20,000-50,000\text{ QPS}$.

Distributed SQL solves this through Multi-Raft:

1. The global keyspace is treated as an infinite, continuous byte array: $[‘‘",+\infty )$.
2. The space is partitioned into contiguous chunks called Regions (in TiKV) or Ranges (in CockroachDB). The default target size is $96\text{ MB}$.
3. Each Region has 3 or 5 replicas distributed across distinct physical nodes, forming an independent Raft consensus group with its own leader, log, and state machine.

Continuous Key Space:
["", "apple")     --> Region 1 (Leader: Node 1, Followers: Node 2, Node 3)
["apple", "pear")  --> Region 2 (Leader: Node 2, Followers: Node 1, Node 4)
["pear", +infinity)--> Region 3 (Leader: Node 3, Followers: Node 2, Node 4)

Dynamic Split Mechanics

When writes cause Region 1’s size to exceed $144\text{ MB}$, the Region leader executes an internal Raft log entry: Split(split_key = "banana").

• Once committed by quorum, Region 1 splits locally into Region 1A ["", "banana") and Region 1B ["banana", "apple").
• Zero data is moved across the network during a split; it is an instantaneous metadata modification in the underlying RocksDB/Pebble engine!

The Google Percolator Distributed 2PC Protocol

TiKV implements ACID transactions using the Percolator protocol (Peng and Dabek, 2010), a decentralized two-phase commit protocol layered on top of MVCC.

Every row key $K$ has three internal column families in RocksDB:

• data:K_startTS $\to$ Actual value payload.
• lock:K $\to$ Active lock containing (primary_key, startTS, TTL).
• write:K_commitTS $\to$ Commit record containing startTS, marking the row visible at commitTS.

Protocol Execution Steps

Client (Coordinator)                      TiKV Node A (Primary)            TiKV Node B (Secondary)
       |                                           |                                  |
1. Acquire start_ts from TSO                       |                                  |
       |                                           |                                  |
2. Phase 1: PREWRITE                               |                                  |
       |-- Prewrite Primary (Key X, val, start_ts) ->|                                  |
       |   [Writes lock:X, data:X_start_ts]        |                                  |
       |<- OK --------------------------------------|                                  |
       |                                           |                                  |
       |-- Prewrite Secondary (Key Y, val, start_ts, primary=X) ------------------------->|
       |   [Writes lock:Y pointing to X, data:Y_start_ts]                              |
       |<- OK -------------------------------------------------------------------------|
       |                                           |                                  |
3. Acquire commit_ts from TSO (commit_ts > start_ts)                                  |
       |                                           |                                  |
4. Phase 2: COMMIT                                 |                                  |
       |-- Commit Primary (Key X, commit_ts) ----->|                                  |
       |   [Removes lock:X, writes write:X_commit_ts]                                  |
       |<- OK (TRANSACTION COMMITTED!) -----------|                                  |
       |                                           |                                  |
       |-- Async Commit Secondary (Key Y, commit_ts) ----------------------------------->|
       |   [Removes lock:Y, writes write:Y_commit_ts]                                  |

Key Invariant: As soon as the Primary Lock is committed in Phase 2, the transaction is guaranteed to be committed. If the client crashes before committing Secondary Key Y, a concurrent reader encountering lock:Y follows the pointer to Key X. Finding Key X committed, the reader rolls Key Y forward itself!

TiDB/TiKV Two-Layer Percolator vs. CockroachDB Single-Layer Parallel Commits

While both engines rely on Multi-Raft over LSM storage, their transaction protocols make distinct latency and deployment trade-offs:

• TiDB + TiKV (Decoupled SQL + PD TSO + Percolator): Stateless TiDB SQL servers allocate start_ts and commit_ts from the Placement Driver (PD) Timestamp Oracle (TSO) (which pre-allocates timestamp windows in memory to serve millions of timestamps/sec). Classic Percolator requires two Raft write phases (Prewrite + Primary Commit), though TiDB 5.0+ adds 1PC (committing single-Region transactions in one Raft round) and Async Commit (calculating commit_ts via per-Region max_ts without waiting for a second TSO/Primary-Commit round before returning to the client).
• CockroachDB (Unified Single Binary + HLC + Parallel Commits): Every CockroachDB node runs both the PostgreSQL-compatible SQL gateway and the Pebble KV/Multi-Raft store. Instead of three RocksDB column families, CockroachDB writes provisional Write Intents inline in Pebble pointing to a single Transaction Record (PENDING $\to$ STAGING $\to$ COMMITTED). Using Parallel Commits (Taft et al., SIGMOD ‘20), the coordinator pipelines all Write Intents and transitions the Transaction Record to STAGING concurrently, committing cross-Range transactions in a single Multi-Raft consensus round-trip once all intent writes and the staging record succeed.

Distributed Time: TrueTime vs Hybrid Logical Clocks

To support Snapshot Isolation ($SI$) and Strict Serializability without lock contention on reads, distributed databases require monotonically increasing timestamps across nodes.

In CockroachDB’s Hybrid Logical Clock (HLC) (Kulkarni et al., 2014), each node maintains tuple $(l_{\text{local}},c_{\text{local}})$, where $l$ tracks the highest physical wall-clock timestamp observed so far and $c$ is a logical tie-breaker counter. Upon receiving an RPC carrying $(l_{\text{received}},c_{\text{received}})$ at local physical wall time $p{t}_{\text{local}}$, the recipient updates its clock via the four-branch rule:

Numerical Verification: Suppose Node A sends a write RPC stamped $(l_{\text{received}}=1050\text{ ms},c_{\text{received}}=4)$ to Node B whose local clock lags at $p{t}_{\text{local}}=1042\text{ ms}$ with $(l_{\text{local}}=1048\text{ ms},c_{\text{local}}=2)$. Node B computes $l_{\text{new}}=\max (1048,1050,1042)=1050\text{ ms}$ and hits branch 3 ($l_{\text{new}}=l_{\text{received}}$), setting $c_{\text{new}}=4+1=5$. The resulting timestamp $(1050,5)$ is strictly lexicographically greater than both $(1048,2)$ and $(1050,4)$, preserving happens-before causality despite $8\text{ ms}$ of backward NTP drift.

4. System Design Patterns & Trade-offs

+----------------------------------------------------------------------------------------------------+
| TRADEOFF MATRIX: NEWSQL ARCHITECTURAL DESIGNS                                                      |
+--------------------------+------------------------------------+------------------------------------+
| Dimension                | TiDB / TiKV (Decoupled Compute/KV) | CockroachDB (Unified Node Binary)  |
+--------------------------+------------------------------------+------------------------------------+
| Deployment Simplicity    | Complex (Separate TiDB, TiKV, PD)  | Simple (Single binary on all nodes)|
| Independent Scalability  | High (Scale SQL CPU without disk)  | Moderate (Scale nodes uniformly)   |
| Timestamp Generation     | Centralized TSO (Placement Driver) | Decentralized HLC (Pure software)  |
| Protocol Compatibility   | Native MySQL Protocol              | Native PostgreSQL Protocol         |
+--------------------------+------------------------------------+------------------------------------+
| Dimension                | Shared-Storage (Amazon Aurora)     | Shared-Nothing (TiDB / Cockroach)  |
+--------------------------+------------------------------------+------------------------------------+
| Write Scalability        | Single write-master node limit     | Unbounded horizontal Multi-Raft    |
| SQL Compatibility        | 100% native PostgreSQL/MySQL engine| High, but some DDL edge differences|
| Cross-Region Active-Act  | Read replicas only                 | Multi-region active-active tables  |
+--------------------------+------------------------------------+------------------------------------+

5. Practical Implementation

Below is a complete TypeScript simulation of a Multi-Raft Range Manager and Percolator 2PC Transaction Engine. It implements continuous key range routing, region splitting, and primary/secondary lock resolution.

export interface KeyValueMVCC {
  key: string;
  value: string;
  startTs: number;
  commitTs?: number;
}

export interface LockRecord {
  key: string;
  primaryKey: string;
  startTs: number;
  ttlMs: number;
}

/**
 * Single Multi-Raft Region covering key range [startKey, endKey)
 */
export class RaftRegion {
  public data: Map<string, KeyValueMVCC[]> = new Map();
  public locks: Map<string, LockRecord> = new Map();

  constructor(
    public readonly regionId: number,
    public startKey: string,
    public endKey: string,
    public leaderNode: string
  ) {}

  public containsKey(key: string): boolean {
    const afterStart = this.startKey === '' || key >= this.startKey;
    const beforeEnd = this.endKey === '' || key < this.endKey;
    return afterStart && beforeEnd;
  }
}

/**
 * NewSQL Distributed Storage Engine Simulator
 */
export class DistributedSqlEngine {
  private regions: RaftRegion[] = [];
  private tsoClock: number = 1000;
  private nextRegionId: number = 3;

  constructor() {
    // Initial cluster starts with 2 regions
    this.regions.push(new RaftRegion(1, '', 'm', 'node-1'));
    this.regions.push(new RaftRegion(2, 'm', '', 'node-2'));
  }

  public getTimestamp(): number {
    return ++this.tsoClock;
  }

  public locateRegion(key: string): RaftRegion {
    const region = this.regions.find((r) => r.containsKey(key));
    if (!region) throw new Error(`Routing error: key ${key} has no owning region`);
    return region;
  }

  /**
   * Splits an existing Multi-Raft Region at splitKey with zero network data movement
   */
  public splitRegion(splitKey: string, newLeaderNode: string = 'node-3'): {
    leftRegion: RaftRegion;
    rightRegion: RaftRegion;
  } {
    const target = this.locateRegion(splitKey);
    if (splitKey === target.startKey) {
      throw new Error(`Invalid splitKey '${splitKey}': matches region start boundary`);
    }

    const originalEndKey = target.endKey;
    target.endKey = splitKey;

    const rightRegion = new RaftRegion(
      this.nextRegionId++,
      splitKey,
      originalEndKey,
      newLeaderNode
    );

    // Partition existing MVCC versions and locks across [startKey, splitKey) and [splitKey, originalEndKey)
    for (const [k, versions] of target.data.entries()) {
      if (k >= splitKey) {
        rightRegion.data.set(k, versions);
        target.data.delete(k);
      }
    }
    for (const [k, lock] of target.locks.entries()) {
      if (k >= splitKey) {
        rightRegion.locks.set(k, lock);
        target.locks.delete(k);
      }
    }

    this.regions.push(rightRegion);
    this.regions.sort((a, b) => a.startKey.localeCompare(b.startKey));
    return { leftRegion: target, rightRegion };
  }

  /**
   * Executes Percolator 2-Phase Commit
   */
  public executeTransaction(mutations: { key: string; value: string }[]): boolean {
    if (mutations.length === 0) return true;

    const startTs = this.getTimestamp();
    const primaryMutation = mutations[0];
    const secondaryMutations = mutations.slice(1);

    // ==========================================
    // PHASE 1: PREWRITE
    // ==========================================

    // 1. Prewrite Primary Key
    const primaryRegion = this.locateRegion(primaryMutation.key);
    if (primaryRegion.locks.has(primaryMutation.key)) {
      throw new Error(`Write conflict: primary key ${primaryMutation.key} is locked`);
    }

    primaryRegion.locks.set(primaryMutation.key, {
      key: primaryMutation.key,
      primaryKey: primaryMutation.key,
      startTs,
      ttlMs: 5000
    });

    // 2. Prewrite Secondary Keys
    const acquiredSecondaries: { key: string; region: RaftRegion }[] = [];
    try {
      for (const mut of secondaryMutations) {
        const region = this.locateRegion(mut.key);
        if (region.locks.has(mut.key)) {
          throw new Error(`Write conflict: secondary key ${mut.key} is locked`);
        }

        region.locks.set(mut.key, {
          key: mut.key,
          primaryKey: primaryMutation.key, // Points to primary!
          startTs,
          ttlMs: 5000
        });
        acquiredSecondaries.push({ key: mut.key, region });
      }
    } catch (err) {
      // Rollback prewrites on conflict
      primaryRegion.locks.delete(primaryMutation.key);
      for (const item of acquiredSecondaries) {
        item.region.locks.delete(item.key);
      }
      return false;
    }

    // ==========================================
    // PHASE 2: COMMIT
    // ==========================================
    const commitTs = this.getTimestamp();

    // 1. Commit Primary (The point of no return)
    primaryRegion.locks.delete(primaryMutation.key);
    if (!primaryRegion.data.has(primaryMutation.key)) {
      primaryRegion.data.set(primaryMutation.key, []);
    }
    primaryRegion.data.get(primaryMutation.key)!.push({
      key: primaryMutation.key,
      value: primaryMutation.value,
      startTs,
      commitTs
    });

    // 2. Asynchronously commit secondaries
    for (const item of acquiredSecondaries) {
      item.region.locks.delete(item.key);
      const mut = secondaryMutations.find((m) => m.key === item.key)!;
      if (!item.region.data.has(item.key)) {
        item.region.data.set(item.key, []);
      }
      item.region.data.get(item.key)!.push({
        key: item.key,
        value: mut.value,
        startTs,
        commitTs
      });
    }

    return true;
  }

  /**
   * Reads data with Snapshot Isolation at current snapshot timestamp
   */
  public readSnapshot(key: string): string | null {
    const readTs = this.getTimestamp();
    const region = this.locateRegion(key);

    // If key is locked with startTs <= readTs, check primary for resolution
    const lock = region.locks.get(key);
    if (lock && lock.startTs <= readTs) {
      throw new Error(`Locked key encountered: resolution required against primary ${lock.primaryKey}`);
    }

    const versions = region.data.get(key) || [];
    // Find newest version committed at or before readTs
    const valid = versions
      .filter((v) => v.commitTs !== undefined && v.commitTs <= readTs)
      .sort((a, b) => b.commitTs! - a.commitTs!);

    return valid.length > 0 ? valid[0].value : null;
  }
}

function runDemo(): void {
  console.log('=== TiDB / TiKV & CockroachDB: Multi-Raft & Percolator 2PC ===');
  const engine = new DistributedSqlEngine();

  // Seed balances across Region 1 ["", "m") and Region 2 ["m", "")
  engine.executeTransaction([
    { key: 'acct_alice', value: '1000' },
    { key: 'merchant_bob', value: '500' },
  ]);

  console.log(
    `Initial Region routing: 'acct_alice' -> Region ${engine.locateRegion('acct_alice').regionId}, ` +
    `'merchant_bob' -> Region ${engine.locateRegion('merchant_bob').regionId}`
  );

  // Split Region 1 ["", "m") at splitKey "f" into Region 1 ["", "f") and Region 3 ["f", "m")
  const { leftRegion, rightRegion } = engine.splitRegion('f', 'node-3');
  console.log(
    `Executed Multi-Raft Split at key 'f': Region ${leftRegion.regionId} ["${leftRegion.startKey}", "${leftRegion.endKey}") ` +
    `& Region ${rightRegion.regionId} ["${rightRegion.startKey}", "${rightRegion.endKey}")`
  );

  // Atomic cross-Region transfer ($100 from acct_alice to merchant_bob)
  const txSuccess = engine.executeTransaction([
    { key: 'acct_alice', value: '900' },
    { key: 'merchant_bob', value: '600' },
  ]);
  console.log('Cross-Region Percolator 2PC transfer committed: ' + txSuccess);
  console.log(
    `Snapshot Isolation balances: acct_alice=$${engine.readSnapshot('acct_alice')}, ` +
    `merchant_bob=$${engine.readSnapshot('merchant_bob')}`
  );
}
runDemo();

Simulation Execution Output & State Transitions

=== TiDB / TiKV & CockroachDB: Multi-Raft & Percolator 2PC ===
Initial Region routing: 'acct_alice' -> Region 1, 'merchant_bob' -> Region 2
Executed Multi-Raft Split at key 'f': Region 1 ["", "f") & Region 3 ["f", "m")
Cross-Region Percolator 2PC transfer committed: true
Snapshot Isolation balances: acct_alice=$900, merchant_bob=$600

6. Failure Scenarios & Edge Cases

1. Monotonic Primary Key Hot Region Overload

An application creates a user table with an auto-incrementing integer id BIGINT AUTO_INCREMENT. All 20,000 new user signups per minute have IDs $1000001,1000002\dots$. Every write routes to the highest Region in the cluster. Even though the cluster has 60 servers, a single server handles 100% of write traffic, bottlenecking write throughput.

• Mitigation: Use AUTO_RANDOM (TiDB) or UUIDv4 primary keys. AUTO_RANDOM assigns random bits to the high-order bits of the integer, dispersing sequential IDs uniformly across the entire continuous keyspace.

2. Coprocessor OOM Under Unindexed Range Scans

A client issues SELECT * FROM orders WHERE status = 'PENDING' without an index on status. TiDB pushes the filter down to all TiKV nodes via the Coprocessor. Each TiKV node scans its local RocksDB store. If a single Region contains millions of records, the coprocessor can consume gigabytes of off-heap memory parsing table records, causing Linux to trigger an Out-Of-Memory (OOM) kill on the storage node.

• Mitigation: Mandate composite secondary indexes. Enforce query execution memory limits (tidb_mem_quota_query = 1GB).

7. Production Operations & Observability

Critical Placement Driver (PD) Schedulers

The Placement Driver continuously analyzes cluster metrics and dispatches balancing operators:

• balance-leader-scheduler: Migrates Raft leaders so CPU utilization is evenly distributed across all nodes.
• balance-region-scheduler: Moves Region peer replicas to balance NVMe disk storage across physical machines.
• hot-region-scheduler: Detects Regions with abnormally high read/write QPS and re-routes replicas to cool down overloaded nodes.

8. Security & Compliance

1. mTLS Wire Encryption: Ensure security.cluster-ssl-ca is configured on all TiDB, TiKV, and PD nodes. All internal gRPC Raft message traffic must use mutual TLS with short-lived X.509 certificates.
2. Transparent Data Encryption (TDE): Enable hardware-accelerated AES-256 encryption on RocksDB SSTables and Raft log files to satisfy PCI-DSS and HIPAA data-at-rest controls.

9. Real-World Case Studies

Pinterest Graph Storage on TiKV

Pinterest migrated massive graph caching workloads to TiKV.

• By decoupling TiDB (stateless compute) from TiKV (storage), Pinterest scaled their caching infrastructure to over $100\text{ million QPS}$.
• Coprocessor pushdown reduced network data transfer by $85\%$, because filtering and counting operations executed directly inside TiKV rather than streaming gigabytes of raw records over the internal network.

10. Technology Comparison Matrix

+----------------------------------------------------------------------------------------------------+
| DISTRIBUTED SQL PLATFORM MATRIX                                                                    |
+----------------------+--------------------+--------------------+-----------------------------------+
| Platform             | Consensus Protocol | Time Mechanism     | Storage Engine                    |
+----------------------+--------------------+--------------------+-----------------------------------+
| TiDB / TiKV          | Multi-Raft         | Placement Driver   | RocksDB LSM-Tree (C++ via Rust    |
|                      |                    | (Centralized TSO)  | FFI in TiKV)                      |
+----------------------+--------------------+--------------------+-----------------------------------+
| CockroachDB          | Multi-Raft         | Hybrid Logical     | Pebble LSM-Tree (Go)              |
|                      |                    | Clocks (HLC)       |                                   |
+----------------------+--------------------+--------------------+-----------------------------------+
| Google Spanner       | Multi-Paxos        | TrueTime (GPS and  | Proprietary Log-Structured Store  |
|                      |                    | Atomic Clocks)     |                                   |
+----------------------+--------------------+--------------------+-----------------------------------+
| YugabyteDB           | Multi-Raft         | Hybrid Logical     | DocDB (Modified RocksDB in C++)   |
|                      |                    | Clocks (HLC)       |                                   |
+----------------------+--------------------+--------------------+-----------------------------------+

11. Design Challenge & Exercise

Scenario: High-Volume Financial Ledger on NewSQL

You are designing the core ledger database for a digital bank with 10,000,000 accounts. Account transactions must be strictly serializable.

Requirements:

1. Zero balance overdraft: If Account A transfers $100 to Account B, both balance updates must commit atomically or both fail.
2. High contention: Merchant accounts (e.g., Walmart, Amazon) process 5,000 incoming payments per second on a single account ID.

Your Task:

• Design the SQL schema and primary key strategy to avoid monotonic key hotspots.
• Trace how Percolator 2PC handles concurrent payments to the same merchant account.
• Propose a conflict-mitigation pattern (such as sub-ledger sharding) to eliminate transaction rollback storms on hot accounts.

-- 1. Core Customer & Merchant Accounts (Dispersed across Regions by UUID / AUTO_RANDOM)
CREATE TABLE accounts (
  account_id     BIGINT PRIMARY KEY AUTO_RANDOM(5),
  account_type   VARCHAR(16) NOT NULL,  -- 'CUSTOMER' | 'MERCHANT'
  balance_cents  BIGINT NOT NULL CHECK (balance_cents >= 0),
  updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) PRE_SPLIT_REGIONS = 32;

-- 2. Immutable Double-Entry Audit Ledger (Idempotent by client_tx_id)
CREATE TABLE ledger_entries (
  entry_id       BIGINT PRIMARY KEY AUTO_RANDOM(5),
  client_tx_id   UUID NOT NULL,
  debit_acct_id  BIGINT NOT NULL,
  credit_acct_id BIGINT NOT NULL,
  amount_cents   BIGINT NOT NULL CHECK (amount_cents > 0),
  created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE INDEX idx_idempotency (client_tx_id)
) PRE_SPLIT_REGIONS = 64;

12. Assessment

Review the 4 scenario-based quiz questions at the top of this lesson to test your mastery of NewSQL architectures, Multi-Raft range partitioning, and Percolator distributed transactions.

13. Summary & Architectural Decision Guide

• TiDB / TiKV Architecture: Separates stateless SQL compute (TiDB) from Multi-Raft key-value storage (TiKV). Relies on centralized Placement Driver (PD) and Timestamp Oracle (TSO) for Google Percolator 2PC transactions.
• CockroachDB Architecture: Unified node architecture where every node runs SQL execution and Multi-Raft Range storage. Eliminates centralized TSO using Hybrid Logical Clocks (HLC) and read-restart uncertainty windows.
• When to Choose TiDB: MySQL compatibility, massive analytical hybrid HTAP queries via TiFlash columnar replicas, and high write volumes.
• When to Choose CockroachDB: PostgreSQL wire compatibility, multi-cloud and cross-region survival, and operational simplicity (single binary).

14. Authoritative References & Version Context

• Peng and Dabek (2010), “Large-scale Incremental Processing Using Distributed Transactions and Notifications”, USENIX OSDI ‘10 (Google Percolator) — usenix.org
• Dongxu Huang et al. (2020), “TiDB: A Raft-based HTAP Database”, VLDB ‘20 — doi.org/10.14778/3415478.3415535
• Rebecca Taft et al. (2020), “CockroachDB: The Resilient Geo-Distributed SQL Database”, ACM SIGMOD ‘20 — doi.org/10.1145/3318464.3386134
• Version Context: Architecture reflects TiDB 7.5 LTS / TiKV 7.5 (Multi-Raft Regions) and CockroachDB 23.x / 24.x LTS (HLC Raft Ranges).