> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # ALTER PARTITION `ALTER PARTITION` is used to add, modify, reset, or remove replication zones for [partitioning](/docs/stable/partitioning.md). It is combined with the `CONFIGURE ZONE` subcommand. To view details about existing replication zones, use [`SHOW ZONE CONFIGURATIONS`](/docs/stable/show-zone-configurations). For more information about replication zones, see [Replication Controls](/docs/stable/configure-replication-zones.md). For instructions showing how to troubleshoot replication zones that may be misconfigured, see [Troubleshoot Replication Zone Configurations](/docs/stable/troubleshoot-replication-zones.md). 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. ## Synopsis ![alter\_zone\_partition syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_zone_partition.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=637cebef36827800af72979ccdc791f3) ## Required privileges The user must have the [`CREATE`](/docs/stable/grant#supported-privileges) privilege on the table. ## Parameters | Parameter | Description | | - | - | | `table_name` | The name of the [table](/docs/stable/create-table.md) with the [replication zone configurations](/docs/stable/configure-replication-zones.md) to modify. | | `partition_name` | The name of the [partition](/docs/stable/partitioning.md) with the [replication zone configurations](/docs/stable/configure-replication-zones.md) to modify. | | `index_name` | The name of the [index](/docs/stable/indexes.md) with the [replication zone configurations](/docs/stable/configure-replication-zones.md) to modify. | | `variable` | The name of the [variable](#variables) to change. | | `value` | The value of the [variable](#variables) to change. | ### 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](/docs/stable/architecture/glossary.md) of data in the zone. When a range reaches this size, CockroachDB will [split it](/docs/stable/architecture/distribution-layer.md#range-splits) into two ranges. **Default:** `536870912` (512 MiB) | | `gc.ttlseconds` | The number of seconds overwritten [MVCC values](/docs/stable/architecture/storage-layer.md#mvcc) will be retained before [garbage collection](/docs/stable/architecture/storage-layer.md#garbage-collection). **Default:** `14400` (4 hours) Smaller values can save disk space and improve performance if values are frequently overwritten or for [queue-like workloads](/docs/stable/understand-hotspots.md#queueing-hotspot). The smallest value we regularly test is `600` (10 minutes); smaller values are unlikely to be beneficial because of the frequency with which GC runs. If you use [non-scheduled incremental backups](/docs/stable/take-full-and-incremental-backups.md#garbage-collection-and-backups), the GC TTL must be greater than the interval between incremental backups. Otherwise, your incremental backups will fail with [the error message `protected ts verification error`](/docs/stable/common-errors#protected-ts-verification-error). To avoid this problem, we recommend using [scheduled backups](/docs/stable/create-schedule-for-backup.md#protected-timestamps-and-scheduled-backups) instead, which automatically [use protected timestamps](/docs/stable/create-schedule-for-backup.md#protected-timestamps-and-scheduled-backups) to ensure they succeed. Larger values increase the interval allowed for [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time) queries and allow for less frequent incremental backups. The largest value we regularly test is `90000` (25 hours). Increasing the GC TTL is not meant to be a solution for long-term retention of history; for that you should handle versioning in the [schema design at the application layer](/docs/stable/schema-design-overview.md). Setting the GC TTL too high can cause problems if the retained versions of a single row approach the [maximum range size](#range-max-bytes). This is important because all versions of a row are stored in a single range that never [splits](/docs/stable/architecture/distribution-layer.md#range-splits). | | `num_replicas` | The number of replicas in the zone, also called the "replication factor". **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](/docs/stable/multiregion-survival-goals.md#survive-region-failures), the default value is `5`; this will include both [voting](#num_voters) and [non-voting replicas](/docs/stable/architecture/replication-layer.md#non-voting-replicas). | | `constraints` | An array of required (`+`) and/or prohibited (`-`) constraints influencing the location of replicas. See [Types of Constraints](/docs/stable/configure-replication-zones.md#types-of-constraints) and [Scope of Constraints](/docs/stable/configure-replication-zones.md#scope-of-constraints) for more details. To prevent hard-to-detect typos, constraints placed on [store attributes and node localities](/docs/stable/configure-replication-zones.md#descriptive-attributes-assigned-to-nodes) 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](/docs/stable/architecture/glossary.md). 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](/docs/stable/configure-replication-zones.md#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](/docs/stable/architecture/transaction-layer.md#non-blocking-transactions), 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](/docs/stable/global-tables). | | `num_voters` | Specifies the number of [voting replicas](/docs/stable/architecture/life-of-a-distributed-transaction.md#consensus). When set, `num_replicas` will be the sum of voting and [non-voting replicas](/docs/stable/architecture/replication-layer.md#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](/docs/stable/multiregion-overview.md). | | `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](/docs/stable/multiregion-overview.md). | > [!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](/docs/stable/configure-replication-zones.md). 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. ## 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](/docs/stable/movr.md). To follow along, run [`cockroach demo`](/docs/stable/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. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo --geo-partitioned-replicas ``` ### Create a replication zone for a partition Once [partitions have been defined for a table or a secondary index](/docs/stable/partitioning.md), to control replication for a partition, use `ALTER PARTITION OF INDEX CONFIGURE ZONE`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER PARTITION us_west OF INDEX vehicles@primary CONFIGURE ZONE USING num_replicas = 5, constraints = '[+region=us-west1]'; ``` ``` CONFIGURE ZONE 1 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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 `@*` syntax to save several steps: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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 OF INDEX `: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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`](/docs/stable/show-create) statement or [`SHOW PARTITIONS`](/docs/stable/show-partitions) statements to view details about all of the replication zones defined for the partitions of a table and its secondary indexes. For instructions showing how to troubleshoot replication zones that may be misconfigured, see [Troubleshoot Replication Zone Configurations](/docs/stable/troubleshoot-replication-zones.md). ## See also * [Table partitioning](/docs/stable/partitioning.md) * [`SHOW PARTITIONS`](/docs/stable/show-partitions) * [Troubleshoot Replication Zones](/docs/stable/troubleshoot-replication-zones.md)