Designing Multi-Region Applications

REGIONAL BY ROW

GLOBAL

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

Prerequisites

• Understanding of CockroachDB range architecture and leaseholder concepts
• Multi-region cluster or

  cockroach demo

  with locality flags for testing
• Knowledge of application write patterns (single-region vs multi-region)

Pattern Selection

Step 1: Identify the Application Write Model

• 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)

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

• 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)

B. Manual Geo-Partitioning with Region-Specific Leaseholders

• 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

• 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

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

• 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

• 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

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;

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

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

GLOBAL

E. Survival Goals

-- 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

Pattern Comparison

Live Demo Setup

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

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

SHOW RANGES FROM INDEX accounts_manual_pkey WITH DETAILS;

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

SHOW RANGES FROM TABLE accounts_rbr WITH DETAILS;

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

Demo Talking Points

• 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

• 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

• 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

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

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