Building a Self-Hosted Distributed SQL Database with CockroachDB: Multi-Region Resilience and Geo-Partitioning

Quick Summary (TL;DR) CockroachDB is a distributed, ACID-compliant SQL database that survives region-level failures while keeping data close to users. This guide walks you through provisioning nodes across regions, installing and initializing a cluster, configuring multi-region replication, implementing geo-partitioning for data locality, and testing automated failover. Expect roughly 9 nodes (3 per region), sub-100ms inter-node latency, and a survival goal of region for production-grade resilience.

Introduction: Why Self-Host CockroachDB?

Managed distributed SQL services are convenient, but they come with trade-offs: vendor lock-in, unpredictable egress costs, and limited control over where your data physically lives. Self-hosting CockroachDB gives you full ownership of your topology, encryption keys, and upgrade cadence — critical for teams bound by data residency regulations or strict compliance regimes.

CockroachDB’s architecture is purpose-built for this. It uses a distributed key-value store, Raft consensus for replication, and a PostgreSQL-compatible SQL layer. Every node participates equally — there is no primary or leader to fail over to. Combined with geo-partitioning, you can pin rows to specific regions, satisfying both performance and sovereignty requirements.

Prerequisites and Infrastructure Planning

Hardware and Network Requirements

Before touching a terminal, plan your topology. For a production multi-region cluster, the minimum viable layout is three regions × three nodes = nine nodes. This lets you survive the loss of an entire region while maintaining quorum.

Component	Minimum	Recommended
Nodes per region	3	3–5
vCPU per node	4	8–16
RAM per node	8 GB	16–32 GB
Storage	SSD, 100 GB	NVMe SSD, 500 GB+
Inter-node latency	< 150 ms	< 100 ms
Inter-node bandwidth	100 Mbps	1 Gbps+

Low, stable latency between regions is the single biggest determinant of write performance. CockroachDB’s Raft consensus requires a quorum of replicas to acknowledge writes, so a slow region drags down the whole cluster.

Software and Security Considerations

• OS: Linux (Ubuntu 22.04 LTS or RHEL 9 recommended).
• Time sync: NTP or chrony is mandatory — clock skew breaks Raft.
• TLS: Generate node and client certificates; never run production without them.
• Firewall: Open ports 26257 (SQL), 26258 (RPC), and 8080 (Admin UI) only between trusted hosts.
• Service user: Run cockroach as a dedicated, non-root system user.

Step 1: Provisioning Nodes Across Regions

Example: Deploying on AWS EC2

Provision three nodes in each of us-east-1, eu-west-1, and ap-southeast-1. Use m6i.2xlarge instances with gp3 EBS volumes. Place them in a single VPC with inter-region VPC peering, or use AWS Transit Gateway. Ensure security groups allow the ports listed above.

Example: Deploying on Bare Metal

For bare metal, colocate racks in three physically separate data centers. Assign static IPs and configure a private network (e.g., WireGuard or a dedicated MPLS circuit) between sites. Bare metal typically delivers the lowest and most consistent latency — ideal for latency-sensitive workloads.

flowchart TD
    subgraph US["us-east-1"]
        N1[Node 1]
        N2[Node 2]
        N3[Node 3]
    end
    subgraph EU["eu-west-1"]
        N4[Node 4]
        N5[Node 5]
        N6[Node 6]
    end
    subgraph AP["ap-southeast-1"]
        N7[Node 7]
        N8[Node 8]
        N9[Node 9]
    end
    N1 <--> N4
    N4 <--> N7
    N7 <--> N1
    N2 <--> N5
    N5 <--> N8
    N8 <--> N2
    N3 <--> N6
    N6 <--> N9
    N9 <--> N3

Step 2: Installing and Configuring CockroachDB

Download and Install

On each node, install the binary and set up the service user:

curl https://binaries.cockroachdb.com/cockroach-v23.2.0.linux-amd64.tgz | tar -xz
sudo cp -i cockroach-v23.2.0.linux-amd64/cockroach /usr/local/bin/
sudo useradd -r -s /bin/false cockroach
sudo mkdir -p /var/lib/cockroach && sudo chown cockroach /var/lib/cockroach

Initialize the Cluster

On the first node in us-east-1, start the service and initialize:

cockroach start \
  --certs-dir=/etc/cockroach/certs \
  --store=/var/lib/cockroach \
  --listen-addr=10.0.1.10:26257 \
  --http-addr=10.0.1.10:8080 \
  --join=10.0.1.10,10.0.2.10,10.0.3.10,10.1.1.10,10.1.2.10,10.1.3.10,10.2.1.10,10.2.2.10,10.2.3.10 \
  --locality=region=us-east-1,zone=a \
  --background

cockroach init --certs-dir=/etc/cockroach/certs --host=10.0.1.10:26257

Add Nodes to the Cluster

Repeat the start command on every remaining node, adjusting --listen-addr and --locality to match its region and zone. The --locality flag is essential — CockroachDB uses it for replica placement and geo-partitioning decisions.

sequenceDiagram
    participant Admin
    participant Node1 as Node 1 (us-east-1)
    participant Node2 as Node 2 (eu-west-1)
    participant Node3 as Node 3 (ap-southeast-1)
    Admin->>Node1: cockroach start --join=...
    Admin->>Node1: cockroach init
    Node1-->>Admin: Cluster initialized
    Admin->>Node2: cockroach start --join=...
    Node2-->>Node1: Join Raft group
    Admin->>Node3: cockroach start --join=...
    Node3-->>Node1: Join Raft group
    Node1-->>Admin: 9 nodes live

Step 3: Configuring Multi-Region Replication

Setting the Primary Region

Connect via cockroach sql and set the database’s primary region. This determines where leaseholders for the majority of data live by default:

ALTER DATABASE app PRIMARY REGION "us-east-1";

Adding Database Regions

Register every region that will hold replicas:

ALTER DATABASE app ADD REGION "eu-west-1";
ALTER DATABASE app ADD REGION "ap-southeast-1";

Configuring Survival Goals

Choose how the cluster should survive failures:

Survival Goal	Tolerates	Replication Factor	Trade-off
ZONE (default)	Single node failure	3	Lower latency, cheaper
REGION	Entire region failure	5	Higher write latency, more storage

ALTER DATABASE app SURVIVE REGION FAILURE;

With SURVIVE REGION FAILURE, CockroachDB places five replicas across regions so that a quorum survives even if one region goes dark.

Step 4: Implementing Geo-Partitioning for Data Locality

Understanding Partitioning vs. Geo-Partitioning

Standard partitioning splits a table by a key (e.g., date) for query efficiency. Geo-partitioning extends this by pinning each partition’s replicas to a specific region via PARTITION ... OF TABLE ... AT LOCALITY. The result: users in Europe read and write to European replicas.

Defining a Geo-Partitioned Table

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  region STRING NOT NULL,
  email STRING NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region);

ALTER PARTITION "us-east-1" OF TABLE users
  CONFIGURE ZONE USING
    constraints = '[+region=us-east-1]',
    num_replicas = 3;

ALTER PARTITION "eu-west-1" OF TABLE users
  CONFIGURE ZONE USING
    constraints = '[+region=eu-west-1]',
    num_replicas = 3;

ALTER PARTITION "ap-southeast-1" OF TABLE users
  CONFIGURE ZONE USING
    constraints = '[+region=ap-southeast-1]',
    num_replicas = 3;

Verifying Data Placement

Use the built-in replication report to confirm replicas land where intended:

SELECT range_id, start_key, lease_holder, replicas
FROM crdb_internal.ranges
WHERE table_name = 'users';

You should see each partition’s replicas concentrated in its target region.

Step 5: Automated Failover and Disaster Recovery

How CockroachDB Handles Failures

There is no failover event to trigger. Every node is a peer. When a node or region becomes unreachable, Raft automatically elects new leaseholders among surviving replicas, and the cluster continues serving requests as long as a quorum exists. Recovery is self-healing: when the failed node returns, it catches up via Raft log replay.

flowchart LR
    A[Region Failure Detected] --> B{Quorum Available?}
    B -- Yes --> C[Elect New Leaseholder]
    C --> D[Cluster Serves Requests]
    B -- No --> E[Writes Block Until Quorum Restored]
    D --> F[Failed Region Recovers]
    F --> G[Raft Log Replay Rebalances]

Testing Failover Scenarios

Simulate failure by stopping all nodes in one region:

# On each node in eu-west-1
cockroach quit --certs-dir=/etc/cockroach/certs --host=10.1.1.10:26257

Then run a write workload against us-east-1. With SURVIVE REGION FAILURE, writes should succeed with only a latency bump. Bring the nodes back and watch the replication report rebalance.

Backup and Restore Strategies

Raft replication is not a backup — it protects against hardware failure, not accidental DROP TABLE. Schedule regular backups:

BACKUP DATABASE app INTO 's3://my-bucket/backups?region=us-east-1'
  AS OF SYSTEM TIME '-10s';

Use BACKUP ... INTO LATEST IN ... for incrementals, and store backups in a bucket geographically close to the cluster to minimize restore time.

Performance Tuning and Monitoring

Key Metrics to Monitor

Metric	Target	Why It Matters
P99 SQL latency	< 50 ms regional	User experience
Raft log apply latency	< 100 ms	Quorum health
Range count per node	Balanced	Hotspot detection
Disk usage	< 70%	Avoid compaction stalls
Clock offset	< 100 ms	Raft correctness

Tuning for Latency and Throughput

• Follower reads: For read-heavy geo-distributed apps, use AS OF SYSTEM TIME follower_read_timestamp() to serve reads from local replicas without hitting the leaseholder.
• Connection pooling: Use PgBouncer or the built-in connection pooler to avoid overwhelming nodes.
• Range size: Default 512 MB is fine; increase only if you have very large scans and low write rates.
• Locality-aware routing: Configure your application’s connection string with --locality so clients prefer local nodes.

Comparison: CockroachDB vs. Other Distributed SQL Databases

Feature	CockroachDB	YugabyteDB	TiDB	Google Spanner
Self-hostable	✅ Yes	✅ Yes	✅ Yes	❌ Managed only
SQL compatibility	PostgreSQL	PostgreSQL	MySQL	GoogleSQL
Consensus protocol	Raft	Raft	Raft	Paxos
Geo-partitioning	✅ Row-level	✅ Table-level	✅ Table-level	✅ Row-level
Multi-region survival	✅ Region goal	✅ Region goal	✅ Region goal	✅ Regional configs
License	BSL / CockroachDB Community	Apache 2.0	Apache 2.0	Proprietary

CockroachDB’s row-level geo-partitioning and PostgreSQL wire compatibility make it the strongest choice when you need fine-grained data placement with minimal application changes.

Conclusion: Best Practices for Production

Self-hosting CockroachDB rewards careful planning. Keep these principles front of mind:

1. Three regions, three nodes each — never fewer for production.
2. Set SURVIVE REGION FAILURE on databases that require regional resilience.
3. Geo-partition by residency key to satisfy compliance and cut latency.
4. Back up religiously — replication is not a backup.
5. Monitor clock skew and Raft latency — these are your early warning signals.
6. Test failover quarterly — untested resilience is theoretical resilience.

With the right topology and configuration, a self-hosted CockroachDB cluster delivers the resilience of a managed global database while keeping your data — and your fate — entirely in your own hands.

FAQ

What is the minimum number of nodes required for a multi-region CockroachDB cluster? At least three nodes for a production cluster. For multi-region deployments, use at least three nodes per region to survive a region failure. A common setup is three regions with three nodes each, totaling nine nodes.

How does geo-partitioning improve performance? Geo-partitioning pins data to specific regions based on a partitioning key (e.g., country or region). This reduces read and write latency by keeping data close to the users who access it, and helps with data residency compliance by ensuring data stays within a geographic boundary.

Can I mix cloud providers or use on-premises with CockroachDB? Yes, CockroachDB is cloud-agnostic and can run across different cloud providers or on-premises. However, network latency between nodes should be low (ideally under 100ms) for optimal performance. Cross-cloud deployments may incur higher latency and egress costs.

What happens during a region failure? CockroachDB automatically detects node failures and rebalances replicas to maintain the configured replication factor. If a region becomes unavailable, the cluster continues serving requests as long as a quorum of replicas is available. With a survival goal of region, the cluster survives the loss of an entire region without data loss.

How do I backup a multi-region CockroachDB cluster? Use CockroachDB’s built-in BACKUP command to create full or incremental backups to cloud storage (S3, GCS) or local storage. For multi-region clusters, schedule backups during low-traffic periods and store them in a region with low latency to the cluster.