The ALTER DATABASE .. ADD SUPER REGION
statement adds a super region to a multi-region database.
This is an enterprise-only feature. Request a 30-day trial license to try it out.
ADD SUPER REGION
is a subcommand of ALTER DATABASE
.
This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.
Synopsis
Parameters
Parameter | Description |
---|---|
database_name |
The database to which you are adding a super region. |
name |
The name of the super region being added to this database. |
name_list |
The super region consists of this set of database regions. |
Required privileges
To add a super region to a database, the user must have one of the following:
- Membership to the
admin
role for the cluster. - Either ownership or the
CREATE
privilege for the database.
Considerations
To use super regions, you must keep the following considerations in mind:
- Your cluster must be a multi-region cluster.
- Super regions must be enabled.
- Super regions can only contain one or more database regions that have already been added with
ADD REGION
. - Each database region can only belong to one super region. In other words, given two super regions A and B, the set of database regions in A must be disjoint from the set of database regions in B.
- You cannot drop a region that is part of a super region until you either alter the super region to remove it, or drop the super region altogether.
Examples
The examples in this section use the following setup.
Setup
Only a cluster region specified at node startup can be used as a database region.
To follow along with the examples below, start a demo cluster with the --global
flag to simulate a multi-region cluster:
$ cockroach demo --global --nodes 9
To see the regions available to the databases in the cluster, use a SHOW REGIONS FROM CLUSTER
statement:
SHOW REGIONS FROM CLUSTER;
region | zones
---------------+----------
europe-west1 | {b,c,d}
us-east1 | {b,c,d}
us-west1 | {a,b,c}
(3 rows)
Set up movr database regions
Execute the following statements. They will tell CockroachDB about the database's regions. This information is necessary so that CockroachDB can later move data around to optimize access to particular data from particular regions. For more information about how this works at a high level, see Database Regions.
ALTER DATABASE movr PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "us-west1";
Set up movr global tables
Because the data in promo_codes
is not updated frequently (a.k.a., "read-mostly"), and needs to be available from any region, the right table locality is GLOBAL
.
ALTER TABLE promo_codes SET locality GLOBAL;
Next, alter the user_promo_codes
table to have a foreign key into the global promo_codes
table. This will enable fast reads of the promo_codes.code
column from any region in the cluster.
ALTER TABLE user_promo_codes
ADD CONSTRAINT user_promo_codes_code_fk
FOREIGN KEY (code)
REFERENCES promo_codes (code)
ON UPDATE CASCADE;
Set up movr regional tables
All of the tables except promo_codes
contain rows which are partitioned by region, and updated very frequently. For these tables, the right table locality for optimizing access to their data is REGIONAL BY ROW
.
Apply this table locality to the remaining tables. These statements use a CASE
statement to put data for a given city in the right region and can take around 1 minute to complete for each table.
rides
ALTER TABLE rides ADD COLUMN region crdb_internal_region AS ( CASE WHEN city = 'amsterdam' THEN 'europe-west1' WHEN city = 'paris' THEN 'europe-west1' WHEN city = 'rome' THEN 'europe-west1' WHEN city = 'new york' THEN 'us-east1' WHEN city = 'boston' THEN 'us-east1' WHEN city = 'washington dc' THEN 'us-east1' WHEN city = 'san francisco' THEN 'us-west1' WHEN city = 'seattle' THEN 'us-west1' WHEN city = 'los angeles' THEN 'us-west1' END ) STORED; ALTER TABLE rides ALTER COLUMN REGION SET NOT NULL; ALTER TABLE rides SET LOCALITY REGIONAL BY ROW AS "region";
user_promo_codes
ALTER TABLE user_promo_codes ADD COLUMN region crdb_internal_region AS ( CASE WHEN city = 'amsterdam' THEN 'europe-west1' WHEN city = 'paris' THEN 'europe-west1' WHEN city = 'rome' THEN 'europe-west1' WHEN city = 'new york' THEN 'us-east1' WHEN city = 'boston' THEN 'us-east1' WHEN city = 'washington dc' THEN 'us-east1' WHEN city = 'san francisco' THEN 'us-west1' WHEN city = 'seattle' THEN 'us-west1' WHEN city = 'los angeles' THEN 'us-west1' END ) STORED; ALTER TABLE user_promo_codes ALTER COLUMN REGION SET NOT NULL; ALTER TABLE user_promo_codes SET LOCALITY REGIONAL BY ROW AS "region";
users
ALTER TABLE users ADD COLUMN region crdb_internal_region AS ( CASE WHEN city = 'amsterdam' THEN 'europe-west1' WHEN city = 'paris' THEN 'europe-west1' WHEN city = 'rome' THEN 'europe-west1' WHEN city = 'new york' THEN 'us-east1' WHEN city = 'boston' THEN 'us-east1' WHEN city = 'washington dc' THEN 'us-east1' WHEN city = 'san francisco' THEN 'us-west1' WHEN city = 'seattle' THEN 'us-west1' WHEN city = 'los angeles' THEN 'us-west1' END ) STORED; ALTER TABLE users ALTER COLUMN REGION SET NOT NULL; ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS "region";
vehicle_location_histories
ALTER TABLE vehicle_location_histories ADD COLUMN region crdb_internal_region AS ( CASE WHEN city = 'amsterdam' THEN 'europe-west1' WHEN city = 'paris' THEN 'europe-west1' WHEN city = 'rome' THEN 'europe-west1' WHEN city = 'new york' THEN 'us-east1' WHEN city = 'boston' THEN 'us-east1' WHEN city = 'washington dc' THEN 'us-east1' WHEN city = 'san francisco' THEN 'us-west1' WHEN city = 'seattle' THEN 'us-west1' WHEN city = 'los angeles' THEN 'us-west1' END ) STORED; ALTER TABLE vehicle_location_histories ALTER COLUMN REGION SET NOT NULL; ALTER TABLE vehicle_location_histories SET LOCALITY REGIONAL BY ROW AS "region";
vehicles
ALTER TABLE vehicles ADD COLUMN region crdb_internal_region AS ( CASE WHEN city = 'amsterdam' THEN 'europe-west1' WHEN city = 'paris' THEN 'europe-west1' WHEN city = 'rome' THEN 'europe-west1' WHEN city = 'new york' THEN 'us-east1' WHEN city = 'boston' THEN 'us-east1' WHEN city = 'washington dc' THEN 'us-east1' WHEN city = 'san francisco' THEN 'us-west1' WHEN city = 'seattle' THEN 'us-west1' WHEN city = 'los angeles' THEN 'us-west1' END ) STORED; ALTER TABLE vehicles ALTER COLUMN REGION SET NOT NULL; ALTER TABLE vehicles SET LOCALITY REGIONAL BY ROW AS "region";
Enable super regions
To enable super regions, set the enable_super_regions
session setting to 'on'
:
SET enable_super_regions = 'on';
SET
You can also set the sql.defaults.super_regions.enabled
cluster setting to true
:
SET CLUSTER SETTING sql.defaults.super_regions.enabled = true;
SET CLUSTER SETTING
Add a super region to a database
To add a super region to a multi-region database, use the ALTER DATABASE ... ADD SUPER REGION
statement:
ALTER DATABASE movr ADD SUPER REGION "usa" VALUES "us-east1", "us-west1";
ALTER DATABASE ADD SUPER REGION
Allow user to modify a primary region that is part of a super region
By default, you may not change the primary region of a multi-region database when that region is part of a super region. This is a safety setting designed to prevent you from accidentally moving the data for a regional table that is meant to be stored in the super region out of that super region, which could break your data domiciling setup.
If you are sure about what you are doing, you can allow modifying the primary region by setting the alter_primary_region_super_region_override
session setting to 'on'
:
SET alter_primary_region_super_region_override = 'on';
SET
You can also accomplish this by setting the sql.defaults.alter_primary_region_super_region_override.enable
cluster setting to true
:
SET CLUSTER SETTING sql.defaults.alter_primary_region_super_region_override.enable = true;
SET CLUSTER SETTING