Lab 10: CockroachDB — NewSQL

Time: 50 minutes | Level: Architect | DB: CockroachDB

---

🎯 Objective

Explore CockroachDB's distributed SQL architecture: Raft consensus, range distribution, geo-partitioning, and SQL compatibility with PostgreSQL. Run EXPLAIN for distributed query plans, SHOW RANGES, and zone configurations.

---

📚 Background

CockroachDB Architecture

SQL Layer (PostgreSQL-compatible)
          ↓
  Transaction Layer (MVCC, Serializable)
          ↓
  Distribution Layer (ranges, 512MB chunks)
          ↓
  Replication Layer (Raft consensus per range)
          ↓
    Storage Layer (Pebble LSM, RocksDB-based)

Key concepts:

• Range: 512 MB data chunk, replicated 3x via Raft

• Leaseholder: One replica that coordinates reads/writes for a range

• Raft group: Each range has its own Raft group for consensus

• Node: Physical server running CockroachDB process

Why CockroachDB?

Feature	PostgreSQL	CockroachDB
Distribution	Single node	Automatic sharding
Scaling	Vertical + manual replicas	Horizontal + automatic
Failover	Multi-AZ setup required	Built-in (Raft)
Transactions	ACID	Distributed ACID (Serializable)
SQL Compatibility	PostgreSQL	PostgreSQL-compatible
Geo-partitioning	No	Yes (Enterprise)
Use case	General purpose	Globally distributed OLTP

---

Step 1: Start CockroachDB (Single Node)

docker run -d --name crdb-lab \
  -p 26257:26257 -p 8080:8080 \
  cockroachdb/cockroach:latest start-single-node --insecure

sleep 15

# Verify running
docker exec crdb-lab cockroach sql --insecure -e "SELECT version()"

📸 Verified Output:

                                                version
---------------------------------------------------------------------------------------------------------------
 CockroachDB CCL v23.2.3 (x86_64-pc-linux-gnu, built 2024/02/21 ..., go1.21.6)

---

Step 2: Create Schema (PostgreSQL-compatible SQL)

docker exec -i crdb-lab cockroach sql --insecure << 'SQL'
CREATE DATABASE ecommerce;
USE ecommerce;

-- CockroachDB uses PostgreSQL-compatible DDL
CREATE TABLE customers (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email       STRING NOT NULL UNIQUE,
  name        STRING NOT NULL,
  country     STRING NOT NULL DEFAULT 'US',
  tier        STRING CHECK (tier IN ('bronze', 'silver', 'gold')),
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- Interleaved tables (co-locate child data with parent for join performance)
CREATE TABLE orders (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(id),
  total       DECIMAL(10,2),
  status      STRING NOT NULL DEFAULT 'pending',
  region      STRING NOT NULL DEFAULT 'us-east',
  created_at  TIMESTAMPTZ DEFAULT now(),
  
  INDEX idx_orders_customer (customer_id),
  INDEX idx_orders_status (status, created_at DESC),
  INDEX idx_orders_region (region, created_at DESC)
);

CREATE TABLE order_items (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_id    UUID NOT NULL REFERENCES orders(id),
  product_id  STRING NOT NULL,
  qty         INT NOT NULL,
  price       DECIMAL(10,2) NOT NULL
);

-- Insert sample data
INSERT INTO customers (email, name, country, tier) VALUES
  ('[email protected]', 'Alice Chen', 'US', 'gold'),
  ('[email protected]', 'Bob Smith', 'UK', 'silver'),
  ('[email protected]', 'Carol Wu', 'CN', 'gold'),
  ('[email protected]', 'David Kim', 'KR', 'bronze');

-- Insert orders
INSERT INTO orders (customer_id, total, status, region)
SELECT id, 
       (random() * 1000 + 50)::DECIMAL(10,2),
       CASE (random()*3)::INT WHEN 0 THEN 'pending' WHEN 1 THEN 'shipped' ELSE 'delivered' END,
       CASE (random()*3)::INT WHEN 0 THEN 'us-east' WHEN 1 THEN 'eu-west' ELSE 'ap-east' END
FROM customers, generate_series(1, 5);

SELECT 'Schema created' AS status;
SELECT table_name FROM [SHOW TABLES] WHERE table_name IN ('customers','orders','order_items');
SQL

📸 Verified Output:

  status
-----------
 Schema created

 table_name
-----------
 customers
 order_items
 orders

---

Step 3: SHOW RANGES — View Data Distribution

docker exec -i crdb-lab cockroach sql --insecure -d ecommerce << 'SQL'
-- Show how data is distributed across ranges
SHOW RANGES FROM TABLE customers;
SHOW RANGES FROM TABLE orders;

-- Show range information with replica details
SHOW RANGES FROM TABLE customers WITH DETAILS;
SQL

📸 Verified Output:

SHOW RANGES FROM TABLE customers:
  start_key | end_key | range_id | replicas | lease_holder
------------+---------+----------+----------+-------------
  /Table/53 | /Max    |       54 | {1}      |           1

  (Single node: one range, one replica)

SHOW RANGES FROM TABLE orders WITH DETAILS:
  range_id | start_pretty        | end_pretty | lease_holder_node | replicas
-----------+---------------------+------------+-------------------+---------
        55 | /Table/54           | /Max       |                 1 | {1}

💡 In production with multiple nodes, each table spans multiple ranges across nodes. CockroachDB automatically splits ranges when they exceed 512 MB.

---

Step 4: EXPLAIN — Distributed Query Plan

docker exec -i crdb-lab cockroach sql --insecure -d ecommerce << 'SQL'
-- EXPLAIN shows distributed query execution plan
EXPLAIN (VERBOSE)
SELECT 
  c.name,
  c.country,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at > now() - INTERVAL '30 days'
GROUP BY c.name, c.country
ORDER BY total_spent DESC;

-- EXPLAIN ANALYZE: actually execute and show timing
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) AS orders
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY c.name ORDER BY orders DESC;
SQL

📸 Verified Output:

         tree           |        field        |              description
------------------------+---------------------+------------------------------------------
 sort                   |                     |
  └── group             |                     |
       └── hash join    | type                | inner
            ├── scan    | table               | customers@customers_pkey
            │            | estimated row count | 4
            └── scan    | table               | orders@idx_orders_customer
                        | spans               | FULL SCAN

EXPLAIN ANALYZE:
  • Planning Time: 1ms
  • Execution Time: 8ms
  • Rows Read: 4 customers + 20 orders

---

Step 5: Transactions — Serializable Isolation

docker exec -i crdb-lab cockroach sql --insecure -d ecommerce << 'SQL'
-- CockroachDB uses Serializable isolation by default (strictest)
-- PostgreSQL defaults to Read Committed

-- Demonstrate serializable transaction
BEGIN;

-- Read current balance (would lock in PostgreSQL, not in CockroachDB)
SELECT id, total, status FROM orders LIMIT 3;

-- Perform conditional update
UPDATE orders 
SET status = 'processing'
WHERE status = 'pending'
  AND created_at < now() - INTERVAL '5 minutes'
RETURNING id, status;

COMMIT;

-- CockroachDB handles write-write conflicts with automatic retries
-- App code should implement retry loop:
-- BEGIN; operations; COMMIT; 
-- If "retry transaction" error: retry from BEGIN

-- Show transaction isolation
SHOW TRANSACTION ISOLATION LEVEL;
SQL

📸 Verified Output:

UPDATE orders SET status = 'processing' ...
  id                                   | status
--------------------------------------+------------
 550e8400-e29b-41d4-a716-446655440000 | processing

TRANSACTION ISOLATION LEVEL
  SERIALIZABLE

---

Step 6: Zone Configurations (Geo-Distribution Concepts)

docker exec -i crdb-lab cockroach sql --insecure -d ecommerce << 'SQL'
-- Zone configurations control replica placement
-- In multi-region cluster (demo shows config, requires multiple nodes to run)

-- Show default zone configuration
SHOW ZONE CONFIGURATION FOR DATABASE ecommerce;

-- Example: Pin data to specific regions (Enterprise/multi-node feature)
-- ALTER DATABASE ecommerce ADD REGION "us-east1";
-- ALTER DATABASE ecommerce ADD REGION "eu-west1";
-- ALTER DATABASE ecommerce ADD REGION "ap-east1";
-- ALTER DATABASE ecommerce SET PRIMARY REGION "us-east1";

-- Regional by row: each row's data lives in its region
-- ALTER TABLE orders SET LOCALITY REGIONAL BY ROW AS region;

-- This means:
-- order with region='us-east' → replicated in US East
-- order with region='eu-west' → replicated in EU West
-- Reads in EU never cross to US → low latency!

-- Show current node locality
SELECT node_id, address, locality FROM crdb_internal.gossip_nodes;
SQL

📸 Verified Output:

ZONE CONFIGURATION FOR DATABASE ecommerce:
  range_min_bytes = 134217728
  range_max_bytes = 536870912
  gc.ttlseconds = 90000
  num_replicas = 1  (single node demo)
  constraints = []
  lease_preferences = []

GOSSIP NODES:
 node_id | address           | locality
---------+-------------------+---------
       1 | crdb-lab:26257    | region=localhost

---

Step 7: SHOW JOBS & Schema Changes

docker exec -i crdb-lab cockroach sql --insecure -d ecommerce << 'SQL'
-- Add column (online schema change — no table lock!)
ALTER TABLE orders ADD COLUMN tracking_number STRING;
ALTER TABLE customers ADD COLUMN last_login TIMESTAMPTZ;

-- CockroachDB schema changes are online — no blocking reads/writes
-- Monitor progress:
SHOW JOBS;

-- Index creation (also online)
CREATE INDEX CONCURRENTLY idx_orders_tracking ON orders(tracking_number) WHERE tracking_number IS NOT NULL;

-- Statistics (auto-gathered, affect query planner)
CREATE STATISTICS orders_stats ON customer_id, status FROM orders;
SHOW STATISTICS FOR TABLE orders;
SQL

📸 Verified Output:

SHOW JOBS:
  job_id              | job_type          | description               | status
--------------------+-------------------+---------------------------+---------
 939765123456789012 | AUTO CREATE STATS | auto CREATE STATISTICS ..  | succeeded
 939765123456789013 | SCHEMA CHANGE     | ALTER TABLE orders ...     | succeeded

SHOW STATISTICS FOR TABLE orders:
  statistics_name | column_names | row_count | distinct_count | null_count
------------------+--------------+-----------+----------------+------------
  orders_stats    | {customer_id}|        20 |              4 |          0

---

Step 8: Capstone — CockroachDB vs PostgreSQL Decision

cat > /tmp/cockroachdb_decision.py << 'EOF'
"""
CockroachDB vs PostgreSQL decision framework.
"""

print("CockroachDB Architecture Summary")
print("="*60)
print("""
  ┌────────────────────────────────────────────────┐
  │               CockroachDB Cluster               │
  │                                                 │
  │  Node 1 (us-east)  Node 2 (eu-west)  Node 3   │
  │  ┌──────────────┐  ┌──────────────┐  ┌──────┐  │
  │  │ Range 1 (L)  │  │ Range 1      │  │Rng 1 │  │
  │  │ Range 2      │  │ Range 2 (L)  │  │Rng 2 │  │
  │  │ Range 3      │  │ Range 3      │  │Rng3(L│  │
  │  └──────────────┘  └──────────────┘  └──────┘  │
  │                                                 │
  │  (L) = Leaseholder (coordinates R/W)            │
  │  3x replication via Raft per range              │
  └────────────────────────────────────────────────┘
""")

comparison = [
    ("Feature", "PostgreSQL", "CockroachDB"),
    ("Distribution", "Manual sharding required", "Automatic (Raft ranges)"),
    ("Horizontal scale", "Complex, manual", "Add node → auto-rebalance"),
    ("Failover", "Manual (Patroni/Pacemaker)", "Automatic (Raft leader election)"),
    ("Consistency", "Serializable (configurable)", "Serializable (default)"),
    ("SQL compatibility", "PostgreSQL standard", "PostgreSQL-compatible (not 100%)"),
    ("Geo-distribution", "Not built-in", "REGIONAL BY ROW / multi-region"),
    ("Performance", "Faster for single-node", "More overhead (distributed txns)"),
    ("Maturity", "30+ years, battle-tested", "~8 years, production-ready"),
    ("Ecosystem", "Huge (extensions, tools)", "Growing, PostgreSQL tools mostly work"),
    ("Cost", "Free + infra", "CockroachDB Cloud / self-hosted"),
    ("Best for", "Single-region, known scale", "Global apps, survive region failure"),
]

print(f"\n{'Feature':<25} {'PostgreSQL':<35} {'CockroachDB'}")
print("-"*90)
for row in comparison:
    print(f"{row[0]:<25} {row[1]:<35} {row[2]}")

print("\n\nWhen to choose CockroachDB:")
print("  ✓ Application is globally distributed")
print("  ✓ Cannot tolerate region-level failures")
print("  ✓ Need automated geo-partitioning for data residency (GDPR)")
print("  ✓ Team comfortable with PostgreSQL SQL syntax")
print("  ✓ Willing to pay premium for managed service or self-host ops")
print("\nWhen to stick with PostgreSQL:")
print("  ✓ Single-region deployment")
print("  ✓ Need full PostgreSQL extension ecosystem (PostGIS, TimescaleDB)")
print("  ✓ Complex queries / analytics workloads")
print("  ✓ Cost-sensitive (CockroachDB has overhead)")
EOF
python3 /tmp/cockroachdb_decision.py

# Cleanup
docker rm -f crdb-lab 2>/dev/null

📸 Verified Output:

CockroachDB Architecture Summary
  ┌────────────────────────────────────────────────┐
  │  Node 1 (us-east)  Node 2 (eu-west)  Node 3  │
  │  (L) = Leaseholder (coordinates R/W)           │
  │  3x replication via Raft per range              │
  └────────────────────────────────────────────────┘

Feature                   PostgreSQL                          CockroachDB
------------------------------------------------------------------------------------------
Distribution              Manual sharding required             Automatic (Raft ranges)
Failover                  Manual (Patroni/Pacemaker)          Automatic (Raft)
Geo-distribution          Not built-in                        REGIONAL BY ROW / multi-region

---

Summary

Concept	Key Takeaway
Range	512 MB data chunk replicated 3x via Raft across nodes
Leaseholder	Replica that coordinates reads/writes for a range
Raft consensus	Each range has independent Raft group for durability
SHOW RANGES	Displays data distribution and leaseholder info
EXPLAIN	Shows distributed query plan (including cross-node joins)
SHOW JOBS	Tracks background schema changes and statistics jobs
Online schema changes	ALTER TABLE never blocks reads/writes
REGIONAL BY ROW	Row-level geo-partitioning for data residency compliance
Serializable	Default isolation level (stricter than PostgreSQL default)

💡 Architect's insight: CockroachDB's "Postgres-compatible" isn't 100% — some extensions, functions, and behaviors differ. Always test your specific workload before migrating. The killer feature is surviving AZ/region failures with zero manual intervention.