global-tables.md

title	summary	toc	redirect_from
The Global Table Locality Pattern	Guidance on using the GLOBAL table locality pattern in a multi-region deployment.	true	topology-duplicate-indexes.html

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 queries, cannot be tied to specific geographies.

In general, this pattern is suited well for reference tables that are rarely updated.

{{site.data.alerts.callout_info}} Tables with the GLOBAL locality can survive zone or region failures, depending on the database-level survival goal setting. {{site.data.alerts.end}}

Prerequisites

Fundamentals

{% include {{ page.version.version }}/topology-patterns/multiregion-fundamentals.md %}

Cluster setup

{% include {{ page.version.version }}/topology-patterns/multi-region-cluster-setup.md %}

Configuration

{{site.data.alerts.callout_info}} GLOBAL tables (and the other multi-region capabilities) require an Enterprise license. {{site.data.alerts.end}}

Summary

Using this pattern, you tell CockroachDB to set the table locality to GLOBAL, and CockroachDB handles the details.

{% include {{page.version.version}}/sql/global-table-description.md %}

Steps

{% include {{page.version.version}}/topology-patterns/multiregion-db-setup.md %}

Next, create a GLOBAL table by issuing the following statement:

{% include copy-clipboard.html %}

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:

{% include copy-clipboard.html %}

> ALTER TABLE postal_codes SET LOCALITY GLOBAL;

To confirm that your postal_codes table data is replicated across the cluster in accordance with the GLOBAL table locality, check the Data Distribution debug page in the DB Console. It will look something like the output below (which is edited for length). Translating from zone configurations into human language, this output says:

• Make the database resilient to zone failures, with replicas in each region (this is the default ZONE survival goal).
• Put the leaseholders in us-east, since it's the primary database region.
• Finally, make the postal_codes table a global table.

ALTER DATABASE test CONFIGURE ZONE USING
    num_replicas = 5,
    num_voters = 3,
    constraints = '{+region=us-central: 1, +region=us-east: 1, +region=us-west: 1}',
    voter_constraints = '[+region=us-east]',
    lease_preferences = '[[+region=us-east]]'
...
ALTER TABLE test.public.postal_codes CONFIGURE ZONE USING
    global_reads = true

{{site.data.alerts.callout_success}} A better 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 to use table localities to improve performance metrics across a multi-region cluster, see Low Latency Reads and Writes in a Multi-Region Cluster. {{site.data.alerts.end}}

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 AZ 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 your application can tolerate historical reads in some cases, consider the Follower Reads pattern.
• 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

{% include {{ page.version.version }}/topology-patterns/see-also.md %}