Regional Tables

• Read and write latency must be low.
• Rows in the table, and all latency-sensitive queries, can be tied to specific regions.

​

Before you begin

​

Fundamentals

• What are your survival goals? Do you need to survive a zone failure? Do you need to survive a region failure?
• What are the table localities that will provide the performance characteristics you need for each table’s data?
  • Do you need low-latency reads and writes from a single region? Do you need that single region to be configurable at the row level? Or will a single optimized region for the entire table suffice?
  • Do you have a “read-mostly” table of reference data that is rarely updated, but that must be read with low latency from all regions?

• Multi-Region Capabilities Overview
• How to Choose a Multi-Region Configuration
• When to use ZONE vs. REGION Survival Goals
• When to use REGIONAL vs. GLOBAL Tables

• The concept of locality, which CockroachDB uses to place and balance data based on how you define survival goal and table locality settings.
• The recommendations in our Production Checklist, including our hardware recommendations. Afterwards, perform a proof of concept to size hardware for your use case.
• SQL Performance Best Practices

​

Cluster setup

​

Hardware

• 3 regions
• Per region, 3+ AZs with 3+ VMs evenly distributed across them
• Region-specific app instances and load balancers
  • Each load balancer redirects to CockroachDB nodes in its region.
  • When CockroachDB nodes are unavailable in a region, the load balancer redirects to nodes in other regions.

​

Cluster startup

$ cockroach start \
--locality=region=us-west,zone=west1 \
--certs-dir=certs \
--advertise-addr=<node1 internal address \
--join=<node1 internal address:26257,<node2 internal address:26257,<node3 internal address:26257 \
--cache=.25 \
--max-sql-memory=.25 \
--background

​

Configuration

​

Summary

​

Regional tables

​

Regional by row tables

• Use unique key lookups or queries with LIMIT clauses to enable locality optimized searches that prioritize rows in the gateway node’s region. If there is a possibility that the results of the query all live in local rows, CockroachDB will first search for rows in the gateway node’s region. The search only continues in remote regions if rows in the local region did not satisfy the query.
• Use foreign keys that reference the crdb_region column in REGIONAL BY ROW tables, unless auto-rehoming is enabled for those tables.
• Turn on auto-rehoming for regional by row tables. A row’s home region will be automatically set to the gateway region of any UPDATE or UPSERT statements that write to those rows.

​

Steps

1. Create a database and set it as the default database:
   CREATE DATABASE test;
   
   USE test;
   
   This cluster is already deployed across three regions. Therefore, to make this database a “multi-region database”, issue the following SQL statement to set the primary region:
   ALTER DATABASE test PRIMARY REGION "us-east";
   

1. Issue the following ADD REGION statements to add the remaining regions to the database:
   ALTER DATABASE test ADD REGION "us-west";
   
   ALTER DATABASE test ADD REGION "us-central";
   
2. Create a users table:
   CREATE TABLE users (
       id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
       city STRING NOT NULL,
       first_name STRING NOT NULL,
       last_name STRING NOT NULL,
       address STRING NOT NULL
   );
   
   By default, all tables in a multi-region cluster default to the REGIONAL BY TABLE locality setting. To verify this, issue a SHOW CREATE on the users table you just created:
   SHOW CREATE TABLE users;
   
     table_name |                        create_statement
   -------------+------------------------------------------------------------------
     users      | CREATE TABLE public.users (
                |     id UUID NOT NULL DEFAULT gen_random_uuid(),
                |     city STRING NOT NULL,
                |     first_name STRING NOT NULL,
                |     last_name STRING NOT NULL,
                |     address STRING NOT NULL,
                |     CONSTRAINT "primary" PRIMARY KEY (id ASC),
                |     FAMILY "primary" (id, city, first_name, last_name, address)
                | ) LOCALITY REGIONAL BY TABLE IN PRIMARY REGION
   
3. Set the table’s locality to REGIONAL BY ROW using the ALTER TABLE ... SET LOCALITY statement:
   ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
   
   NOTICE: LOCALITY changes will be finalized asynchronously; further schema changes on this table may be restricted until the job completes
   ALTER TABLE SET LOCALITY
   
4. Identify which rows need to be optimized for access from which regions. Issue UPDATE statements that modify the automatically created crdb_region column. Issue the statements below to associate each row with a home region that depends on its city column:
   UPDATE users SET crdb_region = 'us-central'   WHERE city IN ('chicago', 'milwaukee', 'dallas');
   UPDATE users SET crdb_region = 'us-east'      WHERE city IN ('washington dc', 'boston', 'new york');
   UPDATE users SET crdb_region = 'us-west'      WHERE city IN ('los angeles', 'san francisco', 'seattle');
   
   By default, the region column will get auto-assigned on insert; this is also known as “auto-homing”. For more information about how the crdb_region column works, see ALTER TABLE ... SET LOCALITY REGIONAL BY ROW.

​

Characteristics

​

Latency

​

Resiliency

​

Alternatives

• If rows in the table cannot be tied to specific geographies, reads must be up-to-date for business reasons or because the table is referenced by foreign keys, and the table is rarely modified, consider the GLOBAL Table Locality Pattern.
• If your application can tolerate historical reads in some cases, consider the Follower Reads pattern.

​

Tutorial

​

See also

• Multi-Region Capabilities Overview
• How to Choose a Multi-Region Configuration
• When to Use ZONE vs. REGION Survival Goals
• When to Use REGIONAL vs. GLOBAL Tables
• Low Latency Reads and Writes in a Multi-Region Cluster
• Migrate to Multi-Region SQL
• Secondary regions
• ALTER DATABASE ... SET SECONDARY REGION
• ALTER DATABASE ... DROP SECONDARY REGION
• Topology Patterns Overview
  • Single-region patterns
    • Development
    • Basic Production
  • Multi-region patterns
    • REGIONAL Tables
    • GLOBAL Tables
    • Follower Reads
    • Follow-the-Workload