← Previous Chapter: Part 4 — MariaDB vs. MySQL | Series Hub | Next Chapter: Part 6 — Apache Kafka vs. NATS JetStream →

Part 5: Sharded MySQL (Vitess) vs. TiDB NewSQL: Distributed ACID, Scale-Out Limits & Latency Penalties

Answer-first: Sharded MySQL (Vitess) delivers unmatched sub-2ms write latency and isolated failure blast radius for clean single-shard workloads (tenant_id/user_id). Conversely, TiDB NewSQL is the definitive architecture for unpartitionable relational schemas and cross-shard queries via zero-touch 96MB Region auto-splits, trading off an 8–15ms write latency floor due to Google Percolator 2PC and Raft consensus hops.

Prerequisite: Knowledge of horizontal database sharding patterns, distributed 2-Phase Commit (2PC), Paxos/Raft consensus algorithms, and LSM-tree compaction.

For foundational distributed systems patterns and Go microservice concurrency engineering, explore our Go Microservices Architecture Guide and comprehensive Systems Architecture Reading Map.

1. Executive Summary & The 10TB Scaling Wall

When transactional database workloads scale past 10TB of active state and 50,000+ write queries per second (QPS), traditional single-primary relational architectures break down:

• Hardware Ceilings: The largest cloud instances (e.g. AWS r6i.32xlarge with 1TB RAM) become exponentially expensive (> $8,000/month) while saturating CPU run queues and InnoDB buffer pool mutexes.
• Replication Lag Spikes: Under heavy write bursts, single-threaded replication appliers fall behind by minutes, invalidating read-your-own-writes consistency on read replicas.
• Maintenance Lockouts: Online DDL operations on multi-billion-row tables introduce catastrophic lock contentions and buffer pool thrashing.

At this inflection point, engineering organizations face a fundamental architectural crossroads:

flowchart TD
    subgraph ClientLayer ["Microservices Client Layer (Golang / Dapr / gRPC)"]
        Client["Application Workload (100k writes/sec)"]
    end

    subgraph ShardedTrack ["Paradigm 1: Sharded MySQL (Vitess Architecture)"]
        direction TB
        VTGate["VTGate Stateless L7 Proxy (VSchema Router)"]
        Shard1["Shard 1 (-80): Primary MySQL InnoDB (Sub-2ms Local ACID)"]
        Shard2["Shard 2 (80-): Primary MySQL InnoDB (Sub-2ms Local ACID)"]
        VRep["VReplication Engine (Online Zero-Downtime Split/Merge)"]
    end

    subgraph NewSQLTrack ["Paradigm 2: TiDB Distributed NewSQL Architecture"]
        direction TB
        TiDB_SQL["TiDB Stateless SQL Nodes (Parser/Cost Optimizer)"]
        PD["Placement Driver (PD Raft Cluster: TSO Allocator & Region Scheduler)"]
        TiKV1["TiKV Node 1 (Raft Group: 96MB Regions, RocksDB LSM)"]
        TiKV2["TiKV Node 2 (Raft Group: 96MB Regions, RocksDB LSM)"]
        TiFlash["TiFlash Columnar Store (Real-time Raft Learner OLAP)"]
    end

    Client -->|"Query with Shard Key (tenant_123)"| VTGate
    VTGate -->|"Direct Routing (Single RTT)"| Shard1
    
    Client -->|"Standard MySQL Protocol"| TiDB_SQL
    TiDB_SQL <-->|"Fetch Monotonic Timestamp (TSO)"| PD
    TiDB_SQL <-->|"Percolator 2PC (Prewrite + Commit + Raft Heartbeats)"| TiKV1
    TiDB_SQL <-->|"Percolator 2PC"| TiKV2
    TiKV1 -.->|"Raft Learner Replication"| TiFlash

1. Paradigm 1: Sharded MySQL with Vitess Proxy Layer: Retains standard standalone MySQL nodes at the storage tier while offloading shard routing, connection pooling, and online resharding to a stateless L7 proxy layer (VTGate) driven by declarative VSchema.
2. Paradigm 2: Distributed NewSQL (TiDB): Re-architects the database engine from scratch into a cloud-native distributed system separating stateless SQL execution (TiDB), global Raft-coordinated metadata scheduling (Placement Driver), and multi-Raft LSM-tree storage (TiKV / RocksDB).

2. Transaction Protocols: Local ACID vs. Google Percolator 2PC

The defining latency differentiator between Sharded MySQL and TiDB lies in their Transaction Coordination Mechanics.

[Vitess Single-Shard Write Flow: ~1.2ms P99]
App ──> VTGate ──> Shard 1 MySQL InnoDB (Local Redo Log Write + Flush) ──> App

[TiDB Percolator Write Flow: ~10.5ms P99]
App ──> TiDB Node ──(1) Get StartTS (Network RTT)──> PD Cluster
                  ──(2) Prewrite Lock (Network RTT)──> TiKV Raft Leader ──(Raft Log to Quorum)──> Follower
                  ──(3) Get CommitTS (Network RTT)──> PD Cluster
                  ──(4) Commit Primary Lock (Network RTT)──> TiKV Raft Leader ──> App

2.1. Vitess: Single-Shard Local ACID Execution

When queries include a designated Shard Key (tenant_id = 'tenant_99'):

• VTGate evaluates the VSchema hash function and routes the TCP stream directly to the authoritative MySQL shard.
• The shard executes a Local InnoDB ACID transaction, appending to the local redo log buffer in a single physical round-trip.
• Latency Floor: P99 write latency operates within 0.8 ms – 2.0 ms, matching raw bare-metal MySQL performance.

2.2. TiDB: The Distributed Transaction Tax (Percolator 2PC)

TiDB coordinates transactions via the Google Percolator two-phase commit protocol over Multi-Raft:

1. Start Timestamp (TSO): The TiDB SQL node requests a globally unique, monotonically increasing StartTS from the Placement Driver (PD) cluster over the network.
2. Prewrite Phase: TiDB designates a Primary Lock and sends prewrite requests across participating TiKV Raft leaders. Each leader writes the lock record to its local Raft log and replicates it across a majority quorum of followers.
3. Commit Timestamp: TiDB executes a second network call to PD to obtain the CommitTS.
4. Commit Phase: TiDB issues the final commit command to the Primary Lock Raft leader. Once the primary lock is committed, the transaction is logically committed. Asynchronous roll-forward commits secondary locks in the background.

• The Physical Latency Floor: Because even single-row updates require 4 to 6 distributed network hops across distinct node tiers, TiDB enforces an irreducible write latency floor of 6 ms – 15 ms.

2.3. Production Vitess VSchema Sharding Configuration

In Vitess, sharding is declaratively specified in a vschema.json file. The following production configuration demonstrates a hash-based primary sharding key (hash vindex on customer_id), a secondary lookup vindex for fast queries by email, and sequence tables for auto-increment IDs:

{
  "sharded": true,
  "vindexes": {
    "hash_index": {
      "type": "hash"
    },
    "lookup_customer_email": {
      "type": "lookup_hash_unique",
      "params": {
        "table": "customer_email_lookup",
        "from": "email",
        "to": "customer_id",
        "autocommit": "true"
      },
      "owner": "customers"
    }
  },
  "tables": {
    "customers": {
      "column_vindexes": [
        {
          "column": "customer_id",
          "name": "hash_index"
        },
        {
          "column": "email",
          "name": "lookup_customer_email"
        }
      ],
      "auto_increment": {
        "column": "customer_id",
        "sequence": "customer_seq"
      }
    },
    "orders": {
      "column_vindexes": [
        {
          "column": "customer_id",
          "name": "hash_index"
        }
      ]
    }
  }
}

2.4. Production TiDB Placement Policy & Storage Tiering DDL

In TiDB NewSQL, operators do not configure manual shard boundaries. Instead, they define declarative Placement Rules via SQL DDL to pin hot active tables to ultra-fast NVMe storage while tiering older historical partitions to cost-effective storage:

-- Define placement policy for NVMe SSD high-IOPS storage tier
CREATE PLACEMENT POLICY nvme_tier 
    PRIMARY_REGION="us-east-1" 
    REGIONS="us-east-1" 
    CONSTRAINTS="[+disk=nvme]";

-- Define placement policy for low-cost archive storage tier
CREATE PLACEMENT POLICY cold_archive_tier 
    PRIMARY_REGION="us-east-1" 
    CONSTRAINTS="[+disk=hdd]";

-- Partition orders table: hot current partitions pinned to NVMe, cold historical to HDD
CREATE TABLE enterprise_orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(32) NOT NULL
) PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p2025 VALUES LESS THAN ('2026-01-01') PLACEMENT POLICY = cold_archive_tier,
    PARTITION p2026 VALUES LESS THAN ('2027-01-01') PLACEMENT POLICY = nvme_tier,
    PARTITION p_future VALUES LESS THAN (MAXVALUE) PLACEMENT POLICY = nvme_tier
);

3. Resharding Mechanics: Dynamic 96MB Regions vs. VReplication

The operational complexity of expanding capacity reveals the deepest architectural divergence between Vitess and TiDB.

flowchart TD
    subgraph TiDB_Split ["TiDB: Zero-Touch 96MB Dynamic Region Splitting"]
        R1["Region 1: Key Range [0x00 - 0x80)<br/>Size: 96MB (Normal)"] -->|"Writes accumulate past 144MB"| Split["Split Trigger: Threshold Exceeded"]
        Split --> R1A["Region 1A: [0x00 - 0x40)<br/>Size: 72MB (Active)"]
        Split --> R1B["Region 1B: [0x40 - 0x80)<br/>Size: 72MB (Active)"]
        R1B -->|"PD Heartbeat Scheduler"| Rebalance["Raft Learner Catch-up -> Leader Transfer"]
        Rebalance --> TiKV_New["Migrated to Underutilized TiKV Node<br/>(Zero Application Impact)"]
    end

3.1. TiDB Dynamic Region Splitting & Multi-Raft Rebalancing

1. Continuous Byte-Range Partitioning: The global keyspace is organized into continuous byte ranges called Regions, each targeting a default size of 96MB.
2. Automatic Split Thresholds: When continuous inserts expand a Region beyond 144MB (or 1.44 million keys), TiKV autonomously splits the Region into two 72MB sibling Regions at the median key boundary.
3. Zero-Touch Background Rebalancing: The Placement Driver (PD) receives heartbeats from every Raft leader every 10 seconds. When PD detects storage or CPU imbalance across TiKV nodes, it orchestrates a non-blocking peer migration:
   • Adds a Raft Learner peer on the target TiKV node.
   • Streams snapshot SSTable data without write locking.
   • Promotes the Learner to a full Voter once caught up, and safely transfers the Raft leader role with zero application downtime.

3.2. Vitess VReplication Online Resharding Pipeline

In Vitess, resharding requires explicit operator intent but provides complete control over data locality and migration windows:

sequenceDiagram
    autonumber
    actor Admin as Database Administrator / CI/CD
    participant VTGate as VTGate Proxy Router
    participant SourceShard as Source Shards (-80)
    participant TargetShards as Target Shards (-40, 40-80)
    participant App as Application Workload

    Admin->>TargetShards: Provision New Shard Instances
    Admin->>VTGate: Reshard Create -source -80 -target -40,40-80
    TargetShards->>SourceShard: VReplication: Copy Table Snapshots
    loop Real-time Replication Catch-up
        SourceShard-->>TargetShards: Stream MySQL Binlog Events
    end
    Admin->>VTGate: SwitchTraffic (Reads: -40, 40-80)
    VTGate->>TargetShards: Route SELECT Queries
    Admin->>VTGate: SwitchTraffic (Writes: Cutover)
    Note over VTGate,SourceShard: Brief Lock Window (< 2 seconds)
    VTGate->>TargetShards: Route All INSERT / UPDATE / DELETE
    Admin->>SourceShard: Decommission Old Shard (-80)

1. Declarative Target Provisioning: The administrator specifies the new keyspace partition boundaries (e.g. splitting Shard -80 into -40 and 40-80).
2. Snapshot Copying & Catch-up: The VReplication engine copies static table snapshots while continuously reading binlog row events from the source MySQL instances.
3. Atomic Traffic Cutover: Once the replication lag drops below 1 second, VTGate executes SwitchTraffic. A momentary write pause ($<2$ seconds) allows pending replication queues to drain before VTGate flips internal routing tables to the new shards.

3.3. TiKV RocksDB LSM-Tree Compaction & Tail Latency Engineering

TiKV implements its local node storage using RocksDB (Log-Structured Merge-tree). While LSM structures absorb massive insert rates in memory (MemTable), they introduce compaction debt that must be rigorously managed to prevent catastrophic tail latency spikes ($P99.9 > 500\text{ms}$):

[RocksDB Write Pipeline in TiKV]
Client Write ──> Write-Ahead Log (WAL) + Active MemTable (RAM)
                         │ (Flushed when 128MB Full)
                         ▼
                   Level 0 SSTables (Disk - Overlapping Key Ranges)
                         │ (Compacted when L0 files >= 4)
                         ▼
                   Level 1 SSTables (Disk - Non-overlapping Ranges)
                         │ (Compacted down to Level 6 via Zstd)
                         ▼
                   Level 2 ... Level 6 SSTables

Production Compaction Tuning (tikv.toml):

[rocksdb]
# Max background threads for flush and compaction
max-background-jobs = 8

[rocksdb.defaultcf]
# Size of in-memory MemTable before triggering flush
write-buffer-size = "128MB"
max-write-buffer-number = 5

# Prevent write stall spikes by smoothing L0 compaction trigger
level0-file-num-compaction-trigger = 4
level0-slowdown-writes-trigger = 20
level0-stop-writes-trigger = 36

# Block cache sizing (allocate 45-50% of node RAM)
block-cache-size = "32GB"
cache-index-and-filter-blocks = true
pin-l0-filter-and-index-blocks-in-cache = true

# Zstandard compression on lower levels to maximize SSD savings
compression-per-level = ["no", "no", "lz4", "lz4", "zstd", "zstd", "zstd"]

4. Blast Radius & Fault Domain Isolation

The structural resilience of the database tier under catastrophic infrastructure degradation depends directly on cluster failure domains:

[Vitess Blast Radius: Isolated Shard Failure]
Shard 1 (-40) ────> [CRASHED / CORRUPTED]  ──> Only 25% of users (Tenant A) impacted
Shard 2 (40-80) ──> [RUNNING NORMALLY]    ──> 75% of platform operates at 100% SLA

[TiDB Blast Radius: Shared Cluster Dependencies]
PD Leader Latency Spike / Raft Storm ──> GLOBAL CLUSTER STALL (100% Impact)

• Vitess (Strictly Partitioned Blast Radius):
  • Each shard is an isolated MySQL cluster with independent memory, disk, and CPU subsystems.
  • An out-of-memory crash, bad query table lock, or storage corruption on Shard 1 affects only the fraction of tenants assigned to that shard. The rest of the platform functions uninterrupted.
• TiDB (Shared Cluster Failure Modes):
  • TiDB shares a central Placement Driver (PD) metadata tier and a unified Multi-Raft network.
  • A PD leader bottleneck, cross-AZ packet loss storm, or unindexed analytical query consuming TiDB memory can trigger cascading cluster-wide stall conditions affecting 100% of tenants simultaneously.

5. High-Concurrency Distributed Transaction Benchmark (Go 1.25)

To quantitatively evaluate transaction latency and write conflict resolution under high concurrency, consider the following production Go 1.25 benchmark client:

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"math/rand"
	"sync"
	"sync/atomic"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type BenchmarkStats struct {
	CommittedTx   uint64
	ConflictRetry uint64
	FailedTx      uint64
	LatencySumUs  uint64
}

// ExecuteTransactionalTransfer executes a 2-legged account transfer with exponential jittered retry
func ExecuteTransactionalTransfer(ctx context.Context, db *sql.DB, fromID, toID int64, amount float64, stats *BenchmarkStats) error {
	maxRetries := 5
	backoff := 2 * time.Millisecond

	for attempt := 0; attempt < maxRetries; attempt++ {
		start := time.Now()
		err := func() error {
			txCtx, cancel := context.WithTimeout(ctx, 3*time.Second)
			defer cancel()

			tx, err := db.BeginTx(txCtx, &sql.TxOptions{Isolation: sql.LevelRepeatableRead})
			if err != nil {
				return err
			}
			defer tx.Rollback()

			// Debit source account
			res1, err := tx.ExecContext(txCtx, "UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
			if err != nil {
				return err
			}
			if rows, _ := res1.RowsAffected(); rows == 0 {
				return errors.New("source account not found")
			}

			// Credit target account
			res2, err := tx.ExecContext(txCtx, "UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
			if err != nil {
				return err
			}
			if rows, _ := res2.RowsAffected(); rows == 0 {
				return errors.New("target account not found")
			}

			return tx.Commit()
		}()

		elapsed := time.Since(start).Microseconds()

		if err == nil {
			atomic.AddUint64(&stats.CommittedTx, 1)
			atomic.AddUint64(&stats.LatencySumUs, uint64(elapsed))
			return nil
		}

		// Check for TiDB Percolator write conflicts / MySQL deadlocks
		atomic.AddUint64(&stats.ConflictRetry, 1)
		jitter := time.Duration(rand.Int63n(int64(backoff)))
		select {
		case <-ctx.Done():
			atomic.AddUint64(&stats.FailedTx, 1)
			return ctx.Err()
		case <-time.After(backoff + jitter):
			backoff *= 2
		}
	}

	atomic.AddUint64(&stats.FailedTx, 1)
	return fmt.Errorf("transaction aborted after %d retries", maxRetries)
}

func main() {
	// Connect to Vitess VTGate or TiDB SQL endpoint via standard MySQL wire protocol
	dsn := "root@tcp(127.0.0.1:4000)/test_db?charset=utf8mb4&parseTime=True"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatalf("Database connection error: %v", err)
	}
	defer db.Close()

	db.SetMaxOpenConns(500)
	db.SetMaxIdleConns(50)

	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	var stats BenchmarkStats
	var wg sync.WaitGroup
	workers := 100

	log.Printf("Starting transaction benchmark with %d concurrent workers...", workers)
	benchStart := time.Now()

	for i := 0; i < workers; i++ {
		wg.Add(1)
		go func(workerID int) {
			defer wg.Done()
			for {
				select {
				case <-ctx.Done():
					return
				default:
					from := rand.Int63n(10000) + 1
					to := rand.Int63n(10000) + 1
					if from == to {
						to = (to % 10000) + 1
					}
					_ = ExecuteTransactionalTransfer(ctx, db, from, to, 10.0, &stats)
				}
			}
		}(i)
	}

	wg.Wait()
	duration := time.Since(benchStart).Seconds()

	committed := atomic.LoadUint64(&stats.CommittedTx)
	retries := atomic.LoadUint64(&stats.ConflictRetry)
	failed := atomic.LoadUint64(&stats.FailedTx)
	avgLat := float64(atomic.LoadUint64(&stats.LatencySumUs)) / float64(committed)

	fmt.Printf("--- Benchmark Results (Duration: %.2fs) ---\n", duration)
	fmt.Printf("Committed Transactions: %d (%.2f TPS)\n", committed, float64(committed)/duration)
	fmt.Printf("Conflict Retries:       %d\n", retries)
	fmt.Printf("Failed Transactions:     %d\n", failed)
	fmt.Printf("Average Commit Latency:  %.2f µs (%.3f ms)\n", avgLat, avgLat/1000.0)
}

6. FinOps & Infrastructure Footprint

Operating a distributed NewSQL cluster requires substantially higher minimum server footprints compared to sharded MySQL:

7. Benchmark Showdown (10,000 Concurrent Connections)

Tested under sysbench-tpcc on 32 vCPUs, 64GB RAM, NVMe SSD storage:

8. Architectural Decision Matrix

flowchart TD
    Start{"Which Scale-Out Database Architecture to Select?"}
    
    Start --> CheckShard{"Does the data model cleanly partition by Shard Key?<br/>(e.g., tenant_id, user_id, organization_id)"}
    
    CheckShard -->|"YES (95%+ Single-Shard Queries)"| CheckLat{"Is sub-2ms write latency a mandatory SLA?<br/>(e.g., Checkout / Payment Engines)"}
    CheckLat -->|"YES"| R_Vitess["<b>SELECT SHARDED MYSQL (VITESS)</b><br/>• Sub-2ms local ACID writes<br/>• Isolated failure blast radius<br/>• 60% lower FinOps infrastructure spend"]
    CheckLat -->|"NO (8-15ms acceptable)"| CheckAuto{"Is zero-touch auto-resharding prioritized?"}
    CheckAuto -->|"YES"| R_TiDB_Auto["<b>SELECT TIDB NEWSQL</b><br/>Zero-touch 96MB Region auto-split"]
    
    CheckShard -->|"NO (Cross-Shard Joins & Unpartitionable Schema)"| R_TiDB_Join["<b>SELECT TIDB NEWSQL</b><br/>• Distributed SQL execution engine<br/>• Real-time HTAP TiFlash columnar analytics"]

9. Frequently Asked Questions (FAQ)

🔗 Related Masterclasses & Architecture Pillars

• 🚀 Deep-Dive Distributed Systems:
  • Explore event-driven scaling at: Part 6: Apache Kafka vs. NATS JetStream Showdown
  • Master microservice performance engineering at: Go Microservices Architecture: Zero Allocations & Production Design
• 📚 Knowledge Hub:
  • Curated learning roadmap: Systems Architecture Reading Map
• 💼 Advisory & Consulting:
  • High-concurrency database architecture consulting: Lê Tuấn Anh — Architecture Consulting & Engineering