ALTER LOCALITY

On this page Carat arrow pointing down

New in v22.2: The ALTER DATABASE .. ALTER LOCALITY statement provides a customization tool for advanced users to persistently modify the configuration generated by the standard multi-region SQL abstractions on a per-region basis.

The feature this statement enables is also known as Zone Config Extensions. For more information about how it works, and the benefits over using the low-level CONFIGURE ZONE statement, see Zone Config Extensions.

Note:

ALTER LOCALITY is a subcommand of ALTER DATABASE.

Synopsis

ALTER DATABASE <database> ALTER LOCALITY <locality_scope> CONFIGURE ZONE USING <set_zone_config>;

Parameters

Parameter Description
database The multi-region database containing the regions whose zone configs you want to modify.
locality_scope One of: GLOBAL (global tables), REGIONAL (all REGIONAL BY TABLE and REGIONAL BY ROW tables), or REGIONAL IN (all REGIONAL BY TABLE IN {region} tables and all {region} partitions of REGIONAL BY ROW tables).
set_zone_config The zone config you want to apply to the schema objects in that region.

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the database.

Examples

The following examples show:

Note:

We strongly recommend using the multi-region abstractions over "rolling your own" using Zone Config Extensions. These examples are provided to show the flexibility of Zone Config Extensions.

Setup

The setup described in this section will be used in the examples below.

Start a cluster

Start a cockroach demo cluster as follows:

icon/buttons/copy
cockroach demo --global --nodes=9

This gives us a (preloaded) MovR database on a cluster with the following regions, which can be viewed with SHOW REGIONS:

icon/buttons/copy
SHOW REGIONS;
     region    |  zones  | database_names | primary_region_of | secondary_region_of
---------------+---------+----------------+-------------------+----------------------
  europe-west1 | {b,c,d} | {}             | {}                | {}
  us-east1     | {b,c,d} | {}             | {}                | {}
  us-west1     | {a,b,c} | {}             | {}                | {}
(3 rows)

Make the database multi-region

Next, modify the database to use the multi-region abstractions as follows:

  1. Set the primary region using SET PRIMARY REGION.
  2. Add the other two regions using ADD REGION.
icon/buttons/copy
ALTER DATABASE movr SET PRIMARY REGION "us-east1";
icon/buttons/copy
ALTER DATABASE movr ADD REGION "us-west1";
icon/buttons/copy
ALTER DATABASE movr ADD REGION "europe-west1";

Override specific fields of a schema object's zone configs

In this example we will configure a multi-region MovR database to update its lease_preferences field.

Setting the primary region to us-east1 during the setup steps added us-east1 to lease_preferences.

icon/buttons/copy
SHOW ZONE CONFIGURATION FROM DATABASE movr;
     target     |                                      raw_config_sql
----------------+-------------------------------------------------------------------------------------------
  DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 5,
                |     num_voters = 3,
                |     constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
                |     voter_constraints = '[+region=us-east1]',
                |     lease_preferences = '[[+region=us-east1]]'
(1 row)

We will now use ALTER DATABASE ... ALTER LOCALITY to overwrite the lease_preferences field to add us-west1 to the list of regions:

icon/buttons/copy
ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "us-east1" CONFIGURE ZONE USING lease_preferences = '[[+region=us-east1], [+region=us-west1]]';

To view the updated zone configs, enter the following statement:

icon/buttons/copy
SHOW ZONE CONFIGURATION FROM DATABASE movr;
     target     |                                      raw_config_sql
----------------+-------------------------------------------------------------------------------------------
  DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 5,
                |     num_voters = 3,
                |     constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
                |     voter_constraints = '[+region=us-east1]',
                |     lease_preferences = '[[+region=us-east1], [+region=us-west1]]'
(1 row)

The lease_preferences field is now updated to include us-west1.

To remove the changes made in this example, reset the Zone Config Extensions.

Failover regions

In this example we will use Zone Config Extensions to configure a multi-region MovR database so that if the primary region fails, enough replicas will be found in another region (the "failover" region) to take over for the primary region. We will set the locality scope for this configuration to be REGIONAL IN, which covers all REGIONAL BY TABLE IN {region} tables and all {region} partitions of REGIONAL BY ROW tables.

Note:

This functionality is already provided by the built-in Secondary regions feature. It is used here to show the flexibility of Zone Config Extensions. We strongly recommend using the built-in multi-region features whenever possible.

First, set the database to have a REGION survival goal using ALTER DATABASE ... SURVIVE REGION FAILURE:

icon/buttons/copy
ALTER DATABASE movr SURVIVE REGION FAILURE;

Next, apply the REGIONAL BY ROW locality to the movr.rides table using the following statement.

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

Next, view the zone configs for the movr.rides table using SHOW ZONE CONFIGURATION:

icon/buttons/copy
SHOW ZONE CONFIGURATION FROM TABLE movr.rides;
     target     |                                      raw_config_sql
----------------+-------------------------------------------------------------------------------------------
  DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 5,
                |     num_voters = 5,
                |     constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
                |     voter_constraints = '{+region=us-east1: 2}',
                |     lease_preferences = '[[+region=us-east1]]'
(1 row)

Remember that we configured us-east1 to be our primary region during cluster setup. The output above confirms that us-east1 is the primary region based on the values of the voter_constraints and lease_preferences keys.

Next, we would like to configure us-west1 to be the failover region for us-east1, so we will update the configuration to keep additional voting replicas and leaseholders in us-west1. The following SQL statement accomplishes this by configuring us-east1 to keep additional voting replicas and leaseholders in us-west1. This means that if us-east1 fails, it will fail over to us-west1.

icon/buttons/copy
ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "us-east1" CONFIGURE ZONE USING voter_constraints = '{+region=us-east1: 2, +region=us-west1: 2}', lease_preferences = '[[+region=us-east1], [+region=us-west1]]';

After updating the configuration as shown above, view the zone configs for the movr.rides table using SHOW ZONE CONFIGURATION:

icon/buttons/copy
SHOW ZONE CONFIGURATION FROM TABLE movr.rides;
     target     |                                      raw_config_sql
----------------+-------------------------------------------------------------------------------------------
  DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 5,
                |     num_voters = 5,
                |     constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
                |     voter_constraints = '{+region=us-east1: 2, +region=us-west1: 2}',
                |     lease_preferences = '[[+region=us-east1], [+region=us-west1]]'
(1 row)

The following changes are shown:

  • There are now 2 voting replicas stored in us-west1.
  • There is now a preference that if leases cannot be placed in us-east1, they should be placed in us-west1.

Both of these changes combine to ensure that if us-east1 goes down, the cluster will still be able to operate until some mitigation is in place.

To remove the zone config changes made in this example, reset the Zone Config Extensions.

Reset a region's Zone Config Extensions

To reset the Zone Config Extension configuration applied to a region to the default settings, enter the following statement:

icon/buttons/copy
ALTER DATABASE movr ALTER LOCALITY CONFIGURE ZONE USING DEFAULT;
Note:

This will not reset any configuration created by the multi-region abstractions.

Discard a region's Zone Config Extensions

To discard the Zone Config Extension settings from a region, enter the following statement:

icon/buttons/copy
ALTER DATABASE movr ALTER LOCALITY CONFIGURE ZONE DISCARD;
Note:

When you discard a zone configuration, the objects it was applied to will then inherit a configuration from an object "the next level up"; e.g., if the object whose configuration is being discarded is a table, it will use its parent database's configuration.

However, this statement will not remove any configuration created by the multi-region abstractions.

See also


Yes No
On this page

Yes No