CockroachDB Multi-Region: 5 Core Patterns for Globally Distributed Active-Active Database

The Pain of Global Data Latency

Users in Tokyo experiencing 3-second order delays, European users reading 5-second-old inventory, GDPR requiring data to stay in the EU, cross-region write conflicts causing data loss — when your business goes global, single-region database latency and compliance issues become fatal bottlenecks. CockroachDB's native multi-region architecture provides a complete solution from "single-region primary-replica" to "global active-active writes."

This article dives into 5 core patterns, from cluster deployment to compliance partitioning, building a production-grade globally distributed active-active database step by step.

---

Core Concepts

Concept	Description	Key Configuration
CockroachDB	Native distributed SQL database, PostgreSQL-compatible	Strong consistency Serializable isolation
Multi-Region	Cross-datacenter deployment, reducing read latency	--locality startup flag
Geo-Partitioning	Partition data by geography, store locally	ALTER PARTITION ... CONFIGURE ZONE
Survival Goal	Region failure tolerance strategy	ZONE/REGION level
Region Config	Database/table-level region topology	ALTER DATABASE ... SET PRIMARY REGION
Lease Preferences	Specify Lease Holder region	lease_preferences
Latency Optimization	Reduce cross-region latency via local reads	FOLLOWER READS
Compliance Partition	Meet GDPR and other data residency requirements	Regional By Row tables

---

Problem Analysis: 5 Multi-Region Challenges

1. High cross-region latency: Default 3 replicas randomly distributed, reads may cross the Atlantic at 200ms+
2. Data consistency is hard: Active-active writes spike transaction conflict rates, retry storms under Serializable
3. Active-active write conflicts: Same row modified in two regions simultaneously, Write Intent conflicts
4. Compliance data localization: GDPR requires EU user data stored within EU borders
5. Region failure recovery: How to guarantee RPO=0, RTO<30s when a single region goes down

---

Pattern 1: Multi-Region Cluster Deployment

-- Specify locality when starting nodes
-- cockroach start --locality=region=us-east-1,zone=us-east-1a ...

-- Set database primary region
ALTER DATABASE global_app SET PRIMARY REGION "us-east-1";
ALTER DATABASE global_app ADD REGION "eu-west-1";
ALTER DATABASE global_app ADD REGION "ap-southeast-1";

-- View region configuration
SHOW REGIONS FROM DATABASE global_app;

-- Set Survival Goal (region-level fault tolerance)
ALTER DATABASE global_app SURVIVE REGION FAILURE;
-- Or zone-level fault tolerance
ALTER DATABASE global_app SURVIVE ZONE FAILURE;

# 9-node multi-region cluster startup example
# us-east-1: 3 nodes
cockroach start \
  --locality=region=us-east-1,zone=us-east-1a \
  --certs-dir=certs \
  --join=node1:26257,node4:26257,node7:26257 \
  --cache=.25 --max-sql-memory=.25 \
  --background

# eu-west-1: 3 nodes
cockroach start \
  --locality=region=eu-west-1,zone=eu-west-1a \
  --certs-dir=certs \
  --join=node1:26257,node4:26257,node7:26257 \
  --cache=.25 --max-sql-memory=.25 \
  --background

---

Pattern 2: Geo-Partitioning & Data Localization

-- Create Regional By Row table (each row associated with a region)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    email STRING NOT NULL,
    region CRDB_INTERNAL_REGION NOT NULL,
    INDEX idx_email (email)
) LOCALITY REGIONAL BY ROW;

-- Data automatically routed to corresponding region
INSERT INTO users (name, email, region) VALUES ('Alice', 'alice@eu.com', 'eu-west-1');
INSERT INTO users (name, email, region) VALUES ('Bob', 'bob@us.com', 'us-east-1');

-- Reads automatically go to nearest replica
SELECT * FROM users WHERE id = 'alice-uuid';

-- Create Global table (full replicas in all regions)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    price DECIMAL(10,2)
) LOCALITY GLOBAL;

-- Create Regional table (only primary region has Lease)
CREATE TABLE config (
    key STRING PRIMARY KEY,
    value STRING
) LOCALITY REGIONAL IN "us-east-1";

---

Pattern 3: Survival Goal High Availability

-- ZONE Survival: single AZ failure tolerance (default)
ALTER DATABASE global_app SURVIVE ZONE FAILURE;
-- Replicas: 3 per Range across different AZs in same region

-- REGION Survival: single region failure tolerance
ALTER DATABASE global_app SURVIVE REGION FAILURE;
-- Replicas: 5 per Range across 3 regions (primary 2 + secondary 2 + lease 1)

-- View current Survival Goal
SHOW ZONE CONFIGURATION FROM DATABASE global_app;

-- Verify region failover
-- Simulate eu-west-1 region outage
-- cockroach node drain --host=eu-node1 --host=eu-node2 --host=eu-node3
-- Verification: cluster auto-migrates Lease to surviving regions

---

Pattern 4: Lease Preferences & Read Latency Optimization

-- Configure Lease preferences for local reads
ALTER TABLE users CONFIGURE ZONE USING
    num_replicas = 5,
    lease_preferences = '[[+region=us-east-1]]';

-- Use Follower Reads for local reads (no Lease needed)
SELECT * FROM orders AS OF SYSTEM TIME INTERVAL '-5s'
WHERE user_id = 123;

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

type RegionalDB struct {
    primaryDB   *sql.DB
    followerDBs map[string]*sql.DB
}

func NewRegionalDB(primaryConn string, followerConns map[string]string) (*RegionalDB, error) {
    primary, err := sql.Open("postgres", primaryConn)
    if err != nil {
        return nil, err
    }
    primary.SetMaxOpenConns(25)
    primary.SetMaxIdleConns(10)

    followers := make(map[string]*sql.DB)
    for region, conn := range followerConns {
        db, err := sql.Open("postgres", conn)
        if err != nil {
            return nil, err
        }
        db.SetMaxOpenConns(15)
        db.SetMaxIdleConns(5)
        followers[region] = db
    }
    return &RegionalDB{primaryDB: primary, followerDBs: followers}, nil
}

func (r *RegionalDB) ReadWithFollower(ctx context.Context, region, query string, args ...interface{}) (*sql.Rows, error) {
    db, ok := r.followerDBs[region]
    if !ok {
        db = r.primaryDB
    }
    followerQuery := fmt.Sprintf("SELECT * FROM (%s) AS OF SYSTEM TIME INTERVAL '-5s'", query)
    return db.QueryContext(ctx, followerQuery, args...)
}

func (r *RegionalDB) Write(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
    return r.primaryDB.ExecContext(ctx, query, args...)
}

func main() {
    rdb, err := NewRegionalDB(
        "postgresql://app:pass@us-east-1:26257/global_app?sslmode=require",
        map[string]string{
            "eu-west-1":       "postgresql://app:pass@eu-west-1:26257/global_app?sslmode=require",
            "ap-southeast-1":  "postgresql://app:pass@ap-southeast-1:26257/global_app?sslmode=require",
        },
    )
    if err != nil {
        log.Fatal(err)
    }
    defer rdb.primaryDB.Close()

    ctx := context.Background()
    rows, err := rdb.ReadWithFollower(ctx, "eu-west-1",
        "SELECT id, name FROM users WHERE region = $1", "eu-west-1")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id, name string
        rows.Scan(&id, &name)
        fmt.Println(id, name)
    }
}

---

Pattern 5: Compliance Partitioning & Data Residency

-- GDPR compliance: EU user data must stay in the EU
CREATE TABLE eu_user_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    personal_info STRING NOT NULL,
    region CRDB_INTERNAL_REGION NOT NULL DEFAULT 'eu-west-1',
    created_at TIMESTAMPTZ DEFAULT NOW()
) LOCALITY REGIONAL BY ROW;

-- Enforce EU data stays in EU (constraint check)
ALTER TABLE eu_user_data ADD CONSTRAINT chk_eu_region
CHECK (region = 'eu-west-1');

-- China data compliance (data export restrictions)
CREATE TABLE cn_user_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    personal_info STRING NOT NULL,
    region CRDB_INTERNAL_REGION NOT NULL DEFAULT 'ap-southeast-1',
    created_at TIMESTAMPTZ DEFAULT NOW()
) LOCALITY REGIONAL BY ROW IN "ap-southeast-1";

-- Verify data residency
SELECT range_id, start_key, end_key,
       array_length(replicas, 1) as replica_count
FROM [SHOW RANGES FROM TABLE eu_user_data]
WHERE locality LIKE '%eu-west-1%';

---

Pitfall Guide

Pitfall 1: ❌ Ignoring Survival Goal Causes Data Loss on Region Failure

-- ❌ ZONE Survival across regions, data loss on region outage
ALTER DATABASE global_app SURVIVE ZONE FAILURE;

-- ✅ Cross-region must use REGION Survival
ALTER DATABASE global_app SURVIVE REGION FAILURE;

Pitfall 2: ❌ Overusing Global Tables Causes High Write Latency

-- ❌ All tables use LOCALITY GLOBAL
CREATE TABLE orders (...) LOCALITY GLOBAL;

-- ✅ Choose by workload: read-heavy → Global, write-heavy → Regional
CREATE TABLE orders (...) LOCALITY REGIONAL BY ROW;

Pitfall 3: ❌ Insufficient Follower Read Time Window

-- ❌ AS OF SYSTEM TIME '-1s' may read un-closed data
SELECT * FROM orders AS OF SYSTEM TIME '-1s';

-- ✅ Wait at least 5 seconds closed-loop window
SELECT * FROM orders AS OF SYSTEM TIME '-5s';

Pitfall 4: ❌ Regional By Row Without Default Region

-- ❌ Forgetting to specify region on insert
INSERT INTO users (name, email) VALUES ('Test', 't@t.com');

-- ✅ Set default region or enforce in application
ALTER TABLE users ALTER COLUMN region SET DEFAULT 'us-east-1';

Pitfall 5: ❌ Cross-Region JOIN Performance Disaster

-- ❌ Cross-region table JOIN
SELECT * FROM eu_users JOIN us_orders ON eu_users.id = us_orders.user_id;

-- ✅ Query by region, aggregate in application
SELECT * FROM eu_users WHERE region = 'eu-west-1';
SELECT * FROM us_orders WHERE region = 'us-east-1';

---

Error Troubleshooting

#	Error Message	Cause	Solution
1	region "xxx" does not exist	Region not added	ALTER DATABASE db ADD REGION "xxx"
2	no lease holder for range	Lease not migrated during region switch	Wait 30s or manually transfer Lease
3	replication factor less than quorum	REGION Survival needs 5 replicas	Ensure at least 3 regions with 2 nodes each
4	cannot set primary region	Existing data needs migration first	Set PRIMARY REGION before importing data
5	follower read timestamp too close	Insufficient Follower Read time window	Use -5s or larger offset
6	transaction retry error	Active-active write conflicts	Use transaction retry wrapper, reduce conflicts
7	insufficient live replicas	Region failure, insufficient replicas	Ensure other regions have enough replicas
8	locality constraint not satisfiable	Node locality mismatches Zone config	Check --locality flags and Zone constraints
9	cannot add region with data	Adding region to table with existing data	Use ALTER TABLE ... SET LOCALITY
10	zone config violates survival goal	Zone config conflicts with Survival Goal	Increase replicas or lower Survival Goal

---

Advanced Optimization

1. Automated Region Routing Middleware

func RouteQuery(ctx context.Context, rdb *RegionalDB, region string, isRead bool) *sql.DB {
    if !isRead {
        return rdb.primaryDB
    }
    if db, ok := rdb.followerDBs[region]; ok {
        return db
    }
    return rdb.primaryDB
}

2. Multi-Region CDC Data Sync

CREATE CHANGEFEED FOR TABLE users
INTO 'kafka://kafka-eu:9092?topic_prefix=eu_'
WITH updated, resolved='5s', region='eu-west-1';

3. Cross-Region Transaction Priority Control

SET TRANSACTION PRIORITY HIGH;
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

4. Multi-Region Backup Strategy

cockroach backup DATABASE global_app
INTO 's3://backup-bucket/multi-region/?AWS_ENDPOINT=https://s3.eu-west-1.amazonaws.com'
WITH revision_history, encryption_passphrase='xxx';

---

Comparison Analysis

Dimension	CockroachDB	TiDB	Spanner	YugabyteDB
Native Multi-Region	✅ SQL-level	Limited (Placement Rules)	✅ SQL-level	✅ SQL-level
Geo-Partitioning	Regional By Row	Placement Rules	Schema constraints	Tablespaces
Survival Goal	ZONE/REGION	No native concept	Fault tolerance domains	Priority
Follower Reads	✅ Native	❌ Not supported	✅ Stale Reads	✅ Stale Reads
Compliance Residency	✅ Regional constraints	Limited	✅	✅
Active-Active Writes	✅ Native	❌ Primary region writes	✅	✅
SQL Compatible	PostgreSQL	MySQL	PostgreSQL	PostgreSQL
Open Source	✅(BSL)	✅	❌	✅(Apache 2.0)
Managed Service	CockroachCloud	TiDB Cloud	Spanner	YugabyteDB Managed

---

Summary: The core of CockroachDB's multi-region architecture is "data follows the user." The 5 patterns build progressively: multi-region cluster deployment → Geo-partitioning data localization → Survival Goal HA → Lease preference latency optimization → compliance partitioning data residency. Key principles: write-heavy → Regional By Row, read-heavy → Follower Reads, cross-region → REGION Survival, compliance → Regional constraints. From single-region to global active-active, CockroachDB solves multi-region challenges with SQL syntax, not ops scripts.

---

Recommended Online Tools

• JSON Formatter: /en/json/format
• Base64 Encode/Decode: /en/encode/base64
• cURL to Code: /en/dev/curl-to-code
• SQL Formatter: /en/dev/sql-format