Update to CockroachDB

{
  "description": "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.",
  "included_files": [],
  "name": "design-multi-region-apps",
  "skill_md_contents": "---\nname: design-multi-region-apps\ndescription: 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.\ncompatibility: \"CockroachDB >= 22.1 with multi-region licensed features. Requires a multi-region cluster or cockroach demo with locality flags.\"\n---\n\n# Designing Multi-Region Applications\n\nGuides 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.\n\n**Complement to other skills:** For transaction design patterns, see [designing-application-transactions](../design-application-txns/SKILL.md). For SQL syntax and schema design, see [cockroachdb-sql](../cockroachdb-sql/SKILL.md).\n\n## When to Use This Skill\n\n- Deciding how to model multi-region read/write behavior in CockroachDB\n- Choosing between active-active and active-passive architectures\n- Evaluating `REGIONAL BY ROW` vs manual geo-partitioning\n- Understanding `GLOBAL` table behavior and trade-offs\n- Designing for local reads and writes in multiple regions\n- Building or presenting a multi-region demo or workshop\n- Validating leaseholder placement and zone configurations\n- Optimizing cross-region transaction latency\n\n**Do not use this skill** when the question is only about SQL syntax, indexing, or generic schema design with no multi-region decision involved.\n\n## Prerequisites\n\n- Understanding of CockroachDB range architecture and leaseholder concepts\n- 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:\n  ```bash\n  # 9-node demo with three regions, three AZs each — note --no-example-database\n  cockroach demo --no-example-database --nodes=9 \\\n    --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\n  ```\n  For long-running clusters, see [setting-up-local-cluster](../setting-up-local-cluster/SKILL.md) and add `--locality=region=...,zone=...` to each `cockroach start` invocation.\n- Knowledge of application write patterns (single-region vs multi-region)\n\n## Pattern Selection\n\n### Step 1: Identify the Application Write Model\n\nAsk first: **is there one write home, or many?**\n\n- 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.\n- If the application needs **low-latency read/write in multiple regions**, evaluate manual geo-partitioning or `REGIONAL BY ROW`.\n- If the table is mostly **reference data** that should read fast everywhere and the write path is not the main focus, consider `GLOBAL` tables.\n\n### Step 2: Choose the Pattern\n\n#### A. Regular Regional Tables (Active-Passive)\n\n**Use when:**\n- The application has one primary region for RW\n- Remote regions are secondary or read-mostly\n- Simplicity matters more than region-local writes everywhere\n\n**Characteristics:**\n- All leaseholders stay in the active region\n- Replicas in other regions provide resiliency and single-region-failure survival\n- Indicative latency: ~20ms writes, ~2-5ms reads (local region)\n\n**Recommendation:** Prefer the higher-level multi-region abstractions first unless the user explicitly needs manual control over partitions, voters, and lease preferences.\n\n#### B. Manual Geo-Partitioning with Region-Specific Leaseholders\n\n**Use when:**\n- The application is active-active\n- The data model is region-keyed\n- The team wants explicit operational control\n- Understanding internal mechanics (partitions, voters, lease preferences) is important\n\n**Characteristics:**\n- Region-specific leaseholder pattern keeps writes around ~20ms and reads around ~2-5ms\n- The application must enforce reads and writes for a key in the same region\n- More DDL and operational burden\n- Best for teaching internals\n\n**Example DDL:**\n\n```sql\nCREATE TABLE accounts_manual (\n  account_id STRING(40),\n  owner_id   STRING(40) NOT NULL,\n  status     STRING(20) NOT NULL,\n  region     STRING(10) NOT NULL,\n  CONSTRAINT accounts_manual_pkey PRIMARY KEY (region, account_id)\n);\n\nALTER INDEX accounts_manual_pkey\n  PARTITION BY LIST (region) (\n    PARTITION na_ne VALUES IN ('NA-NE'),\n    PARTITION na_mw VALUES IN ('NA-MW'),\n    PARTITION na_nw VALUES IN ('NA-NW')\n  );\n\nALTER PARTITION na_ne OF INDEX accounts_manual_pkey\n  CONFIGURE ZONE USING\n    num_replicas      = 5,\n    num_voters        = 5,\n    voter_constraints = '{+region=NA-NE: 2, +region=NA-MW: 2, +region=NA-NW: 1}',\n    lease_preferences = '[[+region=NA-NE]]';\n```\n\n#### C. REGIONAL BY ROW\n\n**Use when:**\n- The workload is active-active\n- Each row naturally belongs to a region\n- The team wants local RW in multiple regions without hand-managing partition zone configs\n- The goal is the developer-facing multi-region abstraction\n\n**Characteristics:**\n- All configured regions are possible home/leaseholder regions\n- Indicative latency: ~20ms writes, ~2-5ms reads (local region)\n- Less manual configuration than geo-partitioning\n- Default recommendation for region-affine application data\n\n**Example DDL:**\n\n```sql\nCREATE DATABASE IF NOT EXISTS example_service_rbr;\nALTER DATABASE example_service_rbr PRIMARY REGION 'NA-NE';\nALTER DATABASE example_service_rbr ADD REGION 'NA-NW';\nALTER DATABASE example_service_rbr ADD REGION 'NA-MW';\nALTER DATABASE example_service_rbr SURVIVE REGION FAILURE;\n\nUSE example_service_rbr;\n\nCREATE TABLE accounts_rbr (\n  account_id STRING(40),\n  owner_id   STRING(40) NOT NULL,\n  status     STRING(20) NOT NULL,\n  region     crdb_internal_region\n    NOT NULL\n    DEFAULT gateway_region()::crdb_internal_region,\n  CONSTRAINT accounts_rbr_pkey PRIMARY KEY (region, account_id)\n) LOCALITY REGIONAL BY ROW AS region;\n```\n\n**Local allocation pattern:**\n\n```sql\nWITH candidate AS (\n  SELECT id, resource_code\n  FROM resource_pool\n  WHERE allocated_at IS NULL\n    AND region = gateway_region()::crdb_internal_region\n  ORDER BY random()\n  LIMIT 1\n  FOR UPDATE\n)\nUPDATE resource_pool\nSET allocated_at = now()\nWHERE id = (SELECT id FROM candidate);\n```\n\n#### D. GLOBAL Tables\n\n**Use when:**\n- The table is global/reference-style data\n- The workload is primarily about broad read locality rather than region-owned writes\n\n**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.\n\n#### E. Survival Goals\n\nChoose the survival goal based on the trade-off between write latency and durability:\n\n```sql\n-- Survive any single zone failure (default, 3+ zones required):\nALTER DATABASE mydb SURVIVE ZONE FAILURE;\n\n-- Survive an entire region going down (3+ regions required):\nALTER DATABASE mydb SURVIVE REGION FAILURE;\n```\n\n| Goal                   | Requirement | Write Latency                   | Data Safety              |\n|------------------------|-------------|---------------------------------|--------------------------|\n| SURVIVE ZONE FAILURE   | 3+ zones    | Low (local consensus)           | Survives 1 zone outage   |\n| SURVIVE REGION FAILURE | 3+ regions  | Higher (cross-region consensus) | Survives 1 region outage |\n\n`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.\n\n### Pattern Comparison\n\n| Aspect             | Regular Regional           | Manual Geo-Partition                    | REGIONAL BY ROW               | GLOBAL                    |\n|--------------------|----------------------------|-----------------------------------------|-------------------------------|---------------------------|\n| Write model        | Single primary region      | Active-active, region-keyed             | Active-active, row-affine     | Write from primary region |\n| Read locality      | Local to primary           | Local to partition                      | Local to row region           | All regions               |\n| Operational burden | Low                        | High                                    | Medium                        | Low                       |\n| Configuration      | Minimal                    | Explicit partitions, zones, lease prefs | Database-level abstractions   | Table-level declaration   |\n| Best for           | Simple primary-region apps | Full control over mechanics             | Developer-facing multi-region | Reference data            |\n\n## Live Demo Setup\n\nFor workshops and technical walkthroughs, use a 9-node local demo cluster to make multi-region locality observable.\n\n### Cluster Setup\n\n```bash\ncockroach demo \\\n  --nodes 9 \\\n  --no-example-database \\\n  --insecure \\\n  --demo-locality=\\\nregion=NA-NE,zone=NA-NE-1:\\\nregion=NA-NE,zone=NA-NE-2:\\\nregion=NA-NE,zone=NA-NE-3:\\\nregion=NA-MW,zone=NA-MW-1:\\\nregion=NA-MW,zone=NA-MW-2:\\\nregion=NA-MW,zone=NA-MW-3:\\\nregion=NA-NW,zone=NA-NW-1:\\\nregion=NA-NW,zone=NA-NW-2:\\\nregion=NA-NW,zone=NA-NW-3\n```\n\n### Demo Flow\n\n**Recommended presentation order:**\n\n1. Start with the manual geo-partitioning path\n2. Show explicit partitioning and zone configuration\n3. Run validation queries and confirm lease homing\n4. Switch to REGIONAL BY ROW\n5. Run RBR validations\n6. Compare operational surface area\n\n### Validation Queries\n\n**Manual partitioning validation:**\n\n```sql\nSHOW RANGES FROM INDEX accounts_manual_pkey WITH DETAILS;\n```\n\nCheck that:\n- All expected partition values are present\n- Lease holder locality matches partition region\n- Mismatches return FAIL, otherwise PASS\n\n**RBR validation:**\n\n```sql\nSHOW RANGES FROM TABLE accounts_rbr WITH DETAILS;\n```\n\nCheck that:\n- Leaseholder locality coverage includes the expected regions\n- There are no unexpected lease regions\n\n### Demo Talking Points\n\n**Manual path:**\n- Precise control over partitions, voters, replicas, and lease preferences\n- More DDL and operational burden\n- Best for teaching internals and understanding what the database does under the hood\n\n**RBR path:**\n- Keeps application intent front and center\n- Less manual configuration\n- Easier to explain for app teams\n- Still grounded in the same topology\n\n## Cross-Region Latency Guidance\n\nTransaction latency increases when the client is remote from the relevant leaseholder/quorum path.\n\n| Client Location            | Local RW Latency | Cross-Region RW Latency |\n|----------------------------|------------------|-------------------------|\n| Same region as leaseholder | ~10-20ms         | —                       |\n| Different region           | —                | ~50-150ms+              |\n\n**Guidance:**\n- Place latency-sensitive services close to their primary data locality\n- Use follower reads for non-critical display/reporting queries\n- Use multi-region table locality and zone configuration intentionally\n- Do not assume \"distributed\" means \"same latency everywhere\"\n\n## Output Expectations\n\nA strong answer using this skill should include:\n\n1. The recommended pattern\n2. Why it fits the workload\n3. What the application must do (routing, row affinity, primary-region assumptions)\n4. What CockroachDB manages automatically vs manually\n5. Expected latency shape or locality behavior\n6. A warning when the user is asking for something the chosen pattern does not optimize for\n\n## Guardrails\n\n- Do not claim that regular primary-region tables provide symmetric low-latency writes from all regions\n- Do not claim that `GLOBAL` is the answer for all-region low-latency writes without supporting documentation\n- When comparing manual geo-partitioning vs `REGIONAL BY ROW`, explicitly call out control vs simplicity\n- When the user wants to understand internal mechanics, bias toward explaining the manual model first\n- When the user wants the best default application pattern, bias toward `REGIONAL BY ROW` for region-affine data\n- Keep region names and locality labels consistent across all SQL\n- Do not mix manual and abstraction approaches in the same explanation unless explicitly comparing them\n- Always include validation, not just DDL\n\n## Multi-Region Migration Checklist\n\nFor teams migrating from single-region PostgreSQL/Oracle to multi-region CockroachDB:\n\n1. Deploy nodes with `--locality=region=<region>,zone=<zone>`\n2. Set primary region: `ALTER DATABASE <db> PRIMARY REGION '<region>'`\n3. Add regions: `ALTER DATABASE <db> ADD REGION '<region>'` (for each)\n4. Set survival goal: `ALTER DATABASE <db> SURVIVE ZONE|REGION FAILURE`\n5. Classify tables: GLOBAL (reference data), REGIONAL BY ROW (row-affine), REGIONAL BY TABLE (default)\n6. Set localities: `ALTER TABLE <t> SET LOCALITY <locality>`\n7. Monitor leaseholder distribution in DB Console\n8. Test failover: kill a zone/region and verify survival goal holds\n\n## Safety Considerations\n\n- Multi-region configuration changes affect data placement across the cluster\n- Test multi-region configurations on demo or staging clusters before production\n- Validate leaseholder placement after configuration changes\n- Allow time for range rebalancing after topology changes\n\n## References\n\n- [CockroachDB Multi-Region Overview](https://www.cockroachlabs.com/docs/stable/multiregion-overview)\n- [REGIONAL BY ROW Tables](https://www.cockroachlabs.com/docs/stable/regional-tables)\n- [GLOBAL Tables](https://www.cockroachlabs.com/docs/stable/global-tables)\n- [Follower Reads Documentation](https://www.cockroachlabs.com/docs/stable/follower-reads)\n- [CockroachDB Transactions](https://www.cockroachlabs.com/docs/stable/transactions)\n- [Performance Best Practices](https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview)\n- [Cross-Regional Latency Impact on Transactions](https://andrewdeally.medium.com/cross-regional-latency-impact-on-transactions-with-cockroachdb-a38e0dcb82f9)\n- [Query Parallelism with CockroachDB](https://andrewdeally.medium.com/when-and-how-to-use-query-parallelism-with-cockroachdb-df92fbe92845)\n- [CockroachDB Best Practices & Anti-Patterns Demo](https://github.com/viragtripathi/cockroachdb-best-practices-demo) -- Demo 10 covers multi-region patterns with runnable examples\n"
}