Distributed SQL: Spanner & CockroachDB

Why This Matters#

Distributed SQL is not "Postgres that scales". It is a latency contract with physics. You get SQL, serializable transactions and automatic sharding. In return, every write goes through a consensus round trip, and every transaction that touches more than one range or region pays for the distance between replicas. Spanner and CockroachDB hide the router, the resharding and the two-phase commit. They do not hide the speed of light. A row in Virginia that must survive the loss of Virginia cannot be committed until a replica in another region has acknowledged it, and that takes 10–70 ms however good the engineering is.

That is why "we'll just use Spanner" is a sentence interviewers lean into. The L5 candidate says it to make sharding and consistency go away. The L6 candidate says "Spanner in a regional configuration, 3 read-write replicas across zones, so a commit costs one in-region Paxos round of 5–10 ms. Primary keys are UUIDv4 so inserts don't all land on the last range. The order and its line items are interleaved under the customer so a checkout is a single-split transaction, and the analytics queries go to a separate store." The L7 candidate asks what the company is buying: one global database for 40 teams, or the right to stop hand-sharding Postgres. Those are different bills, different lock-in and different failure postures.

The L5 → L6 gap is not knowing that Spanner uses TrueTime. It is knowing that the database chooses where every byte lives and how many network hops every commit takes, and your schema and locality settings are the only levers you have over both. Choose the primary key and locality well and distributed SQL feels like a single-node database. Choose badly and you have built a slow, expensive single-node database with a hot range.

The L5 → L6 → L7 Contrast#

The 60-Second Pitch#

"The core is orders, payments and inventory: about 8K writes per second at peak, with multi-row invariants across customers and merchants, so hand-sharding Postgres would turn every cross-shard transfer into a saga. I'd put it on distributed SQL, CockroachDB or Spanner, in one primary region with 3 replicas across zones: commits cost one in-region consensus round, about 5–10 ms, and transactions are serializable. Primary keys are UUIDs so inserts spread across ranges, and child rows are colocated with their parent so most transactions touch one range. Reporting reads use follower or stale reads a few seconds behind, so they never contend with writers, and heavy analytics goes to a column store through CDC. I'm not making it multi-region on day one. When EU users need local writes, I'd make user-owned tables REGIONAL BY ROW so each row lives in its user's region, and keep global tables for read-mostly config. What I'm accepting: higher per-write latency and cost than a single Postgres primary, and client-side retry handling for serialization errors."

The Three Intents#

🎯 Staff Move: "I'll design for the first intent: we've outgrown one Postgres primary, but all our writers are in one region. That means a regional configuration with zone survival, and I'll keep the schema shaped so that going multi-region later is a locality change per table, not a migration. Surviving a whole region with zero data loss is a separate requirement with a separate latency bill, and I'd want the business to ask for it explicitly."

The Staff Positions#

Architecture & Internals#

Both systems share one shape: a SQL layer on top of a transactional, sorted key-value store that is split into ranges, with each range replicated by its own consensus group. Spanner calls the unit a split and replicates it with Paxos. CockroachDB calls it a range and replicates it with Raft. The details differ; the design consequences are the same.

Ranges, Splits and Placement#

The table's primary key is the sort order of the key-value store. Rows are stored in key order, and contiguous key spans are cut into ranges. CockroachDB's current documentation lists a default maximum range size of 512 MiB and a minimum of 128 MiB, with 3 replicas per range by default (5 for system ranges) (CockroachDB replication zones). A range that grows past the maximum splits in two. Both systems also split on load: Spanner's documentation describes adding split boundaries between frequently accessed rows so each lands on a different server (Spanner schema and data model). A rebalancer then moves replicas and leases to even out disk and load across nodes.

What this changes in the design:

• Keys decide placement. Rows with adjacent keys live together; rows with distant keys are on different ranges, often different nodes. Colocation is something you build into the key.
• One range, one writer. Each range has one leaseholder (Spanner: the Paxos leader) that orders its writes. That is a per-range throughput ceiling of a few thousand writes per second in practice, whatever the cluster size.
• Splits are cheap, merges are lazy. The database reshards on its own. You never write a resharding runbook, but you also don't decide when it happens.

Per-Range Consensus and Leaseholders#

A write is durable once a majority of its range's replicas have it: 2 of 3, or 3 of 5. Reads go to the leaseholder, which can answer from its own copy without a quorum round because the lease guarantees no other replica is accepting writes. In current CockroachDB versions the Raft leader and the leaseholder are the same replica (leader leases); the documentation says detecting a node failure and moving the lease to a new node "should complete within a few seconds" (CockroachDB replication layer).

The two facts that matter in an interview:

1. A write costs one round trip from the leaseholder to the nearest majority. With 3 replicas in 3 zones of one region, that's 1–2 ms of network plus disk sync, so 5–10 ms end to end in practice. With replicas in 3 regions, it is the round trip to the second-closest region.
2. Losing a leaseholder costs seconds, not data. The range is unavailable for writes, and for leaseholder reads, until a new lease is established. That is a p99 spike for the ranges on that node, not an outage. Size client timeouts and retries for it.

Transactions: TrueTime vs Hybrid Logical Clocks#

Both systems give every transaction an MVCC timestamp, and both need clocks to make timestamps from different machines comparable. They diverge on how much they trust those clocks.

Spanner: bounded uncertainty, wait it out. TrueTime returns an interval [earliest, latest] guaranteed to contain true time. The OSDI 2012 paper reports that in production the uncertainty ε was a sawtooth between about 1 and 7 ms, so about 4 ms most of the time, driven by time masters with GPS and atomic clocks and a 30-second polling interval (Spanner paper, OSDI 2012). A read-write transaction picks a commit timestamp, then commit-waits until TT.now().earliest is past that timestamp before releasing locks and acknowledging the client. After the wait, any transaction that starts later is guaranteed a larger timestamp, which is external consistency (strict serializability). The wait overlaps with Paxos replication, so in practice it adds little on top of the consensus round.

CockroachDB: commodity clocks, an uncertainty window, and restarts. CockroachDB uses hybrid logical clocks (physical time plus a logical counter) on ordinary NTP-synchronised machines. It cannot bound uncertainty to milliseconds, so it assumes a configured maximum clock offset, --max-offset, with a default of 500 ms; a node whose clock drifts beyond the limit crashes rather than risk serving inconsistent data (cockroach start). Instead of making every write wait, it makes reads deal with uncertainty: if a read at timestamp t finds a value written in (t, t + max_offset], it can't tell whether that write happened before it, so it moves its timestamp forward and may have to restart. Transactions are SERIALIZABLE by default, and conflicts that cannot be resolved surface as retryable errors with SQLSTATE 40001 (CockroachDB transaction retry errors).

🎯 Staff Insight: You don't need to explain TrueTime's algorithm to pass. You need to say who pays for clock uncertainty: in Spanner it's every write, a few milliseconds; in CockroachDB it's reads that land on recently written keys, as occasional restarts. That tells the interviewer you know where the p99 comes from.

Distributed Transactions Across Ranges#

A transaction whose writes all fall in one range commits with one consensus round. A transaction across ranges needs an atomic commit protocol: Spanner runs two-phase commit across Paxos groups, with one group's leader as coordinator; CockroachDB writes provisional intents plus a transaction record. CockroachDB's parallel commits, on by default since v19.2, cut the cross-range commit from two sequential consensus rounds to one by marking the transaction record STAGING with the list of in-flight writes (CockroachDB parallel commits).

Commit latency, back-of-envelope (design numbers, not vendor SLAs):
  single-range write, 3 replicas in one region       ~ 1 consensus round      ->  5-10 ms
  multi-range write, same region                     ~ 1-2 rounds + intents   -> 10-20 ms
  write with quorum spanning regions                 ~ RTT to 2nd-closest region + disk
       us-east + us-central + us-west (quorum = 2 of 3) -> 25-40 ms
  client far from leaseholder/leader                 + 1 RTT client -> leader region
       EU client writing a US-homed row                 +80-90 ms
  contended row (hot counter)                        aborts and retries dominate

The formula worth saying out loud: commit latency ≈ RTT(client → leaseholder) + RTT(leaseholder → nearest quorum) + disk sync (+ commit wait in Spanner). Every multi-region design decision moves one of those terms.

Data Modeling — "The Entire Game"#

In distributed SQL the schema is the physical layout. The primary key decides which range a row lives on, which leaseholder orders its writes, and which other rows share its transaction path. Most performance problems on Spanner and CockroachDB are key-design problems, and they are expensive to fix after the table holds a few billion rows. See Schema Design for the general method and Partitioning for the partitioning theory behind it.

Step 1: Spread the Inserts#

-- Anti-pattern: every insert goes to the last range
CREATE TABLE orders (
  order_id   INT8 DEFAULT unique_rowid() PRIMARY KEY,  -- roughly time-ordered
  ...
);

-- Option A: random UUID primary key (spreads inserts across all ranges)
CREATE TABLE orders (
  order_id   UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  total_cents INT8 NOT NULL
);

-- Option B: keep a time-ordered column, but hash-shard the index (CockroachDB)
CREATE INDEX orders_by_time ON orders (created_at) USING HASH;

Spanner's documentation recommends four fixes for monotonically increasing keys: hash the key and lead with the hash, swap column order, use version 4 UUIDs (random high-order bits), or bit-reverse sequential values (Spanner schema and data model). CockroachDB's hash-sharded indexes prefix the key with a computed shard column; the documentation's examples use 16 buckets, and it notes the cost: range scans over sequential values must now read every bucket (CockroachDB hash-sharded indexes).

The rule: the leading column of every primary key and every high-write secondary index must not be monotonic. That includes timestamps, sequences, and ID schemes that embed time in the high bits (see ID Generation). A Snowflake-style ID that sorts well in Postgres is exactly the key that creates a hot tail here.

Step 2: Colocate What Commits Together#

A transaction that touches one range commits in one round; across ranges it pays the atomic commit protocol. So put rows that are written together under a common key prefix.

-- Spanner: physical interleaving of child rows inside the parent's key range
CREATE TABLE Customers (
  CustomerId STRING(36) NOT NULL,
  Name       STRING(MAX)
) PRIMARY KEY (CustomerId);

CREATE TABLE Orders (
  CustomerId STRING(36) NOT NULL,
  OrderId    STRING(36) NOT NULL,
  Total      INT64
) PRIMARY KEY (CustomerId, OrderId),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

-- CockroachDB: the same effect by leading the child's key with the parent's key
CREATE TABLE orders (
  customer_id UUID NOT NULL,
  order_id    UUID NOT NULL DEFAULT gen_random_uuid(),
  total_cents INT8 NOT NULL,
  PRIMARY KEY (customer_id, order_id)
);

Spanner's documentation notes that interleaving keeps a parent and its children together across splits as long as the parent row plus children stay under the split size limit. The design tension: a big parent (a merchant with 50 million orders) is itself a hot, oversized key range. Colocate by the entity whose transactions are small and frequent (customer, cart, account), not by the one that is merely the biggest.

Step 3: Secondary Indexes Are Distributed Too#

A secondary index is another sorted table with its own ranges. Every write to the base row is also a write to each index's range, which is usually on a different node. One table with 4 secondary indexes turns a single-range insert into a 5-range transaction.

Step 4: Decide Locality Per Table#

Locality is a schema property. CockroachDB exposes three table localities in a multi-region database (CockroachDB table localities):

ALTER DATABASE shop SET PRIMARY REGION "us-east1";
ALTER DATABASE shop ADD REGION "europe-west1";
ALTER DATABASE shop ADD REGION "asia-southeast1";

-- User-owned rows live in the user's region (hidden crdb_region column,
-- defaulting to the gateway region that inserted the row)
ALTER TABLE accounts SET LOCALITY REGIONAL BY ROW;

-- Read-mostly reference data: fast consistent reads everywhere, slow writes
ALTER TABLE currencies SET LOCALITY GLOBAL;

-- Data accessed mostly from one place stays in one region
ALTER TABLE warehouse_jobs SET LOCALITY REGIONAL BY TABLE IN "us-east1";

Spanner makes the equivalent choice mostly at the instance level: regional, dual-region or multi-region configurations, with a default leader region where writes are fastest (Spanner instance configurations). For per-row placement inside one database, Spanner's geo-partitioning lets you create instance partitions with their own regional configuration and place rows in them by a placement key (Spanner geo-partitioning). The simpler and more common pattern is still to choose one instance configuration for the whole database and keep latency-sensitive writers close to the default leader region.

🎯 Staff Move: "I'll classify every table before I pick a region count. Accounts and orders are owned by one user, so they go REGIONAL BY ROW and write locally. Currencies and feature config are read everywhere and written a few times a day, so they're GLOBAL. The inventory ledger is written from one warehouse system, so it's regional by table. The only cross-region transaction left is a currency update, and I'm fine with that being slow."

The Tunable Tradeoff — Survival vs Write Latency vs Read Freshness#

Distributed SQL has three dials, and each one moves a term in the commit-latency formula.

The documented numbers that anchor the dials:

• Region survival in CockroachDB requires at least 3 database regions, raises the replica count to 5, and the documentation states write latency increases by at least the round-trip time to the nearest region (CockroachDB survival goals).
• Global tables use a non-blocking transaction protocol: writes are given a timestamp in the future and commit-wait until it arrives, so reads in every region are consistent and local while writes are slower (CockroachDB global tables). The lead time scales with the maximum clock offset, which is why CockroachDB's multi-region guidance suggests lowering --max-offset to 250 ms where clocks are well synchronised.
• Follower reads with exact staleness should be at least 4.2 seconds in the past to avoid blocking on conflicting writes (CockroachDB follower reads). Spanner's stale reads (exact or bounded staleness) play the same role.

Write latency by configuration (design numbers; US-East/US-Central/US-West, EU ~85 ms away):

  config                                       local writer    remote writer
  regional, zone survival                      5-10 ms         +RTT to region (30-90 ms)
  multi-region, REGIONAL BY ROW, zone survival 5-10 ms (home)  +RTT to home region
  multi-region, REGIONAL BY ROW, region surv.  25-40 ms        +RTT to home region
  GLOBAL table write                           hundreds of ms (future timestamp + commit wait)
  Spanner multi-region (writer near leader)    ~RTT to nearest other read-write region + commit wait

🎯 Staff Insight: "Region survival" is not a database setting; it is a promise to the business that costs a cross-region round trip on every write, forever. Before you turn it on, find the person who will sign off on adding 20–40 ms to checkout and roughly 1.7× the replica bill. If nobody will, zone survival plus asynchronous backups to another region is the honest answer.

Anti-Patterns — What Kills Distributed SQL Deployments#

1. Sequential Primary Keys#

Auto-increment, timestamp-leading or time-ordered IDs funnel every insert to the last range. Symptom: one node at 100% CPU, the rest idle; write throughput flat no matter how many nodes you add. Fix: UUIDv4, hash prefix, bit-reversed sequence or hash-sharded index. See Hot Keys.

2. Treating It as Postgres With Infinite Scale#

Wire compatibility is not behaviour compatibility. A query plan that did a fast index scan on one node can fan out to 200 ranges on 30 nodes. Long-running transactions that held a few row locks on Postgres now hold intents that block other transactions across the cluster. Port the workload with its query plans reviewed, not just its schema. See PostgreSQL.

3. No Retry Loop#

Under serializable isolation, contention produces aborts by design. ORMs that treat 40001 as a fatal error turn a normal contention spike into user-facing 500s. Every transaction needs a bounded retry with jittered backoff, and the transaction body must be safe to re-run (no side effects like emails or payment calls inside it).

4. Hot Rows Disguised as Business Logic#

A global counter, a single "inventory remaining" row for a flash sale, or one account that every transfer debits. Consensus doesn't make one row faster; it makes it slower, because each write is a quorum round and conflicting writers abort. Shard the counter, batch the updates, or move the hot path out of the transactional store. See Concurrency Control and Ticket Drops.

5. Multi-Region Everything#

Making every table span regions with region survival "for resilience" puts a cross-region round trip on writes that never needed it: sessions, audit logs, idempotency keys. Classify data first; most of it is regional or disposable.

6. Analytics on the OLTP Cluster#

Full-table scans for dashboards compete with checkout for the same leaseholders, and on a consensus-replicated store every byte scanned is the most expensive byte you own. Use follower reads for light reporting and CDC into a column store for anything heavier. See OLAP Databases.

7. Giant Transactions and Bulk Deletes#

Deleting 50 million rows in one transaction writes 50 million intents, holds them until commit, and can stall every reader of those ranges. Batch deletes into chunks of 1,000–10,000 rows, or use row-level TTL features where available.

8. Schema Changes Without a Rollout Plan#

Both systems run online schema changes, but backfilling an index on a 5 TB table is hours of extra write and compaction load. Treat a schema change as a deploy: expand, backfill off-peak, verify, then contract. See Schema Design.

The Technology Landscape — Head-to-Head Comparison#

Head-to-head guidance:

• Spanner vs CockroachDB: Spanner if you are on Google Cloud, want the operational burden fully outsourced and value the strongest consistency model. CockroachDB if you need multiple clouds or on-prem, per-row locality controls, or PostgreSQL compatibility for existing code.
• Distributed SQL vs DynamoDB: DynamoDB is cheaper per operation with single-digit-millisecond key access and no query planner to surprise you, but transactions are limited and secondary indexes are eventually consistent. If the access patterns are a dozen known key lookups, DynamoDB wins. If they are relational and evolving, distributed SQL. See DynamoDB.
• Distributed SQL vs Cassandra: Cassandra for write-heavy, multi-region, last-writer-wins data at the lowest cost per write. Distributed SQL when invariants span rows. See Cassandra.

Patterns#

Pattern 1: Regional Transactional Core#

A single-region cluster, 3 replicas across zones, serving the business's transactional core (orders, payments, inventory). UUID keys, parent-prefixed child keys, a retry wrapper in the data-access library. When: you've outgrown one primary, writers are in one region. This is the default for most designs that reach for distributed SQL.

Pattern 2: Home-Region Rows#

REGIONAL BY ROW tables where each user's data lives in their region, giving local writes and data residency by placement. When: users are spread across continents and their data is mostly their own. The trap: unique constraints that don't include the region column must be checked in every region, and joins between rows homed in different regions are cross-region reads. Include the region in unique constraints where the business allows it.

Pattern 3: Global Reference Data#

GLOBAL tables (CockroachDB) or a read-only replica per region with stale reads (Spanner) for config, catalogs, currency rates and permissions. When: read in every request, written a few times an hour. Not for anything written per user action.

Pattern 4: Stale-Read Offload#

Route reporting, search indexing and "my order history" pages to follower or bounded-staleness reads 5–15 s behind. They use any replica, don't contend with writers and keep leaseholders free for the transactional path. When: product can sign off on seconds of staleness for that screen.

Pattern 5: CDC Out of the Core#

Changefeeds (CockroachDB) or change streams (Spanner) publish committed changes to Kafka for search, caches, analytics and the outbox pattern. When: always, once more than one system needs the data. Treat the change stream as an API with a schema and an owner.

Pattern 6: Directory on Distributed SQL#

Even teams that hand-shard their bulk data often keep the shard directory, tenant metadata and global uniqueness (usernames, email addresses) in a small distributed SQL cluster, because it's the piece that needs strong consistency and cross-region availability but carries little volume. See Sharded Database.

Scaling#

The Numbers#

The Spanner compute and storage figures come from its compute capacity documentation (Spanner compute capacity); the availability figures from its instance configuration documentation.

Capacity Math#

Workload: 8K writes/s peak, 60K reads/s, 6 TB data, one region, zone survival.

CockroachDB (self-hosted), sizing rule of thumb per node: ~1-2K writes/s and ~1.5 TB of
replicated data with headroom (design estimate; load-test your own schema):
  replicated data   = 6 TB x 3 replicas = 18 TB  -> 12 nodes at 1.5 TB each
  write capacity    = 8K/s / 1.5K per node      -> ~6 nodes, but every write lands on 3
  reads             = 60K/s, 70% served as stale follower reads -> spread across all nodes
  => 12-15 nodes across 3 zones (multiples of 3 so each zone holds one replica)

Spanner (regional): storage needs 6 TB / 10 TiB per node -> 1 node minimum for storage;
CPU drives it: plan 6-10 nodes for 8K writes/s with secondary indexes, then
run the managed autoscaler against a 65% high-priority CPU target (design estimate).

What Breaks First as You Grow#

1. A hot range long before the cluster runs out of capacity. One sequential index, one popular tenant, one counter.
2. Contention on a few rows: retries grow superlinearly with concurrency on the same key.
3. Secondary index write amplification: at 5+ indexes the write path is mostly index maintenance.
4. Schema change and backfill time on multi-terabyte tables: hours of extra load.
5. Cross-region transactions that crept into the hot path when a second region was added.
6. Cost: consensus-replicated storage at 3–5 copies, on premium SSD, for data that turned out to be logs.

Scaling Moves in Order#

1. Fix keys and indexes (hash-shard or UUID; drop unused indexes).
2. Move tolerant reads to stale or follower reads.
3. Shard hot counters and batch hot-row updates.
4. Add nodes; the rebalancer moves ranges in minutes to hours depending on data size.
5. Move analytics and archives out through CDC; add row-level TTL.
6. Only then: multi-region, table by table.

Failure Modes & Recovery#

1. Hot Range From a Sequential Key#

• Symptom: Write p99 rises from 15 ms to 400 ms; one node at 95% CPU, others under 30%; adding nodes doesn't help.
• Root cause: Primary key or secondary index leads with a timestamp or sequence; every insert targets the rightmost range.
• Detection: Per-range QPS (CockroachDB hot ranges page; Spanner Key Visualizer); leaseholder QPS skew across stores; CPU spread > 3× between nodes.
• Fix: Add a hash-sharded index or new UUID-keyed table and migrate writers; short term, split and scatter the range manually.
• Prevention: Schema review lint: no monotonic leading column on tables over 100 writes/s. See Hot Keys.

2. Retry Storm Under Contention#

• Symptom: A promotion starts; 40001 errors climb from 0.1% to 30% of transactions; throughput falls as concurrency rises.
• Root cause: Thousands of transactions write the same rows (inventory count, a shared balance); each abort retries immediately and contends again.
• Detection: Transaction restart rate by statement fingerprint; txn.restarts metrics; contention event tables; p99 latency of the hot statement.
• Fix: Exponential backoff with jitter and a retry cap of 5–10; queue or batch writes to the hot row.
• Prevention: Load-test the hottest row at 3× peak concurrency; model hot counters as N sharded rows. See Concurrency Control and Backpressure.

3. Region Loss With Zone Survival#

• Symptom: A cloud region goes dark. Users homed there see errors on every write and strong read; other regions stay healthy except for transactions that touch those rows.
• Root cause: With zone survival, all voting replicas of a region's home rows are in that region. This is the configured trade, not a bug.
• Detection: Unavailable range count by region; node liveness by locality; synthetic writes per region.
• Fix: Wait for the region, or fail over from backups to another region and accept the RPO of the last backup. Stale reads may still be served from non-voting replicas elsewhere if configured.
• Prevention: Decide per data class, in writing: region survival for the ledger, zone survival plus frequent cross-region backups for the rest. Rehearse the restore.

4. Clock Skew Takes Nodes Out#

• Symptom: Several CockroachDB nodes crash in a short window; logs show clock offset errors; ranges briefly unavailable while leases move.
• Root cause: NTP misconfiguration, a VM pause, or a time-source change pushed offset past --max-offset. Nodes self-terminate rather than serve inconsistent reads.
• Detection: clock-offset.meannanos per node against the configured limit; alert at 40% of the max offset.
• Fix: Correct the time source; restart nodes once clocks agree.
• Prevention: Cloud provider's time-sync service with leap-second smearing configured identically on every node; alert well before the limit; in Spanner this risk is the provider's.

5. Analytics Query Starves the Transactional Path#

• Symptom: Checkout p99 doubles every morning at 9:00; CPU saturates on leaseholders; no change in traffic.
• Root cause: A dashboard runs full scans with strong reads against the OLTP tables; scans consume leaseholder CPU and I/O.
• Detection: Top statements by CPU and rows read; statement fingerprints from BI users; per-node CPU correlated with a schedule.
• Fix: Move the query to follower reads or kill it; give BI a separate user with admission-control priority set low.
• Prevention: BI goes through CDC to a column store; OLTP access by allow-listed services only. See OLAP Databases.

Operational Reality Matrix#

When to Use vs. Alternatives#

When NOT to Use Distributed SQL#

• The workload fits one Postgres primary. A modern managed Postgres instance handles tens of thousands of writes per second and terabytes of data. Distributed SQL triples storage replicas, adds 5–10 ms to every write, and costs 2–5× more for the same workload. If your 3-year growth curve fits a single primary plus read replicas, buy the cheaper thing. See PostgreSQL.
• Single-region, latency-critical writes. An order-matching engine or a hot leaderboard needs sub-millisecond writes. Consensus on every write is the wrong tool. See Order Matching.
• The data has no cross-row invariants. Event logs, clickstreams, sensor data and chat messages need throughput and cheap storage, not serializable transactions.
• The access pattern is pure key-value at very high scale. DynamoDB or Cassandra deliver predictable single-digit-millisecond access at a fraction of the cost per operation.
• Every transaction is a hot row. Distributed SQL can't make one row faster. If the design is a global counter, fix the design.
• No one will own the cluster. Self-hosted CockroachDB is a fleet with upgrades, backups, clock hygiene and capacity planning. If there's no platform owner, use a managed service or a simpler database.

🎯 Staff Move: "Before I pick distributed SQL, I'll check whether we've actually outgrown Postgres. At 3K writes per second and 2 TB, one primary handles it for years at a third of the cost. The trigger for me is cross-shard transactions becoming the main source of bugs, or writers on two continents. Until then, I'd keep the schema distribution-friendly, UUID keys and parent-prefixed children, so the migration is cheap when it comes."

Operational Concerns#

What the On-Call Actually Does#

1. Watches the skew, not the average. Per-node CPU and leaseholder QPS spread; a 3× gap is a hot range even when cluster CPU is 40%.
2. Triages slow statements by fingerprint: full scans, missing indexes, contention events. Most "the database is slow" pages are one query.
3. Watches retry rates per statement and owns the conversation with the team whose transaction is contending.
4. Runs rolling upgrades (self-hosted): one node at a time, drain leases first, wait for under-replicated ranges to reach zero before the next. On a 30-node cluster this takes hours and is routine.
5. Verifies backups by restoring them into a scratch cluster on a schedule; a backup that has never been restored is a hope.
6. Guards clock hygiene (self-hosted CockroachDB) and the admission-control priorities that keep batch jobs behind user traffic.

Key Metrics & Alerts#

Upgrades and Schema Changes as Ongoing Cost#

Self-hosted CockroachDB ships major versions on a regular cadence, and each has a support window; falling behind ends in a forced multi-step upgrade. Spanner removes this entirely, which is part of what its premium buys. Schema changes, on both, need the same discipline as deploys: expand, backfill, verify, contract, with backfills scheduled off-peak and watched for write-latency impact.

Interview Application — Staff-Level Plays#

Which Case Studies Use Distributed SQL#

Every System Design Question Has a Distributed SQL Moment#

• Payments: "Each account's balance and its ledger entries share a key prefix, so a single-account debit is a one-range transaction at 5–10 ms. Transfers between accounts are cross-range but still serializable, and I keep the idempotency key in the same transaction so retries after 40001 can't double-charge."
• Global social app: "Profiles and posts are REGIONAL BY ROW, homed in the author's region. The follow graph is read everywhere but written per user, so it's also per-row, and the feed is precomputed into a cache. The only global table is configuration."
• Ticketing: "I won't put 50,000 buyers on one inventory row. Seats are rows; a buyer's transaction claims one seat row with a conditional update, so contention spreads across 50,000 keys instead of one."
• URL shortener: "I'd not use distributed SQL here. The access pattern is a key lookup at 100K reads per second, so a key-value store plus a cache is cheaper and faster. See URL Shortener."

What Interviewers Probe#

Common Interview Mistakes#

L5 vs L6 vs L7 Responses#

The Staff Distributed SQL Checklist#

1. Justify it: "We need cross-entity transactions beyond one primary's write capacity, or writers on several continents. Otherwise Postgres."
2. Keys: "UUIDv4 or hash-prefixed primary keys; no index leads with a timestamp; children prefixed with the parent key."
3. Commit math: "Client to leaseholder, plus leaseholder to quorum, plus disk. In-region that's 5–10 ms."
4. Locality per table: "Per-row home regions for user data, global for read-mostly config, regional for the rest."
5. Survival goal, priced: "Zone survival by default; region survival only for the ledger, at a cross-region round trip per write and 5 replicas."
6. Contention and reads: "Retries with jitter on 40001, sharded hot rows, follower reads for anything that tolerates 5 seconds, CDC for analytics."

🎯 Staff Insight: Don't use distributed SQL for event logs, analytics, blobs, queues, sessions, or any workload that fits a single Postgres primary for the next three years. The strongest distributed SQL signal in an interview is explaining why you aren't using it for half the data in the design.

Evaluation Rubric#

Strong hire signals

Lean no-hire signals

Common false positives

• Explaining TrueTime in detail ≠ designing with Spanner. Ask where the leader region is and what an EU write costs.
• "We migrated to CockroachDB" ≠ judgment. Ask what they'd keep in Postgres.
• Knowing Raft ≠ knowing hot ranges. Consensus correctness says nothing about where the inserts land.

Beyond Staff: The Principal View#

Why L7 Sees This Problem Differently#

At Staff level, distributed SQL is a database you configure well: keys, locality, survival, retries. At Principal level it is a bet on where the company's transactional data will live for the next decade, with a vendor, a cost curve and a set of one-way doors. The question changes from "Spanner or CockroachDB for this service?" to "Do we want one shared, strongly consistent OLTP platform that most teams default to, or a few dedicated clusters for the systems that genuinely need it, with Postgres as the default everywhere else? And what does it cost to leave?"

🧭 Principal Move: "Before choosing a vendor, I want three things written down: which data classes need serializable transactions across entities, which need to survive a region with zero data loss, and which need to live in a specific country. That list sets the cluster count, the survival goals and most of the bill. Anything not on it stays on Postgres, and we stop paying consensus prices for logs."

The Org-Level Fault Line#

One shared distributed SQL platform vs per-domain clusters vs Postgres-by-default.

The Principal default: Postgres is the paved road for new services. Distributed SQL is a platform product with an entry review: dedicated clusters per critical domain, owned by a database platform team, offered when a team shows the trigger (cross-entity writes beyond one primary, multi-region writers, or RPO 0 across regions). No shared cluster across unrelated domains, because a cross-domain transaction is usually a coupling to remove, not a feature to enable.

🧭 Principal Insight: "The expensive mistake isn't choosing the wrong vendor. It's letting distributed SQL become the default for data that never needed it, so the company pays 3–5 replicas of premium storage and a consensus round per write for audit logs and session state."

Cost Model#

Assumptions (directional only; check current pricing): self-hosted nodes 16 vCPU / 64 GiB with fast SSD ~$900/month each; managed regional node-equivalents ~$700–1,000/month and multi-region ~2.5–3.5× that; loaded engineer $250K/year ($21K/month). Storage is counted at 3 replicas (zone survival) or 5 (region survival).

Two levers dominate. First, data classification: moving logs, events and analytics out of the cluster often cuts its size by half. Second, survival goals: region survival adds roughly two-thirds more replicas than zone survival for the same data, so it should cover the ledger, not the product catalog.

The 3-Year Evolution Path#

One-Way Doors vs Two-Way Doors#

The Standard I'd Write#

RFC-DATA-007: Using Distributed SQL

Scope: Any new or migrated datastore on the distributed SQL platform.

Entry criteria (one MUST apply):
  a. Cross-entity transactional writes beyond one Postgres primary after tuning.
  b. Writers in two or more regions with local-latency requirements.
  c. Data class requires RPO 0 across a full region loss.

MUST
  1. Primary keys and high-write index leading columns are non-monotonic
     (UUIDv4, hash prefix, bit-reversed, or hash-sharded index). Lint enforced.
  2. Every transaction runs through the shared retry wrapper (jittered backoff,
     max 10 attempts) and has an idempotent body with no external side effects.
  3. Each table declares a locality; each database declares a survival goal,
     with region survival approved by the data owner and finance.
  4. No analytics, logs, blobs or queues. Reporting uses stale/follower reads;
     analytics uses CDC to the column store.
  5. Schema changes follow expand -> backfill off-peak -> verify -> contract.
SHOULD
  6. Children prefixed with the parent key so common transactions touch one range.
  7. No more than 3 secondary indexes on tables above 1K writes/s.

Exceptions: Database platform lead plus owning director; time-boxed; on the dashboard.
Rollout: lint in warn mode for one quarter, then blocking in CI.

Success metrics: zero hot-range incidents per quarter; restart rate < 1% for
every service; restore drill per cluster each quarter; cost per transaction tracked.

What I'd Tell the VP#

"Our order and payment data has outgrown a single database server, and splitting it ourselves would cost us two years of engineering and a long tail of bugs. I recommend a distributed SQL database for that core data only, run by one platform team. It keeps our data consistent as we grow and lets us serve European customers from Europe. It is more expensive per transaction than what we run today, so everything that doesn't need it, like logs and analytics, stays on cheaper systems. Surviving the loss of a whole cloud region with no lost payments is possible, but it slows every payment by a few hundredths of a second and adds roughly two-thirds to that cluster's cost, so I'd apply it to the ledger only."

Principal Interview Signals#

Staff answers that L7 interviewers find insufficient:

• "We'll use Spanner for global consistency" without saying which data needs it and what the rest of the data uses.
• "Multi-region with region survival" without the replica cost, the latency on every write, or who signed off.
• "Use hash-sharded keys" as advice for one service, rather than a lint in the schema-change pipeline for every team.

How Real Companies Built It#

Google Spanner — TrueTime and Commit Wait#

Google's OSDI 2012 paper describes Spanner as the first system to distribute data at global scale and support externally consistent distributed transactions. Data is sharded into Paxos-replicated groups, and the TrueTime API exposes clock uncertainty, which in production was a sawtooth of about 1–7 ms; read-write transactions commit-wait out that uncertainty so commit order matches real time (Spanner paper, OSDI 2012).

Staff insight: The design spends milliseconds on every write to buy global ordering and lock-free consistent snapshot reads. In an interview, say that trade out loud: commit wait is cheap relative to a cross-region quorum, which is why Spanner's real latency cost is replica placement, not clocks.

Google F1 — AdWords on Spanner#

Google's VLDB 2013 paper describes F1, the distributed SQL database built to support the AdWords business, layered on Spanner for synchronous cross-datacenter replication and strong consistency. The paper says the higher commit latency of synchronous replication is mitigated through a hierarchical schema model and application design (F1 paper, VLDB 2013).

Staff insight: The latency problem was solved in the schema: hierarchical (interleaved) tables keep a customer's data together so common transactions stay local. That is the "colocate what commits together" rule, proven on a revenue-critical system.

CockroachDB — Geo-Distributed SQL on Commodity Clocks#

The CockroachDB SIGMOD 2020 paper presents a distributed SQL database built for global OLTP workloads with high availability and strong consistency, covering its Raft-replicated ranges, its transaction model and its data placement and replication. Its current documentation describes the per-row home regions, global tables and survival goals that turn those mechanisms into schema-level choices (CockroachDB SIGMOD 2020 paper, survival goals).

Staff insight: Without hardware clocks, CockroachDB moves the cost of uncertainty to reads and exposes locality as SQL. That makes multi-region design a reviewable schema decision, which is exactly where a Staff engineer wants it.

Uber — Fulfillment Platform on Spanner#

Uber's engineering blog describes rebuilding its fulfillment platform, the system behind trips, orders and the entities that fulfill them, onto Google Cloud Spanner in a North America multi-region configuration. The previous design traded consistency for availability and coordinated multi-entity writes with best-effort mechanisms; the new one relies on cross-table, cross-shard ACID transactions and adds an internal component for at-least-once post-commit work (Uber blog).

Staff insight: The win was deleting application-level consistency code, not raw throughput. Notice the side component: post-commit work like notifications still needed its own at-least-once machinery, because a database transaction can't include an external side effect. See Ride Hailing & Delivery.

Practice Drill#

Prompt: "You run a marketplace on one Postgres primary: 6K writes/s at peak, growing 60% a year, 4 TB. Orders, payments and seller balances must stay consistent. Next year you launch in the EU with a data residency requirement. Design the data layer for the next three years."

Quick Reference Card#

Model:            SQL over a sorted, transactional KV store, split into ranges,
                  each range replicated by its own consensus group
Spanner:          splits + Paxos; TrueTime eps ~1-7 ms (2012 paper); commit wait;
                  external consistency; 1 node = 1,000 PU, 10 TiB per node;
                  99.99% regional, 99.999% multi-region
CockroachDB:      ranges + Raft; HLC; --max-offset 500 ms default (250 ms suggested
                  for multi-region); range 128-512 MiB; 3 replicas (5 system);
                  SERIALIZABLE default, Read Committed available; retries = 40001
Commit latency:   RTT(client->leaseholder) + RTT(leaseholder->quorum) + disk
                  (+ commit wait in Spanner); in-region ~5-10 ms
Survival:         ZONE = 3 voters in home region; REGION = 5 replicas, >= 3 regions,
                  +RTT to nearest region on every write
Localities:       REGIONAL BY TABLE | REGIONAL BY ROW (crdb_region) | GLOBAL
Follower reads:   exact staleness >= 4.2 s; no contention with writers
Parallel commits: cross-range commit in one consensus round (CockroachDB >= 19.2)
Hot-key fixes:    UUIDv4, hash prefix, bit-reversed sequence, hash-sharded index

RED FLAGS
  - Auto-increment or timestamp-leading primary keys or indexes
  - No retry handling for serialization errors
  - Region survival on every table with no latency number
  - Dashboards and full scans on the OLTP cluster
  - Side effects (payments, emails) inside a retryable transaction
  - One shared cluster for unrelated domains
  - Distributed SQL for data that fits one Postgres primary for 3 years