Table Localities

On this page Carat arrow pointing down

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

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:

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.
Tip:

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


Yes No
On this page

Yes No