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 of its ranges is placed, along with a number of voting replicas determined by the survival goal of the database.

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 provide low-latency reads and writes for an entire table from a single region.
• 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 are optimized for low-latency reads from all 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, 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.

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, individual rows are optimized for access from different home regions. Each row's home region is specified in a hidden crdb_region column, and is by default the region of the gateway node from which the row is inserted. The REGIONAL BY ROW setting automatically divides a table and all of its indexes into partitions that use crdb_region as the prefix.

Use regional by row tables when your application requires low-latency reads and writes at a row level where individual rows are primarily accessed from a single region. For an example of a table in a multi-region cluster that can benefit from the REGIONAL BY ROW setting, see the users table from the MovR application, which could store users' data in specific regions for better performance.

To take advantage of 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.

For instructions showing how to set a table's locality to REGIONAL BY ROW and configure the home regions of its rows, see ALTER TABLE ... SET LOCALITY.

For more information on regional by row tables, see the Cockroach Labs blog post.

Indexes on REGIONAL BY ROW tables

In multi-region deployments, most users should use REGIONAL BY ROW tables instead of explicit index partitioning. When you add an index to a REGIONAL BY ROW table, it is automatically partitioned on the crdb_region column. Explicit index partitioning is not required.

While CockroachDB process an ADD REGION or DROP REGION statement on a particular database, creating or modifying an index will throw an error. Similarly, all ADD REGION and 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.

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.

Regional by row tables can take advantage of hash-sharded indexes 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.

For instructions showing how to set a table's locality to GLOBAL, see ALTER TABLE ... SET LOCALITY.

For more information about global tables, including troubleshooting information, see Global Tables.

When to use regional vs. global tables

Use a REGIONAL table locality if:

• Your application requires low-latency reads and writes from a single region (either at the row level or the table level).
• Access to the table's data can be slower (higher latency) from other regions.

Use a GLOBAL table locality 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 that uses a timestamp "in the future". Note that the observed write latency is dependent on the --max-offset setting.

See also

• Multi-Region Capabilities Overview
• How to Choose a Multi-Region Configuration
• When to Use ZONE vs. REGION Survival Goals
• Low Latency Reads and Writes in a Multi-Region Cluster
• Topology Patterns
• Disaster Recovery
• Migrate to Multi-Region SQL
• Secondary regions
• SET SECONDARY REGION
• DROP SECONDARY REGION