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.
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.
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 inREGIONAL 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
orUPSERT
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.
For new clusters using the multi-region SQL abstractions, Cockroach Labs recommends lowering the --max-offset
setting to 250ms
. This setting is especially helpful for lowering the write latency of 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
- 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