Global Tables

On this page Carat arrow pointing down
Warning:
CockroachDB v22.1 is no longer supported. For more details, see the Release Support Policy.

In a multi-region deployment, 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.

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. For existing clusters, changing the setting will require restarting all of the nodes in your cluster at the same time; it cannot be done with a rolling restart.

Before you begin

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

Fundamentals

Multi-region patterns require thinking about the following questions:

For more information about CockroachDB multi-region capabilities, review the following pages:

In addition, reviewing the following information will be helpful:

Cluster setup

Each multi-region pattern assumes the following setup:

Multi-region hardware 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 startup

Start each node 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:

icon/buttons/copy
$ 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, set the table locality to GLOBAL.

A global table is 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.

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

Steps

  1. Create a database and set it as the default database:

    icon/buttons/copy
    CREATE DATABASE test;
    
    icon/buttons/copy
    USE test;
    

    This cluster is already deployed across three regions. Therefore, to make this database a "multi-region database", issue the following SQL statement to set the primary region:

    icon/buttons/copy
    ALTER DATABASE test PRIMARY REGION "us-east";
    
    Note:

    Every multi-region database must have a primary region. For more information, see Database regions.

  2. Issue the following ADD REGION statements to add the remaining regions to the database:

    icon/buttons/copy
    ALTER DATABASE test ADD REGION "us-west";
    
    icon/buttons/copy
    ALTER DATABASE test ADD REGION "us-central";
    
  3. Create a GLOBAL table by issuing the following statement:

    icon/buttons/copy
    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:

    icon/buttons/copy
    > ALTER TABLE postal_codes SET LOCALITY GLOBAL;
    
Tip:

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.

Troubleshooting

High follower read latency on global tables

Reads on multi-region global tables can experience sporadic high latency on follower reads if the round trip time between cluster nodes is higher than 150ms. To work around this issue, consider setting the kv.closed_timestamp.lead_for_global_reads_override cluster setting to a value greater than 800ms.

The value of kv.closed_timestamp.lead_for_global_reads_override will impact write latency to global tables, so you should proceed in 100ms increments until the high read latency no longer occurs. If you've increased the setting to 1500ms and the problem persists, you should contact support.

Alternatives

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


Yes No
On this page

Yes No