# CONFIGURE ZONE

New in v2.1: Use `CONFIGURE ZONE` to add, modify, reset, and remove replication zones. 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 rows and secondary indexes is an enterprise-only feature.

## Synopsis

alter_zone_range_stmt ::=

alter_zone_database_stmt ::=

alter_zone_table_stmt ::=

alter_zone_index_stmt ::=

## Required privileges

Currently, only the `root` user can configure replication zones.

## Parameters

Parameter | Description
--- | ---
`range_name` | The name of the system range for which to show replication zone configurations.
`database_name` | The name of the database for which to show replication zone configurations.
`table_name` | The name of the table for which to show replication zone configurations.
`partition_name` | The name of the partition for which to show replication zone configurations.
`index_name` | The name of the index for which to show replication zone configurations.
`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: `1048576` (1MiB)
`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: `67108864` (64MiB)
`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 64MiB; such oversized ranges could contribute to the server running out of memory or other problems. 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`.
`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. 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 datacenters. Default: No lease location preferences are applied if this field is not specified.

## Examples

### Edit a replication zone

    > ALTER TABLE t CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4, constraints = '[-region=west]';

    CONFIGURE ZONE 1

### Edit the default replication zone

Note:

Changes to the `.default` cluster-wide replication zone are not automatically applied to existing replication zones, including pre-configured zones for important system ranges that must remain available for the cluster as a whole to remain available. The zones for these system ranges have an initial replication factor of 5 to make them more resilient to node failure. However, if you increase the `.default` zone's replication factor above 5, consider increasing the replication factor for important system ranges as well.

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 FOR RANGE default;

      zone_name |                config_sql
    +-----------+------------------------------------------+
      .default  | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 1048576,
                |     range_max_bytes = 67108864,
                |     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 values you want to change (other values will not be affected):

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

    CONFIGURE ZONE 1

    > SHOW ZONE CONFIGURATION FOR DATABASE test;

      zone_name |                config_sql
    +-----------+------------------------------------------+
      test      | ALTER DATABASE test CONFIGURE ZONE USING
                |     range_min_bytes = 1048576,
                |     range_max_bytes = 67108864,
                |     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 values you want to change (other values will not be affected):

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

    CONFIGURE ZONE 1

    > SHOW ZONE CONFIGURATION FOR TABLE customers;

        zone_name    |                 config_sql
    +----------------+--------------------------------------------+
      test.customers | ALTER TABLE customers CONFIGURE ZONE USING
                     |     range_min_bytes = 1048576,
                     |     range_max_bytes = 67108864,
                     |     gc.ttlseconds = 100000,
                     |     num_replicas = 5,
                     |     constraints = '[]',
                     |     lease_preferences = '[]'
    (1 row)

### Create a replication zone for a secondary index

Note:

This is an enterprise-only feature.

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 values you want to change (other values will not be affected).

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 tpch.frequent_customers CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

    CONFIGURE ZONE 1

    > SHOW ZONE CONFIGURATION FOR INDEX tpch.customer@frequent_customers;

                 zone_name             |                                config_sql
    +----------------------------------+--------------------------------------------------------------------------+
      tpch.customer@frequent_customers | ALTER INDEX tpch.public.customer@frequent_customers CONFIGURE ZONE USING
                                       |     range_min_bytes = 1048576,
                                       |     range_max_bytes = 67108864,
                                       |     gc.ttlseconds = 100000,
                                       |     num_replicas = 5,
                                       |     constraints = '[]',
                                       |     lease_preferences = '[]'
    (1 row)

### Create a replication zone for a table or secondary index partition

Note:

This is an enterprise-only feature.

To control replication for table partitions, use the `ALTER PARTITION ... CONFIGURE ZONE` statement to define the values you want to change (other values will not be affected):

    > ALTER PARTITION north_america OF TABLE customers CONFIGURE ZONE USING num_replicas = 5, constraints = '[-region=EU]';

    CONFIGURE ZONE 1

    > SHOW ZONE CONFIGURATION FOR PARTITION north_america OF TABLE customers;

               zone_name           |                                  config_sql
    +------------------------------+-------------------------------------------------------------------------------+
      test.customers.north_america | ALTER PARTITION north_america OF INDEX customers@primary CONFIGURE ZONE USING
                                   |     range_min_bytes = 1048576,
                                   |     range_max_bytes = 67108864,
                                   |     gc.ttlseconds = 100000,
                                   |     num_replicas = 5,
                                   |     constraints = '[-region=EU]',
                                   |     lease_preferences = '[]'
    (1 row)

Tip:

Since the syntax is the same for defining a replication zone for a table or index partition (e.g., `database.table.partition`), give partitions names that communicate what they are partitioning, e.g., `north_america_table` vs `north_america_idx1`.

### 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:

Zone 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 Admin UI. 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 values you want to change (other values will not be affected):

    > ALTER RANGE meta CONFIGURE ZONE USING num_replicas = 7;

    CONFIGURE ZONE 1

    > SHOW ZONE CONFIGURATION FOR RANGE meta;

      zone_name |              config_sql
    +-----------+---------------------------------------+
      .meta     | ALTER RANGE meta CONFIGURE ZONE USING
                |     range_min_bytes = 1048576,
                |     range_max_bytes = 67108864,
                |     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

    > ALTER TABLE t CONFIGURE ZONE DISCARD;

    CONFIGURE ZONE 1

## See also

- Configure Replication Zones
- `SHOW ZONE CONFIGURATIONS`
- `ALTER DATABASE`
- `ALTER TABLE`
- `ALTER INDEX`
- `ALTER RANGE`
- Table Partitioning
- Other SQL Statements