CockroachDB 21.1 separates data locality from failure survival

A table's preferred access region and its required failure tolerance answer different questions. CockroachDB 21.1 gives them separate SQL settings. Table locality describes where reads and writes should be optimized, while the database's survival goal describes which failures it must tolerate.

The release index dates 21.1.0 to May 18. Its original release notes identify the multi-region changes as an enterprise capability. The improvement is a higher-level way to express placement and availability requirements, with the database responsible for the associated configuration.

I accept the coordination latency of CockroachDB when the consistency and failure guarantees justify it. This release makes another part of that decision explicit in SQL. A requirement to survive regional failure can be recorded separately from an application's preference for local access.

Physical regions precede database regions

The multi-region overview published with 21.1 starts with node locality. A node identifies its region and zone at startup. Database regions must come from the regions present in the cluster.

SQL configuration does not create physical infrastructure in a missing geography. It assigns available cluster regions to a particular database. That distinction keeps an administrative command from being mistaken for a provisioning operation.

The first assigned database region becomes its primary region. With this configuration model, the documentation considers the database multi-region even when it currently has only that one assigned region. The term describes participation in this configuration model, not proof that the database already spans several geographies.

For a database named application on a cluster with the named regions, the original references show the following sequence. Region names are illustrative and must match the cluster's actual locality labels.

SHOW REGIONS FROM CLUSTER;

ALTER DATABASE application SET PRIMARY REGION "us-east1";
ALTER DATABASE application ADD REGION "us-west1";
ALTER DATABASE application ADD REGION "europe-west1";

SHOW REGIONS FROM DATABASE application;

The primary-region reference explains that setting the primary region changes default placement. Tables become regional by table in that region. Under the default configuration described there, voting replicas and leaseholders move into the primary region.

The primary-region reference also distinguishes initial configuration from switching an existing primary. Initial assignment requires the appropriate authority over the database and its tables, unless the caller is a cluster administrator. Switching to another already-added region has a database-level privilege requirement. This makes the operation's authorization depend on which transition it performs.

A primary region cannot simply be dropped while other regions remain. The reference requires designating another region as primary first, or removing the other regions. This preserves a defined primary throughout the configuration change and gives region removal a specific order.

That is an actual placement change, not a descriptive label attached to an unchanged database. It matters especially for a cluster whose replicas were previously spread broadly. The high-level command causes the system to pursue a different arrangement.

Zone survival is the default

All tables in a multi-region database share its survival goal. Different databases can select different goals. The default is zone-failure survival, under which the database remains available for reads and writes after one zone fails.

The documentation does not extend that default to arbitrary simultaneous zone failures in one region. Losing a region is a larger failure condition and requires a different setting. Merely adding several database regions does not select region-failure survival.

The overview requires at least three assigned database regions for that stronger goal. Given that prerequisite, the setting is explicit.

ALTER DATABASE application SURVIVE REGION FAILURE;

The documented cost is additional write latency of at least the round-trip time to the nearest region. The overview says read performance is unaffected by choosing the stronger survival goal. Those statements concern the goal change, not a promise that every query in every region has the same latency.

A database can therefore have a regional access preference while retaining a regional failure requirement. The locality setting and survival goal cooperate to configure placement. They do not describe interchangeable modes.

That distinction prevents a common interpretation error. A row's home region tells the database where to optimize access. It does not, by itself, say that the only copy lives there or that losing that region is safe under every survival configuration.

Regional by table selects one access preference

REGIONAL BY TABLE is the default table locality. It optimizes reads and writes for a single region, which defaults to the database's primary region. The SET LOCALITY reference allows explicitly naming a different region.

For a table used mainly by one regional application component, a table-level choice expresses the shared access pattern. The caller does not need separate home-region values for each row.

ALTER TABLE application.public.orders
SET LOCALITY REGIONAL BY TABLE IN "us-east1";

That statement assumes the table already exists in the configured database. It changes locality, not the table's business schema. The underlying placement work still has to bring the configuration into effect.

The benefit of the abstraction is that it states an intent the database understands. A person reading the schema can identify the intended regional access pattern without reconstructing it from a large set of lower-level placement rules.

Regional by row partitions the table and its indexes

Some tables contain rows with different regional access patterns. REGIONAL BY ROW provides a home region for each row and automatically partitions both the table and its indexes. That is more specific than declaring the entire table global or choosing one region for all of it.

ALTER TABLE application.public.users
SET LOCALITY REGIONAL BY ROW;

The default hidden column is crdb_region. A query can select it explicitly to inspect a row's home. An insert can provide it explicitly, or the database can derive the default from the gateway node's region.

If the gateway's region is not part of the database, the documented fallback is the primary region. Connection routing therefore affects default row placement, although the application can override it by supplying a region.

This is an important consequence for application design. A default derived from the gateway is convenient when the gateway represents the intended regional user population. An application whose connection path does not represent that population can choose the row's home explicitly.

The index-partitioning description shipped with the release says new indexes automatically partition on crdb_region. Most users should prefer the regional-by-row mechanism to explicit index partitioning. The database takes on the repeated partition configuration required as indexes are added.

I find automatic index partitioning a substantial part of this feature. A row-locality rule needs to remain useful after the schema gains another index. Having the database apply the partitioning reduces the amount of placement configuration that a schema change must reproduce manually.

Global tables favor reads across regions

GLOBAL locality addresses read-mostly reference data needed throughout a database's regions. The release's global-table description states the tradeoff, with lower read latency across regions and higher write latency because writes must be replicated across every region.

A reference table updated infrequently fits that pattern better than a table with intensive local writes. The setting reflects a read/write distribution, not an overall rank in which global is always preferable to regional.

ALTER TABLE application.public.reference_codes
SET LOCALITY GLOBAL;

The original overview also distinguishes stale follower reads. Those can offer low-latency access from database regions regardless of table locality, with the separate semantic choice to read historical data. A locality setting does not erase that freshness distinction.

The abstraction retains compatibility boundaries

The 21.1 SET LOCALITY reference states that changefeeds are not supported on regional-by-row tables. Setting that locality causes changefeed jobs targeting the table to fail. A placement improvement can therefore affect a separate data-export workflow.

Existing manual zone configurations also matter. The primary-region reference says directly configured database zones block setting the primary region. It documents discarding those configurations to unblock the operation. A database with manual placement rules needs that transition understood before applying the higher-level model.

These limits define which responsibilities the new interface absorbs. It automates placement configuration for supported locality patterns, including index partitioning for regional rows. It still requires physical regions, appropriate privileges and compatible surrounding workflows.

The resulting SQL records useful decisions close to the schema. A reviewer can see whether the database must survive a zone or region failure and which tables favor regional writes or global reads. Those choices supply a more direct explanation of placement than reconstructing the intended behavior from individual replication-zone settings.