designing-multi-region-applications

What this skill does

# 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](../designing-application-transactions/SKILL.md). For SQL syntax and schema design, see [cockroachdb-sql](../../cockroachdb-query-and-schema-design/cockroachdb-sql/SKILL.md).

## 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:
  ```bash
  # 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](../../cockroachdb-onboarding-and-migrations/setting-up-local-cluster/SKILL.md) 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:**

```sql
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:**

```sql
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:**

```sql
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:

```sql
-- 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;
```

| Goal                   | Requirement | Write Latency                   | Data Safety              |
|------------------------|-------------|---------------------------------|--------------------------|
| SURVIVE ZONE FAILURE   | 3+ zones    | Low (local consensus)           | Survives 1 zone outage   |
| SURVIVE REGION FAILURE | 3+ regions  | Higher (cross-region consensus) | Survives 1 region outage |

`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

| Aspect             | Regular Regional           | Manual Geo-Partition                    | REGIONAL BY ROW               | GLOBAL                    |
|--------------------|----------------------------|-----------------------------------------|-------------------------------|---------------------------|
| Write model        | Single primary region      | Active-active, region-keyed             | Active-active, row-affine