CockroachDB Distributed SQL Production Guide

By /

CockroachDB Distributed SQL for Production Deployments

CockroachDB distributed SQL combines the familiarity of PostgreSQL-compatible syntax with the horizontal scalability of NoSQL databases. Therefore, teams can build globally distributed applications without sacrificing transactional consistency. As a result, it has become a leading choice for applications requiring strong consistency across multiple regions.

Architecture and Consensus Protocol

The database stores data in sorted key-value ranges that automatically split, merge, and rebalance across nodes. Moreover, each range maintains three or more replicas using the Raft consensus protocol to ensure durability and consistency. Consequently, the system survives individual node failures without any data loss or availability impact.

The query layer translates SQL into distributed key-value operations. Furthermore, the cost-based optimizer routes queries to the leaseholder replica closest to the data, minimizing cross-region latency.

Distributed database cluster topology diagram
Multi-node cluster with Raft consensus across availability zones

CockroachDB Distributed SQL Multi-Region Configuration

Production deployments spanning multiple regions require careful data placement configuration. Specifically, table-level locality settings control where replicas are stored and which region serves reads. Additionally, you can pin specific rows to regions using regional by row tables for data residency compliance.

-- Configure multi-region database
ALTER DATABASE commerce SET PRIMARY REGION "us-east1";
ALTER DATABASE commerce ADD REGION "us-west2";
ALTER DATABASE commerce ADD REGION "eu-west1";
ALTER DATABASE commerce SET SECONDARY REGION "us-west2";

-- Global table: low-latency reads from any region
CREATE TABLE currencies (
    code STRING PRIMARY KEY,
    name STRING NOT NULL,
    symbol STRING NOT NULL
) LOCALITY GLOBAL;

-- Regional by row: data pinned to user's region
CREATE TABLE user_profiles (
    id UUID DEFAULT gen_random_uuid(),
    region crdb_internal_region NOT NULL DEFAULT 'us-east1',
    email STRING NOT NULL,
    display_name STRING NOT NULL,
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (region, id)
) LOCALITY REGIONAL BY ROW;

-- Zone configuration for survivability
ALTER DATABASE commerce SURVIVE REGION FAILURE;

-- Create index with specific zone config
CREATE INDEX idx_profiles_email ON user_profiles (email)
    STORING (display_name);

-- Query with region-aware filtering
SELECT id, email, display_name
FROM user_profiles
WHERE region = 'us-east1'
  AND created_at > now() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;

The LOCALITY settings control data placement automatically. Therefore, reads from user_profiles in us-east1 never cross region boundaries for locally pinned data.

Serializable Isolation and Performance

The database defaults to serializable isolation, the strongest consistency level in SQL. However, this guarantee comes with performance considerations that teams must understand. In contrast to read-committed isolation used by PostgreSQL defaults, serializable prevents all anomalies including phantom reads and write skew.

Transaction retries are normal under serializable isolation. As a result, applications must implement retry loops or use built-in retry mechanisms for contended workloads.

CockroachDB distributed SQL performance monitoring
Monitoring cluster performance metrics across regions

Production Survivability and Operations

Two survivability modes are available: zone failure survival and region failure survival. Moreover, region failure survival requires at least three regions and five replicas per range, ensuring the database remains available even when an entire cloud region goes offline.

For example, a three-region deployment with SURVIVE REGION FAILURE continues serving reads and writes when any one region becomes unavailable. Additionally, automated rebalancing restores the replication factor once the failed region recovers.

Multi-region database deployment
Multi-region deployment with automatic failover capabilities

Related Reading:

Further Resources:

In conclusion, this database delivers PostgreSQL compatibility with global scale and strong consistency guarantees. Therefore, choose it when your application demands multi-region availability with serializable transactions.

Scroll to Top