CONFIGURE ZONE

On this page

Warning:

CockroachDB v21.2 is no longer supported as of May 16, 2023. For more details, refer to the Release Support Policy.

CONFIGURE ZONE is a subcommand of the ALTER DATABASE, ALTER TABLE, ALTER INDEX, ALTER PARTITION, and ALTER RANGE statements and is used to add, modify, reset, or remove replication zones for those objects. To view details about existing replication zones, see SHOW ZONE CONFIGURATIONS.

In CockroachDB, you can use replication zones to control the number and location of replicas for specific sets of data, both when replicas are first added and when they are rebalanced to maintain cluster equilibrium.

Note:

Adding replication zones for secondary indexes and partitions is an Enterprise-only feature.

Synopsis

alter_zone_database_stmt ::=

alter_zone_table_stmt ::=

alter_zone_index_stmt ::=

alter_zone_partition_stmt ::=

alter_zone_range_stmt ::=

Required privileges

If the target is a system range, the system database, or a table in the system database, the user must be a member of the admin role. For all other databases and tables, the user must have been granted either the CREATE or the ZONECONFIG privilege on the target database or table.

Parameters

Parameter	Description
`range_name`	The name of the system range whose replication zone configurations you want to change.
`database_name`	The name of the database whose replication zone configurations you want to change. If you directly change a database's zone configuration with `ALTER DATABASE ... CONFIGURE ZONE`, CockroachDB will block all `ALTER DATABASE ... SET PRIMARY REGION` statements on the database.
`table_name`	The name of the table whose replication zone configurations you want to change.
`partition_name`	The name of the partition whose replication zone configurations you want to change.
`index_name`	The name of the index whose replication zone configurations you want to change.
`variable`	The name of the variable to change.
`value`	The value of the variable to change.
`DISCARD`	Remove a replication zone.

Variables

Variable	Description
`range_min_bytes`	The minimum size, in bytes, for a range of data in the zone. When a range is less than this size, CockroachDB will merge it with an adjacent range. Default: `134217728` (128 MiB)
`range_max_bytes`	The maximum size, in bytes, for a range of data in the zone. When a range reaches this size, CockroachDB will split it into two ranges. Default: `536870912` (512 MiB)
`gc.ttlseconds`	The number of seconds overwritten values will be retained before garbage collection. Smaller values can save disk space if values are frequently overwritten; larger values increase the range allowed for `AS OF SYSTEM TIME` queries, also know as Time Travel Queries. It is not recommended to set this below `600` (10 minutes); doing so will cause problems for long-running queries. Also, since all versions of a row are stored in a single range that never splits, it is not recommended to set this so high that all the changes to a row in that time period could add up to more than 512 MiB; such oversized ranges could contribute to the server running out of memory or other problems. Note: Ensure that you set `gc.ttlseconds` long enough to accommodate your backup schedule, otherwise your incremental backups will fail with this error. For example, if you set up your backup schedule to recur daily, but you set `gc.ttlseconds` to less than one day, all your incremental backups will fail. Default: `90000` (25 hours)
`num_replicas`	The number of replicas in the zone. Default: `3` For the `system` database and `.meta`, `.liveness`, and `.system` ranges, the default value is `5`. For multi-region databases configured to survive region failures, the default value is `5`; this will include both voting and non-voting replicas.
`constraints`	An array of required (`+`) and/or prohibited (`-`) constraints influencing the location of replicas. See Types of Constraints and Scope of Constraints for more details. To prevent hard-to-detect typos, constraints placed on store attributes and node localities must match the values passed to at least one node in the cluster. If not, an error is signalled. To prevent this error, make sure at least one active node is configured to match the constraint. For example, apply `constraints = '[+region=west]'` only if you had set `--locality=region=west` for at least one node while starting the cluster. Default: No constraints, with CockroachDB locating each replica on a unique node and attempting to spread replicas evenly across localities.
`lease_preferences`	An ordered list of required and/or prohibited constraints influencing the location of leaseholders. Whether each constraint is required or prohibited is expressed with a leading `+` or `-`, respectively. Note that lease preference constraints do not have to be shared with the `constraints` field. For example, it's valid for your configuration to define a `lease_preferences` field that does not reference any values from the `constraints` field. It's also valid to define a `lease_preferences` field with no `constraints` field at all. If the first preference cannot be satisfied, CockroachDB will attempt to satisfy the second preference, and so on. If none of the preferences can be met, the lease will be placed using the default lease placement algorithm, which is to base lease placement decisions on how many leases each node already has, trying to make all the nodes have around the same amount. Each value in the list can include multiple constraints. For example, the list `[[+zone=us-east-1b, +ssd], [+zone=us-east-1a], [+zone=us-east-1c, +ssd]]` means "prefer nodes with an SSD in `us-east-1b`, then any nodes in `us-east-1a`, then nodes in `us-east-1c` with an SSD." For a usage example, see Constrain leaseholders to specific availability zones. Default: No lease location preferences are applied if this field is not specified.
`global_reads`	If `true`, transactions operating on the range(s) affected by this zone config should be non-blocking, which slows down writes but allows reads from any replica in the range. Most users will not need to modify this setting; it is applied automatically when you use the `GLOBAL` table locality in a multi-region cluster.
`num_voters`	Specifies the number of voting replicas. When set, `num_replicas` will be the sum of voting and non-voting replicas. Most users will not need to modify this setting; it is part of the underlying machinery that enables improved multi-region capabilities in v21.1 and above.
`voter_constraints`	Specifies the constraints that govern the placement of voting replicas. This differs from the `constraints` field, which will govern the placement of all voting and non-voting replicas. Most users will not need to modify this setting; it is part of the underlying machinery that enables improved multi-region capabilities in v21.1 and above.

Note:

If a value is not set, new zone configurations will inherit their values from their parent zone (e.g., a partition zone inherits from the table zone), which is not necessarily default.

If a variable is set to COPY FROM PARENT (e.g., range_max_bytes = COPY FROM PARENT), the variable will copy its value from its parent replication zone. The COPY FROM PARENT value is a convenient shortcut to use so you do not have to look up the parent's current value. For example, the range_max_bytes and range_min_bytes variables must be set together, so when editing one value, you can use COPY FROM PARENT for the other. Note that if the variable in the parent replication zone is changed after the child replication zone is copied, the change will not be reflected in the child zone.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo with the --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the movr database.

$ cockroach demo --geo-partitioned-replicas

Edit a replication zone

> ALTER TABLE users CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4;

CONFIGURE ZONE 1

Edit the default replication zone

To edit the default replication zone, use the ALTER RANGE ... CONFIGURE ZONE statement to define the values you want to change (other values will remain the same):

> ALTER RANGE default CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

CONFIGURE ZONE 1

> SHOW ZONE CONFIGURATION FROM RANGE default;

     target     |              raw_config_sql
+---------------+------------------------------------------+
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 100000,
                |     num_replicas = 5,
                |     constraints = '[]',
                |     lease_preferences = '[]'
(1 row)

Create a replication zone for a database

To control replication for a specific database, use the ALTER DATABASE ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):

> ALTER DATABASE movr CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

CONFIGURE ZONE 1

> 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 = 100000,
                |     num_replicas = 5,
                |     constraints = '[]',
                |     lease_preferences = '[]'
(1 row)

Create a replication zone for a table

To control replication for a specific table, use the ALTER TABLE ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):

> ALTER TABLE users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

CONFIGURE ZONE 1

> SHOW ZONE CONFIGURATION FROM TABLE users;

    target    |             raw_config_sql
--------------+-----------------------------------------
  TABLE users | ALTER TABLE users CONFIGURE ZONE USING
              |     range_min_bytes = 134217728,
              |     range_max_bytes = 536870912,
              |     gc.ttlseconds = 100000,
              |     num_replicas = 5,
              |     constraints = '[]',
              |     lease_preferences = '[]'
(1 row)

Create a replication zone for a secondary index

Tip:

The Cost-based Optimizer can take advantage of replication zones for secondary indexes when optimizing queries.

Note:

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

The secondary indexes on a table will automatically use the replication zone for the table. However, with an enterprise license, you can add distinct replication zones for secondary indexes.

To control replication for a specific secondary index, use the ALTER INDEX ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone).

Tip:

To get the name of a secondary index, which you need for the CONFIGURE ZONE statement, use the SHOW INDEX or SHOW CREATE TABLE statements.

> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

CONFIGURE ZONE 1

> SHOW ZONE CONFIGURATION FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;

                         target                        |                                 raw_config_sql
+------------------------------------------------------+---------------------------------------------------------------------------------+
  INDEX vehicles@vehicles_auto_index_fk_city_ref_users | ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
                                                       |     range_min_bytes = 134217728,
                                                       |     range_max_bytes = 536870912,
                                                       |     gc.ttlseconds = 100000,
                                                       |     num_replicas = 5,
                                                       |     constraints = '[]',
                                                       |     lease_preferences = '[]'
(1 row)

Create a replication zone for a partition

Note:

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

Once partitions have been defined for a table or a secondary index, to control replication for a partition, use ALTER PARTITION <partition> OF INDEX <table@index> CONFIGURE ZONE:

> ALTER PARTITION us_west OF INDEX vehicles@primary
    CONFIGURE ZONE USING
      num_replicas = 5,
      constraints = '[+region=us-west1]';

CONFIGURE ZONE 1

> ALTER PARTITION us_west OF INDEX vehicles@vehicles_auto_index_fk_city_ref_users
    CONFIGURE ZONE USING
      num_replicas = 5,
      constraints = '[+region=us-west1]';

CONFIGURE ZONE 1

To define replication zones for identically named partitions of a table and its secondary indexes, you can use the <table>@* syntax to save several steps:

> ALTER PARTITION us_west OF INDEX vehicles@*
    CONFIGURE ZONE USING
      num_replicas = 5,
      constraints = '[+region=us-west1]';

To view the zone configuration for a partition, use SHOW ZONE CONFIGURATION FROM PARTITION <partition> OF INDEX <table@index>:

> SHOW ZONE CONFIGURATION FROM PARTITION us_west OF INDEX vehicles@primary;

                    target                    |                             raw_config_sql
----------------------------------------------+-------------------------------------------------------------------------
  PARTITION us_west OF INDEX vehicles@primary | ALTER PARTITION us_west OF INDEX vehicles@primary CONFIGURE ZONE USING
                                              |     range_min_bytes = 134217728,
                                              |     range_max_bytes = 536870912,
                                              |     gc.ttlseconds = 90000,
                                              |     num_replicas = 5,
                                              |     constraints = '[+region=us-west1]',
                                              |     lease_preferences = '[]'
(1 row)

Tip:

You can also use the SHOW CREATE TABLE statement or SHOW PARTITIONS statements to view details about all of the replication zones defined for the partitions of a table and its secondary indexes.

Create a replication zone for a system range

In addition to the databases and tables that are visible via the SQL interface, CockroachDB stores internal data in what are called system ranges. CockroachDB comes with pre-configured replication zones for some of these ranges:

Target Name	Description
`meta`	The "meta" ranges contain the authoritative information about the location of all data in the cluster. These ranges must retain a majority of replicas for the cluster as a whole to remain available and historical queries are never run on them, so CockroachDB comes with a pre-configured `meta` replication zone with `num_replicas` set to 5 to make these ranges more resilient to node failure and a lower-than-default `gc.ttlseconds` to keep these ranges smaller for reliable performance. If your cluster is running in multiple datacenters, it's a best practice to configure the meta ranges to have a copy in each datacenter.
`liveness`	The "liveness" range contains the authoritative information about which nodes are live at any given time. These ranges must retain a majority of replicas for the cluster as a whole to remain available and historical queries are never run on them, so CockroachDB comes with a pre-configured `liveness` replication zone with `num_replicas` set to 5 to make these ranges more resilient to node failure and a lower-than-default `gc.ttlseconds` to keep these ranges smaller for reliable performance.
`system`	There are system ranges for a variety of other important internal data, including information needed to allocate new table IDs and track the status of a cluster's nodes. These ranges must retain a majority of replicas for the cluster as a whole to remain available, so CockroachDB comes with a pre-configured `system` replication zone with `num_replicas` set to 5 to make these ranges more resilient to node failure.
`timeseries`	The "timeseries" ranges contain monitoring data about the cluster that powers the graphs in CockroachDB's DB Console. If necessary, you can add a `timeseries` replication zone to control the replication of this data.

Warning:

Use caution when editing replication zones for system ranges, as they could cause some (or all) parts of your cluster to stop working.

To control replication for one of the above sets of system ranges, use the ALTER RANGE ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):

> ALTER RANGE meta CONFIGURE ZONE USING num_replicas = 7;

CONFIGURE ZONE 1

> SHOW ZONE CONFIGURATION FROM RANGE meta;

    target   |            raw_config_sql
+------------+---------------------------------------+
  RANGE meta | ALTER RANGE meta CONFIGURE ZONE USING
             |     range_min_bytes = 134217728,
             |     range_max_bytes = 536870912,
             |     gc.ttlseconds = 3600,
             |     num_replicas = 7,
             |     constraints = '[]',
             |     lease_preferences = '[]'
(1 row)

Reset a replication zone

> ALTER TABLE t CONFIGURE ZONE USING DEFAULT;

CONFIGURE ZONE 1

Remove a replication zone

Note:

You cannot DISCARD any zone configurations on multi-region tables, indexes, or partitions if the multi-region abstractions created the zone configuration.

> ALTER TABLE t CONFIGURE ZONE DISCARD;

CONFIGURE ZONE 1

Cockroach
University

Docs Hub

CONFIGURE ZONE

Synopsis

Required privileges

Parameters

Variables

Viewing schema changes

Examples

Setup

Edit a replication zone

Edit the default replication zone

Create a replication zone for a database

Create a replication zone for a table

Create a replication zone for a secondary index

Create a replication zone for a partition

Create a replication zone for a system range

Reset a replication zone

Remove a replication zone

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

CONFIGURE ZONE

Synopsis

Required privileges

Parameters

Variables

Viewing schema changes

Examples

Setup

Edit a replication zone

Edit the default replication zone

Create a replication zone for a database

Create a replication zone for a table

Create a replication zone for a secondary index

Create a replication zone for a partition

Create a replication zone for a system range

Reset a replication zone

Remove a replication zone

See also

Cockroach
University

Cockroach
University