New in v21.1:
The ALTER TABLE .. SET LOCALITY
statement changes the table locality of a table in a multi-region database.
While CockroachDB is processing an ALTER TABLE .. SET LOCALITY
statement that enables or disables REGIONAL BY ROW
on a table within a database, any ADD REGION
and DROP REGION
statements on that database will fail.
SET LOCALITY
is a subcommand of ALTER TABLE
.
Synopsis
Parameters
Parameter | Description |
---|---|
table_name |
The table whose locality you are configuring. |
locality |
The locality to apply to this table. Allowed values:
|
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
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:
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:
ALTER TABLE {table} SET LOCALITY REGIONAL BY TABLE IN "us-east-1";
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
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:
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:
SELECT crdb_region, id FROM {table};
To update an existing row's home region, use an UPDATE
statement like the following:
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: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:
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:
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 there is a performance benefit for queries that select a single row (e.g., SELECT * FROM users WHERE email = 'anemailaddress@gmail.com'
). If 'anemailaddress@gmail.com'
is found in the local region, there is no need to search remote regions. This feature, whereby the SQL engine will avoid sending requests to nodes in other regions when it can read a value from a unique column that is stored locally, is known as locality optimized search.
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:
ALTER TABLE {table} SET LOCALITY GLOBAL;
ALTER TABLE SET LOCALITY
For more information about how this table locality works, see Global tables.