ALTER PARTITION is used to add, modify, reset, or remove replication zones for partitioning. It is combined with the
CONFIGURE ZONE subcommand.
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.
The user must have the
CREATE privilege on the table.
||The name of the table with the replication zone configurations to modify.|
||The name of the partition with the replication zone configurations to modify.|
||The name of the index with the replication zone configurations to modify.|
||The name of the variable to change.|
||The value of the variable to change.|
|| 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.
|| 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.
|| 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
It is not recommended to set this below
Note:If you are not yet using scheduled backups, and instead issuing
However, all CockroachDB Serverless clusters have a default
|| The number of replicas in the zone, also called the "replication factor".
For multi-region databases configured to survive region failures, the default value is
|| An array of required (
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
Default: No constraints, with CockroachDB locating each replica on a unique node and attempting to spread replicas evenly across localities.
||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
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
For a usage example, see Constrain leaseholders to specific availability zones.
Default: No lease location preferences are applied if this field is not specified.
|| Specifies the number of voting replicas. When set,
|| Specifies the constraints that govern the placement of voting replicas. This differs from the
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
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_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.
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
$ cockroach demo --geo-partitioned-replicas
Create a replication zone for a partition
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)