SET LOCALITY

The ALTER TABLE .. SET LOCALITY statement changes the table locality of a table in a multi-region database.

Note:

SET LOCALITY is a subcommand of ALTER TABLE.

Synopsis

ALTER TABLE IF EXISTS relation_expr SET locality

Parameters

Parameter Description
table_name The table whose locality you are configuring.
locality The locality to apply to this table. Allowed values:
Note:

For more information about which table locality is right for your use case, see the following pages:

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Note:

RESTORE on REGIONAL BY TABLE and GLOBAL tables is supported with some limitations — see Restoring to multi-region databases for more detail.

Tables set to a REGIONAL BY ROW table locality cannot be restored. See the Known Limitations page for detail.

Set the table locality to REGIONAL BY TABLE

To optimize read and write access to the data in a table from the primary region, use the following statement:

icon/buttons/copy
ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN PRIMARY REGION;

To optimize read and write access to the data in a table from the us-east-1 region, use the following statement:

icon/buttons/copy
ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN "us-east-1";
Note:

If no region is supplied, REGIONAL BY TABLE defaults to the primary region.

For more information about how table localities work, see Regional tables.

Set the table locality to REGIONAL BY ROW

Note:

Before setting the locality to REGIONAL BY ROW on a table targeted by a changefeed, read the considerations in Changefeeds on regional by row tables.

To make an existing table a regional by row table, use the following statement:

icon/buttons/copy
ALTER TABLE {table} SET LOCALITY REGIONAL BY ROW;

Every row in a regional by row table has a hidden crdb_region column that represents the row's home region. To see a row's region, issue a statement like the following:

icon/buttons/copy
SELECT crdb_region, id FROM {table};

To update an existing row's home region, use an UPDATE statement like the following:

icon/buttons/copy
UPDATE {table} SET crdb_region = "eu-west" WHERE id IN (...)

To add a new row to a regional by row table, you must choose one of the following options.

  • Let CockroachDB set the row's home region automatically. It will use the region of the gateway node from which the row is inserted.

  • Set the home region explicitly using an INSERT statement like the following:

    icon/buttons/copy
    INSERT INTO {table} (crdb_region, ...) VALUES ('us-east-1', ...);
    

This is necessary because every row in a regional by row table must have a home region.

If you do not set a home region for a row in a regional by row table, it defaults to the value returned by the built-in function gateway_region(). If the value returned by gateway_region() does not belong to the multi-region database the table is a part of, the home region defaults to the database's primary region.

For more information about how this table locality works, see Regional by row tables.

Note that you can use a name other than crdb_region for the hidden column by using the following statements:

icon/buttons/copy
ALTER TABLE foo SET LOCALITY REGIONAL BY ROW AS bar;
SELECT bar, id FROM foo;
INSERT INTO foo (bar, ...) VALUES ('us-east-1', ...);

In fact, you can specify any column definition you like for the REGIONAL BY ROW AS column, as long as the column is of type crdb_internal_region and is not nullable. For example, you could modify the movr schema to have a region column generated as:

icon/buttons/copy
ALTER TABLE rides ADD COLUMN region crdb_internal_region AS (
  CASE
    WHEN city IN ('new york', 'boston', 'washington dc', 'chicago', 'detroit', 'minneapolis') THEN 'us-east-1'
    WHEN city IN ('san francisco', 'seattle', 'los angeles') THEN 'us-west-1'
    WHEN city IN ('amsterdam', 'paris', 'rome') THEN 'eu-west-1'  
  END
) STORED;

Note that the SQL engine will avoid sending requests to nodes in other regions when it can instead read a value from a unique column that is stored locally. This capability is known as locality optimized search.

Turn on auto-rehoming for REGIONAL BY ROW tables

Warning:

This is an experimental feature. The interface and output are subject to change.

This feature is disabled by default.

When auto-rehoming is enabled, the home regions of rows in REGIONAL BY ROW tables are automatically set to the region of the gateway node from which any UPDATE or UPSERT statements that operate on those rows originate. This functionality is provided by adding an ON UPDATE expression to the home region column.

Once enabled, the auto-rehoming behavior described here will only apply to newly created REGIONAL BY ROW tables. Existing REGIONAL BY ROW tables will not be auto-rehomed.

To enable it using the session setting, issue the following statement:

icon/buttons/copy
SET experimental_enable_auto_rehoming = true;
SET

To enable it using the cluster setting, issue the following statement:

icon/buttons/copy
SET CLUSTER SETTING sql.defaults.experimental_auto_rehoming.enabled = on;
SET CLUSTER SETTING

Example

  1. Follow steps 1 and 2 from the Low Latency Reads and Writes in a Multi-Region Cluster tutorial. This will involve starting a cockroach demo cluster in a terminal window (call it terminal 1).

  2. From the SQL client running in terminal 1, set the cluster setting that enables auto-rehoming. You must issue this cluster setting (or the session setting described above) before creating the REGIONAL BY ROW tables this feature operates on.

    icon/buttons/copy
    SET CLUSTER SETTING sql.defaults.experimental_auto_rehoming.enabled = on;
    
  3. In a second terminal window (call it terminal 2), finish the tutorial starting from step 3 onward to finish loading the cluster with data and applying the multi-region SQL configuration.

  4. Switch back to terminal 1, and check the gateway region of the node you are currently connected to:

    icon/buttons/copy
    SELECT gateway_region();
    
      gateway_region
    ------------------
      us-east1
    (1 row)
    
  5. Open another terminal (call it terminal 3), and use cockroach sql to connect to a node in a different region in the demo cluster:

    icon/buttons/copy
    cockroach sql --insecure --host localhost --port 26262
    
    # Welcome to the CockroachDB SQL shell.
    # All statements must be terminated by a semicolon.
    # To exit, type: \q.
    #
    # Server version: CockroachDB CCL v21.2.0-rc.2 (x86_64-apple-darwin19, built 2021/10/25 20:36:07, go1.16.6) (same version as client)
    # Cluster ID: 87b22d9b-b9ce-4f3a-8635-acad89c5981f
    # Organization: Cockroach Demo
    #
    # Enter \? for a brief introduction.
    #
    root@localhost:26262/defaultdb>
    
  6. From the SQL shell prompt that appears in terminal 3, switch to the movr database, and verify that the current gateway node is in a different region (us-west1):

    icon/buttons/copy
    USE movr;
    
    icon/buttons/copy
    SELECT gateway_region();
    
      gateway_region
    ------------------
      us-west1
    (1 row)
    
  7. Still in terminal 3, update a row in the vehicles table that is homed in the us-east1 region. After the update, it should be homed in the current gateway node's home region, us-west1.

    1. First, pick a row at random from the us-east1 region:

    icon/buttons/copy

       select * from vehicles where region = 'us-east1' limit 1;
    
                          id                  |  city  | type |               owner_id               |       creation_time        |  status   |       current_location       |                    ext                     |  region
       ---------------------------------------+--------+------+--------------------------------------+----------------------------+-----------+------------------------------+--------------------------------------------+-----------
         3e127e68-a3f9-487d-aa56-bf705beca05a | boston | bike | 2f057d6b-ba8d-4f56-8fd9-894b7c082713 | 2021-10-28 16:19:22.309834 | available | 039 Stacey Plain             | {"brand": "FujiCervelo", "color": "green"} | us-east1
                                              |        |      |                                      |                            |           | Lake Brittanymouth, LA 09374 |                                            |
       (1 row)
    
    1. Next, update that row's city and current_location to addresses in Seattle, WA (USA). Note that this UUID is different than what you will see in your cluster, so you'll have to update the query accordingly.

    icon/buttons/copy

       UPDATE vehicles set (city, current_location) = ('seattle', '2604 1st Ave, Seattle, WA 98121-1305') WHERE id = '3e127e68-a3f9-487d-aa56-bf705beca05a';
    
       UPDATE 1
    
    1. Finally, verify that the row has been auto-rehomed in this gateway's region by running the following statement and checking that the region column is now us-west1 as shown below.

    icon/buttons/copy

       SELECT * FROM vehicles WHERE id = '3e127e68-a3f9-487d-aa56-bf705beca05a';
    
                          id                  |  city   | type |               owner_id               |       creation_time        |  status   |           current_location           |                    ext                     |  region
       ---------------------------------------+---------+------+--------------------------------------+----------------------------+-----------+--------------------------------------+--------------------------------------------+-----------
         3e127e68-a3f9-487d-aa56-bf705beca05a | seattle | bike | 2f057d6b-ba8d-4f56-8fd9-894b7c082713 | 2021-10-28 16:19:22.309834 | available | 2604 1st Ave, Seattle, WA 98121-1305 | {"brand": "FujiCervelo", "color": "green"} | us-west1
       (1 row)
    

Set the table locality to GLOBAL

To optimize read access to the data in a table from any region (that is, globally), use the following statement:

icon/buttons/copy
ALTER TABLE {table} SET LOCALITY GLOBAL;
ALTER TABLE SET LOCALITY

For more information about how this table locality works, see Global tables.

See also

YesYes NoNo