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

# Table Localities

*Table locality* determines how CockroachDB optimizes access to the table's data. Every table in a multi-region database has a "table locality setting" that configures one or more *home regions* at the table or row level. A table or row's home region is where the [leaseholder](/docs/stable/architecture/replication-layer.md#leases) of its ranges is placed, along with a number of voting replicas determined by the applicable [survival goal](/docs/stable/multiregion-survival-goals.md#when-to-use-zone-vs-region-survival-goals). Data confined to a [super region](/docs/stable/multiregion-overview.md#super-regions) uses that super region's explicit survival goal, or inherits the database survival goal if the super region has no explicit goal.

By default, all tables in a multi-region database are *regional* tables—that is, CockroachDB optimizes access to the table's data from a single home region (by default, the database's primary region).

For information about the table localities CockroachDB supports, see the sections:

* [Regional tables](#regional-tables) provide low-latency reads and writes for an entire table from **a single region**.
* [Regional by row tables](#regional-by-row-tables) provide low-latency reads and writes for one or more rows of a table from **a single region**. Different rows in the table can be optimized for access from **different regions**.
* [Global tables](#global-tables) are optimized for low-latency reads from **all regions**.

> [!NOTE]
>
> Table locality settings are used for optimizing latency under different read and write patterns. If you are optimizing for read and write access to all of your tables from a single region (the primary region), there is nothing else you need to do once you set your [database's primary region](/docs/stable/multiregion-overview.md#database-regions).

## 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](/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`](/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](/docs/stable/alter-table), which defaults to the region of the [gateway node](/docs/stable/architecture/life-of-a-distributed-transaction.md#gateway) that inserted the row. The `REGIONAL BY ROW` setting automatically [partitions](/docs/stable/partitioning.md) the table and all of [its indexes](/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](/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`](/docs/stable/limit-offset) clauses to enable [locality optimized searches](/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](/docs/stable/foreign-key.md#rules-for-creating-foreign-keys) that reference the [`crdb_region` column](/docs/stable/alter-table) in [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables, unless [auto-rehoming is enabled](/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](/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`](/docs/stable/update) or [`UPSERT`](/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`](/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](/docs/stable/multiregion-overview.md#secondary-regions) are not compatible with databases containing [`REGIONAL BY ROW`](/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.

To change the column or expression used to determine row locality for a regional by row table defined with `REGIONAL BY ROW AS {column}`, refer to [Modify the region column or its expression](/docs/stable/alter-table.md).

### Indexes on `REGIONAL BY ROW` tables

In [multi-region deployments](/docs/stable/multiregion-overview.md), most users should use [`REGIONAL BY ROW` tables](/docs/stable/table-localities#regional-by-row-tables) instead of explicit index [partitioning](/docs/stable/partitioning.md). When you add an index to a `REGIONAL BY ROW` table, it is automatically partitioned on the [`crdb_region` column](/docs/stable/alter-table). Explicit index partitioning is not required.

While CockroachDB process an [`ADD REGION`](/docs/stable/alter-database#add-region) or [`DROP REGION`](/docs/stable/alter-database#drop-region) statement on a particular database, creating or modifying an index will throw an error. Similarly, all [`ADD REGION`](/docs/stable/alter-database#add-region) and [`DROP REGION`](/docs/stable/alter-database#drop-region) statements will be blocked while an index is being modified on a `REGIONAL BY ROW` table within the same database.

This behavior also applies to [GIN indexes](/docs/stable/inverted-indexes.md).

For an example that uses unique indexes but applies to all indexes on `REGIONAL BY ROW` tables, see [Add a unique index to a `REGIONAL BY ROW` table](/docs/stable/alter-table#add-a-unique-index-to-a-regional-by-row-table).

Regional by row tables can take advantage of [hash-sharded indexes](/docs/stable/hash-sharded-indexes.md) provided the `crdb_region` column is not part of the columns in the hash-sharded index.

## Global tables

A *global* table is optimized for low-latency reads from every region in the database. This means that any region can effectively act as the home region of the table. The tradeoff is that writes will incur higher latencies from any given region, since writes have to be replicated across every region to make the global low-latency reads possible. Use global tables when your application has a "read-mostly" table of reference data that is rarely updated, and needs to be available to all regions.

For an example of a table that can benefit from the *global* table locality setting in a multi-region deployment, see the `promo_codes` table from the [MovR application](/docs/stable/movr.md).

For instructions showing how to set a table's locality to `GLOBAL`, see [`ALTER TABLE ... SET LOCALITY`](/docs/stable/alter-table).

For more information about global tables, including troubleshooting information, see [Global Tables](/docs/stable/global-tables.md).

## When to use regional vs. global tables

Use a [`REGIONAL` table locality](/docs/stable/table-localities#regional-by-row-tables) if:

* Your application requires low-latency reads and writes from a single region (either at the [row level](/docs/stable/table-localities.md#regional-by-row-tables) or the [table level](/docs/stable/table-localities.md#regional-tables)).
* Access to the table's data can be slower (higher latency) from other regions.

Use a [`GLOBAL` table locality](/docs/stable/table-localities#global-tables) if:

* Your application has a "read-mostly" table of reference data that is rarely updated, and that needs to be available to all regions.
* You can accept that writes to the table will incur higher latencies from any given region, since writes use a novel [non-blocking transaction protocol](/docs/stable/architecture/transaction-layer.md#non-blocking-transactions) that uses a timestamp "in the future". Note that the observed write latency is dependent on the [`--max-offset`](/docs/stable/cockroach-start#flags-max-offset) setting.

For new clusters using the [multi-region SQL abstractions](/docs/stable/multiregion-overview.md), Cockroach Labs recommends lowering the [`--max-offset`](/docs/stable/cockroach-start#flags-max-offset) setting to `250ms`. This setting is especially helpful for lowering the write latency of [global tables](/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.

## See also

* [Multi-Region Capabilities Overview](/docs/stable/multiregion-overview.md)
* [How to Choose a Multi-Region Configuration](/docs/stable/choosing-a-multi-region-configuration.md)
* [When to Use `ZONE` vs. `REGION` Survival Goals](/docs/stable/multiregion-survival-goals#when-to-use-zone-vs-region-survival-goals)
* [Topology Patterns](/docs/stable/topology-patterns.md)
* [Disaster Recovery](/docs/stable/disaster-recovery-planning.md)
* [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions)
* [`SET SECONDARY REGION`](/docs/stable/alter-database#set-secondary-region)
* [`DROP SECONDARY REGION`](/docs/stable/alter-database#drop-secondary-region)

For an in-depth data placement support comparison and a demonstration of how the [Federal Wire Act (1961)](https://en.wikipedia.org/wiki/Federal_Wire_Act) can only be satisfied with CockroachDB, watch the following video:

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