In a multi-region deployment, the GLOBAL
table locality is a good choice for tables with the following requirements:
- Read latency must be low, but write latency can be much higher.
- Reads must be up-to-date for business reasons or because the table is referenced by foreign keys.
- Rows in the table, and all latency-sensitive reads, cannot be tied to specific regions.
In general, this pattern is suited well for reference tables that are rarely updated.
Tables with the GLOBAL
locality can survive zone or region failures, depending on the database-level survival goal setting.
For new clusters using the multi-region SQL abstractions, we recommend lowering the --max-offset
setting to 250ms
. This is especially helpful for lowering the write latency of global tables. Note that this will require restarting all of the nodes in your cluster at the same time; it cannot be done with a rolling restart.
GLOBAL
tables (and the other multi-region capabilities) require an Enterprise license.
Prerequisites
Fundamentals
Multi-region patterns require thinking about the following questions:
- What are my survival goals? Do I need to survive a zone failure? A region failure?
- What are the table localities that will provide the performance characteristics I need for each table's data?
- Do I need low-latency reads and writes from a single region? Do I need that single region to be configurable at the row level? Or will a single optimized region for the entire table suffice?
- Do I have a "read-mostly" table of reference data that is rarely updated, but that must be read with low latency from all regions?
For more information about our multi-region capabilities, review the following pages:
- Multi-region overview
- Choosing a multi-region configuration
- When to use
ZONE
vs.REGION
Survival Goals - When to use
REGIONAL
vs.GLOBAL
Tables
In addition, reviewing the following information will be helpful:
- The concept of locality, which CockroachDB uses to intelligently place and balance data based on how you define the following settings:
- The recommendations in our Production Checklist.
- This page doesn't account for hardware specifications, so be sure to follow our hardware recommendations and perform a POC to size hardware for your use case.
- Finally, adopt these SQL Best Practices to get good performance.
Cluster setup
Each multi-region pattern assumes the following setup:
Hardware
- 3 regions
- Per region, 3+ AZs with 3+ VMs evenly distributed across them
- Region-specific app instances and load balancers
- Each load balancer redirects to CockroachDB nodes in its region.
- When CockroachDB nodes are unavailable in a region, the load balancer redirects to nodes in other regions.
Cluster
Each node is started with the --locality
flag specifying its region and AZ combination. For example, the following command starts a node in the west1
AZ of the us-west
region:
$ cockroach start \
--locality=region=us-west,zone=west1 \
--certs-dir=certs \
--advertise-addr=<node1 internal address> \
--join=<node1 internal address>:26257,<node2 internal address>:26257,<node3 internal address>:26257 \
--cache=.25 \
--max-sql-memory=.25 \
--background
Configuration
Summary
To use this pattern, you tell CockroachDB to set the table locality to GLOBAL
.
Global tables are optimized for low-latency reads from every region in the database. 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
Steps
First, create a database and set it as the default database:
CREATE DATABASE test;
USE test;
This cluster is already deployed across three regions. Therefore, to make this database a "multi-region database", you need to issue the following SQL statement that sets the primary region:
ALTER DATABASE test PRIMARY REGION "us-east";
Every multi-region database must have a primary region. For more information, see Database regions.
Next, issue the following ADD REGION
statements to add the remaining regions to the database.
ALTER DATABASE test ADD REGION "us-west";
ALTER DATABASE test ADD REGION "us-central";
Congratulations, test
is now a multi-region database!
Next, create a GLOBAL
table by issuing the following statement:
CREATE TABLE postal_codes (
id INT PRIMARY KEY,
code STRING
) LOCALITY GLOBAL;
Alternatively, you can set an existing table's locality to GLOBAL
using ALTER TABLE ... SET LOCALITY
:
> ALTER TABLE postal_codes SET LOCALITY GLOBAL;
A good way to check that your table locality settings are having the expected effect is by monitoring how the performance metrics of a workload change as the settings are applied to a running cluster. For a tutorial showing how table localities can improve performance metrics across a multi-region cluster, see Low Latency Reads and Writes in a Multi-Region Cluster.
Characteristics
Latency
Global tables support low-latency, global reads of read-mostly data using an extension to CockroachDB's standard transaction protocol called non-blocking transactions.
Reads
Thanks to the non-blocking transaction protocol extension, reads against GLOBAL
tables access a consistent local replica and therefore never leave the region. This keeps read latency low.
Writes
Writes incur higher latencies than reads, since they require a "commit-wait" step to ensure consistency. For more information about how this works, see non-blocking transactions.
Resiliency
Because the test
database does not specify a survival goal, it uses the default ZONE
survival goal. With the default settings, an entire zone can fail without interrupting access to the database.
For more information about how to choose a database survival goal, see When to use ZONE
vs. REGION
survival goals.
Alternatives
- If rows in the table, and all latency-sensitive queries, can be tied to specific geographies, consider the
REGIONAL
Table Locality Pattern pattern.
Tutorial
For a step-by-step demonstration showing how CockroachDB's multi-region capabilities (including GLOBAL
and REGIONAL
tables) give you low-latency reads in a distributed cluster, see the tutorial on Low Latency Reads and Writes in a Multi-Region Cluster.
See also
- Multi-Region Capabilities Overview
- Choosing a multi-region configuration
- When to use
ZONE
vs.REGION
survival goals - When to use
REGIONAL
vs.GLOBAL
tables - Multi-region SQL performance
- Migrate to Multi-region SQL
ALTER DATABASE ... SURVIVE {ZONE,REGION} FAILURE
ALTER TABLE ... SET LOCALITY ...
- Topology Patterns Overview
- Single-region
- Multi-region