CockroachDB table locality and region survival

A table’s locality tells CockroachDB where to optimise access to its data. A whole table can have one home region, different rows can have different homes, or a table can favour reads from every region. The database’s survival goal makes a separate choice: which failures its replica placement must tolerate.

The two choices meet at each range. Homing the shop’s products in West puts their ordinary serving authority there. Surviving West’s loss requires enough voting replicas elsewhere. A North application can therefore have a nearby order and still wait on West for the last blue mug.

This article applies the quorum and request reasoning from Regional placement and survival to CockroachDB v26.2. Inside CockroachDB establishes ranges, leases and gateways. North, West and East are invented labels. The drawings model policies and request dependencies; the native policy check used an isolated v26.2.8 cluster whose labelled nodes all ran on one machine. Neither supplies real regional latency.

Database regions and home regions

A node’s locality describes where it runs, for example region=West,zone=West-1. Set those tiers to match the actual deployment. Three zone names on one host do not make three independent failure domains.

A database region is a region selected for one database from the cluster’s available regions. The primary region supplies its default table home. In a cluster already containing the three regions, these statements make North primary and add West and East to the shop database:

ALTER DATABASE shop PRIMARY REGION "North";
ALTER DATABASE shop ADD REGION "West";
ALTER DATABASE shop ADD REGION "East";

That is database policy. It does not make North the only SQL entry point. The node accepting a connection is its SQL gateway; the relevant range’s leaseholder has authority to serve ordinary current reads and coordinate writes. A gateway in North can route KV work to a leaseholder in West. Larger queries can also distribute their SQL processing.

Regional by table

REGIONAL BY TABLE gives the entire table one home region. New tables in a multi-region database default to the primary region. An explicit home can differ:

ALTER TABLE products
SET LOCALITY REGIONAL BY TABLE IN "West";

In this independent placement, the product catalogue and shared stock remain together in products, homed West. Assume the main stock-changing service runs there. A West gateway can ask the West serving authority for P7’s price within the region. A North gateway normally reaches that same authority remotely; accepting the SQL connection in North has not moved the product’s current-read path there.

Both routes return 20.00. The difference is where the gateway must reach the serving authority. A home region favours access from that region; it is not an exclusive storage boundary. CockroachDB keeps replicas elsewhere according to the database policy. A local follower copy also does not automatically make an ordinary current read local.

The choice suits a table whose important reads and changes mostly come from one region. If the stock service is in West but customers browse from everywhere, catalogue reads and stock allocation have different needs. A qualified historical catalogue read may be acceptable; selling the last mug still needs the guarded stock decision. CockroachDB isolation and follower reads develops those read contracts.

Regional by row

REGIONAL BY ROW lets different rows of one SQL table have different home regions. CockroachDB adds a hidden crdb_region column, of the database’s crdb_internal_region enum type. It automatically partitions the primary index and every secondary index by that region.

For a separate copy of the shop’s orders, choose North for 101 and 103, and West for 102. These are technical placement choices, not facts about where Mina or Tariq lives. The customer-history index from the performance article gets the same regional partitioning as the primary rows.

The North partition holds the keys for 101 and 103 and their history entries. The West partition holds 102. Each partition can span several ranges, and each range has replicas. A home identifies the favoured region, not every place a copy is stored.

Choosing a row’s home region

These statements convert existing copies of the order tables and assign their homes explicitly:

ALTER TABLE orders SET LOCALITY REGIONAL BY ROW;
ALTER TABLE order_items SET LOCALITY REGIONAL BY ROW;

BEGIN;
UPDATE orders SET crdb_region = 'North'
WHERE order_id IN (101, 103);
UPDATE orders SET crdb_region = 'West'
WHERE order_id = 102;
UPDATE order_items AS i SET crdb_region = o.crdb_region
FROM orders AS o WHERE i.order_id = o.order_id;
COMMIT;

SELECT order_id, crdb_region FROM orders ORDER BY order_id;

The hidden column does not appear in SELECT *, so name it when checking homes. Inserting a new row without an explicit region normally uses the gateway’s region. If that region is not in the database, the default falls back to the database’s primary region. Routing inserts through West therefore affects where new rows default, even if the caller is elsewhere.

An ordinary foreign key from order_items.order_id to orders.order_id does not itself copy the parent’s region. A line inserted through West without an explicit home can be homed West while order 103 is North. The reference can be valid and the placement inconvenient. Assign the related rows deliberately, as above. CockroachDB also has an explicitly configured foreign-key region-inference feature; it is not enabled by this ordinary reference.

Nor does reading or ordinarily updating an existing row through another gateway move its home by default. An explicit UPDATE orders SET crdb_region = 'West' WHERE order_id = 103 changes that row’s home. It does not make an ID-only foreign key cascade the region into its lines. Decide whether the whole related group should move. Optional automatic rehoming changes the default update behaviour and needs its own workload decision.

Row locality earns its complexity when requests repeatedly meet rows homed near their gateways. If the application sends every request through North, West-homed rows remain remote from that entry point. If many important queries need every region’s rows, no choice of one row home makes their whole answer local.

The work that remains remote

A North lookup of order 103 can be satisfied from its North home. Replaying the purchase is a different request. Return independently to before 103 exists: P7 has stock 1 and P3 has stock 12. Creating the pending North order and its two lines also needs the West products authority to guard P7 to 0 and P3 to 10. A successful purchase leaves agreed line value 29.00 and no payment.

The database must also maintain the order indexes, check references and enforce globally meaningful order identity. A regional prefix in the physical index does not silently change PRIMARY KEY (order_id) into regional uniqueness. CockroachDB’s implicit partitioning retains uniqueness on the declared key; integer order IDs can require checks in other regional partitions. A local row write is therefore not proof that the entire transaction stays local.

-- 103 already exists in North: this must be rejected.
INSERT INTO orders
  (order_id, customer_id, placed_at, status, crdb_region)
VALUES
  (103, 1, '2026-10-20 10:15+00', 'pending', 'West');

Explicitly declaring PRIMARY KEY (crdb_region, order_id) instead permits the distinct pairs (North, 103) and (West, 103). That changes identity and what a reference must carry. It is a different schema contract, not a free way to remove the global check. A randomly generated ID can avoid some checking work under the engine’s rules, but does not preserve the original integer-ID contract by itself.

Inspect the actual constraint and index work before calling a purchase regional. The optimiser can search a local partition first for suitable lookups and widen the search when required. That is an execution optimisation, not permission to ignore remote qualifying rows. CockroachDB query work and hot ranges follows native plans and access-path repairs.

Global tables

GLOBAL favours up-to-date reads from every database region at the cost of higher write latency. It suits frequently read reference data whose definitions change rarely. It does not give every region an independent write authority.

Suppose the shop needs a separate reference list of its three existing statuses. This illustrative table does not replace the check constraint on orders.status:

CREATE TABLE order_statuses (
  status TEXT PRIMARY KEY
) LOCALITY GLOBAL;
INSERT INTO order_statuses VALUES ('pending'), ('paid'), ('cancelled');

The global-table protocol shifts work to writes so qualified nearby replicas can serve current reads. It uses future write timestamps and a wait that makes those timestamps safe for the read protocol, as well as replica agreement. This is different from an application choosing an older snapshot to get a follower read. The exact wait and request latency depend on clocks, networking, load and the transaction’s other work.

Changing products to GLOBAL would not remove the guarded write that allocates the last mug. It would change that table’s read/write trade. Nor does GLOBAL select region survival: its ranges still depend on the database’s survival goal. Choose the locality for the table’s access pattern, then check the failure promise separately.

Surviving a region failure

The default database survival goal is zone failure. For a standard regional table in a three-region database, its three voters sit in separate zones of the home region. Other regions have non-voting copies. Losing one home zone leaves two of three voters. Losing the whole home region removes all three.

To require survival of any one region, add at least three database regions and select:

ALTER DATABASE shop SURVIVE REGION FAILURE;

For the standard three-region policy, CockroachDB uses five voting replicas spread two–two–one. Two voters remain in the regional table’s home. With products homed West, zone survival can tolerate losing one of West’s voter zones, but loses its whole majority if West disappears. Region survival instead places enough voting history outside West to retain a majority after that loss.

Three voters need two; five need three. The zone-survival copies outside West do not vote, so two surviving copies are not a surviving majority. Under region survival, losing either two-voter region leaves three; losing the one-voter region leaves four. Those are votes for this range. Healthy nodes holding unrelated ranges cannot contribute.

The five-voter placement changes healthy writes too. West has only two local voters, short of the required three, so agreement needs a remote acknowledgement. The survival promise buys resilience by adding regional coordination. Changing the table home changes the favoured serving region; it does not remove the database’s voting requirement. The same goal applies to every table in that database, including regional row partitions and global tables.

What the survival goal promises

A surviving majority permits progress under the assumed failure model. A lost leaseholder or leader still needs detection and replacement. Connections to vanished gateways must be rerouted; affected transactions can need retries. The surviving nodes inherit work and may have less spare capacity. “Survives a region failure” does not promise no interruption, an unchanged response-time distribution or a repaired application endpoint.

With region survival, West’s loss does not require rewriting products’ declared home to North before survivors can serve it. Keep configuration and current authority distinct: the home expresses the preferred healthy placement, while failover changes which surviving replica currently serves a range. A network split also needs communicating voters on the side the application can reach.

Checking policy and placement

Check three different things: what was requested, where the ranges actually are, and whether complete requests meet their requirements. A successful configuration statement establishes the first. Replica movement and lease placement must converge before the second can match it.

SHOW REGIONS FROM CLUSTER;
SHOW REGIONS FROM DATABASE shop;
SHOW CREATE DATABASE shop;
SHOW CREATE TABLE products;
SHOW CREATE TABLE orders;
SHOW CREATE TABLE order_statuses;
SELECT order_id, crdb_region FROM orders ORDER BY order_id;
SHOW INDEXES FROM orders;
SHOW PARTITIONS FROM TABLE orders;

The database output should name North as primary, list all three regions and show the selected survival goal. The table definitions distinguish West-homed products, regional-by-row orders and GLOBAL statuses. Row values check the intended 101/103 North and 102 West assignment. Index and partition output checks that the history index follows the regional policy too.

On the isolated v26.2.8 fixture, SHOW CREATE DATABASE returned North as primary, all three database regions and SURVIVE ZONE FAILURE. That verifies declared policy. It does not establish the illustrated row assignments or replica layouts. Those examples follow documented behaviour; no native result for their row, constraint or placement checks is reported.

Then inspect the actual ranges. Scope the expensive detailed listing to the tables or indexes under investigation:

SHOW RANGES FROM TABLE products WITH DETAILS;
SHOW RANGES FROM TABLE orders WITH INDEXES, DETAILS;

Read the range’s bounds and index name with its lease_holder, lease_holder_locality, voting_replicas, non_voting_replicas and replica_localities. Check voting node IDs against their localities; do not count every item in replicas as a vote. Include secondary-index ranges and any other tables the request needs. A range can cover a wider interval than the particular rows being tested.

If the observed layout is still changing or does not meet the intended policy, retain that finding. Configuration text is not a substitute for the missing placement. Health, under-replication and maintenance diagnosis belong to Operating and securing a CockroachDB cluster.

Testing the complete request

Begin with the shop’s acceptance cases. A North order read and a North purchase have different dependencies. The status lookup has a third access pattern. Run the same application paths through the gateways they will actually use.

A controlled loss test must identify the failure domain being removed, confirm the starting range health and placement, and keep a working application route outside it. Remove all nodes in that domain together; stopping only its gateway tests a different failure. Observe the complete request through the transition, verify data and recognised outcomes, then restore the domain and check convergence before the next case. Keep health repair and planned decommissioning in the operations procedure.

A local cluster with three regional labels can check SQL, inspect placement and exercise loss of selected processes. All processes sharing one host still share its storage, network and failure. Such an experiment does not establish independence from a real regional outage or the time spent crossing real regions. Use the structural voter test to reject a layout that cannot survive the required loss, and a deployment-shaped test to establish the service behaviour the application needs.