CockroachDB for Data Engineering: Multi-Region OLTP, Change Feeds & Postgres Compatibility

cockroachdb is the distributed-SQL database that a senior data engineer reaches for the moment a single-region Postgres or Aurora primary stops answering the "what happens when the region drops?" question — a horizontally-scalable, serializable-consistent, Postgres-wire-compatible OLTP engine whose 512MB ranges are replicated by Raft, whose leaseholder replicas coordinate reads at strong consistency, and whose REGIONAL BY ROW primitive lets a single logical table keep each row's replicas close to the users that write it. The engineering question in 2026 is no longer "should I consider distributed SQL?" — every stack with a multi-region compliance boundary or a multi-continent customer base has already asked it — but "which distributed SQL, and how does its change-feed story land the lakehouse feed?"

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through CockroachDB's range + Raft architecture and how it differs from a Postgres read replica," or "how does CREATE CHANGEFEED compare to Debezium against Postgres logical replication?", or "when would you pick distributed sql over a sharded Postgres, and when would you not?" It covers the four axes senior interviewers actually probe — consistency, latency locality, postgres compatibility depth, and change-feed lakehouse fit — plus the concrete recipes: creating a REGIONAL BY ROW table with SURVIVE REGION FAILURE, wiring a CREATE CHANGEFEED INTO 'kafka://...' with resolved timestamps for exactly-once Iceberg ingest, migrating a Django/SQLAlchemy app off Postgres onto cockroachdb, and monitoring range hot spots. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the design practice library →, and sharpen the streaming axis with the streaming practice library →.

---

On this page

• Why CockroachDB matters for data engineering in 2026
• Distributed architecture — ranges, Raft, leaseholders
• Postgres compatibility + SQL layer
• Change Feeds — CDC for the lakehouse
• Multi-region + when CockroachDB wins + interview signals
• Cheat sheet — CockroachDB recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why CockroachDB matters for data engineering in 2026

Distributed SQL with Postgres wire compat, ACID across regions, and a change-feed that lands the lakehouse — the OLTP primary a senior DE actually defends

The one-sentence invariant: cockroachdb is a distributed, horizontally-scalable SQL database that speaks the Postgres wire protocol, replicates data as 512MB ranges via Raft consensus with a leaseholder replica coordinating strong-consistency reads, exposes multi-region primitives (REGIONAL BY ROW, SURVIVE REGION FAILURE, follower reads) as first-class DDL, and ships a native CREATE CHANGEFEED CDC pipeline whose resolved timestamps convert at-least-once Kafka delivery into exactly-once lakehouse ingest — and the choice binds you for years because every downstream consumer, dashboard, and reconciliation job hard-codes assumptions about which region owns which row, what the leaseholder's read latency looks like from the app pod, and how the changefeed's resolved-ts guarantees interact with the sink's dedupe strategy. The pattern you pick in month one becomes the pattern you defend in year three, because migrating off a distributed SQL primary to a sharded single-region one is a re-architecture, not a config change.

The four axes interviewers actually probe.

• Consistency. CockroachDB is serializable by default — the strictest ANSI SQL isolation level. Every transaction sees a linearizable ordering of committed writes; there is no read-committed anomaly window like Postgres. This costs latency (every write pays a Raft round-trip; every strong read hits the leaseholder), but it eliminates the entire class of "phantom read on the analyst dashboard" bugs. Interviewers open with the isolation question because it separates people who have shipped multi-region OLTP from people who have only read the marketing page.
• Latency locality. In a single-region cluster, CockroachDB is roughly 1.5–3× slower than single-node Postgres per commit (Raft round-trip vs single fsync). In a multi-region cluster, the story flips: REGIONAL BY ROW keeps hot data's replicas inside one region, so a user in eu-west commits at ~5 ms while a user in ap-south commits at ~7 ms — both without a cross-continent round-trip. Follower reads (AS OF SYSTEM TIME follower_read_timestamp()) serve bounded-stale reads from the nearest replica.
• Postgres compatibility depth. Same wire protocol (port 26257 by default; drop-in for psycopg2, SQLAlchemy, Prisma, Hibernate, ActiveRecord), most PG SQL (JSONB, arrays, GIN, CTEs, window functions, foreign keys, prepared statements), most pg_catalog surface. Not supported: triggers, stored procedures (PL/pgSQL), LISTEN/NOTIFY, SEQUENCE (use UUID or unique_rowid()), custom types. A Django or Rails app that avoids those five constructs runs unchanged.
• Change-feed lakehouse fit. CREATE CHANGEFEED INTO 'kafka://...' is native — no Debezium, no separate JVM process, no replication-slot management. Resolved timestamps let downstream sinks (Iceberg, Delta, Snowflake) convert at-least-once Kafka delivery into exactly-once ingest. Schema evolution is automatic in Avro mode. This is the single biggest DE-facing feature that separates CockroachDB from a sharded-Postgres deployment.

The 2026 reality — distributed SQL is a category, and CockroachDB shares it with three others.

• CockroachDB is the "PG-compat + multi-region-first" pick. Default when your workload is multi-region OLTP + reference data with a changefeed feeding the lakehouse. Best-in-class REGIONAL BY ROW and survival goals; weakest in HTAP (real-time analytics on the OLTP data).
• YugabyteDB is the "deeper PG compat" pick. Reuses the Postgres query layer verbatim, so triggers, stored procedures, and extensions work. Weaker multi-region ergonomics; comparable Raft-based storage.
• TiDB is the "HTAP" pick. Row store (TiKV) for OLTP + columnar store (TiFlash) for analytics, both fed from the same Raft groups. Weaker PG compat (MySQL-wire-compatible primary), stronger analytical query performance on operational data.
• Aurora is the "single-region managed" foil. Not distributed SQL — a single-region Postgres/MySQL primary with fast-failover replicas. Simpler to operate, cheaper for small workloads, but cannot survive a region outage without a manual DR promotion.

What interviewers listen for.

• Do you name all four distributed-SQL peers (CockroachDB, YugabyteDB, TiDB, Aurora) unprompted? — senior signal.
• Do you say "serializable by default" in the first sentence when consistency comes up? — required answer.
• Do you push back on "why not just shard Postgres" with the multi-region + changefeed + survival-goal answer? — senior signal.
• Do you name REGIONAL BY ROW as the row-level home-region primitive and not "some kind of geo-partitioning"? — required answer.
• Do you describe changefeeds as "resolved timestamps convert at-least-once into exactly-once" rather than as "CDC to Kafka"? — senior signal.

Worked example — the four-axis comparison across distributed SQL peers

Detailed explanation. The single most useful artifact for a CockroachDB interview is a memorised 4×4 comparison table (four axes × four peers). Every senior discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical multi-region SaaS OLTP workload that needs eu-west, us-east, and ap-south primaries.

• Workload. 20k TPS mixed read/write; row-level home region per tenant; sub-10 ms p50 local commit; sub-second changefeed to Iceberg.
• Consistency. Serializable required (financial workload; no phantom-read tolerance).
• Survival. Cluster must survive a full region outage without operator intervention.
• App stack. Django + SQLAlchemy; some Rails legacy.

Question. Build the four-peer comparison for the workload above and pick the winner for each axis.

Input.

Axis	CockroachDB	YugabyteDB	TiDB	Aurora
Serializable by default	yes	yes (default snapshot)	yes (opt-in)	no (RC default)
Multi-region primary	native (REGIONAL BY ROW)	native (tablespaces)	limited (single-region-primary)	no (single-region only)
Postgres wire	yes	yes (reuses PG query layer)	no (MySQL wire)	yes (Aurora PG)
Native changefeed	yes (CREATE CHANGEFEED)	via Debezium (CDC service)	via TiCDC	via Debezium + logical replication

Code.

-- CockroachDB — one DDL that expresses the multi-region + survival intent
CREATE DATABASE saas PRIMARY REGION 'us-east1'
    REGIONS 'us-east1', 'europe-west1', 'asia-south1'
    SURVIVE REGION FAILURE;

USE saas;

CREATE TABLE tenants (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    region       crdb_internal_region NOT NULL,
    name         STRING      NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

-- Insert routes the row's replicas to the specified region
INSERT INTO tenants (region, name)
VALUES ('europe-west1', 'acme-eu'),
       ('us-east1',     'acme-us'),
       ('asia-south1',  'acme-ap');

Step-by-step explanation.

1. CREATE DATABASE ... PRIMARY REGION ... SURVIVE REGION FAILURE is a single DDL that expresses two orthogonal decisions: which regions the cluster spans, and what failure the cluster must survive without operator intervention. Weak candidates configure survival at the zone-config level (imperative, error-prone); senior candidates use declarative multi-region DDL.
2. LOCALITY REGIONAL BY ROW AS region says "every row has a home region determined by the value in the region column, and the row's replicas are physically placed in that region." A user in europe-west1 writing to their own tenant row commits with a Raft round-trip inside europe-west1 — no trans-Atlantic latency.
3. SURVIVE REGION FAILURE promotes replica count from 3 (default: survive zone failure) to 5 (survive one full region outage). The cost is 2× storage and slightly higher write latency; the benefit is that a whole-region outage does not stop writes.
4. The changefeed you'd wire on top (CREATE CHANGEFEED FOR TABLE tenants INTO 'kafka://...' WITH resolved) inherits the same multi-region topology — the changefeed emits events in per-key order, and resolved timestamps let the Iceberg sink detect completeness across regions.
5. This DDL is the reason CockroachDB wins the axis-by-axis comparison for this workload. Postgres would need pgpool + Patroni + Bucardo/Debezium + application-level tenant sharding — five components, five failure modes. CockroachDB delivers all of it as one declarative DDL surface.

Output.

Axis	Winner	Why
Serializable + multi-region OLTP	CockroachDB	Native REGIONAL BY ROW + SURVIVE REGION FAILURE in one DDL
Deepest PG compat (triggers, PL/pgSQL)	YugabyteDB	Reuses PG query layer verbatim
HTAP (real-time analytics on OLTP)	TiDB	TiFlash columnar replicas fed from same Raft groups
Simplest managed single-region	Aurora	No distribution complexity to operate

Rule of thumb. Never pick a distributed SQL primary based on "which one is trendy." Pick it based on (consistency × multi-region primitives × PG compat × changefeed fit) — the four axes. Write the table on a whiteboard first; the winner falls out of the constraints.

Worked example — what senior interviewers actually probe

Detailed explanation. The senior CockroachDB interview has a predictable structure: the interviewer opens with an ambiguous question ("we're outgrowing single-region Postgres — what would you consider?"), then progressively narrows to test whether you understand the axes. The candidates who name the pattern in sentence one score highest; the candidates who describe "a database migration" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "We're outgrowing single-region Postgres — what's your first suggestion?" — invites you to name a category.
• Follow-up 1. "How does CockroachDB stay consistent across regions?" — probes the Raft + leaseholder + serializable axis.
• Follow-up 2. "What breaks when we migrate our Django app?" — probes the PG-compat-gap axis.
• Follow-up 3. "How do we feed the lakehouse?" — probes the changefeed axis.
• Follow-up 4. "What happens when eu-west drops?" — probes the survival-goal axis.

Question. Draft a 5-minute senior CockroachDB answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Category named	"we'd shard Postgres"	"distributed SQL — CockroachDB for multi-region + changefeed"
Consistency	"it's eventually consistent"	"serializable via Raft consensus per range"
Migration pain	"should just work"	"no triggers, no PL/pgSQL, no LISTEN/NOTIFY, no SEQUENCE"
Lakehouse feed	"we'd add Debezium"	"native CREATE CHANGEFEED with resolved timestamps"
Regional outage	"we'd fail over"	"SURVIVE REGION FAILURE — writes continue"

Code.

Senior CockroachDB answer template (5 minutes)
==============================================

Minute 1 — name the category up front
  "I'd move to distributed SQL — specifically CockroachDB — because
   we need multi-region OLTP with row-level home regions and a native
   changefeed for the lakehouse."

Minute 2 — consistency + architecture
  "CockroachDB replicates data as 512MB ranges via Raft consensus.
   Each range has three (or five, for SURVIVE REGION FAILURE) replicas
   on different nodes; one replica holds the range lease and coordinates
   strong-consistency reads. Isolation is serializable by default —
   strictest ANSI SQL level."

Minute 3 — Postgres compat + migration
  "Same wire protocol (port 26257), same drivers, most SQL. Not
   supported: triggers, stored procedures, LISTEN/NOTIFY, SEQUENCE.
   Our Django app uses SQLAlchemy without triggers so it's a drop-in;
   the Rails legacy has a couple of PL/pgSQL functions we'll port to
   application code."

Minute 4 — changefeed for the lakehouse
  "CREATE CHANGEFEED INTO 'kafka://...' WITH resolved emits per-key
   ordered events plus periodic resolved timestamps. The Iceberg sink
   uses resolved-ts as a watermark — it can compact at-least-once
   delivery into exactly-once ingest. No Debezium, no replication slots
   to babysit."

Minute 5 — multi-region + survival
  "REGIONAL BY ROW keeps hot data local: a user in eu-west commits at
   ~5 ms because the Raft quorum runs inside eu-west. SURVIVE REGION
   FAILURE means one full region can drop and writes keep succeeding.
   Follower reads give sub-millisecond bounded-stale reads for
   dashboards without hitting the leaseholder."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the category ("distributed SQL — specifically CockroachDB") signals you're a decision-maker, not a task-runner. Weak candidates dive into tools ("we'd use Kafka Connect and …") before naming the category.
2. Minute 2 addresses the consistency axis before the interviewer asks. This preempts the common trap where you commit to CockroachDB, then admit you don't know what isolation level it defaults to. Naming "serializable" up front is senior signal.
3. Minute 3 is the migration-pain probe. Every senior migration to CockroachDB has been bitten by the same five gaps — triggers, stored procedures, LISTEN/NOTIFY, SEQUENCE, custom types. Naming them unprompted shows you've done the schema-diff before.
4. Minute 4 is the changefeed pitch. "Native changefeed with resolved timestamps" is the exactly-once story; comparing it to "Debezium + replication slots" is the operational win. Say both unprompted.
5. Minute 5 covers survival and locality — the reason a senior DE picks CockroachDB over sharded Postgres. REGIONAL BY ROW and SURVIVE REGION FAILURE are the two DDL primitives that make multi-region OLTP feel like single-region OLTP from the app's perspective.

Output.

Grading criterion	Weak score	Senior score
Names category in minute 1	rare	mandatory
Names serializable isolation	rare	required
Names PG-compat gaps unprompted	rare	senior signal
Names native changefeed	occasional	senior signal
Names REGIONAL BY ROW + survival	rare	senior signal

Rule of thumb. The senior CockroachDB answer is a 5-minute monologue that covers consistency, PG compat, changefeed, and survival without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — when CockroachDB wins vs when it loses

Detailed explanation. Every senior architect must also name the workloads where CockroachDB is the wrong answer. Nothing kills senior credibility faster than pitching one database as the answer to every question. Walk through three canonical wins and three canonical losses.

• Win 1. Multi-region OLTP with row-level home regions and sub-10 ms local commit p50.
• Win 2. Reference data (product catalogue, feature flags) that must be readable at low latency from every region.
• Win 3. OLTP + a low-latency lakehouse feed via native changefeeds, no Debezium.
• Lose 1. Single-node workloads under 5k TPS — Postgres is cheaper, faster, and simpler.
• Lose 2. Real-time HTAP (OLTP + interactive analytics on the same data) — TiDB's TiFlash beats CockroachDB here.
• Lose 3. Apps heavily dependent on triggers, PL/pgSQL, or LISTEN/NOTIFY — migrate to YugabyteDB or stay on Postgres.

Question. Assess CockroachDB fit for six candidate workloads.

Input.

Workload	Local latency	Multi-region?	HTAP needed?	Triggers used?	CockroachDB fit
Global SaaS tenants	sub-10 ms	yes	no	no	strong win
Reference-data lookups	sub-5 ms	yes	no	no	strong win
OLTP + Iceberg lakehouse feed	sub-second CDC	maybe	no	no	strong win
Small single-region API	any	no	no	no	wrong tool
Real-time analytics on OLTP	ms scan	maybe	yes	no	pick TiDB
Legacy Rails with triggers	any	maybe	no	yes	pick YugabyteDB

Code.

# Decision-tree helper (illustrative)
def pick_distributed_sql(multi_region: bool,
                          needs_htap: bool,
                          heavy_triggers: bool,
                          workload_tps: int) -> str:
    """Return the recommended distributed-SQL primary for a workload."""
    if workload_tps < 5000 and not multi_region:
        return "Postgres (single-region; distributed SQL is overkill)"
    if heavy_triggers or "pl/pgsql" in ""  # placeholder
        # deep PG compat needed
        return "YugabyteDB"
    if needs_htap:
        return "TiDB"
    if multi_region:
        return "CockroachDB"
    return "Aurora (single-region managed PG)"


# Walk the six candidates
print(pick_distributed_sql(True,  False, False, 20000))
# → CockroachDB
print(pick_distributed_sql(False, False, False, 500))
# → Postgres
print(pick_distributed_sql(True,  True,  False, 30000))
# → TiDB
print(pick_distributed_sql(False, False, True,  10000))
# → YugabyteDB

Step-by-step explanation.

1. Workload 1 (global SaaS tenants) is the canonical CockroachDB win — REGIONAL BY ROW was designed for exactly this pattern. Every tenant's rows live in their home region; every commit is a local Raft round-trip.
2. Workload 2 (reference data) is a CockroachDB win via LOCALITY GLOBAL — the table is replicated to every region and reads are served from the nearest replica. Writes pay a cross-region round-trip; reads are local. Perfect for slow-changing catalogues.
3. Workload 3 (OLTP + lakehouse) is a CockroachDB win via native changefeeds — no Debezium, no replication slot management, native resolved-timestamp exactly-once ingest.
4. Workload 4 (small single-region API) is where CockroachDB loses. Below ~5k TPS in a single region, Postgres is cheaper (one node vs three), faster per-commit (single fsync vs Raft round-trip), and simpler (no distributed system to reason about).
5. Workload 5 (HTAP) is where TiDB wins. TiFlash columnar replicas fed from the same Raft groups let you run interactive analytics on operational data without ETL. CockroachDB has no equivalent.
6. Workload 6 (Rails + triggers) is where YugabyteDB wins. YB reuses the Postgres query layer verbatim, so PL/pgSQL and triggers work. On CockroachDB you'd have to port them to application code first.

Output.

Workload	Recommended	Runner-up
Global SaaS tenants (multi-region OLTP)	CockroachDB	YugabyteDB
Reference-data lookups (multi-region)	CockroachDB (LOCALITY GLOBAL)	Postgres + read replicas
OLTP + Iceberg feed	CockroachDB (native changefeed)	Postgres + Debezium
Small single-region API	Postgres	Aurora
Real-time HTAP	TiDB	Snowflake
Legacy Rails with triggers	YugabyteDB	stay on Postgres

Rule of thumb. CockroachDB wins when you need multi-region + serializable + native changefeed. It loses when you're single-region, HTAP-heavy, or trigger-heavy. Naming both categories unprompted is the difference between a fluent senior answer and a marketing recitation.

Senior interview question on CockroachDB fit

A senior interviewer often opens with: "You've been asked to replace a single-region AWS Aurora Postgres primary that serves a global SaaS. The product wants EU and APAC customers to feel local (sub-20 ms writes), the business wants to survive a region outage without operator intervention, and the data platform wants a low-latency lakehouse feed without adding Debezium. Walk me through the migration plan — the schema changes, the survival goal, the changefeed setup, and the rollback plan."

Solution Using CockroachDB multi-region + REGIONAL BY ROW + native changefeed to Kafka

-- Step 1 — create the multi-region database with the survival goal
CREATE DATABASE saas
    PRIMARY REGION 'us-east1'
    REGIONS 'us-east1', 'europe-west1', 'asia-south1'
    SURVIVE REGION FAILURE;

USE saas;

-- Step 2 — migrate the tenants table with a home-region column
CREATE TABLE tenants (
    id           UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
    region       crdb_internal_region NOT NULL,
    name         STRING               NOT NULL,
    plan_tier    STRING               NOT NULL,
    created_at   TIMESTAMPTZ          NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ          NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

-- Step 3 — global reference data (product catalogue) — replicated to every region
CREATE TABLE plans (
    tier          STRING PRIMARY KEY,
    monthly_cents INT8   NOT NULL,
    seat_limit    INT4   NOT NULL
) LOCALITY GLOBAL;

-- Step 4 — enable rangefeeds so changefeeds can subscribe
SET CLUSTER SETTING kv.rangefeed.enabled = true;

-- Step 5 — native changefeed to Kafka with resolved timestamps
CREATE CHANGEFEED FOR TABLE tenants
INTO 'kafka://kafka-broker.internal:9092?topic_prefix=prod.'
WITH  format                 = 'avro',
      confluent_schema_registry = 'http://schema-registry:8081',
      resolved                 = '5s',           -- resolved-ts checkpoint every 5s
      updated,                                     -- include cluster-wide MVCC timestamp
      diff,                                        -- include before-image on UPDATE/DELETE
      min_checkpoint_frequency = '5s',
      initial_scan             = 'yes';           -- bootstrap the full table once

# Step 6 — Iceberg sink config (Kafka Connect worker)
# Consumes prod.tenants topic; uses resolved timestamps as watermarks.
name: iceberg-tenants-sink
config:
  connector.class: org.apache.iceberg.connect.IcebergSinkConnector
  topics: prod.tenants
  iceberg.catalog.type: rest
  iceberg.catalog.uri: http://iceberg-catalog:8181
  iceberg.tables: raw.tenants
  iceberg.tables.evolve-schema-enabled: true
  # Resolved-ts watermarks drive exactly-once commit boundaries
  iceberg.tables.commit-interval-ms: 30000

Step-by-step trace.

Step	Before (single-region Aurora)	After (multi-region CockroachDB)
Regions served	1 (us-east1)	3 (us-east1, europe-west1, asia-south1)
EU write latency p50	~90 ms	~5 ms (local Raft quorum)
APAC write latency p50	~180 ms	~7 ms (local Raft quorum)
Isolation	READ COMMITTED	SERIALIZABLE
Regional outage story	manual DR promotion; hours	SURVIVE REGION FAILURE; zero-op
Lakehouse feed	Debezium + PG logical replication	native CREATE CHANGEFEED
Ordering guarantee	LSN	resolved-ts per changefeed
Sink dedupe	by LSN	by resolved-ts + key

After the migration, EU and APAC customers see local write latencies (5–7 ms p50), the cluster survives a full region outage without operator intervention, and the tenants changefeed lands in Iceberg via Kafka Connect with resolved-timestamp-driven exactly-once commit boundaries. The Aurora Debezium stack is decommissioned.

Output:

Metric	Before (Aurora)	After (CockroachDB multi-region)
Local write p50 (EU)	~90 ms	~5 ms
Local write p50 (APAC)	~180 ms	~7 ms
Regional outage recovery	manual DR (hours)	zero-op continue
Isolation	RC	SERIALIZABLE
CDC pipeline	Debezium + PG slots	native changefeed
Lakehouse freshness	30–60 s	~5 s (resolved-ts cadence)

Why this works — concept by concept:

• Distributed SQL primary — CockroachDB stores data as 512MB ranges replicated by Raft; the leaseholder replica per range coordinates strong-consistency reads. This is qualitatively different from Postgres streaming replication — the write commits only after a Raft quorum ack, so all replicas are in sync at commit time.
• REGIONAL BY ROW — the AS region clause pins each row's replicas to the region named in the region column. A user in europe-west1 writing to their tenant row commits with a Raft quorum inside europe-west1 — no cross-continent latency. This is the primitive that makes multi-region OLTP feel local.
• SURVIVE REGION FAILURE — promotes replica count from 3 (survive zone failure) to 5 (survive one full region outage). Two regions can hold up to two replicas each and the third region holds one; if any one region drops, the remaining regions still form a majority and writes continue.
• Native changefeed with resolved timestamps — CREATE CHANGEFEED ... WITH resolved = '5s' emits per-key ordered events and periodic resolved-ts messages ("no more events with commit timestamp ≤ X will ever appear"). The Iceberg sink uses resolved-ts as a watermark to detect completeness; combined with per-key upserts, at-least-once Kafka delivery becomes exactly-once Iceberg ingest.
• Cost — 5× replica storage (SURVIVE REGION FAILURE) vs 1× Aurora replica, ~3× write CPU (Raft coordination), one Kafka cluster, one Iceberg REST catalogue. The eliminated cost is the Debezium footprint (a JVM per source), the replication-slot on-call rotation, and the manual DR runbook. Net O(1) per commit locally; O(N regions) at rollout.

SQL
Topic — sql
SQL distributed-database and multi-region problems

Practice →

Design Topic — design Design problems on distributed SQL primaries

Practice →

---

2. Distributed architecture — ranges, Raft, leaseholders

512MB ranges replicated by Raft with a leaseholder per range — the load-bearing primitives that make distributed sql behave like one big table

The mental model in one line: CockroachDB stores every table's data as an ordered sequence of 512MB ranges keyed by primary key; each range is replicated (by default 3 copies, 5 under SURVIVE REGION FAILURE) via the Raft consensus protocol across nodes in different failure domains; one replica per range holds the lease and serves consistent reads plus coordinates writes; and the distributed SQL execution engine (DistSQL) plans queries as a DAG of range-scoped operations that ship computation to the nodes holding the data — every architectural conversation about CockroachDB starts with this triple (range, Raft, leaseholder) because every operational behaviour (hot-spot rebalance, follower reads, survival goals) derives from it. Miss this triple and you cannot reason about a single CockroachDB question.

The three primitives, one at a time.

• Range. A contiguous key-space slice of ~512 MB. Every table is split into ranges by primary key; when a range exceeds ~512 MB it splits automatically. Ranges are the unit of replication and the unit of rebalancing — the allocator moves ranges (not individual rows) to balance load.
• Raft. The consensus protocol per range. Each range has 3 (or 5) replicas; every write must be acknowledged by a majority (2 of 3, or 3 of 5) before commit. This is what "distributed SQL" pays to get durable, replicated writes without a single point of failure — one Raft round-trip per commit.
• Leaseholder. One replica per range holds the range lease at any given time. The leaseholder is the only replica that can serve consistent reads without a Raft round-trip (it knows it holds the lease; no one else can hold it), and it's the coordinator that proposes writes to the Raft group. Leases can move (leaseholder rebalance) to keep reads close to their clients.

MVCC + timestamp cache — how CockroachDB stays serializable without global locks.

• MVCC. Every write is a new versioned key-value pair with a commit timestamp; reads see the highest committed version below the transaction's read timestamp. Old versions are garbage-collected after the GC TTL (default 25 hours).
• Timestamp cache. A per-node cache of the highest read timestamp per key. If a write arrives with a lower timestamp than a previous read, the write is bumped to a higher timestamp — this preserves serializability without long-held read locks.
• Transaction record. Each transaction has a record in the meta ranges; commit flips the record to COMMITTED and all provisional writes become visible atomically. Aborted transactions leave provisional writes that are cleaned up lazily.
• Retries. Under contention, transactions may retry (client-side or server-side). The Postgres wire returns a 40001 serialization_failure — the driver must retry. This is the operational tax of serializable isolation.

Locality-aware placement — the primitive that makes multi-region cheap.

• Locality tiers. Every node is started with a --locality=region=us-east1,zone=us-east1-a flag. This gives the allocator a topology it can honour when placing replicas.
• Zone configs. ALTER TABLE ... CONFIGURE ZONE USING num_replicas = 5, constraints = '[+region=us-east1]' explicitly pins replicas. Multi-region DDL (REGIONAL BY ROW, LOCALITY GLOBAL, etc.) generates these zone configs under the hood.
• Rebalance. The allocator continuously moves ranges to satisfy zone constraints, balance disk usage, and reduce hot spots. This is why a CockroachDB cluster self-heals when you add or remove a node.

DistSQL — how queries fan out.

• Plan. The optimizer builds a physical plan; DistSQL splits it into per-range operations.
• Ship. Range-scoped scans (INDEX SCAN, TABLE SCAN) are shipped to the leaseholder node. Filters and projections run inline. Aggregations run distributed with a final merge.
• Result. The gateway node collects results and returns them to the client. Users see a single SQL response; internally, N nodes cooperated.

Common interview probes on architecture.

• "What is a range?" — required answer: 512 MB contiguous key-space slice; unit of replication and rebalancing.
• "How is durability guaranteed?" — required answer: Raft majority ack per commit.
• "What is a leaseholder?" — required answer: replica that coordinates writes and serves consistent reads for a range.
• "How does CockroachDB stay serializable without global locks?" — MVCC + timestamp cache + transaction records.
• "What does --locality do?" — tells the allocator the node's failure-domain tags so replica placement honours the topology.

Worked example — creating a table with a leaseholder preference

Detailed explanation. A products reference table is served from a US-primary cluster with EU and APAC read replicas. Reads dominate; writes are rare. Pinning the leaseholder to us-east1 centralises write coordination, while EU and APAC pods use follower reads (bounded stale) for lookups. Walk through the DDL that expresses this intent without giving up serializability.

• Table. products (sku PK, name, price_cents, category, updated_at).
• Placement. Replicas in us-east1, europe-west1, asia-south1.
• Leaseholder. Pin to us-east1 — writes always land there.
• Reads. EU and APAC apps read via follower reads (AS OF SYSTEM TIME follower_read_timestamp()).

Question. Write the DDL, the zone config, and the follower-read query pattern.

Input.

Component	Value
Table	products
Replicas	5 (SURVIVE REGION FAILURE)
Leaseholder region	us-east1
Follower-read staleness	~4.8 s (default follower_read_timestamp())
App locations	us-east1 (writes), europe-west1 (reads), asia-south1 (reads)

Code.

-- 1. Create the products table under the multi-region database
CREATE TABLE products (
    sku          STRING     PRIMARY KEY,
    name         STRING     NOT NULL,
    price_cents  INT8       NOT NULL,
    category     STRING     NOT NULL,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY TABLE IN 'us-east1';

-- 2. The REGIONAL BY TABLE IN 'us-east1' clause emits a zone config
--    that pins the leaseholder to us-east1 while still holding replicas
--    in europe-west1 and asia-south1 for survival.
SHOW ZONE CONFIGURATION FROM TABLE products;

-- 3. Read pattern from EU / APAC pods — follower reads via AOST
SELECT sku, name, price_cents
FROM   products AS OF SYSTEM TIME follower_read_timestamp()
WHERE  category = 'shoes';

-- 4. Write pattern from us-east1 pod — leaseholder is local; ~5 ms commit
INSERT INTO products (sku, name, price_cents, category)
VALUES ('SHOE-042', 'runner-x', 8900, 'shoes');

Step-by-step explanation.

1. LOCALITY REGIONAL BY TABLE IN 'us-east1' is the whole-table version of REGIONAL BY ROW — every row has the same home region. The clause emits a zone config that pins the leaseholder to us-east1 while keeping replicas in the other regions for survival.
2. SHOW ZONE CONFIGURATION FROM TABLE products reveals the generated zone config — you'll see lease_preferences = '[[+region=us-east1]]' and num_replicas = 5 under SURVIVE REGION FAILURE. This is the "audit trail" for what the DDL produced.
3. The EU/APAC read pattern uses AS OF SYSTEM TIME follower_read_timestamp(). This is a bounded-stale read (default ~4.8 seconds behind live), served from the nearest replica — no leaseholder round-trip. Latency drops from ~90 ms (round-trip to us-east1 leaseholder) to ~1 ms (local replica).
4. Writes from the us-east1 pod hit the local leaseholder, propose to the Raft group, and commit after majority ack. All replicas (including EU/APAC) apply the write, so subsequent follower reads within the same region see the new value ~5 s later.
5. This DDL is the reason CockroachDB wins for global reference data. You get local reads everywhere, serializable writes on demand, no application-level cache-invalidation logic, and one DDL surface — the zone config is generated for you.

Output.

Component	Configuration	Rationale
Replica count	5	SURVIVE REGION FAILURE quorum
Leaseholder region	us-east1	writes centralised; predictable coordination
Follower-read staleness	~4.8 s	bounded-stale; sub-ms local reads
Read pattern	AOST follower_read_timestamp()	one clause, no app changes
Write pattern	plain INSERT	leaseholder-local; ~5 ms commit

Rule of thumb. For any read-heavy multi-region reference table, use REGIONAL BY TABLE IN 'primary-region' + follower reads in remote regions. This is the cheapest multi-region pattern in the DDL vocabulary; it does not need REGIONAL BY ROW.

Worked example — tracing a SELECT under range hopping

Detailed explanation. A senior interviewer often asks "walk me through what happens when I run SELECT * FROM orders WHERE id = 42 on a three-node cluster." The answer must cover DistSQL planning, range lookup, leaseholder routing, and the network hops. Walk through the trace end-to-end.

• Query. SELECT * FROM orders WHERE id = 42 — a point lookup on the primary key.
• Cluster. Three nodes: n1 (us-east1), n2 (europe-west1), n3 (asia-south1).
• Range. The row with id = 42 lives in range r7, whose leaseholder is on n1.
• Gateway. The app connects to n2 (the pod's closest node).

Question. Trace every step from the app's SQL to the returned row.

Input.

Step	Actor	Latency contribution
Parse + plan	n2 (gateway)	~1 ms
Range lookup (meta)	n2 → n1	~90 ms (cross-region)
Read via leaseholder	n1	~1 ms
Return result	n1 → n2 → app	~90 ms
Total		~180 ms

Code.

-- Trace the query with EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 42;

-- Sample output:
-- distribution: local
-- vectorized:   true
-- planning time: 0.5ms
-- execution time: 90.2ms
-- • scan
--   table: orders@primary
--   spans: [/42 - /42]
--   estimated row count: 1 (0.00% of the table; stats collected 10s ago)

-- Deeper trace with SHOW TRACE FOR SESSION
SET tracing = on;
SELECT * FROM orders WHERE id = 42;
SET tracing = off;

SELECT tag, message
FROM   [SHOW TRACE FOR SESSION]
WHERE  message LIKE '%range%' OR message LIKE '%lease%'
ORDER  BY timestamp;

Step-by-step explanation.

1. The app connects to n2 (the gateway). n2 parses and plans the query, recognises it as a point lookup on the primary index, and prepares a Scan on range r7.
2. n2 looks up which node holds the range lease for r7. This lookup uses the meta ranges (r1 and r2); if n2's cache is stale, it may need a network hop to resolve. Assume cache is warm; lookup is local.
3. n2 sends the scan request to n1 (the leaseholder for r7). This is a cross-region hop from europe-west1 to us-east1 — the biggest latency cost in the trace.
4. n1 reads the row from its local storage (Pebble/RocksDB) under the timestamp cache and MVCC rules, returns the row to n2.
5. n2 returns the row to the app. Total: ~180 ms, dominated by two cross-region hops. Fix: either move the app to us-east1 (~2 ms total), or use REGIONAL BY ROW to place the row's leaseholder in europe-west1 (~5 ms total), or use AS OF SYSTEM TIME follower_read_timestamp() for bounded-stale local reads (~1 ms total).

Output.

Component	Latency	Notes
Parse + plan	~1 ms	local on n2
Range meta lookup	~0 (cached)	may add 5–10 ms if cold
Cross-region hop (n2 → n1)	~90 ms	dominant cost
Leaseholder read	~1 ms	local Pebble read
Cross-region hop (n1 → n2)	~90 ms	dominant cost
Total	~180 ms	fixable by locality or AOST

Rule of thumb. Every strong-consistency read in a multi-region CockroachDB cluster pays for one round-trip to the leaseholder. If your app cannot tolerate that, either pin the leaseholder to the app's region (REGIONAL BY TABLE IN 'region') or use follower reads (AS OF SYSTEM TIME follower_read_timestamp()) for bounded staleness.

Senior interview question on distributed architecture

A senior interviewer might ask: "Design a CockroachDB deployment for a 20k-TPS multi-tenant SaaS with tenants in three regions. Include the range strategy, the survival goal, the leaseholder placement, and the monitoring for range hot spots. Then explain what happens if one region drops and the app keeps writing."

Solution Using REGIONAL BY ROW + SURVIVE REGION FAILURE + hot-range monitoring

-- 1. Multi-region database + survival goal
CREATE DATABASE saas
    PRIMARY REGION 'us-east1'
    REGIONS 'us-east1', 'europe-west1', 'asia-south1'
    SURVIVE REGION FAILURE;
USE saas;

-- 2. Tenants table with row-level home region
CREATE TABLE tenants (
    id         UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
    region     crdb_internal_region NOT NULL,
    name       STRING               NOT NULL,
    created_at TIMESTAMPTZ          NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

-- 3. Users table — same locality as their tenant
CREATE TABLE users (
    id           UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    UUID                 NOT NULL REFERENCES tenants(id),
    region       crdb_internal_region NOT NULL,
    email        STRING               NOT NULL,
    created_at   TIMESTAMPTZ          NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

CREATE INDEX idx_users_tenant ON users (tenant_id);

-- 4. Hot-range monitoring — top-10 hottest ranges by QPS
SELECT range_id,
       leaseholder,
       start_pretty,
       end_pretty,
       queries_per_second::INT AS qps,
       writes_per_second::INT  AS wps
FROM   crdb_internal.ranges
JOIN   crdb_internal.node_liveness ON node_liveness.node_id = ranges.leaseholder
WHERE  database_name = 'saas'
ORDER  BY qps DESC
LIMIT  10;

-- 5. Manual split hint if a range is hot (rare; the allocator usually handles this)
ALTER TABLE users SPLIT AT VALUES ('<uuid-boundary>');

# 6. Prometheus scrape config for CockroachDB _status/vars endpoint
scrape_configs:
  - job_name: cockroachdb
    metrics_path: /_status/vars
    static_configs:
      - targets: ['crdb-n1:8080', 'crdb-n2:8080', 'crdb-n3:8080']
    # Key metrics:
    # sql_exec_latency (histogram)
    # ranges_underreplicated (gauge; alert on > 0 for 5m)
    # replicas_leaseholders (gauge; per-node lease count)
    # changefeed_emitted_messages (counter)

Step-by-step trace.

Layer	Config	Result
Database	SURVIVE REGION FAILURE	5 replicas per range across 3 regions
Tenants + users	REGIONAL BY ROW AS region	rows placed near their home region
Local write path	tenant + user in same region	~5 ms Raft quorum inside region
Hot-range detection	crdb_internal.ranges QPS ranking	on-call has a query, not a page
Regional outage (eu-west)	5 replicas → 3 remaining form quorum	writes continue in eu-west customers via other regions' replicas
Monitoring	Prometheus scrape /_status/vars	ranges_underreplicated + latency histograms

After deployment, EU tenants commit at ~5 ms locally; APAC tenants commit at ~7 ms locally; US tenants commit at ~4 ms locally. Range hot spots are detected via a scheduled Prometheus alert on the top-QPS range; the allocator auto-rebalances by splitting the hot range or moving its leaseholder. A full europe-west1 outage removes 2 of 5 replicas from every range; the remaining 3 replicas (in us-east1 and asia-south1) form a majority and writes continue for EU tenants (via cross-region Raft quorum, ~85 ms until eu-west returns). No manual DR.

Output:

Metric	Value
Local write p50 (EU / APAC / US)	~5–7 ms
Replica count per range	5
Regions tolerated for outage	1 (SURVIVE REGION FAILURE)
Hot-range detection	crdb_internal.ranges QPS ranking
Monitoring endpoint	/_status/vars (Prometheus scrape)
Regional-outage recovery	zero-op, continues serving

Why this works — concept by concept:

• Range as unit of replication — the allocator's job is much simpler when it moves 512MB slices rather than individual rows. Rebalancing is a background operation with no application impact; hot ranges are split automatically when they exceed the size threshold.
• Raft consensus per range — every write needs a majority ack from the range's replicas. With 5 replicas across 3 regions, losing one region still leaves 3 replicas (a majority of 5), so writes continue. This is what makes SURVIVE REGION FAILURE actually work.
• Leaseholder for reads — the leaseholder is the only replica that can serve strong-consistency reads without a Raft round-trip. REGIONAL BY ROW places the leaseholder in the row's home region, so local reads are ~5 ms; follower reads (bounded-stale) are ~1 ms.
• Locality-aware allocator — the --locality=region=X,zone=Y flag on every node tells the allocator the topology. Zone configs (generated by multi-region DDL) express replica placement constraints. Without locality flags, the allocator would spread replicas naively and blow the multi-region latency budget.
• Cost — 5× storage for SURVIVE REGION FAILURE (vs 1× for single-region Postgres), one Prometheus scrape per node, no manual DR runbook. Compared to a manually-sharded multi-region Postgres deployment with cross-region Bucardo replication, this is dramatically simpler and dramatically more reliable. O(1) per commit locally; O(N regions) at rollout.

Design
Topic — design
Design problems on Raft, ranges, and leaseholders

Practice →

SQL Topic — sql SQL distributed-transaction and MVCC problems

Practice →

---

3. Postgres compatibility + SQL layer

Same wire, same drivers, most SQL — the compat depth that lets a Django app drop in without re-tooling

The mental model in one line: CockroachDB implements the PostgreSQL wire protocol on port 26257 (default) and exposes most of the pg_catalog surface, so any PG driver (psycopg2, SQLAlchemy, Prisma, Hibernate, ActiveRecord, JDBC, node-postgres) connects unchanged; most PG SQL syntax works verbatim (JSONB, arrays, GIN indexes, CTEs, window functions, foreign keys, prepared statements, ON CONFLICT); and a handful of PG features — triggers, stored procedures / PL/pgSQL, LISTEN/NOTIFY, SEQUENCE semantics, custom types, some pg_catalog internals — are either unsupported or behave differently, forcing the migration team to identify those gaps up front and either port them to application code or reach for YugabyteDB instead. Migrating a well-written Django app is often a config change; migrating a legacy Rails app with heavy PL/pgSQL is a project.

What works verbatim.

• Wire + drivers. Connect with postgresql://user:pass@crdb:26257/dbname?sslmode=verify-full. Any PG-compatible driver just works. The default port is 26257 (not 5432) — trap #1 for migrations.
• Data types. JSONB, arrays (INT8[], STRING[]), UUID, INTERVAL, INET, TIMESTAMPTZ, NUMERIC. Full expression support inside JSONB.
• SQL features. CTEs (recursive too), window functions, ON CONFLICT ... DO UPDATE, RETURNING, subqueries, lateral joins, foreign keys (with ON DELETE CASCADE), check constraints, prepared statements, transactions.
• Indexes. B-tree (default), INVERTED (for JSONB and arrays; the CRDB equivalent of GIN), hash-sharded (for anti-hot-spot on monotonic keys), partial indexes (WHERE), covering indexes (STORING).
• Schema. Schemas, roles, GRANT/REVOKE, row-level security-like patterns via views, database-level RBAC.

What is different or unsupported.

• Triggers. No CREATE TRIGGER. Migration path: move the logic to application code, or use a changefeed for after-commit reactions.
• Stored procedures / PL/pgSQL. No PL/pgSQL. User-defined functions (UDFs) support SQL bodies only. Migration path: application code.
• LISTEN/NOTIFY. Not supported. Migration path: use changefeeds, or an external pub/sub (Kafka, NATS).
• SEQUENCE. Supported but rarely optimal — sequences serialise across the cluster (single global counter → hot range). Prefer UUID (gen_random_uuid()) or unique_rowid() for auto-generated keys.
• pg_catalog internals. Most exposed; some vendor-specific views/functions missing (e.g. pg_stat_activity has a different shape). Migration path: use crdb_internal equivalents.

The migration playbook — what senior teams actually do.

• Step 1 — schema diff. Run pg_dump --schema-only, load into a scratch CockroachDB, look at the errors. Every error is a compat gap you must resolve.
• Step 2 — port PL/pgSQL to app code. Every trigger, every stored procedure, every rule. Even if the port is verbose, this is the reliable path.
• Step 3 — swap SEQUENCE for UUID. Especially for high-write tables. Sequences serialise the range holding the counter into a hot range that no amount of sharding can save.
• Step 4 — dual-write shadow test. Route a fraction of production writes to both PG and CockroachDB; diff the results daily; catch behaviour differences before cutover.
• Step 5 — cutover. Point the app at CockroachDB; roll back to PG if p99 latency or error rate breaches SLO. Keep the shadow running for 30 days for regression detection.

Common interview probes on Postgres compatibility.

• "Is CockroachDB a drop-in for Postgres?" — required answer: for most apps yes, with five known gaps (triggers, PL/pgSQL, LISTEN/NOTIFY, SEQUENCE, custom types).
• "Why not use SEQUENCE?" — required answer: single global counter → hot range.
• "How do you migrate triggers?" — required answer: port to app code, or replace with changefeed for after-commit fan-out.
• "Which port?" — required answer: 26257 by default (not 5432).
• "How is LISTEN/NOTIFY replaced?" — changefeeds + Kafka/NATS, or app-level polling.

Worked example — dumping Postgres → CockroachDB and resolving the DDL diff

Detailed explanation. The canonical first day of a migration: dump the Postgres schema, load it into CockroachDB, watch what breaks, patch the gaps. Walk through a realistic dump for a Django app with a mix of Django models and one hand-rolled trigger.

• Source. Postgres 15 production database; 42 tables.
• Target. CockroachDB 24 cluster.
• Known gaps. One bump_updated_at() PL/pgSQL trigger; two SEQUENCE-backed PKs; one LISTEN/NOTIFY channel for websocket push.

Question. Dump the schema, load it into CockroachDB, list the errors, and provide the patched schema.

Input.

Object	Postgres form	CockroachDB form
PK on high-write table	id SERIAL PRIMARY KEY	id UUID PRIMARY KEY DEFAULT gen_random_uuid()
updated_at maintenance	BEFORE UPDATE trigger	application-side updated_at = now()
WebSocket notify	LISTEN/NOTIFY	changefeed → Kafka → websocket relay
Ordinary tables	CREATE TABLE ...	works verbatim

Code.

# 1. Dump the Postgres schema
pg_dump --schema-only --no-owner --no-privileges \
        --host pg-primary --user migrator production \
        > pg_schema.sql

# 2. Load into CockroachDB and see what breaks
cockroach sql --url 'postgresql://user@crdb:26257/production?sslmode=verify-full' \
              -f pg_schema.sql 2> load_errors.txt

# 3. Inspect the errors
head -30 load_errors.txt
# → ERROR: unimplemented: PL/pgSQL not supported
# → ERROR: unimplemented: LISTEN/NOTIFY not supported
# → NOTICE: SEQUENCE created but consider UUID for high-write tables

-- 4. Patched schema — Django models converted to CockroachDB-idiomatic form
CREATE TABLE public.orders (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID        NOT NULL,
    total_cents   INT8        NOT NULL,
    status        STRING      NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer  ON public.orders (customer_id);
CREATE INDEX idx_orders_updated   ON public.orders (updated_at);

-- Trigger port — the Django model now maintains updated_at
-- in the app's pre-save hook rather than a DB trigger:
--
--   class Order(models.Model):
--       def save(self, *args, **kwargs):
--           self.updated_at = timezone.now()
--           super().save(*args, **kwargs)

-- LISTEN/NOTIFY port — the WebSocket relay now consumes a changefeed
CREATE CHANGEFEED FOR TABLE public.orders
INTO 'kafka://kafka:9092?topic_prefix=ws.'
WITH format = 'json', resolved = '1s';

# 5. Django settings.py — swap the DB backend and connection URL
DATABASES = {
    "default": {
        "ENGINE":   "django_cockroachdb",   # CockroachDB Django adapter
        "NAME":     "production",
        "HOST":     "crdb.internal",
        "PORT":     "26257",                # trap #1: not 5432
        "USER":     "app",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "OPTIONS":  {"sslmode": "verify-full"},
    }
}

Step-by-step explanation.

1. pg_dump --schema-only gives you the DDL without any data. Loading it into a fresh CockroachDB catches every compat gap in one pass — much faster than discovering them one at a time in dev.
2. The three errors above map to the three known gaps: PL/pgSQL (trigger port), LISTEN/NOTIFY (changefeed port), and SEQUENCE (UUID port). Any well-run Postgres schema will show these three plus maybe custom types.
3. The trigger port moves bump_updated_at from a BEFORE UPDATE trigger to a Django save() override. The behaviour is identical from the app's perspective; the enforcement moves from DB to code. The trade-off: any raw SQL update path in another codebase (analytics scripts, admin) must also set updated_at.
4. The LISTEN/NOTIFY port replaces DB-native push with a native changefeed piped to Kafka. The websocket relay subscribes to Kafka instead of Postgres. This is actually an upgrade — the changefeed persists, replays, and scales; LISTEN/NOTIFY drops messages on client disconnect.
5. The Django settings swap uses the django_cockroachdb backend (a thin subclass of django.db.backends.postgresql with CockroachDB-specific type mapping and transaction-retry logic for 40001 errors). Port 26257 is the CockroachDB default. sslmode=verify-full is mandatory in production.

Output.

Migration step	Before (Postgres)	After (CockroachDB)
PK type on orders	SERIAL (SEQUENCE)	UUID
updated_at maintenance	PL/pgSQL trigger	Django save() override
WebSocket push	LISTEN/NOTIFY	changefeed → Kafka → relay
DB port	5432	26257
Django engine	django.db.backends.postgresql	django_cockroachdb
ORM code changes	none	none

Rule of thumb. For any Django or Rails migration to CockroachDB, plan for four ports: (a) SERIAL → UUID on high-write tables, (b) triggers → application code, (c) LISTEN/NOTIFY → changefeed, (d) any PL/pgSQL stored procs → application code. The rest is a config change.

Worked example — 40001 serialization_failure retry loop

Detailed explanation. CockroachDB's serializable isolation means that under contention, some transactions abort with SQLSTATE 40001 (serialization_failure). The driver is expected to retry — this is a normal operational event, not an error. Every senior migration adds a retry helper to the ORM or DB abstraction. Walk through the pattern.

• The error. SQLSTATE 40001 restart transaction: TransactionRetryWithProtoRefreshError.
• The correct response. Retry the entire transaction with a small backoff; typically 3–5 attempts.
• The wrong response. Retry a single statement, or ignore the error. Both violate serializability.

Question. Write a retry decorator that wraps a transactional function and retries on 40001 with exponential backoff.

Input.

Parameter	Value
Max attempts	5
Backoff base	50 ms
Backoff cap	500 ms
Retry-eligible errors	SQLSTATE 40001
Non-retry errors	everything else (raise)

Code.

# CockroachDB retry helper — wraps a transactional function
import random
import time
import psycopg2
from functools import wraps

MAX_ATTEMPTS   = 5
BACKOFF_BASE   = 0.05   # 50 ms
BACKOFF_CAP    = 0.5    # 500 ms

def with_cockroach_retry(fn):
    @wraps(fn)
    def wrapped(conn, *args, **kwargs):
        for attempt in range(1, MAX_ATTEMPTS + 1):
            try:
                with conn:                         # BEGIN/COMMIT; rollback on exception
                    return fn(conn, *args, **kwargs)
            except psycopg2.errors.SerializationFailure:
                if attempt == MAX_ATTEMPTS:
                    raise
                sleep = min(BACKOFF_CAP,
                            BACKOFF_BASE * 2 ** (attempt - 1))
                # add jitter to avoid thundering herd
                sleep += random.uniform(0, sleep / 2)
                time.sleep(sleep)
        raise RuntimeError("unreachable")
    return wrapped


@with_cockroach_retry
def transfer(conn, from_id, to_id, cents):
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (cents, from_id))
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (cents, to_id))
        cur.execute("INSERT INTO transfers(from_id, to_id, cents) VALUES (%s, %s, %s)",
                    (from_id, to_id, cents))

Step-by-step explanation.

1. The decorator wraps a function that takes conn as its first argument. Inside, it opens a with conn: block (implicit BEGIN/COMMIT/ROLLBACK) and calls the wrapped function. On success, it returns; on SerializationFailure, it sleeps and retries.
2. The backoff is exponential (50 ms → 100 ms → 200 ms → 400 ms → 500 ms cap) with a jitter component. Jitter prevents a "thundering herd" where many retrying transactions retry at the same time and re-contend.
3. Only SerializationFailure (SQLSTATE 40001) is retry-eligible. Every other error (unique-constraint violation, syntax error, connection lost) is re-raised. Retrying a unique-violation would silently mask a bug.
4. The transfer function is written normally — no retry logic in the business code. This is the crucial ergonomics win: the retry pattern is centralised in the decorator, and every transactional function is decorated. New engineers don't have to remember to add retries.
5. Under contention (concurrent transfers between the same accounts), the retry pattern kicks in transparently. The application observes slightly higher p99 latency (each retry adds 50–500 ms); it does not observe serialization anomalies.

Output.

Attempt	Backoff sleep	Cumulative wait	Outcome
1	0	0 ms	fails with 40001
2	50 ms + jitter	~60 ms	fails with 40001
3	100 ms + jitter	~170 ms	fails with 40001
4	200 ms + jitter	~400 ms	succeeds
5 (unused)	400 ms + jitter	~800 ms	(would succeed or raise)

Rule of thumb. Every ORM or DB helper in a CockroachDB app must wrap transactional writes with a retry loop on SQLSTATE 40001. Exponential backoff with jitter; 3–5 attempts; retry the entire transaction, not individual statements. Skipping this turns normal contention into user-visible errors.

Worked example — replacing SEQUENCE with UUID for high-write tables

Detailed explanation. The SERIAL / SEQUENCE pattern on Postgres relies on a single global counter. On CockroachDB this maps to a single range that becomes a hot spot for every INSERT. The correct replacement is UUID (gen_random_uuid()), which distributes the primary key across the whole key space. Walk through the migration and quantify the improvement.

• Before. id BIGSERIAL PRIMARY KEY on orders — 1000 INSERT/s → one hot range serving 100% of PK writes.
• After. id UUID PRIMARY KEY DEFAULT gen_random_uuid() — 1000 INSERT/s distributed across ~200 ranges.
• Trade-off. UUIDs are 16 bytes vs 8 bytes; PK indexes are ~2× larger. Range distribution eliminates the hot spot.

Question. Migrate the orders PK from SERIAL to UUID and quantify the write-throughput improvement.

Input.

Metric	Before (SERIAL)	After (UUID)
PK width	8 bytes	16 bytes
Ranges receiving writes	1 (hot)	~200 (distributed)
Steady-state INSERT/s	1,200 (before saturation)	8,000
p99 INSERT latency	~180 ms (contention)	~25 ms

Code.

-- 1. New table with UUID PK
CREATE TABLE public.orders_v2 (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID        NOT NULL,
    total_cents   INT8        NOT NULL,
    status        STRING      NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- carry the old integer key during migration for lookup compat
    legacy_id     INT8        UNIQUE
);

CREATE INDEX idx_orders_v2_customer ON public.orders_v2 (customer_id);
CREATE INDEX idx_orders_v2_legacy   ON public.orders_v2 (legacy_id);

-- 2. Backfill from old table
INSERT INTO public.orders_v2 (id, customer_id, total_cents, status,
                              created_at, updated_at, legacy_id)
SELECT gen_random_uuid(),
       customer_id,
       total_cents,
       status,
       created_at,
       updated_at,
       id
FROM   public.orders;

-- 3. Application dual-writes to both tables for a week (via a wrapper)
-- 4. Cut over reads to orders_v2
-- 5. Drop the old table
DROP TABLE public.orders;
ALTER TABLE public.orders_v2 RENAME TO orders;

# Application — writes now use UUIDs; legacy int IDs kept for URL compat
import uuid

def create_order(conn, customer_id: str, total_cents: int) -> str:
    order_id = str(uuid.uuid4())
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO public.orders (id, customer_id, total_cents, status)
            VALUES (%s, %s, %s, 'pending')
        """, (order_id, customer_id, total_cents))
    return order_id

Step-by-step explanation.

1. The new PK type is UUID with DEFAULT gen_random_uuid(). This generates a random UUID per INSERT; because UUIDs are uniformly distributed, consecutive INSERTs land in different key ranges. The single-hot-range bottleneck disappears.
2. The legacy_id column preserves the old integer PK for URL compat and any external systems (webhooks, analytics feeds) that reference the old key. It's UNIQUE so lookups by legacy ID stay fast.
3. Backfilling with gen_random_uuid() for each row means the migrated data has UUIDs — but you keep the legacy integer in legacy_id for cutover safety. During the dual-write phase, both tables receive every new order.
4. The dual-write period (usually 1–2 weeks) exists to catch discrepancies before cutover. Every night, a diff job compares row counts and field values between orders and orders_v2; any mismatch stops the migration.
5. After cutover, the old orders table is dropped. Throughput jumps from ~1200 INSERT/s to ~8000 INSERT/s at similar CPU because writes are now distributed across ~200 ranges instead of contending on one hot range.

Output.

Metric	SERIAL PK	UUID PK
Steady-state INSERT/s	~1,200	~8,000
p99 INSERT latency	~180 ms	~25 ms
Hot ranges	1 (100% of writes)	~200 (distributed)
PK width	8 bytes	16 bytes
Index overhead	1×	~2×

Rule of thumb. For any table with > 100 INSERT/s on CockroachDB, use UUID (gen_random_uuid()) or unique_rowid() as the PK, not SEQUENCE. The 2× index overhead is trivial compared to the write-throughput win from eliminating the hot-range bottleneck.

Senior interview question on Postgres compatibility

A senior interviewer might ask: "Your team wants to migrate a Rails 7 app from Aurora Postgres to CockroachDB. The schema has 50 tables, three PL/pgSQL functions, two triggers, and five SEQUENCE-backed PKs on high-write tables. Walk me through the migration plan, the per-gap fix, the dual-write shadow test, and the rollback story if p99 latency breaches SLO."

Solution Using pg_dump diff + gap-by-gap ports + dual-write shadow + gated cutover

# 1. Schema diff — dump PG, load into scratch CRDB, list errors
pg_dump --schema-only --no-owner --no-privileges \
        --host pg-primary --user migrator production > pg_schema.sql

cockroach sql --url 'postgresql://migrator@crdb-scratch:26257/production?sslmode=verify-full' \
              -f pg_schema.sql 2> load_errors.txt

# Expected errors:
#   ERROR: unimplemented: PL/pgSQL not supported          (3 functions)
#   ERROR: unimplemented: CREATE TRIGGER                  (2 triggers)
#   NOTICE: SEQUENCE created but hot-range warning         (5 tables)

-- 2. Per-gap fixes

-- (a) PL/pgSQL functions → Rails service objects
-- Before: CREATE FUNCTION calc_shipping_cost(...) LANGUAGE plpgsql AS $$ ... $$;
-- After:  app/services/shipping_cost_calculator.rb

-- (b) BEFORE UPDATE triggers → Rails before_save callbacks
-- Before: CREATE TRIGGER trg_bump_updated ...
-- After:  class Order < ApplicationRecord
--           before_save :bump_updated_at
--           def bump_updated_at; self.updated_at = Time.current; end
--         end

-- (c) SEQUENCE PKs → UUID with legacy_id retention
CREATE TABLE public.orders (
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    legacy_id    INT8         UNIQUE,        -- keep for URL / webhook compat
    customer_id  UUID         NOT NULL,
    total_cents  INT8         NOT NULL,
    status       STRING       NOT NULL,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

# 3. Rails dual-write wrapper — writes go to both PG and CRDB for 2 weeks
module DualWriteOrder
  def self.create!(attrs)
    order = Order.transaction { Order.create!(attrs) }               # CRDB
    LegacyOrder.transaction { LegacyOrder.create!(attrs.merge(id: order.legacy_id)) }  # PG
    order
  end
end

# 4. Retry helper for 40001 serialization_failure
module CockroachRetry
  def self.with_retry(max_attempts: 5)
    attempt = 0
    begin
      attempt += 1
      yield
    rescue ActiveRecord::SerializationFailure => e
      raise if attempt >= max_attempts
      sleep([0.05 * 2 ** (attempt - 1), 0.5].min + rand * 0.05)
      retry
    end
  end
end

Step-by-step trace.

Stage	Duration	Actions	Rollback path
Schema diff	1 day	dump + load + list gaps	discard scratch cluster
Function/trigger port	1 week	port PL/pgSQL to Ruby; add before_save callbacks	keep PG live
PK migration	1 sprint	new UUID tables + backfill + legacy_id retention	drop new tables
Dual-write shadow	2 weeks	wrapper writes to both; nightly diff	disable wrapper
Cutover	1 hour	flip Rails DB URL to CRDB; keep dual-write for 1 day	flip back
Post-cutover monitor	30 days	p99 latency + error rate + shadow diff	flip back if breach

After the migration, the Rails app runs on CockroachDB with all PL/pgSQL and triggers ported to Ruby, UUID PKs on high-write tables, native retry on 40001, and a 30-day shadow watchdog for regression detection. The old Aurora Postgres cluster remains a read-only shadow for the shadow period, then is decommissioned.

Output:

Metric	Postgres (before)	CockroachDB (after)
p99 write latency	~40 ms	~55 ms (accepted trade for multi-region)
INSERT throughput ceiling	~1,200/s (SERIAL hot spot)	~8,000/s (UUID distributed)
Regional failover	manual DR	zero-op (SURVIVE REGION FAILURE)
Triggers	2 (DB-side)	0 (Ruby callbacks)
PL/pgSQL functions	3	0 (Ruby services)
Migration duration	8 weeks	(rollback available for 30 more days)

Why this works — concept by concept:

• pg_dump-driven gap discovery — loading the dumped schema into a scratch CockroachDB catches every unsupported feature in one pass. Every error message maps to a well-defined port path (PL/pgSQL → app code, trigger → callback, SEQUENCE → UUID). No guessing.
• Application-side updated_at + business logic — moving triggers and PL/pgSQL to app code makes the behaviour visible to every developer, testable in the app's test suite, and portable across any DB backend. The trade-off is one line of Ruby per model; the win is clarity.
• UUID PKs for high-write tables — SERIAL / SEQUENCE serialises writes into a single hot range on distributed SQL. UUIDs distribute writes across ~200 ranges; throughput jumps ~6× at the same CPU. The 2× index overhead is trivial.
• Dual-write shadow + nightly diff — the safety net that catches behaviour differences before cutover. Any diff between PG and CRDB for the same input stops the migration; every senior migration has caught at least one difference this way.
• Cost — 8-week migration project, ~2× PK index overhead, one 40001 retry helper, one dual-write shadow window. The eliminated cost is the manual DR runbook, the sharded-Postgres complexity, and the Debezium footprint. Net one-time engineering cost; recurring wins on multi-region + lakehouse feed.

SQL
Topic — sql
SQL migration and Postgres-compat problems

Practice →

Design Topic — design Design problems on database migration

Practice →

---

4. Change Feeds — CDC for the lakehouse

CREATE CHANGEFEED INTO 'kafka://...' — native CDC with resolved timestamps that convert at-least-once delivery into exactly-once lakehouse ingest

The mental model in one line: CockroachDB's CREATE CHANGEFEED is a first-class SQL primitive that subscribes to one or more tables via internal rangefeeds, emits per-key ordered change events (INSERT/UPDATE/DELETE with before and after images when WITH diff is set) into a Kafka topic, webhook endpoint, or cloud-storage bucket, and periodically emits resolved timestamps — messages that assert "no more events with commit timestamp less than or equal to X will ever appear" — which downstream sinks use as watermarks to convert Kafka's at-least-once delivery into exactly-once ingest into Iceberg / Delta / Snowflake — and this is the primitive that lets a CockroachDB deployment feed the lakehouse without Debezium, without a replication slot on-call rotation, and without a JVM connector per source. Every senior lakehouse conversation around CockroachDB starts with the resolved-timestamp guarantee.

The four axes for changefeeds.

• Delivery. At-least-once by default. Resolved timestamps + idempotent sinks convert this to exactly-once at the sink.
• Ordering. Per-key ordered (all events for the same primary key arrive in commit order). Not globally ordered — different keys can interleave freely across partitions.
• Latency. Sub-second in steady state; the changefeed writes to Kafka as soon as it observes a Raft-committed range event. Resolved-ts cadence (default 30s; tunable via resolved = '5s') sets the watermark tick rate.
• Sinks. Kafka (most common), webhook (HTTP POST per event), cloud storage (S3 / GCS / Azure Blob) with newline-delimited JSON, Google Cloud Pub/Sub. Format: JSON, Avro (with Confluent schema registry), Parquet.

Rangefeeds — the internal primitive.

• What. A subscription to a range's Raft log; emits events as they commit. Enabled cluster-wide via SET CLUSTER SETTING kv.rangefeed.enabled = true.
• How it works. Every leaseholder maintains a closed timestamp — the highest commit timestamp for which no in-flight transaction could still commit below. The rangefeed emits events plus resolved timestamps up to the closed timestamp.
• Why it matters. Zero source-table load — rangefeeds tap into the Raft log, they don't scan tables. Compared to timestamp CDC's per-poll table scan, this is a step-change improvement.

Resolved timestamps — the exactly-once primitive.

• The message. Periodically (WITH resolved = '5s'), the changefeed emits a message per partition saying "no more events with commit timestamp ≤ T will appear on this partition."
• The sink's job. Buffer events; when every partition has emitted a resolved-ts ≥ T, commit all events with commit_ts ≤ T atomically to the sink. Events past T stay buffered.
• The result. Even if Kafka redelivers, the sink dedupes by (key, commit_ts). Combined with resolved-ts commit boundaries, this is exactly-once ingest.

Comparison to Debezium against Postgres logical replication.

• Setup complexity. CockroachDB: SET CLUSTER SETTING kv.rangefeed.enabled = true + one CREATE CHANGEFEED statement. Postgres + Debezium: wal_level = logical + REPLICATION role + PUBLICATION + replication slot + Debezium connector + schema-history topic + slot-lag monitoring. Roughly 6 vs 60 lines of config.
• Operational surface. CockroachDB: changefeeds are cluster-managed jobs; monitored via SHOW JOBS and crdb_internal.jobs. Postgres + Debezium: separate JVM per connector; replication-slot lag monitoring; Kafka Connect cluster to manage.
• Multi-region. CockroachDB changefeeds are aware of the source cluster's topology; events land in Kafka in commit-order per key across regions. Postgres + Debezium: one connector per region (or one against the primary + eventual consistency across replicas).
• Schema evolution. CockroachDB: automatic in Avro mode; the changefeed re-registers the schema on ALTER TABLE. Debezium: same, via schema registry.

Common interview probes on changefeeds.

• "How is changefeed delivery guaranteed?" — required answer: at-least-once + resolved timestamps → exactly-once at the sink.
• "What is a resolved timestamp?" — required answer: a watermark asserting no more events with commit_ts ≤ T will appear.
• "How does this differ from Debezium?" — native primitive vs external JVM connector; ~10× simpler setup.
• "How is source load impacted?" — required answer: ~zero (rangefeeds tap the Raft log, not the tables).
• "What sinks are supported?" — Kafka, webhook, cloud storage, Pub/Sub; JSON, Avro, Parquet.

Worked example — setting up a Kafka changefeed for the orders table

Detailed explanation. The canonical setup: enable rangefeeds cluster-wide, then run CREATE CHANGEFEED with the target Kafka broker, format, and resolved-ts cadence. Walk through the DDL, the event shape, and the topic layout.

• Source. public.orders in the saas database.
• Sink. Kafka broker at kafka:9092; topic-prefixed as prod..
• Format. Avro with Confluent schema registry for typed events.
• Resolved cadence. 5 seconds — a balance between watermark freshness and Kafka message overhead.
• Bootstrap. initial_scan = 'yes' — emit every existing row as a synthetic INSERT before tailing.

Question. Write the changefeed DDL, show the event shape landing in Kafka, and describe the topic layout.

Input.

Parameter	Value
Source table	public.orders
Sink	kafka://kafka:9092
Topic prefix	prod.
Format	avro
Resolved cadence	5s
Initial scan	yes
Diff (before-image)	yes

Code.

-- 1. Enable rangefeeds cluster-wide (once per cluster)
SET CLUSTER SETTING kv.rangefeed.enabled = true;

-- 2. Create the changefeed
CREATE CHANGEFEED FOR TABLE public.orders
INTO 'kafka://kafka:9092?topic_prefix=prod.'
WITH  format                    = 'avro',
      confluent_schema_registry = 'http://schema-registry:8081',
      resolved                  = '5s',
      updated,                     -- add cluster-wide MVCC ts to each event
      diff,                        -- add before-image on UPDATE / DELETE
      initial_scan              = 'yes',
      min_checkpoint_frequency  = '5s';

-- 3. Verify the job is running
SELECT job_id, status, description
FROM   [SHOW CHANGEFEED JOBS]
WHERE  description LIKE '%orders%';

// 4. Sample event on prod.orders topic (Avro-decoded to JSON for readability)
{
  "before": {
    "id": "3b8b1e0e-6f5f-4c1e-9c0e-...",
    "customer_id": "7c0e...",
    "total_cents": 1500,
    "status": "pending",
    "created_at": "2026-07-27T10:00:00Z",
    "updated_at": "2026-07-27T10:00:00Z"
  },
  "after": {
    "id": "3b8b1e0e-6f5f-4c1e-9c0e-...",
    "customer_id": "7c0e...",
    "total_cents": 1500,
    "status": "shipped",
    "created_at": "2026-07-27T10:00:00Z",
    "updated_at": "2026-07-27T10:00:03Z"
  },
  "updated": "1720051260000000000.0000000000",   // cluster MVCC ts
  "op":      "u"                                   // c=create, u=update, d=delete
}

// 5. Sample resolved-ts message on prod.orders topic
{
  "resolved": "1720051265000000000.0000000000"     // 5s after the update
}

Step-by-step explanation.

1. SET CLUSTER SETTING kv.rangefeed.enabled = true is a one-time cluster-wide enablement. Without it, CREATE CHANGEFEED fails with a clear error. This setting is enabled by default on new CockroachCloud clusters.
2. CREATE CHANGEFEED FOR TABLE public.orders INTO 'kafka://...' starts an ongoing background job. The job appears in crdb_internal.jobs and can be paused, resumed, or cancelled via PAUSE JOB, RESUME JOB, CANCEL JOB.
3. WITH format = 'avro' + confluent_schema_registry = '...' produces typed Avro records; schema evolution on ALTER TABLE auto-registers a new schema version. Downstream consumers get typed events, not string blobs.
4. WITH diff includes the before-image on UPDATE / DELETE events. Without it, UPDATE events would carry only the new row; downstream consumers wanting to compute deltas would need to fetch the previous state themselves.
5. WITH resolved = '5s' is the critical flag for exactly-once sinks. Every partition periodically emits a {"resolved": "..."} message; the sink uses these to bound its commit windows.

Output.

Topic	Message shape	Key
prod.orders	Avro-encoded INSERT/UPDATE/DELETE change events	primary key UUID
prod.orders	Avro-encoded {"resolved": "<ts>"} per 5s per partition	(special key)
Consumer log-tail	events for a given key in commit order	ordered per key
Global order	not guaranteed across keys	not guaranteed

Rule of thumb. For any CockroachDB → lakehouse feed, use CREATE CHANGEFEED ... WITH format = 'avro', resolved = '5s', diff, initial_scan = 'yes'. This is the safe default: typed events, watermarks every 5s, before-image on updates, and a one-time full-table bootstrap.

Worked example — using resolved timestamps for exactly-once Iceberg ingest

Detailed explanation. The changefeed alone gives at-least-once delivery — Kafka can redeliver events on consumer restart, partition rebalance, or explicit offset reset. Exactly-once ingest is a property of the sink: it must dedupe by key and honour resolved-ts commit boundaries. Walk through the Iceberg sink logic.

• Sink. Iceberg raw.orders table with id as the merge key.
• Buffer. Per-partition in-memory buffer keyed by (partition, offset).
• Commit trigger. When every partition has emitted a resolved-ts ≥ T, commit all buffered events with commit_ts ≤ T.
• Dedupe. Iceberg MERGE on id with an ORDER BY commit_ts to pick the newest version.

Question. Walk through the sink's per-event logic and the commit cycle.

Input.

Parameter	Value
Source	prod.orders Kafka topic
Partitions	8
Sink	Iceberg raw.orders (REST catalogue)
Commit interval	30 s
Dedupe key	id + commit_ts

Code.

# Iceberg sink — exactly-once via resolved-ts watermarks
from collections import defaultdict

def run_sink(kafka_consumer, iceberg_writer):
    """Consume orders changefeed; commit to Iceberg on resolved-ts watermarks."""
    buffer: dict[int, list[dict]] = defaultdict(list)  # partition -> events
    resolved_ts: dict[int, str]   = {}                 # partition -> resolved-ts

    for msg in kafka_consumer:
        event = msg.value                              # decoded Avro dict
        partition = msg.partition

        # Resolved-ts messages are watermarks; not real events
        if "resolved" in event:
            resolved_ts[partition] = event["resolved"]
            _maybe_commit(buffer, resolved_ts, iceberg_writer)
            continue

        buffer[partition].append({
            "commit_ts": event["updated"],
            "key":       event["after"]["id"] if event.get("after") else event["before"]["id"],
            "op":        event["op"],
            "row":       event.get("after") or event.get("before"),
        })


def _maybe_commit(buffer, resolved_ts, iceberg_writer):
    """When every partition has a resolved-ts, commit up to the minimum."""
    if len(resolved_ts) < NUM_PARTITIONS:
        return                                          # not every partition heard from yet

    watermark = min(resolved_ts.values())               # oldest resolved-ts across partitions

    # Collect events with commit_ts <= watermark from every partition buffer
    to_commit: list[dict] = []
    for partition in list(buffer.keys()):
        keep, ship = [], []
        for ev in buffer[partition]:
            (ship if ev["commit_ts"] <= watermark else keep).append(ev)
        buffer[partition] = keep
        to_commit.extend(ship)

    if not to_commit:
        return

    # Dedupe by key, keep newest commit_ts (Iceberg MERGE semantics)
    to_commit.sort(key=lambda e: (e["key"], e["commit_ts"]))
    # Iceberg MERGE handles the actual upsert / delete atomically
    iceberg_writer.merge(to_commit, key_column="id", ts_column="commit_ts")

-- Iceberg MERGE — atomic upsert + delete by commit_ts
MERGE INTO raw.orders AS tgt
USING (
  SELECT key, MAX_BY(row, commit_ts) AS row, MAX(commit_ts) AS commit_ts,
         MAX_BY(op, commit_ts)  AS op
  FROM   staging.orders_changefeed_batch
  GROUP  BY key
) src
ON  tgt.id = src.key
WHEN MATCHED AND src.op = 'd'
     THEN DELETE
WHEN MATCHED
     THEN UPDATE SET tgt = src.row
WHEN NOT MATCHED AND src.op != 'd'
     THEN INSERT (id, customer_id, total_cents, status, created_at, updated_at)
          VALUES (src.row.id, src.row.customer_id, src.row.total_cents,
                  src.row.status, src.row.created_at, src.row.updated_at);

Step-by-step explanation.

1. The sink buffers every real event by partition. Resolved-ts messages are not buffered — they update the per-partition resolved_ts watermark.
2. When every partition has emitted at least one resolved-ts, the sink computes watermark = min(resolved_ts.values()). This is the timestamp below which every partition has asserted "no more events."
3. The sink ships all buffered events with commit_ts ≤ watermark to Iceberg. Events past the watermark stay in the buffer until the next watermark advance.
4. The Iceberg MERGE deduplicates by key + commit_ts. If Kafka re-delivered an event (at-least-once), the MERGE picks the newest commit_ts and discards the older duplicate. MAX_BY(op, commit_ts) = 'd' handles the delete case atomically.
5. The result: every distinct row-version lands in Iceberg exactly once, in commit-order per key, with delete semantics preserved. At-least-once Kafka becomes exactly-once Iceberg.

Output.

Kafka delivery mode	Sink outcome
One-time delivery	one row inserted / updated
Re-delivered event (dup)	MERGE picks max commit_ts; effectively one write
Out-of-order across keys	irrelevant — order preserved per key
Partial-partition failure	watermark never advances past failed partition; safe
Sink restart	resume from last committed Kafka offset; MERGE dedupes

Rule of thumb. For any changefeed → lakehouse deployment, drive commits from resolved-ts watermarks (not from event batches), and always MERGE by key + commit_ts. Skipping either step converts at-least-once into "sometimes duplicated" — a subtle data-quality bug that only surfaces at audit time.

Worked example — sinking to S3 with parquet for lakehouse ingest

Detailed explanation. Not every lakehouse consumer wants Kafka in the middle. CockroachDB changefeeds also sink directly to S3 (or GCS or Azure Blob) as newline-delimited JSON or Parquet, with resolved-ts messages included as separate objects. Walk through the S3 sink setup and the downstream ingest path.

• Sink. s3://lake-raw/orders/ bucket.
• Format. Parquet with resolved-ts sidecar files.
• Consumer. Spark / Trino / Snowflake external table reading the Parquet files.

Question. Configure the S3 sink and describe how the downstream lakehouse ingests the files.

Input.

Parameter	Value
Sink URL	s3://lake-raw/orders/?AWS_ACCESS_KEY_ID=...&AWS_SECRET_ACCESS_KEY=...
Format	parquet
Resolved-ts sidecars	yes (JSON sidecar per resolved-ts)
File rotation	every 30s or 128MB
Downstream reader	Snowflake external table + copy-into

Code.

-- 1. Changefeed to S3 with parquet format
CREATE CHANGEFEED FOR TABLE public.orders
INTO 's3://lake-raw/orders/?AWS_ACCESS_KEY_ID=redacted&AWS_SECRET_ACCESS_KEY=redacted'
WITH format                = 'parquet',
     resolved               = '30s',
     compression            = 'gzip',
     updated,
     diff,
     initial_scan           = 'yes',
     min_checkpoint_frequency = '30s';

-- 2. S3 layout after the changefeed runs
--   s3://lake-raw/orders/2026-07-27/0000/data-000001.parquet
--   s3://lake-raw/orders/2026-07-27/0000/data-000002.parquet
--   s3://lake-raw/orders/2026-07-27/0000/resolved-1720051260000.json
--   s3://lake-raw/orders/2026-07-27/0030/data-000003.parquet
--   s3://lake-raw/orders/2026-07-27/0030/resolved-1720051290000.json

-- 3. Snowflake external table over the S3 lake
CREATE OR REPLACE EXTERNAL TABLE analytics.orders_raw
  LOCATION = @lake_stage/orders/
  FILE_FORMAT = (TYPE = PARQUET)
  PATTERN = '.*data-.*.parquet'
  AUTO_REFRESH = TRUE;

-- 4. Nightly MERGE into a curated table using resolved-ts watermarks
--    (Read the newest resolved-ts sidecar; MERGE all rows with commit_ts <= watermark)
MERGE INTO analytics.orders AS tgt
USING (
    SELECT after:id::STRING              AS id,
           after:customer_id::STRING     AS customer_id,
           after:total_cents::NUMBER     AS total_cents,
           after:status::STRING          AS status,
           updated::NUMBER               AS commit_ts,
           op::STRING                    AS op
    FROM   analytics.orders_raw
    WHERE  commit_ts <= (SELECT MAX(resolved_ts) FROM analytics.orders_watermarks)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY commit_ts DESC) = 1
) src
ON  tgt.id = src.id
WHEN MATCHED     AND src.op = 'd' THEN DELETE
WHEN MATCHED                       THEN UPDATE SET status = src.status, total_cents = src.total_cents
WHEN NOT MATCHED AND src.op != 'd' THEN INSERT (id, customer_id, total_cents, status)
                                        VALUES (src.id, src.customer_id, src.total_cents, src.status);

Step-by-step explanation.

1. The S3 sink is a drop-in replacement for the Kafka sink — same CREATE CHANGEFEED DDL, different URL. Parquet is the recommended format for lakehouse workloads because it's columnar, compressed, and query-engine-friendly.
2. The changefeed writes one Parquet file per "checkpoint" (30 s or 128 MB, whichever comes first). Resolved-ts messages become sidecar JSON files (resolved-<ts>.json) that downstream readers use as watermarks.
3. Snowflake's external table reads the Parquet files without copying them into Snowflake storage. Auto-refresh detects new files via S3 event notifications.
4. The nightly MERGE reads all rows with commit_ts ≤ watermark (from the resolved-ts sidecar), deduplicates via QUALIFY ROW_NUMBER(), and applies the change to the curated table. op = 'd' triggers a DELETE; everything else is UPSERT.
5. This pattern gives exactly-once ingest into Snowflake without a Kafka cluster in the middle — cheaper for teams that don't already run Kafka, but with higher latency (30 s + MERGE cadence instead of sub-second).

Output.

Pipeline component	Config	Freshness
Changefeed cadence	30 s / 128 MB	latency floor
Snowflake auto-refresh	on S3 event	~1 min
Nightly MERGE	scheduled	24 h (or hourly if wanted)
End-to-end freshness	30 s (raw) / 1 h (curated)	tunable
Cost	S3 + Snowflake external table	no Kafka

Rule of thumb. For CockroachDB → lakehouse without Kafka, use S3 sink with Parquet + resolved-ts sidecars + Snowflake external table + scheduled MERGE. This is the cheapest lakehouse feed you can build; it trades sub-second freshness for operational simplicity.

Senior interview question on change feeds

A senior interviewer might ask: "You have a 100k-TPS CockroachDB cluster serving OLTP for a global SaaS. The lakehouse team wants a sub-10-second freshness feed of every mutation on 30 tables landing in Iceberg with exactly-once semantics. Walk me through the changefeed design, the Kafka topic layout, the Iceberg sink logic, and the monitoring for changefeed lag."

Solution Using multi-table changefeed + Kafka + Iceberg sink with resolved-ts commit boundaries

-- 1. Enable rangefeeds cluster-wide (one time)
SET CLUSTER SETTING kv.rangefeed.enabled = true;

-- 2. One multi-table changefeed feeds Kafka; simpler ops than 30 separate feeds
CREATE CHANGEFEED FOR TABLE
    public.orders,
    public.customers,
    public.shipments,
    public.payments,
    public.inventory,
    -- ... 25 more tables ...
    public.audit_events
INTO 'kafka://kafka-broker.internal:9092?topic_prefix=lake.'
WITH format                    = 'avro',
     confluent_schema_registry = 'http://schema-registry:8081',
     resolved                  = '5s',
     updated,
     diff,
     initial_scan              = 'yes',
     min_checkpoint_frequency  = '5s',
     protect_data_from_gc_on_pause = 'yes';   -- pause won't lose events

-- 3. Verify + note the job id for monitoring
SELECT job_id, status, running_status, high_water_timestamp
FROM   [SHOW CHANGEFEED JOBS]
WHERE  description LIKE '%lake.%';

# 4. Iceberg sink connector (Kafka Connect worker)
name: iceberg-lake-sink
config:
  connector.class: org.apache.iceberg.connect.IcebergSinkConnector
  topics.regex: "lake\\..*"                          # subscribe to every lake.* topic
  iceberg.catalog.type: rest
  iceberg.catalog.uri: http://iceberg-catalog:8181
  iceberg.tables.dynamic-enabled: true               # auto-create per source table
  iceberg.tables.route-field: "__table__"            # route by topic name
  iceberg.tables.commit-interval-ms: 5000            # resolved-ts-aligned commits
  iceberg.tables.evolve-schema-enabled: true         # auto-schema-evolve on ALTER
  key.converter: io.confluent.connect.avro.AvroConverter
  key.converter.schema.registry.url: http://schema-registry:8081
  value.converter: io.confluent.connect.avro.AvroConverter
  value.converter.schema.registry.url: http://schema-registry:8081

-- 5. Monitoring — changefeed lag alert
SELECT job_id,
       description,
       high_water_timestamp::DECIMAL / 1e9 AS high_water_epoch,
       (extract(epoch FROM now()) - high_water_timestamp::DECIMAL / 1e9) AS lag_seconds
FROM   [SHOW CHANGEFEED JOBS]
WHERE  description LIKE '%lake.%';

-- Alert if lag_seconds > 60 for 5 min → changefeed falling behind

Step-by-step trace.

Layer	Component	Purpose
Source	30 CockroachDB tables	one multi-table changefeed
Rangefeed	Raft-log subscription	zero source-table load
Kafka	30 topics (lake.public.<table>)	per-key ordered events
Resolved-ts	5s cadence per partition	exactly-once watermark
Iceberg sink	per-topic Iceberg table	atomic MERGE on resolved-ts commit
Monitoring	high_water_timestamp lag	alert > 60s for 5m

After deployment, every mutation on the 30 source tables reaches Iceberg within ~5–10 s of source commit (5 s resolved-ts cadence + Kafka Connect commit overhead). Schema evolution on any source table auto-propagates through the schema registry and into Iceberg. A changefeed pause via PAUSE JOB retains the source WAL data (protect_data_from_gc_on_pause = yes), so a maintenance pause doesn't lose events.

Output:

Metric	Value
End-to-end freshness	~5–10 s
Source-table load	~0 (rangefeed)
Resolved-ts cadence	5 s
Kafka topics	30 (one per source table)
Iceberg tables	30 (auto-created)
Schema evolution	automatic (schema registry)
Lag monitoring	SHOW CHANGEFEED JOBS.high_water_timestamp

Why this works — concept by concept:

• Multi-table changefeed — one job feeds 30 tables into one Kafka broker. Operationally simpler than 30 separate changefeeds; monitored via one SHOW CHANGEFEED JOBS row. Resolved-ts messages are per-partition so partitions can still make independent progress.
• Resolved timestamps — the exactly-once primitive. The Iceberg sink commits only when every partition has emitted a resolved-ts ≥ T; MERGE on (key, commit_ts) deduplicates any Kafka re-deliveries. At-least-once + resolved-ts = exactly-once at the sink.
• Rangefeed on Raft log — the changefeed reads from the Raft log, not from the tables. Source-table load is effectively zero — no polling, no triggers, no shadow tables.
• Auto-schema-evolve via schema registry — Avro + Confluent schema registry means an ALTER TABLE orders ADD COLUMN priority INT on CockroachDB auto-registers a new Avro schema version; the Iceberg sink adapts. No downstream break.
• Cost — one changefeed job (~5% source CPU total for rangefeed decode), one Kafka cluster, one Iceberg sink worker, one schema registry. Compared to Debezium against 30 Postgres tables with 30 replication slots, this is dramatically simpler and dramatically more reliable. O(1) per commit on the source; O(events × sinks) downstream.

Streaming
Topic — streaming
Streaming CDC and changefeed problems

Practice →

Design Topic — design Design problems on lakehouse CDC ingestion

Practice →

---

5. Multi-region + when CockroachDB wins + interview signals

REGIONAL BY ROW, SURVIVE REGION FAILURE, follower reads — the three DDL primitives that make multi-region OLTP feel local

The mental model in one line: CockroachDB's multi-region primitives collapse the whole "geo-distributed OLTP" problem into three DDL concepts — REGIONAL BY ROW (each row's replicas live in the region named by a column), REGIONAL BY TABLE IN 'region' (whole-table home region), LOCALITY GLOBAL (replicated to every region for local reads), plus survival goals (SURVIVE ZONE FAILURE for 3 replicas, SURVIVE REGION FAILURE for 5 replicas across 3+ regions), plus follower reads (AS OF SYSTEM TIME follower_read_timestamp() for bounded-stale local reads) — and this vocabulary is what senior interviewers listen for when they ask "how would you design a multi-region OLTP primary?" Miss this vocabulary and you cannot pass the multi-region CockroachDB interview.

The three table localities — one at a time.

• REGIONAL BY TABLE IN 'region'. Whole-table home region. Every row's replicas live in that region (plus survival-goal replicas in other regions). Best for reference-data-per-region (e.g. products_eu, products_us).
• REGIONAL BY ROW AS region. Per-row home region driven by a column value. Every row lands its replicas in the region named by region. Best for multi-tenant SaaS where each tenant has a home region.
• LOCALITY GLOBAL. Replicated to every region; local reads everywhere; writes pay a cross-region round-trip. Best for slow-changing reference data (product catalogue, feature flags) that must be readable at low latency from every region.

The two survival goals.

• SURVIVE ZONE FAILURE (default). 3 replicas per range; loses continuity if a whole region drops. Fine for single-region deployments or multi-region deployments where regional outages are tolerable.
• SURVIVE REGION FAILURE. 5 replicas per range across 3+ regions; any one region can drop and the remaining 3 replicas still form a majority. The cost: 5× storage, slightly higher write latency (cross-region Raft quorum for some writes), noticeably higher network cost. The benefit: zero-op regional outage recovery.

Follower reads — the bounded-stale primitive.

• The clause. SELECT ... AS OF SYSTEM TIME follower_read_timestamp() FROM ... — the default is ~4.8 s stale.
• What it does. Reads from the nearest replica instead of the leaseholder. Serialisable is relaxed to bounded-stale; latency drops from cross-region round-trip to local.
• When to use. Dashboards, list-views, feed pagination, any read that tolerates a few seconds of staleness. Never use for read-your-own-write flows.
• Custom staleness. AS OF SYSTEM TIME '-1m' for one-minute-stale; AS OF SYSTEM TIME with_max_staleness('10s') for at-most-10s-stale.

When CockroachDB wins vs the alternatives.

• vs Postgres (single-region). CockroachDB wins when multi-region is required or when survival must be zero-op. Postgres wins for single-region workloads under ~5k TPS — simpler, cheaper, faster per commit.
• vs YugabyteDB. CockroachDB wins on multi-region ergonomics (declarative DDL vs tablespace-level config). YugabyteDB wins on PG-compat depth (triggers, PL/pgSQL work verbatim).
• vs TiDB. CockroachDB wins on PG compat + multi-region. TiDB wins on HTAP (TiFlash columnar replicas fed from same Raft groups let you query OLTP data with columnar-analytics speed).
• vs Aurora. CockroachDB wins on multi-region + zero-op survival + native changefeed. Aurora wins on ease of operation for single-region workloads (fully managed, familiar Postgres/MySQL).

Common interview probes on multi-region.

• "What is REGIONAL BY ROW?" — required answer: per-row home region driven by a column value.
• "How does SURVIVE REGION FAILURE work?" — 5 replicas across 3+ regions; one region can drop and 3 remaining replicas form quorum.
• "When would you use LOCALITY GLOBAL?" — slow-changing reference data with low-latency reads everywhere.
• "What are follower reads?" — bounded-stale reads from the nearest replica; avoids leaseholder round-trip.
• "When does CockroachDB lose to Postgres?" — single-region workloads under ~5k TPS.

Worked example — REGIONAL BY ROW for a multi-tenant SaaS

Detailed explanation. A three-region SaaS (us-east1, europe-west1, asia-south1) has customers pinned to a home region. Each customer's data (orders, users, invoices) must commit locally at sub-10 ms. Use REGIONAL BY ROW AS region on every tenant-scoped table with a region column populated from the tenant's home region. Walk through the DDL and the write path.

• Tenants. tenants(id UUID PK, region crdb_internal_region, name).
• Orders. orders(id UUID PK, tenant_id UUID, region crdb_internal_region, total_cents, ...).
• Users. users(id UUID PK, tenant_id UUID, region crdb_internal_region, email, ...).

Question. Write the DDL and trace a write from an EU customer.

Input.

Object	Locality	Purpose
tenants	REGIONAL BY ROW AS region	tenant home region set at signup
orders	REGIONAL BY ROW AS region	order lives where the tenant lives
users	REGIONAL BY ROW AS region	user lives where their tenant lives
plans	LOCALITY GLOBAL	reference data replicated everywhere

Code.

-- 1. Multi-region database with survival goal
CREATE DATABASE saas
    PRIMARY REGION 'us-east1'
    REGIONS 'us-east1', 'europe-west1', 'asia-south1'
    SURVIVE REGION FAILURE;
USE saas;

-- 2. Tenants — per-row home region
CREATE TABLE tenants (
    id         UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
    region     crdb_internal_region NOT NULL,
    name       STRING               NOT NULL,
    created_at TIMESTAMPTZ          NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

-- 3. Orders — same locality as their tenant
CREATE TABLE orders (
    id           UUID                 PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    UUID                 NOT NULL REFERENCES tenants(id),
    region       crdb_internal_region NOT NULL,
    total_cents  INT8                 NOT NULL,
    status       STRING               NOT NULL,
    created_at   TIMESTAMPTZ          NOT NULL DEFAULT now()
) LOCALITY REGIONAL BY ROW AS region;

CREATE INDEX idx_orders_tenant ON orders (tenant_id);

-- 4. Plans — global reference data
CREATE TABLE plans (
    tier          STRING PRIMARY KEY,
    monthly_cents INT8   NOT NULL
) LOCALITY GLOBAL;

INSERT INTO plans VALUES ('free', 0), ('pro', 2900), ('enterprise', 29900);

-- 5. Write path — EU customer places an order
INSERT INTO orders (tenant_id, region, total_cents, status)
VALUES ('a1b2...eu-tenant', 'europe-west1', 4500, 'pending');

Step-by-step explanation.

1. CREATE DATABASE ... SURVIVE REGION FAILURE sets the whole database to survive a full region outage. Every table under this database inherits the 5-replica-across-3-regions layout.
2. LOCALITY REGIONAL BY ROW AS region places each row's replicas in the region named by the region column. The application must set this column when inserting; there is no default derivation.
3. LOCALITY GLOBAL on plans replicates the table to every region and serves reads from the nearest replica. Writes pay a cross-region round-trip; this is fine for slow-changing reference data.
4. The EU customer's INSERT sets region = 'europe-west1'. CockroachDB places the row's 5 replicas across the 3 regions (2 in eu-west, 2 in us-east, 1 in ap-south is one valid layout) and the leaseholder in eu-west; the Raft quorum runs inside eu-west, so commit is ~5 ms.
5. A subsequent SELECT * FROM orders WHERE tenant_id = ... from an EU app pod hits the local leaseholder → local read at ~1 ms. A US app pod reading the same row hits the eu-west leaseholder → ~90 ms cross-region hop. Follower reads (AS OF SYSTEM TIME follower_read_timestamp()) let the US pod read locally at ~1 ms with bounded staleness.

Output.

Row	Locality	Leaseholder region	Local write p50
EU tenant order	REGIONAL BY ROW / eu-west	europe-west1	~5 ms
US tenant order	REGIONAL BY ROW / us-east	us-east1	~4 ms
AP tenant order	REGIONAL BY ROW / ap-south	asia-south1	~7 ms
plans row	GLOBAL	replicated to every region	~1 ms local read; ~90 ms write

Rule of thumb. For any multi-tenant SaaS on CockroachDB, use REGIONAL BY ROW AS region on tenant-scoped tables and LOCALITY GLOBAL on reference tables. This is the pattern that makes multi-region OLTP feel local from the app.

Worked example — follower reads for the dashboard read path

Detailed explanation. The customer-facing dashboard shows aggregated stats — orders in the last 24 hours, top items, revenue by day. These reads don't need read-your-own-write consistency; a few seconds of staleness is fine. Use follower reads to serve them from local replicas at ~1 ms instead of ~90 ms. Walk through the query pattern and the staleness budget.

• Dashboard. Aggregated stats per tenant.
• Staleness budget. Up to 10 seconds (users don't notice sub-10s freshness on aggregate views).
• Latency target. p95 < 50 ms per page load.
• Read pattern. AS OF SYSTEM TIME follower_read_timestamp() on every dashboard query.

Question. Write the dashboard query pattern and quantify the latency win.

Input.

Component	Value
Query type	aggregation on orders
Staleness clause	AOST follower_read_timestamp()
Staleness observed	~4.8 s (default)
Local read p50	~1 ms
Leaseholder read p50 (cross-region)	~90 ms
Latency win	~90×

Code.

-- Dashboard query — bounded-stale, local, no cross-region hop
SELECT date_trunc('day', created_at) AS day,
       count(*)                       AS n_orders,
       sum(total_cents)               AS revenue_cents
FROM   orders AS OF SYSTEM TIME follower_read_timestamp()
WHERE  tenant_id = $1
  AND  created_at >= now() - INTERVAL '30 days'
GROUP  BY day
ORDER  BY day;

-- Custom staleness — cap at 10 seconds for a tighter freshness contract
SELECT ...
FROM   orders AS OF SYSTEM TIME with_max_staleness('10s')
WHERE  ...;

-- Fixed staleness — one minute ago
SELECT ...
FROM   orders AS OF SYSTEM TIME '-1m'
WHERE  ...;

# Application — dashboard vs transactional queries
def get_dashboard_stats(conn, tenant_id: str) -> list[dict]:
    """Bounded-stale reads; served locally."""
    with conn.cursor() as cur:
        cur.execute("""
            SELECT date_trunc('day', created_at) AS day,
                   count(*)                       AS n_orders,
                   sum(total_cents)               AS revenue_cents
            FROM   orders AS OF SYSTEM TIME follower_read_timestamp()
            WHERE  tenant_id = %s
              AND  created_at >= now() - INTERVAL '30 days'
            GROUP  BY day
            ORDER  BY day
        """, (tenant_id,))
        return [dict(zip(("day", "n_orders", "revenue_cents"), r)) for r in cur.fetchall()]


def place_order(conn, tenant_id: str, total_cents: int) -> str:
    """Read-your-own-write path; strong consistency required — no follower read."""
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO orders (tenant_id, region, total_cents, status)
            VALUES (%s, (SELECT region FROM tenants WHERE id = %s), %s, 'pending')
            RETURNING id
        """, (tenant_id, tenant_id, total_cents))
        return cur.fetchone()[0]

Step-by-step explanation.

1. The dashboard query uses AS OF SYSTEM TIME follower_read_timestamp(). This function returns a timestamp ~4.8 s in the past — the "safe" default for reading from any follower replica.
2. Every replica of the range holds the state at every past timestamp (MVCC). Reading AS OF SYSTEM TIME X where X is ~5 s ago is guaranteed to be served correctly by any replica, not just the leaseholder.
3. The gateway node picks the nearest replica for the read — local Pebble read at ~1 ms. Compare to a strong-consistency read that would have to hit the leaseholder, adding a ~90 ms cross-region hop for non-local tenants.
4. with_max_staleness('10s') is a tighter staleness contract — CockroachDB serves the read from a follower if the closed timestamp is within 10 seconds; else falls back to the leaseholder. This gives you a freshness upper bound.
5. The application splits queries into two categories: dashboard (bounded-stale, follower reads) and transactional (strong, leaseholder). The former dominates request volume; the latter dominates user-visible correctness. This split is where the multi-region latency budget is actually spent.

Output.

Query type	Latency (local)	Latency (cross-region)	Staleness
Follower read (default AOST)	~1 ms	~1 ms	~4.8 s
Custom with_max_staleness('10s')	~1 ms	~1 ms	≤ 10 s
Strong read (leaseholder)	~5 ms	~90 ms	0
INSERT / UPDATE	~5 ms	~5–90 ms	N/A

Rule of thumb. For every read-only dashboard / feed / list-view query in a multi-region CockroachDB app, use AS OF SYSTEM TIME follower_read_timestamp(). Reserve strong reads for read-your-own-write paths (post-place-order confirmations, form re-render after save). This split turns cross-region OLTP into local-feeling reads.

Worked example — surviving a regional outage without operator intervention

Detailed explanation. A three-region CockroachDB cluster running SURVIVE REGION FAILURE. europe-west1 drops (VPC networking failure; all EU nodes unreachable). Walk through what happens to writes and reads from every region during and after the outage.

• Cluster. 9 nodes: 3 in each region.
• Replicas. 5 per range across 3 regions (2+2+1 layout is one valid).
• Failure. Loss of all 3 EU nodes.
• Expectation. Writes continue for every region's traffic; reads to EU-home rows served via cross-region Raft.

Question. Trace write and read behaviour for US-home, EU-home, and AP-home tenants during the outage.

Input.

Row locality	Pre-outage leaseholder	Post-outage behaviour
US-home	us-east1	leaseholder unchanged; commits ~5 ms
EU-home	europe-west1	leaseholder moves to us-east1 or ap-south1; commits ~90 ms
AP-home	asia-south1	leaseholder unchanged; commits ~7 ms
GLOBAL reference	any region	unchanged; writes ~90 ms

Code.

-- 1. Pre-outage: check replica placement for an EU-home row
SELECT range_id, replicas, leaseholder, replica_localities
FROM   [SHOW RANGE FROM TABLE orders FOR ROW ('a1b2...eu-order-id')];

-- Sample output pre-outage:
-- range_id | replicas       | leaseholder | replica_localities
-- 7        | {n1,n4,n5,n7,n9} | n4         | {us-east1,eu-west1,eu-west1,ap-south1,us-east1}

-- 2. Simulate EU outage (in test): remove all EU nodes
-- (In production, this happens automatically on network partition)

-- 3. Post-outage: leaseholder migrates to a surviving replica
SELECT range_id, replicas, leaseholder, replica_localities
FROM   [SHOW RANGE FROM TABLE orders FOR ROW ('a1b2...eu-order-id')];

-- Sample output post-outage:
-- range_id | replicas       | leaseholder | replica_localities
-- 7        | {n1,n7,n9}     | n1         | {us-east1,ap-south1,us-east1}
-- (3 replicas still form majority of the original 5; writes continue)

-- 4. Once EU is healed, cockroach re-adds replicas automatically
--    (no operator action)
SELECT count(*) AS underreplicated
FROM   crdb_internal.ranges
WHERE  array_length(replicas, 1) < 5;

Step-by-step explanation.

1. Pre-outage, an EU-home row's leaseholder is in europe-west1 (n4). The row has 5 replicas: 2 in eu-west, 2 in us-east, 1 in ap-south. Local writes from the EU app pod commit at ~5 ms.
2. When the EU nodes drop, the range loses 2 of 5 replicas. The remaining 3 (2 US + 1 AP) still form a majority of the original 5, so writes continue. CockroachDB detects the leaseholder loss (heartbeat timeout ~9 s) and promotes a surviving replica to leaseholder — in this case n1 (us-east1).
3. Writes to the EU-home row from EU app pods now fail (the pods can't reach the cluster). Writes from US or AP app pods succeed; leaseholder is n1 (us-east); Raft quorum is 2 of 3 remaining replicas. Commit latency for EU-home rows via US app pods is ~5 ms; via AP app pods is ~85 ms (cross-region hop to us-east).
4. Reads from surviving app pods work identically. Follower reads served locally; strong reads hit the new leaseholder. No manual DR; no operator intervention.
5. When EU is healed and nodes rejoin the cluster, CockroachDB automatically re-adds the missing replicas — crdb_internal.ranges.replicas returns to 5 within minutes. Leaseholders may migrate back to europe-west1 under the multi-region locality preferences. SELECT count(*) FROM crdb_internal.ranges WHERE array_length(replicas, 1) < 5 returns 0 when recovery is complete.

Output.

Phase	US-home write	EU-home write (from US)	AP-home write
Pre-outage (steady state)	~5 ms	~5 ms (from EU pod)	~7 ms
During outage	~5 ms	~5 ms (from US pod; EU pods offline)	~7 ms
Post-heal (transient)	~5 ms	rebalancing; ~10 ms	~7 ms
Post-heal (steady state)	~5 ms	~5 ms (leaseholders back in EU)	~7 ms

Rule of thumb. SURVIVE REGION FAILURE gives you zero-op regional outage recovery. The cost is 5× storage; the benefit is that a full region drop does not stop writes. Verify with periodic game-day exercises: cockroach node decommission all nodes in one region and confirm writes continue.

Senior interview question on multi-region + comparison

A senior interviewer might ask: "Compare CockroachDB, YugabyteDB, TiDB, and Aurora across four axes: consistency, multi-region ergonomics, Postgres compat depth, and lakehouse-feed story. Then pick the winner for four workloads: (1) global SaaS OLTP with per-tenant home region, (2) legacy Rails app with heavy triggers moving to distributed SQL, (3) HTAP OLTP + interactive analytics on operational data, (4) single-region small-scale API."

Solution Using axis-by-axis comparison with per-workload winners and interview probe checklist

Axis 1 — Consistency
  CockroachDB : SERIALIZABLE by default
  YugabyteDB  : SNAPSHOT ISOLATION default; SERIALIZABLE opt-in
  TiDB        : SNAPSHOT ISOLATION default; SERIALIZABLE opt-in
  Aurora      : READ COMMITTED default (Postgres) or REPEATABLE READ (MySQL)

Axis 2 — Multi-region ergonomics
  CockroachDB : REGIONAL BY ROW / TABLE / GLOBAL + SURVIVE REGION FAILURE
                — declarative DDL; the class leader
  YugabyteDB  : tablespace-level placement + preferred-zones
                — more manual but more granular
  TiDB        : primary + placement rules; less multi-region-primary-oriented
  Aurora      : single-region primary only; Global Database for cross-region reads

Axis 3 — Postgres compatibility depth
  CockroachDB : wire + most SQL; NO triggers/PL/pgSQL/LISTEN/NOTIFY/SEQUENCE
  YugabyteDB  : reuses PG query layer; triggers + PL/pgSQL + extensions work
  TiDB        : MySQL-wire compat; PG compat is not the primary story
  Aurora      : full PG or full MySQL (managed fork)

Axis 4 — Lakehouse-feed story
  CockroachDB : native CREATE CHANGEFEED with resolved timestamps
  YugabyteDB  : YB CDC service or Debezium via yugabyted-cdc
  TiDB        : TiCDC (native, mature)
  Aurora      : Debezium against PG logical replication (mature but external)

Per-workload winners

Workload 1 — Global SaaS OLTP with per-tenant home region
  Winner:    CockroachDB
  Why:       REGIONAL BY ROW + SURVIVE REGION FAILURE + native changefeed = one DDL surface
  Runner-up: YugabyteDB (deeper PG compat if the app uses triggers)

Workload 2 — Legacy Rails app with heavy triggers moving to distributed SQL
  Winner:    YugabyteDB
  Why:       PG query layer reuse; triggers + PL/pgSQL work verbatim
  Runner-up: CockroachDB (accept the trigger port cost; better multi-region)

Workload 3 — HTAP: OLTP + interactive analytics on operational data
  Winner:    TiDB
  Why:       TiFlash columnar replicas fed from same Raft groups; sub-second scan
  Runner-up: CockroachDB + separate lakehouse via changefeed

Workload 4 — Single-region small-scale API (~500 TPS)
  Winner:    Aurora Postgres (or plain Postgres)
  Why:       simplest ops; cheapest; distributed SQL is over-engineering
  Runner-up: CockroachDB single-region (if multi-region is planned soon)

Interview probe checklist — what to name unprompted

For every distributed SQL question:
  □ Name serializable-by-default vs snapshot-isolation-default
  □ Name Raft consensus per range
  □ Name leaseholder role (writes + consistent reads)
  □ Name PG-compat gaps (triggers, PL/pgSQL, LISTEN/NOTIFY, SEQUENCE)
  □ Name native changefeed with resolved timestamps
  □ Name SURVIVE REGION FAILURE + follower reads
  □ Name at least one workload where each database loses

Step-by-step trace.

Axis	CockroachDB pick	Reason
Consistency	serializable default	strictest ANSI; fewer surprise anomalies
Multi-region ergonomics	REGIONAL BY ROW + SURVIVE REGION FAILURE	one declarative DDL
PG compat depth	wire + most SQL	good enough for most apps; not for trigger-heavy legacy
Lakehouse feed	native CREATE CHANGEFEED	no Debezium; resolved-ts exactly-once

After running the axis-by-axis comparison the winners fall out per workload: CockroachDB owns multi-region SaaS OLTP with lakehouse feeds; YugabyteDB owns deep PG-compat migrations; TiDB owns HTAP; Aurora / plain Postgres owns small-scale single-region. Naming all four winners and their trade-offs unprompted is the difference between a senior answer and a marketing recitation.

Output:

Workload	Winner	Runner-up	Key axis
Global SaaS OLTP	CockroachDB	YugabyteDB	multi-region ergonomics
Legacy trigger-heavy Rails	YugabyteDB	CockroachDB	PG compat depth
HTAP	TiDB	CRDB + separate lake	integrated columnar analytics
Small single-region	Aurora / Postgres	CockroachDB	simplicity + cost

Why this works — concept by concept:

• Axis-first framing — comparing distributed databases along four objective axes (consistency, multi-region, PG compat, lakehouse) beats any "which is best" answer. Every workload maps to a different axis winner; the answer is a table, not a single pick.
• REGIONAL BY ROW — the CockroachDB primitive that makes multi-region OLTP feel local. Per-row home region driven by a column value; leaseholder placed in the row's home region; local Raft quorum for commit. This is what wins Workload 1.
• SURVIVE REGION FAILURE — 5 replicas across 3+ regions; any one region can drop and 3 remaining replicas form majority. The zero-op regional-outage story that Aurora Global Database cannot match.
• Follower reads — the bounded-stale primitive that turns cross-region reads into local reads for dashboards / feeds / list-views. AS OF SYSTEM TIME follower_read_timestamp() on every read-only query in the multi-region path.
• Cost — 5× storage (SURVIVE REGION FAILURE) vs 1× single-region Postgres, ~3× write CPU for Raft coordination, one native changefeed job for lakehouse. Compared to running sharded Postgres across three regions with Bucardo replication and Debezium, this is dramatically simpler and dramatically more reliable. O(1) per commit locally; O(N regions) at rollout.

Design
Topic — design
Design problems on multi-region OLTP

Practice →

SQL
Topic — sql
SQL problems on distributed transactions and locality

Practice →

---

Cheat sheet — CockroachDB recipes

• Which distributed SQL when. CockroachDB is the default when you need multi-region OLTP + Postgres wire compat + native changefeed + SURVIVE REGION FAILURE. Pick YugabyteDB when your app leans hard on PL/pgSQL triggers and extensions — YB reuses the PG query layer verbatim. Pick TiDB when you need HTAP — TiFlash columnar replicas fed from the same Raft groups let you query OLTP data at analytical speeds. Pick Aurora (or plain Postgres) when you're single-region under ~5k TPS — distributed SQL is over-engineering and you'll pay 3× the write CPU for a Raft round-trip you don't need. Every workload maps to a different winner; write the four-axis table on a whiteboard before committing.
• Multi-region database template. CREATE DATABASE saas PRIMARY REGION 'us-east1' REGIONS 'us-east1', 'europe-west1', 'asia-south1' SURVIVE REGION FAILURE; — one DDL expresses both the multi-region topology and the survival goal. Every table under this database inherits the 5-replica-across-3-regions layout. Skip the SURVIVE REGION FAILURE clause only if you're willing to lose write availability during a full region outage.
• REGIONAL BY ROW template. CREATE TABLE tenants (id UUID PK DEFAULT gen_random_uuid(), region crdb_internal_region NOT NULL, name STRING NOT NULL) LOCALITY REGIONAL BY ROW AS region; — every row's 5 replicas live in the region named by the region column. Application must set region on INSERT (there is no derivation). Users in the row's home region commit at ~5 ms local Raft quorum; users in other regions pay a cross-region round-trip to the leaseholder.
• REGIONAL BY TABLE template. CREATE TABLE products (sku STRING PK, name STRING, price_cents INT8) LOCALITY REGIONAL BY TABLE IN 'us-east1'; — whole-table home region; leaseholder pinned to us-east1. Best for per-region reference data (products_eu, products_us). Reads from other regions pay a cross-region round-trip unless you add follower reads (AS OF SYSTEM TIME follower_read_timestamp()), which serve from the nearest replica at ~1 ms with bounded staleness.
• LOCALITY GLOBAL template. CREATE TABLE plans (tier STRING PK, monthly_cents INT8) LOCALITY GLOBAL; — replicated to every region and served from the nearest replica for reads; writes pay a cross-region round-trip. Ideal for slow-changing reference data (product catalogue, feature flags, currency conversion rates). Not for high-write workloads — every write is a global operation.
• Native changefeed template. SET CLUSTER SETTING kv.rangefeed.enabled = true; (once per cluster) then CREATE CHANGEFEED FOR TABLE orders INTO 'kafka://kafka:9092?topic_prefix=prod.' WITH format = 'avro', confluent_schema_registry = 'http://schema-registry:8081', resolved = '5s', updated, diff, initial_scan = 'yes'; — enables typed events, resolved-timestamp watermarks every 5 s, before-image on updates, and a one-time full-table bootstrap. This is the lakehouse-feed default. Never skip resolved if the downstream sink needs exactly-once ingest.
• Follower read snippet. SELECT ... FROM tbl AS OF SYSTEM TIME follower_read_timestamp() WHERE ...; — bounded-stale read (~4.8 s default) served from the nearest replica. Use for dashboards / feeds / list-views; never for read-your-own-write flows. For tighter staleness use AS OF SYSTEM TIME with_max_staleness('10s'). Every read-only multi-region query in your app should use this pattern; otherwise you pay leaseholder round-trip on every cross-region read.
• Postgres migration DDL diff. The five gaps: (a) SERIAL/SEQUENCE → UUID DEFAULT gen_random_uuid() on high-write tables; (b) CREATE TRIGGER ... → application-side callback (Rails before_save, Django save() override); (c) PL/pgSQL stored procedures → application service objects; (d) LISTEN/NOTIFY → native CREATE CHANGEFEED to Kafka; (e) custom types → JSONB or STRING with app-side validation. Dump the PG schema with pg_dump --schema-only, load into scratch CRDB, resolve every error before writing app code. Port 26257 (not 5432); driver stays the same.
• Survival goals matrix. SURVIVE ZONE FAILURE (default) = 3 replicas per range, cluster survives loss of one AZ, does NOT survive full region loss. SURVIVE REGION FAILURE = 5 replicas per range across 3+ regions, cluster survives loss of one full region. Cost of upgrading: 5/3 = 67% more storage, slightly higher write latency (some writes need cross-region Raft), noticeably higher inter-region network cost. Benefit: zero-op regional outage recovery. For any production multi-region deployment with SLA on regional outage: SURVIVE REGION FAILURE.
• Range hot-spot watchdog SQL. SELECT range_id, leaseholder, queries_per_second::INT AS qps, writes_per_second::INT AS wps, start_pretty, end_pretty FROM crdb_internal.ranges WHERE database_name = 'saas' ORDER BY qps DESC LIMIT 10; — top-10 hottest ranges by QPS. Add to a scheduled Prometheus job or a Grafana panel; alert if any single range exceeds 30% of cluster QPS. Auto-remediation: the allocator splits hot ranges; manual override via ALTER TABLE ... SPLIT AT VALUES (...) if you know the boundary.
• 40001 retry wrapper. Every ORM / DB helper must retry SQLSTATE 40001 (serialization_failure) with exponential backoff. Python example: @with_cockroach_retry decorator that opens with conn: inside a retry loop of up to 5 attempts, backoff 50 ms → 500 ms + jitter, retries only on psycopg2.errors.SerializationFailure, re-raises everything else. Ruby / Java / Node have analogous patterns. Skipping this turns normal contention into user-visible errors and violates serializability.
• Changefeed lag monitoring. SELECT job_id, description, high_water_timestamp::DECIMAL / 1e9 AS high_water_epoch, (extract(epoch FROM now()) - high_water_timestamp::DECIMAL / 1e9) AS lag_seconds FROM [SHOW CHANGEFEED JOBS] WHERE status = 'running'; — measures how far behind the changefeed is from live. Alert if lag_seconds > 60 for 5 minutes (changefeed falling behind). Runbook: check Kafka broker health, check schema-registry availability, look at crdb_internal.jobs for the changefeed's error field. Use WITH protect_data_from_gc_on_pause = 'yes' on any changefeed you'd ever pause for maintenance.
• Django / Rails ORM config. Django: ENGINE = 'django_cockroachdb', PORT = '26257', OPTIONS = {'sslmode': 'verify-full'}. Rails: adapter: cockroachdb, port: 26257, sslmode: verify-full, add activerecord-cockroachdb-adapter gem. Both frameworks handle 40001 retries via helper mixins; wrap every transactional service object. For SQLAlchemy: use the cockroachdb:// URL prefix or the sqlalchemy-cockroachdb dialect; retries wrap session.commit().
• Zone config inspection. After any multi-region DDL, run SHOW ZONE CONFIGURATION FROM TABLE orders; to see the generated zone config: num_replicas, constraints, lease_preferences. This is the audit trail — every DDL primitive translates to a zone config, and reading it back is the fastest way to verify your intent matches reality.

Frequently asked questions

What is CockroachDB in one sentence?

CockroachDB is a distributed, horizontally-scalable SQL database that speaks the PostgreSQL wire protocol on port 26257, stores every table's data as 512MB ranges replicated by Raft consensus across nodes in different failure domains, uses a leaseholder replica per range to serve strong-consistency (serializable) reads and coordinate writes, exposes multi-region primitives (REGIONAL BY ROW, SURVIVE REGION FAILURE, follower reads) as declarative DDL, and ships a native CREATE CHANGEFEED CDC pipeline whose resolved timestamps let downstream sinks convert at-least-once Kafka delivery into exactly-once lakehouse ingest — the four axes that senior data engineers evaluate (consistency, latency locality, Postgres compat depth, changefeed fit) all resolve into "distributed SQL primary for multi-region OLTP + reference data + lakehouse feed without Debezium." Every senior data engineering interview probes CockroachDB because it's the load-bearing distributed-database pattern for the modern multi-region SaaS stack.

CockroachDB vs Postgres — when do I pick each?

Default to Postgres for any single-region workload under ~5k TPS, any app that leans heavily on triggers / stored procedures / LISTEN/NOTIFY / SEQUENCE PKs, or any deployment where "one node with a fast fsync" is genuinely enough. Postgres is cheaper (one node vs three), faster per commit (single fsync vs Raft round-trip ~1.5–3× slower in single-region), and simpler operationally. Pick CockroachDB when you need multi-region OLTP with per-tenant home regions, zero-op regional outage recovery (SURVIVE REGION FAILURE), or a native changefeed feeding the lakehouse without Debezium. The migration cost from Postgres to CockroachDB is real — five known gaps (triggers, PL/pgSQL, LISTEN/NOTIFY, SEQUENCE, custom types) plus a 40001 retry helper — so don't migrate speculatively. The rule of thumb: if the words "multi-region" or "cross-continent" or "survive a region outage" appear in your requirements, CockroachDB starts winning; below that, Postgres is almost always the right answer.

What is a range and what is a leaseholder?

A range in CockroachDB is a contiguous key-space slice of approximately 512 MB — every table is split into ranges by primary key, and when a range exceeds ~512 MB it splits automatically. Ranges are the unit of replication (each range has 3 copies by default, 5 under SURVIVE REGION FAILURE) and the unit of rebalancing (the allocator moves ranges, not individual rows, to balance disk and load across nodes). The leaseholder is the one replica per range that holds the range lease at any given time — it's the only replica that can serve strong-consistency reads without a Raft round-trip (it knows it holds the lease; no other replica can hold it), and it's the coordinator that proposes writes to the Raft group for consensus. Leases can migrate — the multi-region DDL LOCALITY REGIONAL BY TABLE IN 'us-east1' emits a zone config pinning the leaseholder to us-east1 even if replicas span all three regions; this keeps writes coordinated in the home region and makes local writes commit at ~5 ms. Follower reads (AS OF SYSTEM TIME follower_read_timestamp()) bypass the leaseholder entirely for bounded-stale reads.

How do change feeds work and how are they different from Debezium?

CockroachDB change feeds are a native SQL primitive: SET CLUSTER SETTING kv.rangefeed.enabled = true then CREATE CHANGEFEED FOR TABLE orders INTO 'kafka://...' WITH format = 'avro', resolved = '5s', diff, initial_scan = 'yes' starts a background job that subscribes to internal rangefeeds (Raft-log-tapping subscriptions), emits per-key ordered INSERT/UPDATE/DELETE events plus periodic resolved-timestamp watermarks, and delivers them to Kafka, webhook, or cloud storage (S3/GCS/Azure) as JSON, Avro, or Parquet. Resolved timestamps let downstream sinks convert at-least-once Kafka delivery into exactly-once ingest — the sink buffers events, commits only when every partition has emitted a watermark past T, and deduplicates by (key, commit_ts). Compared to Debezium against Postgres logical replication, changefeeds require ~10× less configuration (one SQL statement vs wal_level=logical + REPLICATION role + PUBLICATION + replication slot + Debezium connector + schema-history topic + slot-lag monitoring), have no external JVM connector to manage, are cluster-native jobs monitored via SHOW CHANGEFEED JOBS, and are aware of the source's multi-region topology. Debezium remains the answer against Postgres primaries; changefeeds are the answer against CockroachDB primaries — same downstream contract (Kafka topics + resolved-ts / LSN watermarks), radically different operational surface.

What is REGIONAL BY ROW?

REGIONAL BY ROW is a CockroachDB table-locality clause that assigns each row's replicas to the region named by a designated column — typically region crdb_internal_region NOT NULL — so that a table can host data for multiple regions inside a single logical table while keeping each row's Raft quorum inside its own region. The DDL looks like CREATE TABLE tenants (id UUID PK DEFAULT gen_random_uuid(), region crdb_internal_region NOT NULL, name STRING NOT NULL) LOCALITY REGIONAL BY ROW AS region;; an INSERT that sets region = 'europe-west1' places the row's 5 replicas across the 3 regions (typically 2 in eu-west, 2 in us-east, 1 in ap-south under SURVIVE REGION FAILURE), pins the leaseholder to europe-west1, and lets EU app pods commit the row at ~5 ms via a local Raft quorum. Compared to per-region tables (orders_eu, orders_us, orders_ap) or application-level sharding, REGIONAL BY ROW collapses the whole geo-distribution story into one DDL clause — the schema stays unified, joins across regions still work (paying cross-region hops), and the CDC changefeed emits one topic per table rather than one per region. This is the primitive that makes CockroachDB the class leader for multi-tenant SaaS OLTP.

Is CockroachDB Postgres-compatible enough to migrate a real app?

Yes — for most well-written apps, CockroachDB is a drop-in replacement for Postgres at the wire layer, with five known gaps that must be resolved before cutover: (1) SERIAL/SEQUENCE PKs → replace with UUID DEFAULT gen_random_uuid() on any high-write table (SEQUENCE serialises writes into a single hot range and destroys throughput); (2) triggers → port to application-side callbacks (Rails before_save, Django save() override, or a CREATE CHANGEFEED for after-commit fan-out); (3) PL/pgSQL stored procedures → port to application service objects; (4) LISTEN/NOTIFY → replace with CREATE CHANGEFEED to Kafka + a websocket relay; (5) custom types → use JSONB or STRING with application-side validation. Everything else — JSONB, arrays, GIN (as INVERTED in CRDB), CTEs, window functions, foreign keys, prepared statements, ON CONFLICT, RETURNING, psycopg2/SQLAlchemy/Prisma/Hibernate/ActiveRecord — works verbatim on port 26257 with sslmode=verify-full. The migration playbook is: pg_dump --schema-only, load into scratch CRDB, resolve every error, port the five gaps, add a 40001 (serialization_failure) retry helper, dual-write shadow for 2 weeks, cutover with a gated 30-day rollback window. Migrating a Django app with no triggers takes about a sprint; migrating a legacy Rails app with heavy PL/pgSQL takes about 8 weeks. When the gaps are too painful (deep PL/pgSQL dependency), YugabyteDB — which reuses the Postgres query layer verbatim — is a better landing zone.

Practice on PipeCode

• Drill the SQL practice library → for the distributed-transaction, multi-region, changefeed, and Postgres-migration problems senior interviewers love.
• Rehearse on the design practice library → for the Raft, ranges, leaseholder, and SURVIVE REGION FAILURE scenarios that show up in senior distributed-database interviews.
• Sharpen the streaming axis with the streaming practice library → for the CockroachDB changefeed → Kafka → Iceberg exactly-once patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the CockroachDB axes (consistency, locality, PG compat, changefeed) against real graded inputs.

Lock in cockroachdb muscle memory

Docs explain the DDL. PipeCode drills explain the decision — when `REGIONAL BY ROW` earns its keep, when `SURVIVE REGION FAILURE` is worth the 5× storage, when a native changefeed beats Debezium, when Postgres is genuinely the better answer. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →