CockroachDB: Distributed SQL for Global Scale

In the modern cloud-native world, applications need to serve users globally with low latency and high availability. Traditional SQL databases like PostgreSQL or MySQL are great for consistency but struggle to scale horizontally. NoSQL databases like MongoDB scale well but sacrifice ACID transactions. CockroachDB bridges this gap: it's a distributed SQL database that offers full ACID transactions, horizontal scalability, and resilience to failures. Inspired by Google's Spanner, CockroachDB uses a combination of Raft consensus and distributed transactions to provide a familiar SQL interface with PostgreSQL compatibility. In this tutorial, you'll learn how CockroachDB works under the hood, how to set it up, and how to build production-ready applications that require global scale without compromising on consistency.

What is CockroachDB?

CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally, survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention. CockroachDB is wire-compatible with PostgreSQL, meaning you can use existing PostgreSQL drivers and tools. It supports full SQL, including joins, subqueries, and ACID transactions. The database automatically replicates and rebalances data across nodes, ensuring high availability and consistent performance. CockroachDB is ideal for applications that require global scale, such as SaaS platforms, e-commerce, and financial services.

-- Connect to CockroachDB using PostgreSQL client
-- psql -h localhost -p 26257 -d defaultdb -U root

-- Create a simple table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    email STRING UNIQUE,
    created_at TIMESTAMP DEFAULT now()
);

-- Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Query data
SELECT * FROM users;

Architecture: How CockroachDB Works

CockroachDB's architecture is based on a sorted key-value store divided into ranges (default 64 MB). Each range is replicated across multiple nodes using the Raft consensus algorithm. Writes go through a Raft leader, ensuring strong consistency. The database automatically splits and merges ranges based on size and load. SQL queries are parsed and optimized into distributed execution plans that operate on ranges in parallel. CockroachDB uses a distributed transaction protocol based on Percolator (Google) to provide serializable isolation. The cluster is managed by a gossip protocol that shares node and range metadata.

-- View cluster ranges
SHOW RANGES FROM TABLE users;

-- View node status
SELECT node_id, address, locality, is_live FROM crdb_internal.gossip_nodes;

-- Check replication factor
SHOW ZONE CONFIGURATION FOR TABLE users;

Setting Up a CockroachDB Cluster

To set up a CockroachDB cluster, download the binary or use Docker. Start multiple nodes, each with a unique store and address. Use the --join flag to connect nodes. For production, deploy across multiple data centers with locality settings. Enable encryption, authentication, and use secure connections. CockroachDB provides a built-in SQL shell and a web UI for monitoring.

# Start first node (insecure mode for demo)
cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259

# Start second node
cockroach start --insecure --store=node2 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259

# Start third node
cockroach start --insecure --store=node3 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259

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

# Connect to SQL shell
cockroach sql --insecure --host=localhost:26257

SQL Operations and Transactions

CockroachDB supports standard SQL DDL and DML operations. Transactions are fully ACID with SERIALIZABLE isolation by default. Use BEGIN and COMMIT to group statements. CockroachDB automatically retries transactions that encounter conflicts, but application code should handle retryable errors (code 40001). Use SAVEPOINT for partial rollback. CockroachDB also supports distributed transactions across ranges and even across data centers.

-- Start a transaction
BEGIN;

-- Insert with potential conflict
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

-- Update another row
UPDATE accounts SET balance = balance - 100 WHERE user_id = 'some-uuid';

-- Commit
COMMIT;

-- If retryable error occurs (40001), retry the entire transaction
-- Example retry logic in application (pseudo-code):
-- while true:
--     try:
--         BEGIN
--         ...
--         COMMIT
--         break
--     except RetryableError:
--         continue

Indexing and Performance Tuning

CockroachDB supports secondary indexes, including unique, composite, and partial indexes. Indexes are automatically distributed across nodes. Use EXPLAIN ANALYZE to understand query plans. CockroachDB provides automatic statistics collection for the optimizer. For performance, ensure that queries use indexes effectively. Avoid full table scans on large tables. Use SHOW INDEXES to inspect indexes. CockroachDB also supports hash-sharded indexes to reduce hotspots on sequential keys.

-- Create a secondary index
CREATE INDEX idx_users_email ON users (email);

-- Create a composite index
CREATE INDEX idx_users_name_created ON users (name, created_at);

-- Create a hash-sharded index to avoid hotspots
CREATE INDEX idx_users_id_hash ON users (id) USING HASH WITH BUCKET_COUNT = 8;

-- Explain a query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

Geo-partitioning and Multi-region Deployment

CockroachDB supports geo-partitioning to keep data close to users, reducing latency. You can define zone configurations to control replication and placement. Use ALTER TABLE ... CONFIGURE ZONE to set constraints like num_replicas and constraints. For multi-region deployments, use the REGIONAL BY TABLE or REGIONAL BY ROW features. CockroachDB also supports global tables for low-latency reads across regions.

-- Set zone configuration for a table to keep data in us-east
ALTER TABLE users CONFIGURE ZONE USING constraints = '[+region=us-east]';

-- Create a regional by row table (CockroachDB 21.1+)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    region STRING NOT NULL,
    ...
) LOCALITY REGIONAL BY ROW;

-- Set the region for a row
UPDATE orders SET crdb_region = 'us-east' WHERE id = '...';

Monitoring and Maintenance

CockroachDB provides a built-in web UI on port 8080 (by default) for monitoring cluster health, SQL metrics, and node status. Use cockroach node status and cockroach debug zip for diagnostics. Regularly back up your data using BACKUP and RESTORE commands. CockroachDB supports incremental backups and point-in-time recovery. For maintenance, you can decommission nodes gracefully with cockroach node decommission.

-- Create a full backup
BACKUP DATABASE defaultdb TO 's3://bucket/backup';

-- Restore from backup
RESTORE DATABASE defaultdb FROM 's3://bucket/backup';

-- Decommission a node (run from a different node)
cockroach node decommission 1 --host=localhost:26257

-- Check node status
cockroach node status --host=localhost:26257