cockroachdb

CockroachDB

CockroachDB is a cloud-native, distributed SQL database. It survives disk, machine, rack, and even datacenter failures with zero downtime. It speaks PostgreSQL.

When to Use

• Global Distributed SQL: Multi-region enterprise applications requiring PostgreSQL compatibility with multi-region write latency optimization.
• Zero-Downtime Surviving Outages: Applications requiring automated failover and self-healing data survival across region or datacenter outages.
• Strict Serializable ACID Transactions: Systems requiring the highest isolation level (Serializable) to prevent phantom reads and write skews.
• Horizontal Scaling without Sharding: Scaling relational databases beyond single-instance limits without manual application-layer sharding.

Quick Start

-- Create a table with UUID primary key for distributed scale
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    balance DECIMAL(19,4)
);

-- Distributed Transaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 'uuid-1'; UPDATE accounts SET balance = balance + 100 WHERE id = 'uuid-2'; COMMIT;

## Core Concepts

### Raft Consensus Ranges & Distributed Storage

Tables are automatically split into 64MB ordered contiguous chunks called Ranges, replicated across nodes via Raft:

```text
[ Table: orders ]
  ├── Range 1 (Keys: 000-100) ──→ Replicated via Raft (Node 1, Node 2, Node 3)
  └── Range 2 (Keys: 101-200) ──→ Replicated via Raft (Node 2, Node 3, Node 4)

Multi-Region Table Topologies (REGIONAL vs GLOBAL)

Optimizes data locality to keep data close to users and comply with data residency regulations (GDPR):

-- Multi-region database and table definition
ALTER DATABASE enterprise_crm SET PRIMARY REGION "us-east-1";
ALTER DATABASE enterprise_crm ADD REGION "eu-west-1";
ALTER DATABASE enterprise_crm ADD REGION "ap-southeast-1";

-- Regional table colocates rows in the user's home region
CREATE TABLE enterprise_crm.customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    region crdb_region NOT NULL,
    full_name STRING,
    email STRING
) LOCALITY REGIONAL BY ROW AS region;

Strict Serializable Transaction Isolation

CockroachDB runs all transactions at SERIALIZABLE isolation using hybrid logical clocks (HLC) and multi-version concurrency control (MVCC):

BEGIN TRANSACTION PRIORITY HIGH;
UPDATE accounts SET balance = balance - 100 WHERE id = 'acc_1';
UPDATE accounts SET balance = balance + 100 WHERE id = 'acc_2';
COMMIT;

Common Patterns

Multi-Region Row-Level Geo-Partitioning

Problem: Cross-continent latency degrades transactions when data must travel across oceans for consensus.

Solution: Assign multi-region survival goals with regional tables:

ALTER DATABASE global_store SET PRIMARY REGION "us-east1";
ALTER DATABASE global_store ADD REGION "eu-west1";

CREATE TABLE global_store.accounts (
  account_id UUID DEFAULT gen_random_uuid(),
  region crdb_region NOT NULL,
  balance DECIMAL(15, 2),
  PRIMARY KEY (region, account_id)
) LOCALITY REGIONAL BY ROW AS region;

Best Practices

Do:

• Use Multi-Region Survivability Goals: Configure SURVIVE REGION FAILURE to allow clusters to operate through entire cloud region outages.
• Use UUIDs or Hash-Sharded Indexes: Avoid monotonically increasing primary keys (SERIAL / timestamps) which create hot-spot ranges.
• Implement Client-Side Transaction Retries: Handle error code 40001 (transaction serialization retry errors) with exponential backoff.
• Use AS OF SYSTEM TIME for Analytical Queries: Read from historical MVCC snapshots to eliminate read lock contention.

Don't:

• Use sequential integer IDs as primary keys: Sequential IDs force all insert writes onto a single Raft range node.
• Execute massive unbounded transactions: Transactions affecting millions of rows create heavy memory pressure on the Raft coordinator.
• Ignore table locality configurations: Missing multi-region locality rules forces cross-continental WAN roundtrips on every commit.

Troubleshooting

Error	Cause	Solution
TransactionRetryWithProtoRefreshError (40001)	Concurrent transactions modified overlapping key ranges.	Wrap business logic in an automated retry loop for serialization conflicts.
Node dead / Raft quorum degraded	Network partition or host failure dropping available range replicas.	Check CockroachDB DB Console node status and ensure odd number of replicas.
Slow query due to full range scan	Secondary index missing for query WHERE predicates.	Run EXPLAIN and add composite indexes covering lookup columns.

References

• CockroachDB Documentation