> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Regional Tables

In a [multi-region deployment](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality), [regional table locality](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality) is a good choice for tables with the following requirements:

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

Tables with regional table locality can survive zone or region failures, depending on the database-level [survival goal](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#survival-goals) setting.

For new clusters using the [multi-region SQL abstractions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality), Cockroach Labs recommends lowering the [`--max-offset`](https://docs.cockroachlabs.com/docs/stable/cockroach-start#flags-max-offset) setting to `250ms`. This setting is especially helpful for lowering the write latency of [global tables](https://docs.cockroachlabs.com/docs/stable/table-localities.md#global-tables). Nodes can run with different values for `--max-offset`, but only for the purpose of updating the setting across the cluster using a rolling upgrade.

## Before you begin

### Fundamentals

Multi-region patterns require thinking about the following questions:

* What are your [survival goals](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#survival-goals)?  Do you need to survive a [zone failure](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals.md#survive-zone-failures)? Do you need to survive a [region failure](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals.md#survive-region-failures)?
* What are the [table localities](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality) 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](https://docs.cockroachlabs.com/docs/stable/table-localities.md#regional-by-row-tables)? Or will [a single optimized region for the entire table](https://docs.cockroachlabs.com/docs/stable/table-localities.md#regional-tables) suffice?
  * Do you have a "read-mostly" [table of reference data that is rarely updated](https://docs.cockroachlabs.com/docs/stable/table-localities.md#global-tables), but that must be read with low latency from all regions?

For more information about CockroachDB multi-region capabilities, review the following pages:

* [Multi-Region Capabilities Overview](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality)
* [How to Choose a Multi-Region Configuration](https://docs.cockroachlabs.com/docs/stable/choosing-a-multi-region-configuration.md)
* [When to use `ZONE` vs. `REGION` Survival Goals](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals#when-to-use-zone-vs-region-survival-goals)
* [When to use `REGIONAL` vs. `GLOBAL` Tables](https://docs.cockroachlabs.com/docs/stable/table-localities#when-to-use-regional-vs-global-tables)

In addition, reviewing the following information will be helpful:

* The concept of [locality](https://docs.cockroachlabs.com/docs/stable/cockroach-start.md#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](https://docs.cockroachlabs.com/docs/stable/recommended-production-settings.md), including our [hardware recommendations](https://docs.cockroachlabs.com/docs/stable/recommended-production-settings.md#hardware). Afterwards, perform a proof of concept to size hardware for your use case.
* [SQL Performance Best Practices](https://docs.cockroachlabs.com/docs/stable/performance-best-practices-overview.md)

### Cluster setup

Each [multi-region pattern](https://docs.cockroachlabs.com/docs/stable/topology-patterns.md#multi-region) assumes the following setup:

![Multi-region hardware setup](https://mintcdn.com/cockroachlabs/iiBVxthghoAOO3UM/images/common/topology-patterns/topology_multi-region_hardware.png?fit=max\&auto=format\&n=iiBVxthghoAOO3UM\&q=85\&s=810cffba5f7294be67bc34e0b3276c28)

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

Start each node with the [`--locality`](https://docs.cockroachlabs.com/docs/stable/cockroach-start#locality) flag specifying its region and AZ combination. For example, the following command starts a node in the `west1` AZ of the `us-west` region:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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

To use this pattern, set the [table locality](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality) to either [`REGIONAL BY TABLE`](#regional-tables) or [`REGIONAL BY ROW`](#regional-by-row-tables).

#### Regional tables

In a *regional* table, access to the table will be fast in the table's home region and slower in other regions. In other words, CockroachDB optimizes access to data in a regional table from a single region. By default, a regional table's home region is the [database's primary region](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#database-regions), but that can be changed to use any region in the database. Regional tables work well when your application requires low-latency reads and writes for an entire table from a single region.

For instructions showing how to set a table's locality to `REGIONAL BY TABLE` and configure its home region, see [`ALTER TABLE ... SET LOCALITY`](https://docs.cockroachlabs.com/docs/stable/alter-table).

By default, all tables in a multi-region database are *regional* tables that use the database's primary region. Unless you know your application needs different performance characteristics than regional tables provide, there is no need to change this setting.

#### Regional by row tables

In a *regional by row* table, each row is optimized for access from a specific home region. Each row's home region is specified in a hidden [`crdb_region` column](https://docs.cockroachlabs.com/docs/stable/alter-table), which defaults to the region of the [gateway node](https://docs.cockroachlabs.com/docs/stable/architecture/life-of-a-distributed-transaction.md#gateway) that inserted the row. The `REGIONAL BY ROW` setting automatically [partitions](https://docs.cockroachlabs.com/docs/stable/partitioning.md) the table and all of [its indexes](https://docs.cockroachlabs.com/docs/stable/table-localities.md#indexes-on-regional-by-row-tables) by region using `crdb_region` as the partition key prefix.

Use regional by row tables when individual rows are frequently accessed from a single region, and your application requires low-latency reads and writes at the row level. A typical `REGIONAL BY ROW` use case is the `users` table in the [MovR application](https://docs.cockroachlabs.com/docs/stable/movr.md), where user data can be co-located with the user's region for better performance.

To take advantage of regional by row tables:

* Use unique key lookups or queries with [`LIMIT`](https://docs.cockroachlabs.com/docs/stable/limit-offset) clauses to enable [locality optimized searches](https://docs.cockroachlabs.com/docs/stable/cost-based-optimizer.md#locality-optimized-search-in-multi-region-clusters) 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](https://docs.cockroachlabs.com/docs/stable/foreign-key.md#rules-for-creating-foreign-keys) that reference the [`crdb_region` column](https://docs.cockroachlabs.com/docs/stable/alter-table) in [`REGIONAL BY ROW`](https://docs.cockroachlabs.com/docs/stable/table-localities#regional-by-row-tables) tables, unless [auto-rehoming is enabled](https://docs.cockroachlabs.com/docs/stable/alter-table.md#turn-on-auto-rehoming-for-regional-by-row-tables) for those tables.

* [Turn on auto-rehoming for regional by row tables](https://docs.cockroachlabs.com/docs/stable/alter-table.md#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`](https://docs.cockroachlabs.com/docs/stable/update) or [`UPSERT`](https://docs.cockroachlabs.com/docs/stable/upsert) statements that write to those rows.

For instructions showing how to set a table's locality to `REGIONAL BY ROW` and configure the home regions of its rows, refer to [`ALTER TABLE ... SET LOCALITY`](https://docs.cockroachlabs.com/docs/stable/alter-table).

For more information on regional by row tables, see the [Cockroach Labs blog post](https://www.cockroachlabs.com/blog/regional-by-row/).

[Secondary regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions) are not compatible with databases containing [`REGIONAL BY ROW`](https://docs.cockroachlabs.com/docs/stable/table-localities#regional-by-row-tables) tables. CockroachDB does not prevent you from defining secondary regions on databases with regional by row tables, but the interaction of these features is not supported. Therefore, Cockroach Labs recommends that you avoid defining secondary regions on databases that use regional by row table configurations.

### Steps

> [!NOTE]
>
> By default, all tables in a multi-region database are [regional tables](#regional-tables).  Therefore, the steps below show how to set up [regional by row tables](#regional-by-row-tables).

1. Create a database and set it as the default database:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE DATABASE test;
   ```

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   USE test;
   ```

   [This cluster is already deployed across three regions](#cluster-setup). Therefore, to make this database a "multi-region database", issue the following SQL statement to [set the primary region](https://docs.cockroachlabs.com/docs/stable/alter-database.md#set-the-primary-region):

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER DATABASE test PRIMARY REGION "us-east";
   ```

> [!NOTE]
>
> Every multi-region database must have a primary region.  For more information, see [Database regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#database-regions).

1. Issue the following [`ADD REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#add-region) statements to add the remaining regions to the database:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER DATABASE test ADD REGION "us-west";
   ```

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER DATABASE test ADD REGION "us-central";
   ```

2. Create a `users` table:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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`](#regional-tables) locality setting.  To verify this, issue a [`SHOW CREATE`](https://docs.cockroachlabs.com/docs/stable/show-create) on the `users` table you just created:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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`](#regional-by-row-tables) using the [`ALTER TABLE ... SET LOCALITY`](https://docs.cockroachlabs.com/docs/stable/alter-table#set-locality) statement:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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`](https://docs.cockroachlabs.com/docs/stable/update) statements that modify the automatically created [`crdb_region`](https://docs.cockroachlabs.com/docs/stable/alter-table) column. Issue the statements below to associate each row with a home region that depends on its `city` column:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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`](https://docs.cockroachlabs.com/docs/stable/alter-table).

Note that the [SQL engine](https://docs.cockroachlabs.com/docs/stable/architecture/sql-layer.md) will avoid sending requests to nodes in other regions when it can instead read a value from a unique column that is stored locally. This capability is known as [*locality optimized search*](https://docs.cockroachlabs.com/docs/stable/cost-based-optimizer.md#locality-optimized-search-in-multi-region-clusters).

> [!TIP]
>
> A good way to check that your [table locality settings](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality) are having the expected effect is by monitoring how the performance metrics of a workload change as the settings are applied to a running cluster.

## Characteristics

### Latency

For [`REGIONAL BY TABLE` tables](#regional-tables), you get low latency for single-region writes and reads, as well as multi-region stale reads.

For [`REGIONAL BY ROW` tables](#regional-by-row-tables), you get low-latency consistent multi-region reads & writes for rows which are homed in specific regions, and low-latency multi-region stale reads from all other regions.

### Resiliency

Because the `test` database does not specify a [survival goal](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#survival-goals), it uses the default [`ZONE` survival goal](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals#survive-zone-failures).  With the default settings, an entire availability zone (AZ) can fail without interrupting access to the database.

For more information about how to choose a database survival goal, see [When to use `ZONE` vs. `REGION` survival goals](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals#when-to-use-zone-vs-region-survival-goals).

## 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](https://docs.cockroachlabs.com/docs/stable/foreign-key.md), and the table is rarely modified, consider the [`GLOBAL` Table Locality Pattern](https://docs.cockroachlabs.com/docs/stable/global-tables).
* If your application can tolerate historical reads in some cases, consider the [Follower Reads pattern](https://docs.cockroachlabs.com/docs/stable/topology-follower-reads.md).

## Demo video

If you'd prefer to watch a video on Regional Tables, check out the following video:

[YouTube video player](https://www.youtube.com/embed/3gmF_yXj3q8)

## See also

* [Multi-Region Capabilities Overview](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#table-locality)
* [How to Choose a Multi-Region Configuration](https://docs.cockroachlabs.com/docs/stable/choosing-a-multi-region-configuration.md)
* [When to Use `ZONE` vs. `REGION` Survival Goals](https://docs.cockroachlabs.com/docs/stable/multiregion-survival-goals#when-to-use-zone-vs-region-survival-goals)
* [When to Use `REGIONAL` vs. `GLOBAL` Tables](https://docs.cockroachlabs.com/docs/stable/table-localities#when-to-use-regional-vs-global-tables)
* [Secondary regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions)
* [`ALTER DATABASE ... SET SECONDARY REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#set-secondary-region)
* [`ALTER DATABASE ... DROP SECONDARY REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#drop-secondary-region)
* [Topology Patterns Overview](https://docs.cockroachlabs.com/docs/stable/topology-patterns.md)
  * Single-region patterns
    * [Basic Production](https://docs.cockroachlabs.com/docs/stable/topology-basic-production.md)
  * Multi-region patterns
    * [`REGIONAL` Tables](https://docs.cockroachlabs.com/docs/stable/regional-tables)
    * [`GLOBAL` Tables](https://docs.cockroachlabs.com/docs/stable/global-tables)
    * [Follower Reads](https://docs.cockroachlabs.com/docs/stable/topology-follower-reads.md)
    * [Follow-the-Workload](https://docs.cockroachlabs.com/docs/stable/topology-follow-the-workload.md)