The ALTER DATABASE ... PLACEMENT RESTRICTED
statement is used to constrain the replica placement for a multi-region database's regional tables to the home regions associated with those tables. Regional tables are those with REGIONAL BY ROW
or REGIONAL BY TABLE
localities. ALTER DATABASE ... PLACEMENT RESTRICTED
is a way of opting out of non-voting replicas for regional tables to accomplish one or more of the following goals:
- Implement a data domiciling strategy.
- Reduce the amount of data stored on the cluster.
- Reduce the overhead of replicating data across a large number of regions (e.g., 10 or more) for databases with heavier write loads.
Note that this statement does not allow you to opt out of placing non-voting replicas entirely. For example, GLOBAL
tables in the database will remain unaffected by this statement. GLOBAL
tables are designed to have replicas placed across all available cluster regions to ensure fast local reads.
This is a subcommand of ALTER DATABASE
.
Synopsis
ALTER DATABASE {database_name} PLACEMENT {placement_policy}
Parameters
Parameter | Description |
---|---|
database_name |
The database whose replica placement you want to constrain to its home region. |
placement_policy |
The replica placement policy that will be used for regional tables. For more information, see the list below. |
The replica placement policies available via this statement are:
DEFAULT
(Default): If the replica placement policy is set to 'default', CockroachDB will use its default replica placement settings, which mean that:- Data will be placed in as many regions as necessary to ensure your database survival goals are met.
- You can get fast stale reads from all database regions.
RESTRICTED
: If the replica placement policy is set to 'restricted', CockroachDB will constrain replica placement to only those regions where the table has voting replicas (that is, replicas which participate in the Raft quorum). In practice, this means that voting replicas for the table will be constrained to the table's home region. Specifically, forREGIONAL BY TABLE
tables, it will only place replicas in the defined region (or the database's primary region); forREGIONAL BY ROW
tables, it will only place replicas for each underlying partition in the partition's specified region. Finally, note that:- Regional tables with this placement setting will no longer provide "fast stale reads" from other (non-home) regions, since fast stale reads rely on the presence of non-voting replicas.
- The
RESTRICTED
replica placement policy is only available for databases with theZONE
survival goal. - This setting does not affect how
GLOBAL
tables work; they will still place replicas in all database regions.
Required privileges
To use this statement, the user must have one of the following:
- Membership to the
admin
role for the cluster. - Ownership or the
CREATE
privilege for the database and all tables in the database.
Examples
To follow along with the examples below:
Start a demo cluster with the
--global
flag to simulate a multi-region cluster:cockroach demo --global --nodes 9
Set the demo cluster's database regions and table localities as described in Low Latency Reads and Writes in a Multi-Region Cluster (specifically, starting at Step 5. Execute multi-region SQL statements).
Enable the replica placement syntax with either the session variable or the cluster setting as shown below.
To use the session variable:
SET enable_multiregion_placement_policy = on;
To use the cluster setting:
SET CLUSTER SETTING sql.defaults.multiregion_placement_policy.enabled = on;
Create a database with the replica placement policy set to restricted
If you know at database creation time that you'd like to set the database's replica placement policy to "restricted", you can do so in a CREATE DATABASE
statement as shown below:
CREATE DATABASE movr2 PRIMARY REGION "us-east1" REGIONS "us-west1", "europe-west1" PLACEMENT RESTRICTED;
CREATE DATABASE
Set the replica placement policy to restricted
When you set the database's placement policy to "restricted", you are saying that you want the underlying data to be restricted to the table or partition's home region.
ALTER DATABASE movr PLACEMENT RESTRICTED;
ALTER DATABASE PLACEMENT
Set the replica placement policy to default
If previously you set the replica placement policy to "restricted", you can set it back to the default by issuing the following statement:
ALTER DATABASE movr PLACEMENT DEFAULT;
ALTER DATABASE PLACEMENT