Eric Campbell 5 min read Databases

NewSQL Databases: CockroachDB and TiDB

Traditional relational databases gave us ACID guarantees but hit scaling walls. NoSQL databases offered horizontal scalability but sacrificed consistency and familiar SQL interfaces. NewSQL emerged...

Key Insights

  • NewSQL databases like CockroachDB and TiDB deliver distributed ACID transactions with horizontal scalability, solving the false choice between consistency and scale that plagued earlier distributed systems
  • CockroachDB offers PostgreSQL wire compatibility and excels at multi-region deployments with built-in geo-partitioning, while TiDB provides MySQL compatibility and hybrid transactional/analytical processing (HTAP) through TiFlash
  • Choose CockroachDB for globally distributed applications requiring strong consistency across regions; choose TiDB when you need real-time analytics alongside transactional workloads or have existing MySQL infrastructure

Understanding the NewSQL Category

Traditional relational databases gave us ACID guarantees but hit scaling walls. NoSQL databases offered horizontal scalability but sacrificed consistency and familiar SQL interfaces. NewSQL emerged to bridge this gap, proving that the CAP theorem doesn’t force you to completely abandon consistency for availability and partition tolerance—you can make intelligent trade-offs.

NewSQL databases use distributed consensus protocols, sophisticated sharding strategies, and modern storage engines to deliver both strong consistency and horizontal scalability. CockroachDB and TiDB represent two mature, production-ready implementations of this vision, each with distinct architectural choices and strengths.

CockroachDB: Distributed SQL with PostgreSQL Compatibility

CockroachDB implements a distributed SQL layer on top of a transactional key-value store. Every node in a CockroachDB cluster can serve both reads and writes, with data automatically replicated and rebalanced across nodes using the Raft consensus algorithm.

The architecture uses range-based sharding, where data is divided into 64MB ranges that are replicated across multiple nodes (typically 3 or 5). Each range has a leader that coordinates writes using Raft, ensuring strong consistency without requiring a central coordinator.

Let’s start a local 3-node cluster to demonstrate CockroachDB’s capabilities:

# Start first node
cockroach start --insecure --store=node1 --listen-addr=localhost:26257 \
  --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

# Start second and third nodes
cockroach start --insecure --store=node2 --listen-addr=localhost:26258 \
  --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

cockroach start --insecure --store=node3 --listen-addr=localhost:26259 \
  --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259 \
  --background

# Initialize the cluster
cockroach init --insecure --host=localhost:26257

One of CockroachDB’s killer features is geo-partitioning, which lets you control data placement for compliance and latency optimization:

-- Create a multi-region database
CREATE DATABASE global_app PRIMARY REGION "us-east" REGIONS "us-west", "eu-west";

-- Create a table with geo-partitioned data
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING NOT NULL,
    region STRING NOT NULL,
    created_at TIMESTAMP DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

-- Insert data that will be automatically partitioned
INSERT INTO users (email, region) VALUES 
    ('user1@example.com', 'us-east'),
    ('user2@example.com', 'eu-west'),
    ('user3@example.com', 'us-west');

-- Query shows data locality
SELECT region, count(*) FROM users GROUP BY region;

CockroachDB’s survivability is remarkable. Let’s demonstrate resilience to node failure:

-- Create a test table
CREATE TABLE resilience_test (id INT PRIMARY KEY, value STRING);
INSERT INTO resilience_test VALUES (1, 'before failure');

-- Now kill node 2 (simulate failure)
-- cockroach quit --insecure --host=localhost:26258

-- Queries still work with one node down
SELECT * FROM resilience_test;
INSERT INTO resilience_test VALUES (2, 'during failure');

-- Data remains consistent when node returns

TiDB: MySQL-Compatible Distributed Database with HTAP

TiDB takes a different architectural approach with three key components: the TiDB server layer (stateless SQL processing), TiKV (distributed transactional key-value store using Raft), and PD (Placement Driver for metadata and cluster management).

What sets TiDB apart is its HTAP capability. TiFlash, a columnar storage extension, replicates data from TiKV in real-time, enabling analytical queries without impacting transactional workloads.

Deploy TiDB using TiUP, the official cluster management tool:

# Install TiUP
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh

# Deploy a local test cluster
tiup playground v7.5.0 --db 2 --pd 3 --kv 3 --tiflash 1

# Or deploy a production cluster with topology file
tiup cluster deploy prod-cluster v7.5.0 ./topology.yaml --user tidb
tiup cluster start prod-cluster

Here’s a production topology example:

# topology.yaml
global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

pd_servers:
  - host: 10.0.1.1
  - host: 10.0.1.2
  - host: 10.0.1.3

tidb_servers:
  - host: 10.0.1.4
  - host: 10.0.1.5

tikv_servers:
  - host: 10.0.1.6
  - host: 10.0.1.7
  - host: 10.0.1.8

tiflash_servers:
  - host: 10.0.1.9
    data_dir: /tiflash-data

TiDB’s HTAP capability shines with TiFlash. Here’s how to leverage it:

-- Create a table with transactional data
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

-- Create TiFlash replica for analytics
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Check replica status
SELECT * FROM information_schema.tiflash_replica WHERE TABLE_NAME = 'orders';

-- This analytical query automatically uses TiFlash
SELECT 
    DATE(created_at) as order_date,
    status,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY order_date, status
ORDER BY order_date DESC;

-- Verify TiFlash usage
EXPLAIN SELECT DATE(created_at), COUNT(*) FROM orders GROUP BY DATE(created_at);

Horizontal scaling in TiDB is straightforward:

# Scale out by adding TiKV nodes
tiup cluster scale-out prod-cluster scale-out.yaml

# scale-out.yaml content:
tikv_servers:
  - host: 10.0.1.10
  - host: 10.0.1.11

# TiDB automatically rebalances data across new nodes

Performance Characteristics and Benchmarks

Both databases handle OLTP workloads effectively, but with different characteristics. CockroachDB typically shows higher latency due to its distributed consensus overhead, especially for single-row writes. TiDB often delivers lower latency for point queries due to its optimized TiKV storage engine.

Here’s a YCSB benchmark setup for comparison:

# CockroachDB YCSB test
./bin/ycsb load jdbc -s -P workloads/workloada \
  -p db.driver=org.postgresql.Driver \
  -p db.url="jdbc:postgresql://localhost:26257/ycsb?sslmode=disable" \
  -p db.user=root

./bin/ycsb run jdbc -s -P workloads/workloada \
  -p db.driver=org.postgresql.Driver \
  -p db.url="jdbc:postgresql://localhost:26257/ycsb?sslmode=disable" \
  -p db.user=root -threads 64

# TiDB YCSB test  
./bin/ycsb load jdbc -s -P workloads/workloada \
  -p db.driver=com.mysql.cj.jdbc.Driver \
  -p db.url="jdbc:mysql://localhost:4000/ycsb" \
  -p db.user=root

./bin/ycsb run jdbc -s -P workloads/workloada \
  -p db.driver=com.mysql.cj.jdbc.Driver \
  -p db.url="jdbc:mysql://localhost:4000/ycsb" \
  -p db.user=root -threads 64

In typical benchmarks, CockroachDB excels at multi-region consistency and handles network partitions gracefully. TiDB shows superior performance for mixed workloads combining transactional and analytical queries, especially when TiFlash is utilized.

Production Considerations and Use Cases

Choose CockroachDB when:

  • You need true multi-region active-active deployments
  • Regulatory compliance requires data residency controls
  • Your team has PostgreSQL expertise
  • Survivability and consistency trump absolute performance

Choose TiDB when:

  • You’re migrating from MySQL and want compatibility
  • Real-time analytics on transactional data is critical
  • You need predictable latency for point queries
  • HTAP workloads are part of your architecture

Migration from PostgreSQL to CockroachDB is relatively smooth:

# Export PostgreSQL schema and data
pg_dump -h localhost -U postgres -d myapp --schema-only > schema.sql
pg_dump -h localhost -U postgres -d myapp --data-only > data.sql

# Modify schema for CockroachDB compatibility (if needed)
# Import to CockroachDB
cockroach sql --insecure --host=localhost:26257 < schema.sql
cockroach sql --insecure --host=localhost:26257 < data.sql

Backup strategies differ between the two:

# CockroachDB backup to cloud storage
BACKUP DATABASE myapp TO 's3://backup-bucket/myapp?AWS_ACCESS_KEY_ID=xxx&AWS_SECRET_ACCESS_KEY=yyy';

# TiDB backup using BR (Backup & Restore)
tiup br backup full --pd "127.0.0.1:2379" \
  --storage "s3://backup-bucket/tidb-backup" \
  --s3.region "us-west-2"

Operational complexity is real for both. CockroachDB requires understanding of range splits, replication zones, and lease preferences. TiDB adds complexity with its multi-component architecture, though TiUP significantly simplifies operations.

Making the Choice

Both CockroachDB and TiDB represent the maturation of NewSQL. They’ve proven themselves in production at companies like DoorDash (CockroachDB) and Square (TiDB). Your choice should align with your specific requirements:

For global applications with strong consistency requirements across continents, CockroachDB’s architecture is purpose-built for that challenge. For applications needing real-time analytics alongside transactions, or those with heavy MySQL investment, TiDB offers a compelling path forward.

The NewSQL category has moved beyond proof-of-concept. These databases handle production workloads at scale, and their ecosystems continue maturing with better tooling, monitoring, and operational support. The question isn’t whether NewSQL is ready—it’s which NewSQL database fits your architecture.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.