From cockroachdb
Guides developers in selecting and implementing multi-region patterns for CockroachDB applications, covering active-passive vs active-active architectures, REGIONAL BY ROW, GLOBAL tables, manual geo-partitioning with lease preferences, and live demo setup with validation queries. Use when designing multi-region database topologies, choosing between REGIONAL BY ROW and manual partitioning, building multi-region demos, or optimizing cross-region latency.
npx claudepluginhub cockroachdb/claude-plugin --plugin cockroachdbThis skill uses the workspace's default tool permissions.
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.
Guides multi-region deployments for globally distributed systems, high availability, low latency, and disaster recovery. Covers active-passive/active-active models, data replication, failover patterns.
Designs scalable database architectures from scratch, selects technologies like PostgreSQL or DynamoDB, models schemas, indexes, and plans migrations or re-architecting.
Designs scalable database architectures from scratch or re-architects existing systems. Selects SQL/NoSQL/TimeSeries technologies, models schemas, applies normalization, and plans migrations for performance.
Share bugs, ideas, or general feedback.
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.
REGIONAL BY ROW vs manual geo-partitioningGLOBAL table behavior and trade-offsDo not use this skill when the question is only about SQL syntax, indexing, or generic schema design with no multi-region decision involved.
cockroach demo with locality flags for testingAsk first: is there one write home, or many?
REGIONAL BY ROW.GLOBAL tables.Use when:
Characteristics:
Recommendation: Prefer the higher-level multi-region abstractions first unless the user explicitly needs manual control over partitions, voters, and lease preferences.
Use when:
Characteristics:
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]]';
Use when:
Characteristics:
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);
Use when:
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.
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;
| 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.
| Aspect | Regular Regional | Manual Geo-Partition | REGIONAL BY ROW | GLOBAL |
|---|---|---|---|---|
| Write model | Single primary region | Active-active, region-keyed | Active-active, row-affine | Write from primary region |
| Read locality | Local to primary | Local to partition | Local to row region | All regions |
| Operational burden | Low | High | Medium | Low |
| Configuration | Minimal | Explicit partitions, zones, lease prefs | Database-level abstractions | Table-level declaration |
| Best for | Simple primary-region apps | Full control over mechanics | Developer-facing multi-region | Reference data |
For workshops and technical walkthroughs, use a 9-node local demo cluster to make multi-region locality observable.
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
Recommended presentation order:
Manual partitioning validation:
SHOW RANGES FROM INDEX accounts_manual_pkey WITH DETAILS;
Check that:
RBR validation:
SHOW RANGES FROM TABLE accounts_rbr WITH DETAILS;
Check that:
Manual path:
RBR path:
Transaction latency increases when the client is remote from the relevant leaseholder/quorum path.
| Client Location | Local RW Latency | Cross-Region RW Latency |
|---|---|---|
| Same region as leaseholder | ~10-20ms | — |
| Different region | — | ~50-150ms+ |
Guidance:
A strong answer using this skill should include:
GLOBAL is the answer for all-region low-latency writes without supporting documentationREGIONAL BY ROW, explicitly call out control vs simplicityREGIONAL BY ROW for region-affine dataFor teams migrating from single-region PostgreSQL/Oracle to multi-region CockroachDB:
--locality=region=<region>,zone=<zone>ALTER DATABASE <db> PRIMARY REGION '<region>'ALTER DATABASE <db> ADD REGION '<region>' (for each)ALTER DATABASE <db> SURVIVE ZONE|REGION FAILUREALTER TABLE <t> SET LOCALITY <locality>