CockroachDB: Distributed SQL Database Guide

CockroachDB has become the go‑to choice for teams that need a SQL database with the resilience of a NoSQL system. It blends the familiarity of PostgreSQL syntax with a truly distributed architecture, letting you scale horizontally without sacrificing ACID guarantees. In this guide we’ll walk through the core concepts, spin up a local cluster, and build a few real‑world examples that showcase its power.

What Makes CockroachDB Different?

At its heart, CockroachDB is a distributed relational database built on a shared‑nothing architecture. Each node stores a subset of the data and participates in a consensus protocol (Raft) to ensure consistency. This means you can add or remove nodes on the fly, and the system automatically rebalances data while preserving strong consistency.

Unlike traditional single‑node PostgreSQL deployments, CockroachDB offers automatic replication, survivable node failures, and geo‑partitioning out of the box. It also supports the full PostgreSQL wire protocol, so existing ORMs and tools work with minimal changes.

Key Terminology

• Range: The basic unit of data distribution, typically 64 MiB, replicated across multiple nodes.
• Leaseholder: The node that currently owns the lease for a range and serves reads/writes for it.
• Zone Config: Policies that dictate replication factor, survivability, and locality for each table or index.

Pro tip: Use SHOW ZONE CONFIGURATION early in your project to verify that critical tables are replicated across the desired regions.

Installing CockroachDB Locally

The quickest way to get started is with the official binary. Download the latest release for your OS, unzip, and add it to your PATH. For macOS and Linux you can also use Homebrew or apt.

# Example for macOS using Homebrew
$ brew install cockroachdb

# Verify the installation
$ cockroach version
CockroachDB v23.2.0 (x86_64-apple-darwin)

Once installed, spin up a three‑node cluster on your laptop using Docker. This mimics a production topology while keeping the setup simple.

# Start three containers
$ docker run -d --name=roach1 -p 26257:26257 -p 8080:8080 cockroachdb/cockroach start --insecure --join=roach1,roach2,roach3 --store=type=mem,size=1Gi
$ docker run -d --name=roach2 -p 26258:26257 cockroachdb/cockroach start --insecure --join=roach1,roach2,roach3 --store=type=mem,size=1Gi
$ docker run -d --name=roach3 -p 26259:26257 cockroachdb/cockroach start --insecure --join=roach1,roach2,roach3 --store=type=mem,size=1Gi

# Initialize the cluster
$ docker exec -it roach1 cockroach init --insecure

With the cluster up, you can connect using any PostgreSQL client. The built‑in SQL UI is reachable at http://localhost:8080 for quick schema inspection.

Your First SQL Session

Open a psql‑compatible shell and create a simple “accounts” table. This example demonstrates CockroachDB’s support for standard DDL and data types.

# Connect to the cluster
$ cockroach sql --insecure --host=localhost --port=26257

# Inside the SQL shell
CREATE DATABASE bank;
USE bank;

CREATE TABLE accounts (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_name  STRING NOT NULL,
    balance_cents BIGINT NOT NULL CHECK (balance_cents >= 0),
    created_at  TIMESTAMPTZ DEFAULT now()
);

Insert a few rows and query them back. Notice how the RETURNING clause works just like PostgreSQL, making it easy to retrieve generated IDs.

INSERT INTO accounts (owner_name, balance_cents)
VALUES ('Alice', 100000), ('Bob', 25000)
RETURNING id, owner_name, balance_cents;

SELECT * FROM accounts ORDER BY created_at DESC;

Transactional Guarantees in a Distributed World

One of the biggest misconceptions about distributed databases is that they sacrifice ACID properties. CockroachDB uses a distributed transaction layer that implements two‑phase commit (2PC) over Raft, guaranteeing serializable isolation.

Let’s see a money‑transfer operation that debits one account and credits another in a single transaction. The code uses Python’s psycopg2 driver, which works seamlessly with CockroachDB.

import psycopg2
import uuid

conn = psycopg2.connect(
    host='localhost',
    port=26257,
    dbname='bank',
    user='root',
    password='',
    sslmode='disable'  # Insecure mode for local dev
)
conn.autocommit = False  # Explicit transaction control

def transfer_funds(sender_id: uuid.UUID, receiver_id: uuid.UUID, amount_cents: int):
    with conn.cursor() as cur:
        # Lock rows in a deterministic order to avoid deadlocks
        cur.execute(
            "SELECT balance_cents FROM accounts WHERE id = %s FOR UPDATE",
            (sender_id,)
        )
        sender_balance = cur.fetchone()[0]

        if sender_balance < amount_cents:
            raise ValueError('Insufficient funds')

        # Debit sender
        cur.execute(
            "UPDATE accounts SET balance_cents = balance_cents - %s WHERE id = %s",
            (amount_cents, sender_id)
        )
        # Credit receiver
        cur.execute(
            "UPDATE accounts SET balance_cents = balance_cents + %s WHERE id = %s",
            (amount_cents, receiver_id)
        )
    conn.commit()

# Example usage
alice_id = uuid.UUID('...')  # Replace with actual UUID from earlier INSERT
bob_id   = uuid.UUID('...')
transfer_funds(alice_id, bob_id, 5000)

If any node fails during the commit, CockroachDB automatically retries the transaction, preserving atomicity. The developer sees a clean, synchronous API without having to write custom retry loops.

Pro tip: Wrap every write in a transaction, even simple INSERTs. CockroachDB’s transaction layer is highly optimized, and this habit prevents subtle consistency bugs later on.

Geo‑Partitioning for Low‑Latency Global Apps

When your users span continents, latency becomes a critical factor. CockroachDB’s geo‑partitioning lets you colocate data with the users who query it most often, while still maintaining a single logical database.

Define a zone configuration that forces a table’s rows to live in a specific region based on a column value. Below we create a orders table and partition it by region.

CREATE TABLE orders (
    order_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL,
    region     STRING NOT NULL,  -- e.g., 'us-east', 'eu-west'
    amount_cents BIGINT NOT NULL,
    placed_at  TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE orders EXPERIMENTAL_RELOCATE PARTITION ALL TO REGION 'us-east' USING (region = 'us-east');
ALTER TABLE orders EXPERIMENTAL_RELOCATE PARTITION ALL TO REGION 'eu-west' USING (region = 'eu-west');

Now, a user in Europe will have their orders stored on nodes located in the eu-west data center, dramatically reducing round‑trip time.

Changefeeds: Reacting to Data in Real Time

Many modern applications need to push updates to clients as soon as data changes. CockroachDB’s changefeeds expose a stream of row‑level changes via the SQL interface, which can be consumed by Kafka, Pulsar, or even a simple HTTP endpoint.

Below is a minimal Python example that subscribes to a changefeed on the orders table and prints each new order as it arrives.

import psycopg2
import json

conn = psycopg2.connect(
    host='localhost',
    port=26257,
    dbname='bank',
    user='root',
    sslmode='disable'
)
cur = conn.cursor(name='order_feed')  # Server‑side cursor for streaming

cur.execute("""
    SELECT * FROM CHANGEFEED('SELECT order_id, user_id, amount_cents FROM orders')
""")

print('Listening for new orders...')
for row in cur:
    # Each row is a JSON string
    payload = json.loads(row[0])
    print('New order:', payload)

The feed is exactly‑once, ordered, and automatically resumes after temporary network glitches. This makes it ideal for building event‑driven microservices or real‑time dashboards.

Real‑World Use Cases

Financial services rely on strong consistency for ledger entries. CockroachDB’s serializable isolation guarantees that double‑spend scenarios cannot occur, even across data centers.

Gaming platforms benefit from geo‑partitioning to keep player state close to the client, while still allowing global leaderboards that aggregate data from all regions.

IoT telemetry streams generate massive write workloads. CockroachDB’s distributed architecture scales writes linearly, and changefeeds enable downstream analytics pipelines without additional ETL layers.

Monitoring, Scaling, and Maintenance

Observability is baked into CockroachDB. The built‑in admin UI (port 8080) shows node health, range distribution, and query performance. For production, integrate with Prometheus and Grafana using the /metrics endpoint.

Scaling is as simple as adding a new node to the cluster. CockroachDB automatically rebalances ranges, and you can watch the process in the UI. Remember to adjust zone configs if you add nodes in new regions.

# Add a new node to an existing cluster
$ docker run -d --name=roach4 -p 26260:26257 cockroachdb/cockroach start \
    --insecure --join=roach1,roach2,roach3,roach4 --store=type=mem,size=1Gi

# Verify rebalancing
$ cockroach node status --insecure --host=localhost:26257

Pro tip: Enable --max-sql-memory to cap per‑node SQL memory usage, preventing out‑of‑memory crashes under heavy load.

Security Best Practices

While the examples above use insecure mode for brevity, production deployments should always enable TLS for node‑to‑node and client connections. CockroachDB can generate self‑signed certs or integrate with your existing PKI.

Role‑based access control (RBAC) lets you grant the principle of least privilege. Create dedicated users for application services, analytics pipelines, and admin tasks.

-- Create a read‑only user for reporting
CREATE USER reporter WITH PASSWORD 's3cr3t';
GRANT SELECT ON DATABASE bank TO reporter;

-- Create an application user with INSERT/UPDATE rights
CREATE USER app_user WITH PASSWORD 'appPass!';
GRANT INSERT, UPDATE, SELECT ON TABLE accounts TO app_user;

Finally, enable audit logging to track DDL changes and privileged queries. This is especially important for compliance‑heavy industries.

Backup & Restore Strategies

CockroachDB supports both full and incremental backups via the BACKUP and RESTORE statements. Store backups in cloud object stores (AWS S3, GCS, Azure Blob) for durability.

-- Full backup of the entire cluster
BACKUP TO 's3://my-backups/cockroachdb/full' WITH revision_history;

-- Incremental backup of a single database
BACKUP DATABASE bank TO 's3://my-backups/cockroachdb/bank_inc';

-- Restoring to a new cluster (useful for disaster recovery)
RESTORE FROM 's3://my-backups/cockroachdb/full' WITH into_db='bank_copy';