- 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 .
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 , 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 toREGIONAL BY TABLE and configure its home region, see .
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 , and is by default the region of the from which the row is inserted. TheREGIONAL BY ROW setting automatically divides a table and all of into 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 , 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 clauses to enable 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 that reference the in tables, unless for those tables.
- . A row’s home region will be automatically set to the gateway region of any or statements that write to those rows.
REGIONAL BY ROW and configure the home regions of its rows, see .
For more information on regional by row tables, see the Cockroach Labs blog post.
are not compatible with databases containing 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.
Indexes on REGIONAL BY ROW tables
In , most users should use instead of explicit index . When you add an index to a REGIONAL BY ROW table, it is automatically partitioned on the . Explicit index partitioning is not required.
While CockroachDB process an or statement on a particular database, creating or modifying an index will throw an error. Similarly, all and 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 .
For an example that uses unique indexes but applies to all indexes on REGIONAL BY ROW tables, see .
Regional by row tables can take advantage of 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 thepromo_codes table from the .
For instructions showing how to set a table’s locality to GLOBAL, see .
For more information about global tables, including troubleshooting information, see .
When to use regional vs. global tables
Use a if:- Your application requires low-latency reads and writes from a single region (either at the or the ).
- Access to the table’s data can be slower (higher latency) from other regions.
- 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 that uses a timestamp “in the future”. Note that the observed write latency is dependent on the setting.
250ms. This setting is especially helpful for lowering the write latency of . 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.