ALTER DATABASE ... PLACEMENT (RESTRICTED | DEFAULT)

On this page

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.

Synopsis

ALTER DATABASE {database_name} PLACEMENT {placement_policy}

Parameters

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

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

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

   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:

      SET enable_multiregion_placement_policy = on;
      

   2. 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

See also

• Multi-Region Capabilities Overview
• ALTER DATABASE
• ADD REGION
• Range
• Non-voting replicas
• SQL Statements
• Data Domiciling with CockroachDB

Was this helpful?