Designing Multi Region Applications

Designing Multi-Region Applications

Guides developers through selecting the right multi-region pattern for their CockroachDB application and implementing it with proper validation. Covers the decision model for choosing between regular regional tables, REGIONAL BY ROW, GLOBAL tables, and manual geo-partitioning, plus a hands-on demo framework for comparing approaches.

Complement to other skills: For transaction design patterns, see designing-application-transactions. For SQL syntax and schema design, see cockroachdb-sql.

When to Use This Skill

• Deciding how to model multi-region read/write behavior in CockroachDB
• Choosing between active-active and active-passive architectures
• Evaluating REGIONAL BY ROW vs manual geo-partitioning
• Understanding GLOBAL table behavior and trade-offs
• Designing for local reads and writes in multiple regions
• Building or presenting a multi-region demo or workshop
• Validating leaseholder placement and zone configurations
• Optimizing cross-region transaction latency

Do not use this skill when the question is only about SQL syntax, indexing, or generic schema design with no multi-region decision involved.

Prerequisites

• Understanding of CockroachDB range architecture and leaseholder concepts
• A multi-region cluster with nodes started using --locality=region=...,zone=... matching the regions used in the examples below. Without matching localities the DDL errors with region "..." does not exist and constraints like +region=... match no nodes. Quickest path locally:

  # 9-node demo with three regions, three AZs each — note --no-example-database
  cockroach demo --no-example-database --nodes=9 \
    --demo-locality=region=NA-NE,az=1:region=NA-NE,az=2:region=NA-NE,az=3:region=NA-MW,az=1:region=NA-MW,az=2:region=NA-MW,az=3:region=EU-DE,az=1:region=EU-DE,az=2:region=EU-DE,az=3
  

  For long-running clusters, see setting-up-local-cluster and add --locality=region=...,zone=... to each cockroach start invocation.
• Knowledge of application write patterns (single-region vs multi-region)

Pattern Selection

Step 1: Identify the Application Write Model

Ask first: is there one write home, or many?

• If the application has one primary region for read/write, start with a primary-region / regular regional-table model or a manually configured active-passive design.
• If the application needs low-latency read/write in multiple regions, evaluate manual geo-partitioning or REGIONAL BY ROW.
• If the table is mostly reference data that should read fast everywhere and the write path is not the main focus, consider GLOBAL tables.

Step 2: Choose the Pattern

A. Regular Regional Tables (Active-Passive)

Use when:

• The application has one primary region for RW
• Remote regions are secondary or read-mostly
• Simplicity matters more than region-local writes everywhere

Characteristics:

• All leaseholders stay in the active region
• Replicas in other regions provide resiliency and single-region-failure survival
• Indicative latency: ~20ms writes, ~2-5ms reads (local region)

Recommendation: Prefer the higher-level multi-region abstractions first unless the user explicitly needs manual control over partitions, voters, and lease preferences.

B. Manual Geo-Partitioning with Region-Specific Leaseholders

Use when:

• The application is active-active
• The data model is region-keyed
• The team wants explicit operational control
• Understanding internal mechanics (partitions, voters, lease preferences) is important

Characteristics:

• Region-specific leaseholder pattern keeps writes around ~20ms and reads around ~2-5ms
• The application must enforce reads and writes for a key in the same region
• More DDL and operational burden
• Best for teaching internals

Example DDL:

CREATE TABLE accounts_manual (
  account_id STRING(40),
  owner_id   STRING(40) NOT NULL,
  status     STRING(20) NOT NULL,
  region     STRING(10) NOT NULL,
  CONSTRAINT accounts_manual_pkey PRIMARY KEY (region, account_id)
);

ALTER INDEX accounts_manual_pkey
  PARTITION BY LIST (region) (
    PARTITION na_ne VALUES IN ('NA-NE'),
    PARTITION na_mw VALUES IN ('NA-MW'),
    PARTITION na_nw VALUES IN ('NA-NW')
  );

ALTER PARTITION na_ne OF INDEX accounts_manual_pkey
  CONFIGURE ZONE USING
    num_replicas      = 5,
    num_voters        = 5,
    voter_constraints = '{+region=NA-NE: 2, +region=NA-MW: 2, +region=NA-NW: 1}',
    lease_preferences = '[[+region=NA-NE]]';

C. REGIONAL BY ROW

Use when:

• The workload is active-active
• Each row naturally belongs to a region
• The team wants local RW in multiple regions without hand-managing partition zone configs
• The goal is the developer-facing multi-region abstraction

Characteristics:

• All configured regions are possible home/leaseholder regions
• Indicative latency: ~20ms writes, ~2-5ms reads (local region)
• Less manual configuration than geo-partitioning
• Default recommendation for region-affine application data

Example DDL:

CREATE DATABASE IF NOT EXISTS example_service_rbr;
ALTER DATABASE example_service_rbr PRIMARY REGION 'NA-NE';
ALTER DATABASE example_service_rbr ADD REGION 'NA-NW';
ALTER DATABASE example_service_rbr ADD REGION 'NA-MW';
ALTER DATABASE example_service_rbr SURVIVE REGION FAILURE;

USE example_service_rbr;

CREATE TABLE accounts_rbr (
  account_id STRING(40),
  owner_id   STRING(40) NOT NULL,
  status     STRING(20) NOT NULL,
  region     crdb_internal_region
    NOT NULL
    DEFAULT gateway_region()::crdb_internal_region,
  CONSTRAINT accounts_rbr_pkey PRIMARY KEY (region, account_id)
) LOCALITY REGIONAL BY ROW AS region;

Local allocation pattern:

WITH candidate AS (
  SELECT id, resource_code
  FROM resource_pool
  WHERE allocated_at IS NULL
    AND region = gateway_region()::crdb_internal_region
  ORDER BY random()
  LIMIT 1
  FOR UPDATE
)
UPDATE resource_pool
SET allocated_at = now()
WHERE id = (SELECT id FROM candidate);

D. GLOBAL Tables

Use when:

• The table is global/reference-style data
• The workload is primarily about broad read locality rather than region-owned writes

Important constraint: GLOBAL tables optimize for fast reads everywhere. Do not position them as an "RW everywhere" pattern without verifying product-specific behavior in the official documentation.

E. Survival Goals

Choose the survival goal based on the trade-off between write latency and durability:

-- Survive any single zone failure (default, 3+ zones required):
ALTER DATABASE mydb SURVIVE ZONE FAILURE;

-- Survive an entire region going down (3+ regions required):
ALTER DATABASE mydb SURVIVE REGION FAILURE;

SURVIVE REGION FAILURE adds write latency because Raft consensus must span regions, but guarantees zero data loss even if an entire cloud region goes offline.

Pattern Comparison

Live Demo Setup

For workshops and technical walkthroughs, use a 9-node local demo cluster to make multi-region locality observable.

Cluster Setup

cockroach demo \
  --nodes 9 \
  --no-example-database \
  --insecure \
  --demo-locality=\
region=NA-NE,zone=NA-NE-1:\
region=NA-NE,zone=NA-NE-2:\
region=NA-NE,zone=NA-NE-3:\
region=NA-MW,zone=NA-MW-1:\
region=NA-MW,zone=NA-MW-2:\
region=NA-MW,zone=NA-MW-3:\
region=NA-NW,zone=NA-NW-1:\
region=NA-NW,zone=NA-NW-2:\
region=NA-NW,zone=NA-NW-3

Demo Flow

Recommended presentation order:

1. Start with the manual geo-partitioning path
2. Show explicit partitioning and zone configuration
3. Run validation queries and confirm lease homing
4. Switch to REGIONAL BY ROW
5. Run RBR validations
6. Compare operational surface area

Validation Queries

Manual partitioning validation:

SHOW RANGES FROM INDEX accounts_manual_pkey WITH DETAILS;

Check that:

• All expected partition values are present
• Lease holder locality matches partition region
• Mismatches return FAIL, otherwise PASS

RBR validation:

SHOW RANGES FROM TABLE accounts_rbr WITH DETAILS;

Check that:

• Leaseholder locality coverage includes the expected regions
• There are no unexpected lease regions

Demo Talking Points

Manual path:

• Precise control over partitions, voters, replicas, and lease preferences
• More DDL and operational burden
• Best for teaching internals and understanding what the database does under the hood

RBR path:

• Keeps application intent front and center
• Less manual configuration
• Easier to explain for app teams
• Still grounded in the same topology

Cross-Region Latency Guidance

Transaction latency increases when the client is remote from the relevant leaseholder/quorum path.

Guidance:

• Place latency-sensitive services close to their primary data locality
• Use follower reads for non-critical display/reporting queries
• Use multi-region table locality and zone configuration intentionally
• Do not assume "distributed" means "same latency everywhere"

Output Expectations

A strong answer using this skill should include:

1. The recommended pattern
2. Why it fits the workload
3. What the application must do (routing, row affinity, primary-region assumptions)
4. What CockroachDB manages automatically vs manually
5. Expected latency shape or locality behavior
6. A warning when the user is asking for something the chosen pattern does not optimize for

Guardrails

• Do not claim that regular primary-region tables provide symmetric low-latency writes from all regions
• Do not claim that GLOBAL is the answer for all-region low-latency writes without supporting documentation
• When comparing manual geo-partitioning vs REGIONAL BY ROW, explicitly call out control vs simplicity
• When the user wants to understand internal mechanics, bias toward explaining the manual model first
• When the user wants the best default application pattern, bias toward REGIONAL BY ROW for region-affine data
• Keep region names and locality labels consistent across all SQL
• Do not mix manual and abstraction approaches in the same explanation unless explicitly comparing them
• Always include validation, not just DDL

Multi-Region Migration Checklist

For teams migrating from single-region PostgreSQL/Oracle to multi-region CockroachDB:

1. Deploy nodes with --locality=region=<region>,zone=<zone>
2. Set primary region: ALTER DATABASE <db> PRIMARY REGION '<region>'
3. Add regions: ALTER DATABASE <db> ADD REGION '<region>' (for each)
4. Set survival goal: ALTER DATABASE <db> SURVIVE ZONE|REGION FAILURE
5. Classify tables: GLOBAL (reference data), REGIONAL BY ROW (row-affine), REGIONAL BY TABLE (default)
6. Set localities: ALTER TABLE <t> SET LOCALITY <locality>
7. Monitor leaseholder distribution in DB Console
8. Test failover: kill a zone/region and verify survival goal holds

Safety Considerations

• Multi-region configuration changes affect data placement across the cluster
• Test multi-region configurations on demo or staging clusters before production
• Validate leaseholder placement after configuration changes
• Allow time for range rebalancing after topology changes

References

• CockroachDB Multi-Region Overview
• REGIONAL BY ROW Tables
• GLOBAL Tables
• Follower Reads Documentation
• CockroachDB Transactions
• Performance Best Practices
• Cross-Regional Latency Impact on Transactions
• Query Parallelism with CockroachDB
• CockroachDB Best Practices & Anti-Patterns Demo -- Demo 10 covers multi-region patterns with runnable examples