Where the Ocean Comes Back

Why Priya’s new reading still crosses the ocean to check its key, and how putting the region in the key stops it

★Why is Priya’s insert still slow?

“Why ask Europe? She’s never been there.”

KEY UNIQUE ACROSS THE WHOLE TABLE → CHECK EVERY REGION

STORED AS mumbai/priya:r9 · priya:r9 MUST BE UNIQUE

Part II ended on a question: how can Priya’s new reading be inserted without first asking Iowa and Europe? The readings she wrote in Mumbai are homed there, so updating them never leaves Mumbai, but a new reading’s key, priya:r9, must be unique across the whole table, and as far as the database knows a row with that key could be homed in any region, like her US-trip rows in Iowa (Part II’s panel 02). So by default the insert asks Iowa and Europe whether priya:r9 is taken, and Mumbai’s write waits on both.

01How do you keep inserts local?

“Can an insert skip the other regions?”

(user, reading)

↓

(crdb_region, user, reading)

crdb_region = A ROW’S HOME
BY DEFAULT, THE GATEWAY’S REGION

Every row already carries its home in the hidden crdb_region column (Part II’s panel 01), so put that column into the unique keys, primary keys included. What must be unique is then mumbai/priya:r9, which only a row homed in Mumbai could share, so Priya’s insert is checked in Mumbai alone and never waits on Iowa or Europe. The docs say this removes the added latency from uniqueness checks.

02What does a per-region key give up?

OR A KEY THAT NEEDS NO CHECK

DEFAULT gen_random_uuid()

KEYS FILLED BY THIS SKIP THE UNIQUENESS CHECK BY DEFAULT

The price is what the key means: “unique worldwide” becomes “unique within a region”, so the same key can exist once per region, and a retry that lands in another region can duplicate it. If that matters, a key filled by gen_random_uuid() skips the check by default, so there is no check for the insert to wait on. Don’t reach for the skip_unique_checks switch instead; used where uniqueness isn’t actually guaranteed, it can leave logically duplicate keys.

SUPPORTING — SHOULD EACH USER HAVE ONE HOME REGION?▾

A row’s home can come from the gateway default or from a home region you keep for each user, and the choice decides whether a retry can duplicate a key (panel 02).

	GATEWAY DEFAULT	HOME BY USER
Server must know each user’s home region	No	Yes, a new field per user
A travelling user’s history	Split across regions	All in one region
Region-level unique keys (panels 01 and 02)	The same key can exist once per region, so a retry that lands in another region can duplicate it	Safe

Two questions, not one. Which region serves a request (the load balancer, in Part II’s “How does Priya’s request reach Mumbai’s nodes?”) and which region is a row’s home are separate questions; the gateway default just answers the second with the first.

Auto-rehoming exists but is in preview: when it’s on, a row’s home moves to the gateway region of any UPDATE or UPSERT that writes it. It only applies to newly created regional-by-row tables unless you add it to an existing one by hand.

SUPPORTING — WHAT ABOUT READS?▾

A read stays local when it reads rows homed in your own region and the query can tell that’s where they are. Reading the latest of rows homed elsewhere, or of rows that could be anywhere, crosses the ocean unless you ask for a stale (follower) read.

What can still cross the ocean on a read?

Two kinds of read still cross the ocean: reading the latest of a row homed somewhere else, like Priya in Mumbai reading Dana’s Iowa row, and a query that can’t tell which regions hold its rows. The panels below take them one at a time, then follow Priya through a month in two countries.

Latest, or a few seconds ago?

SELECT … FROM … AS OF SYSTEM TIME follower_read_timestamp();

A normal read has to reach the row’s lead, which lives in the row’s home, so when Dana shares her readings with Priya, Priya reading Dana’s Iowa rows from Mumbai crosses the ocean. Ask for a stale read instead and the nearest copy answers locally, a few seconds behind, often the read-only copy of Part II’s panel 03 (CockroachDB calls this a follower read). You opt in, per query or per session, because that copy may not reflect the latest writes; nothing is stale unless you ask.

Is reading a row homed in my region free?

“How do I know a row found locally is the latest?”

Reading a row homed in your own region is latest without leaving it, because a row homed here has its lead here. Only reading a row homed somewhere else pays for the ocean, as the previous panel showed. The catch, a few panels on, is a query that can’t tell where its rows live.

Priya’s month across two countries

Priya spends 25 days of the month in India and 5 in the US, and every reading is homed where it was written (the gateway default, Part II’s panel 02). So her month lives in two regions: 25 days in Mumbai, 5 in Iowa. She has no single home; each of her readings does.

Priya, in Iowa, asks for her latest reading

~~“She’s in Iowa, so it only hits Iowa?”~~

A lookup by unique key, or a LIMIT that any matching rows can fill, can search the local region first and go remote only if it comes up short. Her newest is a LIMIT 1 that only the newest row can fill, and a newer reading could sit in another region, so a latest read has to hear from every region that might hold one, Europe included, even when the newest is right there in Iowa. A stale read answers from Iowa’s local copies of rows homed in all three regions.

Back in Mumbai, scrolling her month

SAME IOWA DAYS · THEY’RE DAYS OLD

Home again, a latest read of her month fetches the 5 Iowa days across the ocean and still asks Europe, while a stale read serves the whole month from Mumbai, Iowa days included. The Iowa readings are days old, so for them a stale read’s “few seconds behind” changes nothing. Either way the Iowa days are shown; only where they’re read from changes.

Does everyone else pay too?

Dana never leaves Iowa, but the database can’t know she has no rows elsewhere, so under latest reads her history query can end up asking Mumbai and Europe too. Old readings don’t change, which makes history screens a natural fit for stale reads; keep latest reads for the check right before a write, such as reading a row you’re about to update. Where you know the rows are local, putting the region in the query should keep it local.

SUPPORTING — WHAT IF DATA MUST STAY IN ONE COUNTRY?▾

Read-only copies mean every region holds a copy of every row (Part II’s panel 03). If a rule says some data must stay in one country, CockroachDB has super regions (a preview feature): a named group of regions, where rows homed in a member region keep all their copies, voting and read-only, only inside the group.

ALTER DATABASE … ADD SUPER REGION "<name>" VALUES "<region>", …;

You turn super regions on first (enable_super_regions), add one only once those regions are already in the database, and each region can belong to only one super region. To still survive losing a region, the super region itself needs 3 or more regions.

The other tool is ALTER DATABASE … PLACEMENT RESTRICTED, which keeps a table’s copies in its home region only (it needs the zone survival goal, the default, in Part II’s “How can the vote stay in one region and still survive failures?”), at the cost of fast stale reads from other regions.

One caveat on super regions: they ride on the replication system, whose “top priority is to prevent the loss of data and it may override the zone configurations if necessary”. The story above needs neither: read-only copies in other regions are fine there.