ALTER DATABASE ... PLACEMENT (RESTRICTED | DEFAULT)

New in v21.2: 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.

Note:

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:

  • 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, for REGIONAL BY TABLE tables, it will only place replicas in the defined region (or the database's primary region); for REGIONAL 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 the ZONE 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:

Examples

To follow along with the examples below:

  1. Start a demo cluster with the --global flag to simulate a multi-region cluster:

    icon/buttons/copy
    cockroach demo --global --nodes 9
    
  2. 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).

  3. Enable the replica placement syntax with either the session variable or the cluster setting as shown below.

    1. To use the session variable:

      icon/buttons/copy
      SET enable_multiregion_placement_policy = on;
      
    2. To use the cluster setting:

      icon/buttons/copy
      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 table creation time that you'd like to set the table's replica placement policy to "restricted", you can do so in a CREATE TABLE statement as shown below:

icon/buttons/copy
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.

icon/buttons/copy
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:

icon/buttons/copy
ALTER DATABASE movr PLACEMENT DEFAULT;
ALTER DATABASE PLACEMENT

See also

YesYes NoNo