YugabyteDB Deep Dive: DocDB Storage, YSQL vs YCQL & Geo-Distributed Data

yugabytedb is the distributed-SQL database senior data engineers now reach for whenever a Postgres-shaped workload needs multi-region durability without a query rewrite — an Apache-2 open-source engine that reuses the upstream Postgres 15 parser, planner, and executor on top of a purpose-built distributed storage layer called DocDB, and simultaneously exposes a Cassandra-compatible NoSQL API called YCQL on the same tablets underneath. Every operational database mutation your business writes — an order status flip in us-east-1, a customer address change replicated to eu-west-1, a device metric batched into a wide-column time-series table — has to reach the right region, respect the placement policy your compliance team locked in, land under a consistent commit ordering across nodes, and survive an entire data-centre outage without a manual failover playbook. The engineering trade-off does not live in "do we need distributed SQL" — every multi-region OLTP stack eventually does — but in which distributed SQL engine you pick and how deeply it composes with the Postgres ecosystem you already rely on.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through DocDB — what makes YugabyteDB's storage layer different from Postgres or Cassandra", or "your DBA needs multi-region writes but the app was written for vanilla Postgres — how do you migrate", or "explain YSQL vs YCQL and when you'd pick each on the same cluster." It walks through the five load-bearing concepts — why yugabytedb matters against CockroachDB / TiDB / Postgres+Patroni, the DocDB storage layer (RocksDB tablets, Raft consensus, hybrid logical clocks), the YSQL Postgres-compat surface (parser reuse, extensions, colocated tables, pg_dump migration), the YCQL Cassandra-compat API (keyspaces, wide-column rows, LWT via Paxos, TTL), and the geo-distribution primitives (tablespaces, placement policies, read replicas, xCluster async) — plus the interview signals that separate architects who have run YugabyteDB in production from candidates who have only read the docs. 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 system design against the design practice library →, and sharpen the analytics axis with the aggregation practice library →.

---

On this page

• Why YugabyteDB matters for DE in 2026
• DocDB storage layer — RocksDB, tablets, Raft, HLC
• YSQL — Postgres compatibility at depth
• YCQL — Cassandra-compatible NoSQL API
• Geo-distribution and when YugabyteDB wins
• Cheat sheet — YugabyteDB recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why YugabyteDB matters for DE in 2026

Distributed SQL with deeper Postgres compatibility than any competitor, plus a Cassandra-compatible API on the same cluster

The one-sentence invariant: YugabyteDB is a horizontally-scalable, strongly-consistent distributed SQL database that reuses the upstream Postgres 15 parser, planner, and executor as its YSQL layer, exposes a Cassandra-compatible YCQL API on top of the same DocDB storage engine, spreads data as Raft-replicated tablets across nodes and regions with hybrid-logical-clock (HLC) ordering, and is licensed Apache 2 — which together make it the default answer whenever a Postgres workload outgrows a single primary and the team refuses to trade PG feature depth for a hand-rolled sharded architecture. Every senior interviewer in 2026 asks about YugabyteDB because it is the only distributed SQL engine that lets you migrate a real Postgres app with pg_dump and keep triggers, stored procedures, extensions, JSONB, and FDWs — while still surviving a full-region outage without a manual promotion.

The four axes interviewers actually probe.

• Postgres compatibility depth. YugabyteDB reuses the upstream Postgres source code for parsing, planning, and executing SQL — not a re-implementation, not a wire-compat shim, but the actual PG code linked into the YB-tserver process. That means triggers, stored procedures, extensions, materialised views, pg_dump/pg_restore, and driver ecosystems Just Work. CockroachDB re-implements PG semantics; TiDB uses MySQL wire; only YugabyteDB uses the Postgres codebase itself. This is the single biggest architectural distinction.
• DocDB storage engine. A purpose-built distributed document-oriented store based on a heavily-modified RocksDB, sharded into tablets, replicated by per-tablet Raft groups, and ordered by hybrid logical clocks. DocDB is the layer that both YSQL and YCQL sit on top of; understanding it is the load-bearing DE interview probe because it explains how transactions, indexes, and multi-region placement actually work.
• Dual API on one cluster. YSQL (Postgres wire) and YCQL (Cassandra CQL wire) can coexist in the same YugabyteDB cluster on the same DocDB tablets. Teams that inherited both Postgres and Cassandra workloads can consolidate onto one engine without giving up either API. No other distributed SQL engine offers this.
• Geo-distribution primitives baked in. Placement policy per tablespace, per-tablet leader placement, async read replicas per region, xCluster async cross-cluster replication, follower reads for low-latency stale reads. All part of the core product — no extra tier, no add-on. Multi-region OLTP is what YugabyteDB is designed for, not an afterthought.

The 2026 reality — YugabyteDB is one of four viable distributed SQL choices.

• YugabyteDB. Apache 2 OSS + YugabyteDB Aeon managed offering. Postgres-first (YSQL) with Cassandra-compat (YCQL) on the same cluster. Reuses upstream Postgres 15 code for SQL layer. Popular for teams migrating from Postgres → multi-region without rewriting queries. Netflix, GM, Robinhood, and several fintechs run it in production.
• CockroachDB. Postgres wire compatible but re-implements SQL semantics — no reuse of upstream PG code. No YCQL equivalent. BSL license (not fully OSS after 2023). Best-known distributed SQL brand; comparable to YugabyteDB on multi-region durability but shallower on PG feature parity.
• TiDB. MySQL wire compat, HTAP with TiFlash column store. Apache 2 license. Popular in China, Japan, and increasingly Europe. Different SQL dialect (MySQL, not PG); different architectural bet (row store + column replica for HTAP).
• Postgres + Patroni. Single-region highly-available Postgres via Patroni + Zookeeper/etcd. Not distributed SQL — writes still hit one primary. The default answer when you don't need multi-region writes; the wrong answer when you do.

What interviewers listen for.

• Do you name DocDB as the storage layer that sits under both YSQL and YCQL — not "YugabyteDB uses RocksDB directly"? — senior signal.
• Do you say "YugabyteDB reuses upstream Postgres 15 parser, planner, executor" in the first sentence about YSQL? — required answer.
• Do you name Raft consensus per tablet group as the replication primitive, not "sharding" or "eventual consistency"? — required answer.
• Do you name hybrid logical clocks (HLC) as the ordering primitive, not "wall-clock timestamps"? — senior signal.
• Do you distinguish YSQL vs YCQL as two APIs on the same DocDB, not as separate products? — required answer.
• Do you name tablespaces + placement policies as the geo-distribution primitive, not "regions"? — senior signal.

Worked example — the four-way comparison for a multi-region OLTP migration

Detailed explanation. The single most useful artifact for a YugabyteDB interview is a memorised 4-column comparison table across YugabyteDB, CockroachDB, TiDB, and Postgres+Patroni. Every senior distributed-SQL 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 Postgres app currently running on a single db.r6g.4xlarge RDS instance in us-east-1 that needs multi-region writes.

• Workload. ~300 tables, ~2 TB active data, ~2000 writes/sec, ~30k reads/sec. Uses triggers, pg_partman, pg_trgm, jsonb, and a couple of stored procedures.
• Requirement. Sub-100ms writes from us-west-2 and eu-west-1 while keeping us-east-1 primary.
• Constraint. The engineering team refuses to rewrite the ORM or the SQL — the app must keep talking Postgres wire.

Question. Build the four-way comparison for the migration candidates and pick the engine each requirement drives you toward.

Input.

Engine	SQL layer	PG feature depth	Geo-distribution	License
YugabyteDB	reuses PG 15 code	very deep (triggers, procs, extensions, FDW)	native (tablespaces + xCluster)	Apache 2
CockroachDB	re-implemented PG semantics	medium (no upstream PG code)	native	BSL (not full OSS)
TiDB	MySQL wire	N/A for this migration	native (Placement Rules)	Apache 2
Postgres + Patroni	vanilla PG	100% (it is PG)	none (single-region HA)	PostgreSQL

Code.

# YugabyteDB cluster deployment sketch — three regions, one universe
universe:
  name: prod-multiregion
  version: 2.20                           # current LTS as of 2026
  regions:
    - name: us-east-1
      zones: [us-east-1a, us-east-1b, us-east-1c]
      node_count: 3
    - name: us-west-2
      zones: [us-west-2a, us-west-2b, us-west-2c]
      node_count: 3
    - name: eu-west-1
      zones: [eu-west-1a, eu-west-1b, eu-west-1c]
      node_count: 3
  replication_factor: 3                   # per tablet: one replica per region
  placement:
    default:
      preferred_region: us-east-1         # tablet leaders default to us-east-1

Step-by-step explanation.

1. The comparison table forces the four axes into a single view: SQL-layer strategy, Postgres feature depth, native geo-distribution, and license. YugabyteDB wins on PG feature depth because it is upstream Postgres code; CockroachDB wins on brand recognition and mature managed offerings; TiDB wins on HTAP with TiFlash; Postgres+Patroni wins on "we don't need distributed writes."
2. For a Postgres migration with triggers, extensions, and stored procedures, YugabyteDB is the only engine that doesn't force a rewrite. CockroachDB's PG compat drops triggers and most extensions; TiDB is on MySQL wire so the ORM change alone is a two-quarter project.
3. The BSL license on CockroachDB matters for two teams in three: if your company forbids non-OSS databases in production, CockroachDB is off the table. Apache 2 (YugabyteDB, TiDB) and PostgreSQL license (vanilla PG) are the safe choices.
4. Geo-distribution on YugabyteDB is a first-class feature — CREATE TABLESPACE ... WITH (replica_placement = ...) is core DDL. Postgres+Patroni requires an outside layer (Bucardo, pgLogical, or a manual sharding library) to get anywhere close.
5. The default answer for our scenario is YugabyteDB: it accepts the existing pg_dump, keeps the triggers and extensions, gives you native multi-region writes, and remains Apache 2. Consider CockroachDB only if the shop already runs it; consider TiDB only if the shop is MySQL-first.

Output.

Requirement	Recommended engine	Why
Migrate PG app without query rewrite	YugabyteDB	Reuses upstream PG 15 code; pg_dump works
Sub-100ms writes from three regions	YugabyteDB or CockroachDB	Both offer native multi-region
Keep triggers + pg_partman + pg_trgm	YugabyteDB	Deepest PG extension compatibility
Full OSS license	YugabyteDB or TiDB	Apache 2; not BSL
Zero migration cost, single-region only	Postgres + Patroni	If multi-region isn't a requirement, don't over-engineer

Rule of thumb. Pick YugabyteDB when the migration source is Postgres, the destination is multi-region, and the team refuses to rewrite queries. Pick CockroachDB when brand and managed maturity outweigh license and PG-depth. Pick TiDB when MySQL-native and HTAP matter. Pick Postgres+Patroni when you don't actually need distributed writes.

Worked example — what interviewers actually probe on YugabyteDB

Detailed explanation. The senior data-engineering YugabyteDB interview has a predictable structure: the interviewer opens with an ambiguous question ("what's your take on distributed SQL in 2026?"), then progressively narrows to test whether you understand DocDB, HLC, and the YSQL/YCQL split. The candidates who name the pattern in sentence one score highest; the candidates who describe "another Postgres alternative" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you scale a Postgres app to multi-region writes?" — invites you to name YugabyteDB and DocDB.
• Follow-up 1. "Explain DocDB — how is it different from Postgres storage?" — probes the storage-layer knowledge.
• Follow-up 2. "How does YugabyteDB order transactions across regions?" — probes HLC understanding.
• Follow-up 3. "Why would you pick YCQL over YSQL on the same cluster?" — probes the dual-API story.
• Follow-up 4. "How does YugabyteDB compare to CockroachDB?" — probes competitive positioning.

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

Input.

Interview signal	Weak answer	Senior answer
Storage layer named	"it uses RocksDB"	"DocDB — RocksDB-based tablets, Raft-replicated, HLC-ordered"
Ordering primitive	"timestamps"	"hybrid logical clocks (HLC), physical + logical counter"
Postgres compat depth	"supports PG syntax"	"reuses upstream PG 15 parser + planner + executor code"
Dual API	"supports SQL and NoSQL"	"YSQL and YCQL are two wire protocols on top of the same DocDB tablets"
Multi-region primitive	"sharding"	"tablespaces with placement policies + per-tablet Raft leader pinning"

Code.

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

Minute 1 — name the pattern up front
  "I'd default to YugabyteDB for a multi-region Postgres migration —
   Apache 2 distributed SQL, reuses upstream PG 15 parser and planner
   as the YSQL layer, and runs on a purpose-built distributed storage
   engine called DocDB."

Minute 2 — DocDB explained
  "DocDB is a distributed document-oriented store built on a
   heavily-modified RocksDB. Data is sharded into tablets; each tablet
   is replicated by its own Raft group; the write path goes through
   Raft consensus and lands in RocksDB LSM. HLC — hybrid logical clock
   — gives a physical + logical counter timestamp that orders
   transactions across nodes without needing atomic clocks."

Minute 3 — YSQL depth
  "YSQL isn't a re-implementation of Postgres — it links the actual
   upstream Postgres 15 source code as the parser, planner, and
   executor inside the YB-tserver process. That means triggers,
   stored procedures, extensions (pg_partman, pg_trgm, PostGIS-lite),
   FDW, JSONB, materialised views — all inherited automatically.
   Migration from vanilla PG is pg_dump | ysqlsh."

Minute 4 — YCQL and the dual-API story
  "YCQL is the Cassandra-compatible API on the same DocDB. Keyspaces,
   tables with partition + clustering keys, wide-column rows, TTL,
   secondary indexes, and LWT (INSERT IF NOT EXISTS via Paxos). Teams
   with both PG and Cassandra workloads can consolidate onto one
   YugabyteDB cluster; both APIs read and write the same tablets
   underneath."

Minute 5 — geo-distribution + when YB wins
  "Geo-distribution primitives are first-class: CREATE TABLESPACE with
   a replica_placement JSON pins each tablet's leader to a region;
   read replicas per region give low-latency stale reads; xCluster
   async replication ships WAL from one universe to another for DR.
   YugabyteDB wins vs CockroachDB when PG depth matters, vs TiDB when
   Postgres wire matters, vs PG+Patroni when you actually need
   multi-region writes."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming both DocDB and "reuses upstream PG 15" signals you're a decision-maker who has read the docs and cross-referenced against CockroachDB and TiDB. Weak candidates say "it's like Postgres but distributed" and leave it there.
2. Minute 2 addresses the storage-layer axis. Interviewers want to hear "tablets + Raft + HLC + RocksDB" as four separate primitives, not "distributed RocksDB." The HLC callout is the senior differentiator because most candidates don't know YugabyteDB uses HLC (as opposed to CockroachDB's HLC-with-uncertainty-window or Spanner's TrueTime).
3. Minute 3 is the Postgres depth argument. The single most-important sentence is "reuses upstream Postgres 15 source code." Every other distributed SQL vendor re-implements PG semantics; YugabyteDB inherits them. This is why pg_partman works on YugabyteDB and doesn't work on CockroachDB.
4. Minute 4 addresses the dual-API question. Most interviewers assume YugabyteDB is only YSQL; naming YCQL and the "same tablets underneath" architecture shows you've dug past the marketing homepage.
5. Minute 5 wraps with the competitive positioning. Every senior interviewer wants to hear when YugabyteDB doesn't win (single-region → PG+Patroni is fine; MySQL shop → TiDB fits better). Refusing to over-sell YugabyteDB signals mature judgement.

Output.

Grading criterion	Weak score	Senior score
Names DocDB in minute 1	rare	mandatory
Names HLC as ordering primitive	rare	senior signal
Names "reuses upstream PG 15 code"	occasional	mandatory
Names YCQL alongside YSQL	rare	senior signal
Names tablespaces + placement	rare	senior signal
Refuses to over-sell YugabyteDB	rare	senior signal

Rule of thumb. The senior YugabyteDB answer is a 5-minute monologue that covers DocDB, HLC, YSQL depth, YCQL, and geo-primitives without waiting for the follow-ups. Rehearse it once; deploy it every time.

Senior interview question on YugabyteDB adoption

A senior interviewer often opens with: "You inherit a 2 TB single-region Postgres app running on RDS in us-east-1. Latency to European users is 120 ms; regulatory pressure requires the EU customer data to be stored in eu-west-1. Walk me through why YugabyteDB is a candidate, what changes on the app side, what the migration plan looks like, and when you'd choose not to migrate."

Solution Using a phased YugabyteDB migration with pg_dump bootstrap and tablespace placement

# Step 1 — deploy YugabyteDB universe (three regions, RF=3)
apiVersion: yugabytedb.com/v1alpha1
kind: YBUniverse
metadata:
  name: prod-migration
spec:
  version: 2.20
  replicationFactor: 3
  masterCount: 3
  tserverCount: 9        # 3 tservers per region
  regions:
    - name: us-east-1
      preferred: true    # tablet leaders default here
      zones: [us-east-1a, us-east-1b, us-east-1c]
    - name: us-west-2
      zones: [us-west-2a, us-west-2b, us-west-2c]
    - name: eu-west-1
      zones: [eu-west-1a, eu-west-1b, eu-west-1c]

# Step 2 — bootstrap schema from vanilla PG via pg_dump
pg_dump --schema-only --format=plain \
        --host=rds-primary.us-east-1.rds.amazonaws.com \
        --username=migration_user \
        production > schema.sql

# Step 3 — apply schema to YugabyteDB via ysqlsh (Postgres-compat client)
ysqlsh -h yb-tserver.prod.internal -U yugabyte -d production \
       -f schema.sql

# Step 4 — data load (per-table, parallel, deferred index build)
for tbl in orders customers events; do
    pg_dump --data-only --format=custom \
            --table=public.${tbl} \
            --host=rds-primary.us-east-1.rds.amazonaws.com \
            production | \
    ysqlsh -h yb-tserver.prod.internal -c \
           "SET yb_disable_transactional_writes = ON; \
            \\copy public.${tbl} FROM STDIN"
done

-- Step 5 — pin EU customer data to eu-west-1 via tablespace + placement
CREATE TABLESPACE eu_only WITH (
    replica_placement = '{
        "num_replicas": 3,
        "placement_blocks": [
            {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1},
            {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1b","min_num_replicas":1},
            {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1c","min_num_replicas":1}
        ]
    }'
);

-- Move only the EU rows to the EU-pinned tablespace via table partitioning
CREATE TABLE public.customers_eu PARTITION OF public.customers
    FOR VALUES IN ('EU') TABLESPACE eu_only;

Step-by-step trace.

Phase	Step	Reasoning
1	Deploy YB universe	Three regions, RF=3, one replica per region
2	pg_dump schema	Reuses PG 15 code → schema goes through unchanged
3	Apply via ysqlsh	Same wire protocol; extensions installed identically
4	Per-table data load	yb_disable_transactional_writes bulk-loads faster
5	Tablespace + placement	EU rows physically stored in eu-west-1
6	Cut over reads	App connects to YB via same PG driver
7	Cut over writes	Dual-write for one week; then flip DNS

After the migration, EU users hit eu-west-1 tservers directly and see sub-30ms writes on customers_eu rows; US users still hit us-east-1 and see the same low latency they had on RDS. Triggers and stored procedures ported by pg_dump fire correctly. The BI team's Snowflake sink continues to consume via the same PG logical replication protocol (YugabyteDB supports it as of 2.18+).

Output:

Metric	Before (RDS single region)	After (YugabyteDB three regions)
Write latency (EU user)	~120 ms (transatlantic hop)	~30 ms (in-region)
Write latency (US user)	~10 ms	~10 ms
Data residency (EU customer)	US soil	EU soil (compliant)
Failover on us-east-1 loss	manual promotion	automatic (Raft re-elects)
PG feature loss	none	none (upstream PG code preserved)
License	AWS-managed	Apache 2 self-hosted or Aeon-managed

Why this works — concept by concept:

• Reuses upstream Postgres 15 code — YSQL is not a re-implementation. The YB-tserver process links the actual Postgres 15 parser, planner, and executor as a library, so every SQL feature that works on vanilla PG 15 works on YugabyteDB. pg_dump output applies unchanged; extensions ship as ports of the upstream code.
• DocDB tablets + Raft per tablet — data is sharded into tablets (hash or range), each tablet has its own 3-node Raft group, and writes go through Raft consensus. This gives you the "one primary per shard, quorum writes" model without a single global leader — writes to different tablets can happen in parallel on different nodes.
• Tablespaces + placement_replica — CREATE TABLESPACE ... WITH (replica_placement = ...) pins tablets in that tablespace to specific regions. Combined with table partitioning, this lets you keep EU customer data on EU soil (a compliance requirement) while everything else stays global. No manual sharding needed.
• pg_dump | ysqlsh migration — the fastest migration path from vanilla PG. Because YSQL speaks PG wire and understands PG DDL natively, the ancient pg_dump tool works out of the box. yb_disable_transactional_writes speeds up bulk-load by skipping the distributed txn coordinator for the initial COPY.
• Cost — YugabyteDB Aeon managed pricing is comparable to RDS Multi-AZ at the same node count; self-hosted is cheaper but requires ops attention. Compared to CockroachDB, you pay ~0 extra for the PG feature depth. Compared to a hand-rolled Citus + logical replication solution, you save an engineer-year in ongoing maintenance. Net: one engineering quarter to migrate, zero ORM changes, ~30-50% reduction in cross-region latency for the affected user segment.

SQL
Topic — sql
SQL practice for distributed SQL migrations

Practice →

Design Topic — design Design problems on multi-region OLTP architecture

Practice →

---

2. DocDB storage layer — RocksDB, tablets, Raft, HLC

The single storage engine that sits underneath both YSQL and YCQL — sharded, quorum-replicated, and HLC-ordered

The mental model in one line: DocDB is YugabyteDB's distributed document-oriented storage engine — data is sharded into tablets (typically 8-32 per table by default), each tablet's data lives in a per-tablet RocksDB LSM store on disk, the tablet is replicated across replication_factor nodes via its own Raft group, transactions are ordered by hybrid logical clocks that combine wall-clock physical time with a Lamport-style logical counter, and both YSQL and YCQL SQL/CQL requests are ultimately translated into DocDB key-value operations against the tablet holding the requested key range. Every senior DE interview probes DocDB because understanding it explains how YugabyteDB gets its consistency, durability, and scale properties in one coherent story.

The four DocDB primitives to know cold.

• Tablets. A tablet is a hash-range or range-range shard of a table's rows. Each YSQL/YCQL table is split into N tablets (configurable via SPLIT INTO ... DDL or auto-split as the tablet grows past 10 GB). One tablet lives on multiple nodes as replicas; each replica writes into its own local RocksDB instance. Reads go to the tablet's Raft leader by default; follower reads are opt-in for stale-tolerant queries.
• RocksDB LSM per tablet. Every tablet has its own RocksDB instance on disk — separate SST files, separate memtable, separate write-ahead log. This isolation lets one hot tablet flush independently of cold tablets and lets tablet-split operations move files around without touching neighbours. YugabyteDB uses a heavily-modified RocksDB fork (MVCC extensions, split-friendly SST layout, custom compaction) called "YB-RocksDB" internally.
• Raft consensus per tablet group. Every tablet's replicas form their own Raft consensus group — leader + followers, ~3 nodes typically. Writes are proposed by the leader, replicated to followers, committed once a quorum ((N/2)+1) has ack'd. This gives you strong per-key durability without a single global leader; different tablets can commit independently on different nodes.
• Hybrid logical clock (HLC). Every commit is stamped with an HLC timestamp = (physical_ts, logical_counter). Physical time comes from NTP-synced wall clocks; the logical counter bumps to preserve causality when physical clocks are equal or move backwards. Compared to Google Spanner's TrueTime (which needs atomic clocks + GPS), HLC works on commodity hardware — the price is a slightly relaxed guarantee (no globally-monotonic timestamps, but causally-consistent ones).

The write path — end to end in eight steps.

• Step 1. Client sends INSERT/UPDATE/DELETE via YSQL wire (Postgres protocol) or YCQL wire (CQL protocol) to any YB-tserver.
• Step 2. YB-tserver's SQL layer parses/plans the statement (YSQL uses upstream PG 15 code; YCQL uses YugabyteDB's own CQL parser).
• Step 3. SQL layer resolves which DocDB tablet(s) hold the affected keys, using metadata cached from YB-master.
• Step 4. For a single-row single-tablet write, the tserver hosting the tablet leader is contacted; for multi-tablet writes, the distributed txn coordinator (a special role on some tservers) is engaged.
• Step 5. The tablet leader appends the write to its Raft log, replicates to followers, waits for a Raft quorum to ack.
• Step 6. On quorum, the leader applies the write to its RocksDB memtable and returns success upstream.
• Step 7. Followers apply the write to their memtables as they receive committed entries.
• Step 8. Background compaction eventually flushes memtable → SST files on each replica independently.

Common interview probes on DocDB.

• "How does YugabyteDB shard data?" — required answer: hash or range tablets, ~8-32 per table default, auto-split at ~10 GB, per-tablet RocksDB + Raft.
• "How does replication work?" — required answer: Raft consensus per tablet, (N/2)+1 quorum, leader on preferred region if placement configured.
• "How are transactions ordered?" — required answer: hybrid logical clocks (physical + logical counter); provides causal consistency without atomic clocks.
• "What happens when a tablet grows too large?" — auto-split at threshold; splits into two tablets, each with its own Raft group, replicas rebalance across nodes.
• "How does YugabyteDB survive a region outage?" — Raft leader election on the surviving replicas; if RF=3 across 3 regions, losing one region loses 1/3 replicas per tablet, quorum still available.

Worked example — tracing a write from YSQL to disk

Detailed explanation. The single most-useful mental model for DocDB is a step-by-step trace of a real INSERT from the moment it hits the YSQL wire to the moment it's durable on all Raft followers. Walk through a INSERT INTO orders (id, status) VALUES (42, 'pending') and every layer it traverses.

• Client. psql or a Postgres driver connects to any YB-tserver on port 5433.
• Tablet. orders has 24 tablets; row (id=42) hashes into tablet #17.
• Leader. Tablet #17's Raft leader lives on yb-tserver-4 in us-east-1a.
• Followers. Replicas on yb-tserver-7 (us-east-1b) and yb-tserver-11 (us-east-1c).

Question. Trace the write path in 8 explicit steps and identify which step provides which guarantee.

Input.

Layer	Component	Node
Client	psql	laptop
YSQL entry	YB-tserver PG layer	yb-tserver-1 (any tserver)
Metadata	YB-master (tablet map)	yb-master-1 (any master)
Tablet leader	Raft leader for tablet #17	yb-tserver-4 (us-east-1a)
Followers	Raft followers	yb-tserver-7, yb-tserver-11
Storage	RocksDB LSM per tablet	local disk on each replica

Code.

-- The write the client issues (YSQL wire, Postgres protocol)
INSERT INTO public.orders (id, customer_id, status, total_cents, updated_at)
VALUES (42, 7, 'pending', 1500, clock_timestamp());

Write path — 8 steps
====================

1. Client psql opens TCP conn to yb-tserver-1:5433 (Postgres wire).
2. YB-tserver-1's PG layer parses INSERT via upstream PG 15 code,
   plans it, hands off to DocDB layer as key-value writes.
3. DocDB layer computes DocKey for (id=42), consults tablet map
   cache: (id=42) hashes into tablet #17.
4. YB-tserver-1 issues gRPC to tablet #17's leader (yb-tserver-4).
5. yb-tserver-4 (leader) proposes the write to its Raft log,
   assigns HLC timestamp = (2026-07-27 10:15:22.345, logical=3).
6. Raft leader replicates log entry to yb-tserver-7 and yb-tserver-11.
7. Quorum ack: leader + one follower = 2 of 3 replicas → committed.
8. Leader applies to local RocksDB memtable, returns success to
   yb-tserver-1, which returns success to psql client.

Latency profile (in-region, healthy cluster):
  - psql → yb-tserver-1                 :  ~0.5 ms
  - yb-tserver-1 → yb-tserver-4 (gRPC)  :  ~0.3 ms
  - Raft propose + replicate + quorum   :  ~1.2 ms
  - RocksDB memtable insert             :  ~0.05 ms
  - Reply chain back to client          :  ~0.5 ms
Total end-to-end write latency          :  ~2.5 ms (single-shard)

Step-by-step explanation.

1. Steps 1-2: the entry point. YSQL is Postgres wire on port 5433 (not 5432, so PG and YB can coexist on the same host). The PG layer inside YB-tserver is literal upstream Postgres 15 code linked into the tserver process — same parser, same planner, same executor.
2. Step 3: tablet resolution. Each table has a stable partition function (default: hash of primary key modulo tablet count). The tserver caches the tablet map; on cache miss it consults YB-master. This is why "cold-cache first query" can be ~5 ms slower than steady state.
3. Steps 4-6: Raft. This is where the durability guarantee comes from. The leader must get a quorum ack before returning success. If two of three replicas ack, the write is committed even if the third replica is offline. This is the "one node can die, no data loss" invariant.
4. Step 5's HLC assignment: the leader stamps every Raft log entry with an HLC that is at least one tick larger than any HLC it has seen. This preserves causality — if txn A committed before txn B was received by any replica, A's HLC < B's HLC.
5. Step 8: apply. The memtable insert is O(log memtable size); background flush → SST is asynchronous. The write is durable-in-memory across quorum before the ack, and durable-on-disk after the next memtable flush + fsync on each replica (typically <30 seconds).

Output.

Step	Component	Guarantee
1-2	psql → YB-tserver PG layer	Postgres wire compatibility
3	DocKey resolution	Consistent hashing → same tablet always
4	Route to leader	Serialisable single-tablet writes
5	Raft propose + HLC	Causal ordering across nodes
6	Quorum replication	(N/2)+1 replicas ack before commit
7	Commit	Write is durable-in-memory across quorum
8	Apply to RocksDB memtable	Visible to subsequent reads

Rule of thumb. For any DocDB write-path question in an interview, walk the 8 steps out loud in order: client → tserver PG layer → DocKey resolution → route to leader → Raft propose with HLC → replicate to followers → quorum commit → apply to RocksDB. Naming HLC in step 5 is the single biggest senior signal.

Worked example — auto tablet split under load

Detailed explanation. DocDB tablets auto-split when they grow past a configurable threshold (default 10 GB). The split is online — no downtime — but understanding how it happens tells you a lot about the operational profile of YugabyteDB. Walk through an events table that starts as one tablet and grows to eight over a month.

• Threshold. --tablet_split_size_threshold_bytes (default 10 GiB).
• Split key. Midpoint of the tablet's key range (for range-partitioned) or hash midpoint (for hash-partitioned).
• Online. Writes continue to the pre-split tablet; the split happens in the background; reads switch to the new tablets once ready.
• Effect. Tablet count doubles for that table; new tablets get their own Raft groups; the two child tablets rebalance across nodes.

Question. Trace an events table growing from 1 → 2 → 4 → 8 tablets over a month, and quantify the operational impact at each split.

Input.

Time	Table size	Tablet count	Tablets per node (RF=3, 9 nodes)
Day 0	5 GB	1	1 leader + 2 followers = 3 replicas total
Day 7	12 GB	2 (first split fired)	2 leaders + 4 followers = 6 replicas
Day 15	45 GB	4	4 leaders + 8 followers = 12 replicas
Day 30	180 GB	8	8 leaders + 16 followers = 24 replicas

Code.

-- Pre-split at CREATE time (skip the first few auto-splits by declaring intent)
CREATE TABLE public.events (
    id          UUID PRIMARY KEY,
    device_id   BIGINT NOT NULL,
    event_ts    TIMESTAMPTZ NOT NULL,
    event_type  TEXT NOT NULL,
    payload     JSONB
) SPLIT INTO 8 TABLETS;

-- Manually split a specific tablet (rare — usually auto is fine)
-- Find the tablet ID first via:
--   SELECT * FROM yb_table_properties('public.events'::regclass);

-- Inspect tablet sizes
SELECT
    table_name,
    tablet_id,
    pg_size_pretty(sst_files_size) AS sst_size,
    replica_count,
    leader_node
FROM   yb_local_tablets                     -- YSQL system view
WHERE  table_name = 'events';

# Auto-split tuning (yb-tserver flags)
--tablet_split_size_threshold_bytes: 10737418240   # 10 GiB (default)
--enable_automatic_tablet_splitting: true
--tablet_split_low_phase_shard_count_per_node: 8   # split more aggressively when few tablets
--tablet_split_high_phase_shard_count_per_node: 24 # slow splits when many tablets per node

Step-by-step explanation.

1. Day 0: single tablet, single Raft group of 3 replicas. All writes serialise through one leader. This is the "cold table" state — fine for small tables, a bottleneck at scale.
2. Day 7: the tablet crosses 10 GB. Auto-split fires: YB-master picks the split key (hash midpoint), coordinates a split at each replica, creates two child tablets, drops the parent. The split takes ~30 seconds; writes continue to the parent during the split, then flip to the correct child.
3. Day 15: four tablets. Now writes to four different key ranges can commit in parallel — write throughput has 4x'd. Leaders may rebalance across nodes to spread load evenly (YB-master's load balancer runs continuously).
4. Day 30: eight tablets. With 9 nodes and RF=3, each node hosts ~2-3 tablet replicas (either leader or follower). Read throughput scales linearly with tablet count as long as queries hit different tablets.
5. Pre-splitting via SPLIT INTO N TABLETS at CREATE time avoids the "one tablet bottleneck" for tables you know will grow. Rule of thumb: for a table expected to exceed 100 GB in the first year, pre-split into max(3, number_of_nodes) tablets.

Output.

Time	Tablets	Write parallelism	Read parallelism	Splits fired
Day 0	1	serial (1 leader)	serial (1 leader)	0
Day 7	2	2x	2x	1
Day 15	4	4x	4x	3 total
Day 30	8	8x	8x	7 total

Rule of thumb. Never leave a fast-growing table on a single tablet. Either pre-split via SPLIT INTO N TABLETS at CREATE time (fastest) or rely on auto-split (fine for gradual growth). Monitor tablet size via yb_local_tablets — any tablet over 20 GB indicates auto-split thresholds are too high for your workload.

Senior interview question on DocDB internals

A senior interviewer might ask: "Walk me through what happens when the Raft leader for a hot tablet dies mid-write. Cover leader election, the fate of the in-flight write, and the client-visible latency spike. Then extend to the case where an entire region is lost and you need multi-region write recovery."

Solution Using Raft leader election, WAL replay, and multi-region quorum

Fault: leader for tablet #17 dies at t=100ms into a 3-node
       single-region Raft group (leader on us-east-1a, followers
       on us-east-1b and us-east-1c).

Sequence of events:

t=0     Leader receives INSERT, appends to local Raft log,
        proposes replication to followers.
t=50    Leader dies (kernel panic, hardware failure, kill -9).
t=50-2000 (up to ~2 seconds by default)
        Followers notice missing heartbeats from leader.
        Election timeout expires (randomised, 1.5-3s typically).
t=2100  A follower (say us-east-1b) transitions to CANDIDATE,
        bumps its Raft term, requests votes.
t=2150  us-east-1c votes for the candidate; quorum reached
        (candidate + 1 vote = 2 of 3 remaining nodes).
t=2200  us-east-1b becomes leader; replays any uncommitted
        Raft log entries from before the crash.
t=2300  Client sees the write either fail (retry needed) or
        succeed (if it was already quorum-committed before crash).

-- Multi-region setup with RF=3 across 3 regions
-- Now losing an entire region loses 1 of 3 replicas per tablet.
-- Quorum = 2 of 3 → still available.

CREATE TABLESPACE global WITH (
    replica_placement = '{
        "num_replicas": 3,
        "placement_blocks": [
            {"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
            {"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1},
            {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1}
        ]
    }'
);

# Client-side retry pattern for Raft leader-election spikes
import psycopg2
import time
from psycopg2 import errors as pg_errors

def with_retry(func, max_attempts=5, backoff_base=0.1):
    """Retry pattern for transient YugabyteDB errors (leader election, etc)."""
    for attempt in range(max_attempts):
        try:
            return func()
        except (pg_errors.SerializationFailure,
                pg_errors.DeadlockDetected,
                pg_errors.OperationalError) as e:
            if attempt == max_attempts - 1:
                raise
            time.sleep(backoff_base * (2 ** attempt))    # exponential backoff
    return None

Step-by-step trace.

Time	Event	Client-visible effect
t=0	Client issues INSERT	Latency clock starts
t=50ms	Leader dies mid-write	Client's inflight query hangs
t=50-2100ms	Election timeout	Client sees connection timeout OR retries
t=2100-2200ms	New leader elected	Client retry lands on new leader
t=2200-2300ms	New leader replays log	Retry either succeeds or receives failure
t=~2500ms	Full recovery	Steady state resumed

For multi-region RF=3 with one replica per region, losing an entire region drops one Raft follower per tablet — quorum (2 of 3) remains achievable, so writes continue with elevated latency (the two surviving replicas must ack; one may be far away). Client-side retries with exponential backoff mask the leader-election spike from most users.

Output:

Failure mode	Data loss	Client-visible spike	Recovery
Single tserver crash	none (quorum ack)	~2-3 s election	automatic
Full-region loss (RF=3, 3 regions)	none (2 of 3 replicas)	~2-3 s election + higher latency	automatic
Two-region loss (RF=3)	writes stall (no quorum)	indefinite until region returns	manual intervention
Complete cluster loss	recover from backup	outage until restore	full DR

Why this works — concept by concept:

• Raft consensus per tablet — every tablet is its own consensus group of replication_factor replicas. Losing the leader triggers an election among the surviving replicas; the new leader replays any uncommitted log entries and continues serving writes. This isolates failures to per-tablet scope; unrelated tablets keep serving traffic unaffected.
• (N/2)+1 quorum — for RF=3, quorum is 2 of 3. Losing one replica is tolerated (writes continue); losing two blocks writes. Placing one replica per region across 3 regions means one region loss = one replica loss = quorum still available.
• HLC preserves causality across the election — the new leader's HLC picks up where the old leader's left off (or one tick higher), so no committed write ever loses its ordering position across a leader change.
• Client-side retry pattern — SerializationFailure, DeadlockDetected, and OperationalError are the three exceptions psycopg2 will surface during a leader election. Wrapping DB calls in with_retry with exponential backoff hides the spike from application code.
• Cost — one election window per failure = 2-3 s of elevated latency for the affected tablets. If failures are rare (~monthly per node), this is well within any SLO. The eliminated cost is the manual failover playbook — no on-call engineer paging in, no promotion script, no split-brain risk. Net: O(1) client-visible latency spike per failure, O(0) engineer time.

Design
Topic — design
Design problems on distributed storage engines

Practice →

SQL Topic — sql SQL practice for distributed transactions

Practice →

---

3. YSQL — Postgres compatibility at depth

ysql reuses upstream Postgres 15 source code — parser, planner, executor, extensions — as a library linked into every YB-tserver

The mental model in one line: YSQL is not a Postgres-flavoured re-implementation of SQL — it is the actual upstream Postgres 15 source tree patched to route storage calls to DocDB instead of local heap files, and linked into every YB-tserver process, which means every SQL feature that works on vanilla Postgres 15 (triggers, stored procedures, extensions, foreign data wrappers, JSONB, materialised views, functions in PL/pgSQL, LISTEN/NOTIFY) works on YugabyteDB — with the only exception being features that assume a single-node local disk (e.g. pg_prewarm, physical replication). This is the load-bearing architectural distinction between YugabyteDB and every other Postgres-shaped distributed SQL: CockroachDB re-implements PG semantics from scratch, TiDB is on MySQL, but YugabyteDB inherits Postgres feature parity by construction.

The four YSQL primitives interviewers probe.

• Wire compat. YSQL listens on port 5433 (not 5432 — allows PG and YB to coexist). Any Postgres driver connects unchanged: psql, psycopg2, JDBC, node-postgres, Rails, Django, SQLAlchemy. Query results, error codes, and prepared-statement behaviour all match upstream PG 15.
• Feature depth. Because YSQL links upstream PG 15 code, features work by inheritance rather than re-implementation. Triggers (BEFORE/AFTER/INSTEAD OF, FOR EACH ROW/STATEMENT), PL/pgSQL stored procedures, materialised views, LATERAL joins, GROUPING SETS, WITH RECURSIVE, JSONB operators, pg_trgm, pg_partman, pg_stat_statements, PostGIS-lite (via postgis extension port), FDWs — all present.
• Distributed-native indexes. YSQL indexes are LSM-based inside DocDB, not B-trees. This is a physical difference; logically they behave identically to Postgres B-tree indexes for SELECT/UPDATE/DELETE. Covering indexes, expression indexes, partial indexes, and unique indexes all work.
• Migration story. pg_dump --schema-only | ysqlsh bootstraps the schema; pg_dump --data-only | ysqlsh --set=yb_disable_transactional_writes=on bulk-loads data at 10-50 MB/s per parallel stream. Applications reconnect with the same connection string (port 5433 instead of 5432).

What YSQL does not inherit from upstream Postgres.

• Physical replication. No pg_basebackup, no physical streaming replication. YugabyteDB replaces this with Raft per tablet — a fundamentally different model.
• pg_prewarm, shared_buffers tuning. DocDB has its own block-cache (--memory_limit_hard_bytes tserver flag); PG shared_buffers is not the caching layer that matters.
• Certain extensions. Extensions that patch the executor deeply (e.g. Citus, pglogical) don't apply — YugabyteDB is the distribution layer, and duplicates them.
• Sequences with cache=1 semantics. Distributed sequences serialise through YB-master; using CACHE 100 or CACHE 1000 is strongly recommended to avoid per-INSERT round trips.

The pg_dump | ysqlsh migration recipe.

• Step 1. pg_dump --schema-only --format=plain from source PG.
• Step 2. Edit the dump if needed — mostly rewriting CREATE INDEX to CREATE INDEX ... USING lsm (though YSQL accepts USING btree and internally uses LSM anyway).
• Step 3. Apply schema to YugabyteDB via ysqlsh -f schema.sql.
• Step 4. Load data per table in parallel with \copy ... FROM STDIN and SET yb_disable_transactional_writes = ON for bulk speed.
• Step 5. Re-enable transactional writes; run ANALYZE on every table.
• Step 6. Point application connection strings at YugabyteDB port 5433.

Colocated tables — the small-table optimisation.

• The problem. Many Postgres apps have hundreds of tables, most small. Sharding each into 8 tablets = thousands of tablets = high metadata overhead.
• The fix. Colocated tables share one tablet with the rest of the colocated group. Reads and writes to colocated tables go to the same tablet leader — cheap, no distributed txn overhead.
• The DDL. CREATE DATABASE ... WITH COLOCATION = true; at DB creation; then every table in that DB is colocated by default (opt out with WITH (colocated = false)).
• The trade-off. Colocated tables don't scale writes beyond one tablet leader — fine for tables under ~100 GB, bad for growth to TB scale.

Common interview probes on YSQL.

• "How does YSQL compare to CockroachDB's Postgres compat?" — required answer: YSQL is upstream PG code; CRDB re-implements semantics.
• "Can I use pg_partman on YugabyteDB?" — yes; YSQL supports extension ports.
• "How do I migrate from vanilla Postgres?" — pg_dump | ysqlsh for schema + data; app reconnects on port 5433.
• "What are colocated tables?" — small-table optimisation; multiple tables share one tablet's Raft group.

Worked example — migrating a 200-table Postgres schema via pg_dump

Detailed explanation. The canonical Postgres → YugabyteDB migration: pg_dump the schema and data from vanilla PG, apply to YugabyteDB via ysqlsh, and reconnect the app. Walk through a realistic ~200-table schema with triggers, stored procedures, and pg_partman.

• Source. PostgreSQL 15 on RDS, 200 tables, 500 GB data.
• Destination. YugabyteDB 2.20, 9-tserver cluster in three regions.
• Extensions. pg_partman, pg_trgm, pg_stat_statements, pgcrypto.
• Downtime target. < 30 min for cutover (dual-write during migration).

Question. Write the migration recipe as a runnable script and estimate the wall-clock cost.

Input.

Component	Count / Size
Tables	200
Data	500 GB
Triggers	45
Stored procedures	12
Extensions	4
Estimated migration time	3-6 hours for data + 15 min schema

Code.

#!/usr/bin/env bash
set -euo pipefail

SOURCE_HOST="rds-primary.us-east-1.rds.amazonaws.com"
SOURCE_DB="production"
SOURCE_USER="migration_user"

TARGET_HOST="yb-tserver.prod.internal"
TARGET_DB="production"
TARGET_USER="yugabyte"

# 1. Dump schema (DDL only)
pg_dump --schema-only \
        --no-owner --no-privileges \
        --format=plain \
        --host="$SOURCE_HOST" --username="$SOURCE_USER" \
        "$SOURCE_DB" > /tmp/schema.sql

# 2. Install extensions on YugabyteDB (must precede schema apply)
ysqlsh -h "$TARGET_HOST" -U "$TARGET_USER" -d "$TARGET_DB" <<SQL
CREATE EXTENSION IF NOT EXISTS pg_partman;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SQL

# 3. Apply schema
ysqlsh -h "$TARGET_HOST" -U "$TARGET_USER" -d "$TARGET_DB" \
       -v ON_ERROR_STOP=1 \
       -f /tmp/schema.sql

# 4. Dump + load data per table, in parallel (say 8 workers)
tables=$(ysqlsh -h "$SOURCE_HOST" -U "$SOURCE_USER" -d "$SOURCE_DB" -tAc \
        "SELECT tablename FROM pg_tables WHERE schemaname='public'")

echo "$tables" | xargs -n 1 -P 8 -I {} bash -c '
    pg_dump --data-only --format=custom --table=public.{} \
            --host="'$SOURCE_HOST'" --username="'$SOURCE_USER'" \
            '$SOURCE_DB' | \
    pg_restore --host="'$TARGET_HOST'" --username="'$TARGET_USER'" \
               --dbname="'$TARGET_DB'" --no-owner --no-privileges
'

# 5. ANALYZE every table so the YSQL planner has statistics
ysqlsh -h "$TARGET_HOST" -U "$TARGET_USER" -d "$TARGET_DB" -c "ANALYZE VERBOSE;"

# 6. Sanity check
ysqlsh -h "$TARGET_HOST" -U "$TARGET_USER" -d "$TARGET_DB" -c "
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 20;
"

-- Optional colocation for the ~150 small tables (< 100 GB expected each)
-- Do this at CREATE DATABASE time BEFORE applying schema
CREATE DATABASE production_colocated WITH COLOCATION = true;

-- Then in the schema apply, override colocation on the big tables:
CREATE TABLE public.events (
    id UUID PRIMARY KEY,
    ...
) WITH (colocated = false)
  SPLIT INTO 8 TABLETS;

Step-by-step explanation.

1. Step 1 dumps the schema with --schema-only --no-owner --no-privileges. no-owner/no-privileges matter because RDS-specific role names won't exist on YugabyteDB; skip them and grant separately.
2. Step 2 installs extensions before applying the schema. Any table that uses pg_trgm operators in an index definition will fail to create if the extension isn't already loaded.
3. Step 3 applies the schema. ON_ERROR_STOP=1 halts on the first DDL error — critical during migration so you don't ship half a schema and think it succeeded.
4. Step 4 parallelises data load across tables with xargs -P 8. Each worker pipes pg_dump --data-only into pg_restore (or psql). For 500 GB across 200 tables, 8 workers finish in ~4 hours on a 3-region YugabyteDB cluster.
5. Step 5 (ANALYZE) populates pg_class.reltuples and pg_statistic so the YSQL query planner picks correct plans. Without this, every SELECT starts with default statistics and picks bad plans until the auto-analyze daemon catches up.
6. The colocation optimisation is optional but valuable for schemas with many small tables. It saves tablet-metadata overhead and keeps small-table reads/writes local to one tablet leader.

Output.

Migration phase	Wall-clock time	Downtime
Schema dump + apply	~15 min	0 (target is empty)
Data load (500 GB, 8 workers)	~4 h	0 (dual-write in parallel)
ANALYZE	~10 min	0
App cutover (connection string flip)	~5 min	~5 min
Total	~4.5 h	~5 min

Rule of thumb. For any Postgres → YugabyteDB migration, use pg_dump --schema-only --no-owner --no-privileges, install extensions before applying schema, parallelise data load per-table with xargs -P 8, and run ANALYZE before pointing traffic. Dual-write for a week before flipping DNS gives you a rollback lane.

Worked example — colocated tables for a small-table-heavy schema

Detailed explanation. A typical enterprise Postgres schema has ~200 tables where 90% are lookup / config / small-reference tables under 100 MB, and 10% are the big transactional tables (orders, events, logs). Sharding every small table into 8 tablets means 200 * 8 = 1600 tablets, most of them nearly empty. Colocation groups all the small tables into one shared tablet, dropping metadata overhead by 10x.

• Colocated tables. Share one tablet's Raft group across many tables. Multi-table transactions inside the colocation group avoid the distributed txn coordinator entirely.
• When to use. Small (< 100 GB expected), read-heavy, transactional (need cross-table foreign keys with fast joins).
• When not to use. Tables expected to grow past ~100 GB or with write hotspots — they need their own tablet(s).

Question. Design the colocation strategy for the 200-table migration: which tables go colocated, which get their own tablets.

Input.

Table class	Count	Size each	Colocation?
Lookup / config	80	< 10 MB	yes
Small transactional (customers, addresses, phone_numbers)	100	10 MB - 1 GB	yes
Medium (products, orders, shipments)	15	1 - 50 GB	maybe (join workload)
Large (events, audit_log, sensor_readings)	5	> 100 GB	no — own tablets

Code.

-- 1. Create the colocated DB (must happen at CREATE DATABASE)
CREATE DATABASE production_colo WITH COLOCATION = true;

\c production_colo;

-- 2. Small + medium tables — inherit colocation (default = true in this DB)
CREATE TABLE public.customers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country CHAR(2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE TABLE public.addresses (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT REFERENCES public.customers(id),
    line1 TEXT NOT NULL,
    postcode TEXT NOT NULL
);

CREATE TABLE public.products (
    id BIGSERIAL PRIMARY KEY,
    sku TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    price_cents BIGINT NOT NULL
);

-- 3. Large tables — override colocation, get their own tablets
CREATE TABLE public.events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id BIGINT NOT NULL,
    event_ts TIMESTAMPTZ NOT NULL,
    payload JSONB
) WITH (colocated = false)
  SPLIT INTO 16 TABLETS;

CREATE TABLE public.audit_log (
    id BIGSERIAL PRIMARY KEY,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    table_name TEXT NOT NULL,
    op CHAR(1) NOT NULL,
    row_id BIGINT NOT NULL,
    before JSONB,
    after JSONB
) WITH (colocated = false)
  SPLIT INTO 8 TABLETS;

-- 4. Verify colocation
SELECT relname, reloptions FROM pg_class
WHERE relnamespace = 'public'::regnamespace
  AND relkind = 'r'
ORDER BY relname;

Step-by-step explanation.

1. CREATE DATABASE ... WITH COLOCATION = true sets the DB-level default. Any table created without an explicit WITH (colocated = ...) clause is colocated. This makes migration trivial — the pg_dump output creates 200 tables and 195 of them are colocated by default.
2. Small and medium tables (customers, addresses, products) share one tablet's Raft group. Cross-table joins like customers JOIN addresses ON customer_id execute inside a single tablet — no distributed txn overhead, ~1ms latency versus ~5-10ms for a cross-tablet join.
3. The big tables (events, audit_log) opt out with WITH (colocated = false) and pre-split into their own tablet counts (SPLIT INTO 16 TABLETS for events, SPLIT INTO 8 TABLETS for audit_log). These scale write throughput independently.
4. Colocated tables give up horizontal write scaling in exchange for lower operational overhead. If a table grows past ~100 GB, migrate it out of colocation by CREATE TABLE ... WITH (colocated = false) in a new table + INSERT INTO ... SELECT + rename.
5. The tradeoff to explain to the DBA: colocated write throughput is capped at ~5-10k writes/sec (single tablet leader). Everything above that needs its own tablets. Configuration-heavy schemas (lookups, reference data) are ideal candidates; append-heavy tables are not.

Output.

Table set	Tablet count	Write pattern	Read latency
195 colocated tables	1 shared tablet	serial on one leader	~1 ms in-region
events (own tablets)	16 tablets	16x parallel	~2-3 ms
audit_log (own tablets)	8 tablets	8x parallel	~2-3 ms
Metadata overhead	~24 tablets total	dramatically lower vs 1600	negligible

Rule of thumb. Colocate tables that are small (< 100 GB), read-heavy, and often joined to each other; give each big or write-heavy table its own tablets via WITH (colocated = false) + SPLIT INTO N TABLETS. The rough guideline: any table under 100 GB with < 5000 writes/sec is a colocation candidate.

Senior interview question on YSQL migration

A senior interviewer might ask: "A team has a 300-table Postgres 15 app on RDS with 800 GB of data, 60 triggers, and 20 stored procedures. Design the YSQL migration plan, quantify the downtime, and identify the two most likely failure modes during the cutover."

Solution Using pg_dump bootstrap + logical replication catch-up + connection-string cutover

#!/usr/bin/env bash
# YSQL migration — schema + bulk load + logical CDC catch-up + cutover
set -euo pipefail

SOURCE_HOST="rds-primary.us-east-1.rds.amazonaws.com"
TARGET_HOST="yb-tserver.prod.internal"
SOURCE_DB="production"
TARGET_DB="production"

# ─────────────────────────────────────────────────────────────
# Phase 1 — schema dump + apply (T-24h)
# ─────────────────────────────────────────────────────────────
pg_dump --schema-only --no-owner --no-privileges \
        --host="$SOURCE_HOST" --username=migration_user \
        "$SOURCE_DB" > /tmp/schema.sql

ysqlsh -h "$TARGET_HOST" -U yugabyte -d "$TARGET_DB" \
       -c "CREATE EXTENSION IF NOT EXISTS pg_partman;
           CREATE EXTENSION IF NOT EXISTS pg_trgm;
           CREATE EXTENSION IF NOT EXISTS pgcrypto;"

ysqlsh -h "$TARGET_HOST" -U yugabyte -d "$TARGET_DB" \
       -v ON_ERROR_STOP=1 -f /tmp/schema.sql

# ─────────────────────────────────────────────────────────────
# Phase 2 — snapshot bulk load (T-20h, ~4-6h wall clock)
# ─────────────────────────────────────────────────────────────
pg_dump --data-only --format=directory --jobs=8 \
        --host="$SOURCE_HOST" --username=migration_user \
        --file=/tmp/data "$SOURCE_DB"

pg_restore --jobs=8 --host="$TARGET_HOST" --username=yugabyte \
           --dbname="$TARGET_DB" --no-owner --no-privileges /tmp/data

ysqlsh -h "$TARGET_HOST" -U yugabyte -d "$TARGET_DB" -c "ANALYZE;"

# ─────────────────────────────────────────────────────────────
# Phase 3 — logical replication catch-up (T-14h to T-0)
# ─────────────────────────────────────────────────────────────
# On source Postgres:
#   CREATE PUBLICATION yb_migration_pub FOR ALL TABLES;
# On YugabyteDB (subscribing side):
#   CREATE SUBSCRIPTION yb_sub CONNECTION '...' PUBLICATION yb_migration_pub;
# YugabyteDB 2.18+ supports being a logical-replication subscriber.

ysqlsh -h "$TARGET_HOST" -U yugabyte -d "$TARGET_DB" <<SQL
CREATE SUBSCRIPTION yb_migration_sub
  CONNECTION 'host=$SOURCE_HOST port=5432 dbname=$SOURCE_DB user=migration_user'
  PUBLICATION yb_migration_pub
  WITH (copy_data = false);   -- data already loaded via pg_dump
SQL

# ─────────────────────────────────────────────────────────────
# Phase 4 — cutover window (T=0, ~5 min downtime)
# ─────────────────────────────────────────────────────────────
# 1. Stop application writes to source Postgres
# 2. Wait ~30s for logical replication to catch up
# 3. Verify: SELECT max(updated_at) matches on both sides
# 4. Flip application connection string from RDS to YB
# 5. Drop subscription on YB

-- Post-cutover verification queries
SELECT 'source' AS side, tablename, n_live_tup
FROM   pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC LIMIT 20;

-- Compare to YB
SELECT 'yb' AS side, tablename, n_live_tup
FROM   pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC LIMIT 20;

Step-by-step trace.

Phase	Duration	Downtime	Rollback
Phase 1 — schema	~15 min	0	drop DB on target
Phase 2 — bulk load	~4-6 h	0	truncate + reload
Phase 3 — logical rep catch-up	continuous	0	drop subscription
Phase 4 — cutover	~5 min	~5 min	flip DNS back to RDS
Total	~5-7 h	~5 min	per-phase

After the plan, YugabyteDB serves all live traffic starting at T=0, logical replication is torn down, and RDS is kept warm for one week as a rollback lane. Triggers and stored procedures ported by pg_dump fire correctly; the pg_partman daemon runs on the YB side to maintain partitions.

Output:

Metric	Value
Total wall-clock	~5-7 hours
Actual downtime	~5 minutes (cutover window)
Data drift risk	~30s (logical rep lag at cutover)
Rollback cost	~5 min DNS flip; RDS kept warm for a week
Failure mode 1	Schema apply fails (missing extension port)
Failure mode 2	Logical rep lag exceeds 30s at cutover

Why this works — concept by concept:

• pg_dump | ysqlsh bootstrap — the fastest bulk-load path from vanilla PG. pg_dump --data-only --format=directory --jobs=8 parallelises the dump; pg_restore --jobs=8 parallelises the load. Because YSQL is upstream PG code, no format conversion is needed.
• Logical replication for catch-up — YugabyteDB 2.18+ supports being a Postgres logical-replication subscriber. This closes the gap between "snapshot load complete" and "cutover moment" so downtime shrinks from hours to minutes. copy_data = false skips re-copying data already loaded via pg_dump.
• Extension pre-install — extensions like pg_partman and pg_trgm must be created before the schema apply, or DDL that references extension operators fails. YugabyteDB ships ported versions of the most common extensions; verify each one exists before starting.
• ANALYZE after bulk load — the YSQL planner uses PG's statistics infrastructure. Without ANALYZE, table row counts are unknown and query plans are wrong. Run ANALYZE after every bulk-load, and let auto-analyze take over from there.
• Cost — one migration engineer for ~1 week (planning, dry-runs, cutover), ~5 min of actual downtime, ~$0 in extra infrastructure (YB cluster runs alongside RDS during migration). The eliminated cost is a query rewrite project (weeks per team) or a re-architecture project (months). Compared to CockroachDB migration, YugabyteDB saves you the "fix broken triggers" phase entirely because the triggers just work.

SQL
Topic — sql
SQL practice for Postgres-compat distributed databases

Practice →

Design Topic — design Design problems on database migration planning

Practice →

---

4. YCQL — Cassandra-compatible NoSQL API

ycql is the CQL wire on top of the same DocDB — pick it for wide-column, TTL, and Cassandra migrations without leaving the YugabyteDB cluster

The mental model in one line: YCQL is a Cassandra-compatible NoSQL API — CQL syntax, keyspaces, tables with partition + clustering keys, wide-column rows, secondary indexes, native TTL, and lightweight transactions via Paxos — that sits on the same DocDB storage engine as YSQL, meaning a single YugabyteDB cluster can serve both Postgres-shaped OLTP and Cassandra-shaped wide-column workloads without running two databases, without cross-database ETL, and (crucially) without the operational complexity of maintaining a separate Cassandra ring. Every senior DE at a company that inherited both Postgres and Cassandra should know YCQL because it unlocks a consolidation story that saves both an entire database tier and a full ops team.

The four YCQL primitives.

• Keyspaces + tables. Same as Cassandra — CREATE KEYSPACE metrics WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 3}; CREATE TABLE metrics.events (...) WITH partition_key ... clustering columns .... Wire protocol is CQL v4 (port 9042 by default).
• Wide-column rows. A "row" in CQL is really a partition — one partition key selects a set of rows sharing that key, further ordered by clustering columns. Ideal for time series (device_id partition + event_ts DESC clustering), event logs, and per-user activity streams.
• Native TTL. INSERT INTO ... USING TTL 2592000 (30 days). YugabyteDB honours the TTL by tombstoning rows past their expiry; compaction sweeps them. No application-side cleanup required.
• LWT (lightweight transactions). INSERT ... IF NOT EXISTS, UPDATE ... IF value = ... — these compile to a Paxos consensus round across the tablet's replicas. Higher latency than plain writes (~3-5x) but essential for uniqueness enforcement without a full distributed txn.

When YCQL vs YSQL on the same YugabyteDB cluster.

• YSQL when. SQL joins matter; ACID transactions across multiple tables; existing Postgres codebase; you need triggers, stored procedures, or extensions; strong consistency across arbitrary key ranges.
• YCQL when. Wide-column time-series / per-user-activity shape; native TTL is required; you're migrating from Cassandra and don't want to rewrite the data model; write throughput needs to exceed ~50k/sec on a single table (YCQL has less per-op overhead than YSQL).
• Both when. Consolidating two databases (Cassandra + Postgres) into one cluster; different services owning different tables with different access shapes.
• Never both on the same table. YSQL and YCQL tables are separate — a table created via CQL is not visible to YSQL and vice versa. They share tablets underneath but expose different SQL surface.

The Cassandra migration path.

• Step 1. nodetool describecluster on the source Cassandra cluster; confirm data model works with YCQL (mostly a check for user-defined types and materialized views).
• Step 2. Export schema via cqlsh -e "DESCRIBE KEYSPACE ...".
• Step 3. Apply schema to YugabyteDB via ycqlsh -e "SOURCE 'schema.cql'".
• Step 4. Data migration via cassandra-loader (bulk), dsbulk (bulk), or YugabyteDB's yb-migrate tool.
• Step 5. Application reconnects using Datastax Cassandra driver pointed at YugabyteDB port 9042.

Common interview probes on YCQL.

• "When would you pick YCQL over YSQL?" — required answer: wide-column, TTL, Cassandra migration.
• "How does YCQL relate to Cassandra?" — required answer: wire-compatible, but on YugabyteDB's DocDB storage — no Cassandra ring, no Merkle-tree read repair, no eventual consistency (YCQL is strongly consistent by default).
• "What is LWT in YCQL?" — required answer: Paxos-backed conditional writes; use for uniqueness; slower than plain writes.
• "Can I query the same tablet from YSQL and YCQL?" — no; separate tables even though they share storage.

Worked example — a time-series event table in YCQL

Detailed explanation. The canonical YCQL use case: high-throughput time-series ingestion with per-device partitioning and native TTL. Walk through building an events table for a fleet of 100k IoT devices producing 10 events/sec each.

• Partition key. device_id — all events for one device land in one partition; reads for one device are cheap.
• Clustering columns. event_ts DESC — inside a partition, events are ordered newest-first; scans read the most-recent events first.
• TTL. 30 days — old data auto-expires.
• Throughput target. 1M writes/sec cluster-wide.

Question. Design the YCQL schema, insert query, and typical read query for the events use case.

Input.

Attribute	Value
Devices	100,000
Events per device per second	10
Total write rate	1,000,000 / sec
Retention	30 days
Row size	~500 B

Code.

-- Create the keyspace + table (via ycqlsh)
CREATE KEYSPACE IF NOT EXISTS metrics
    WITH replication = {
        'class': 'NetworkTopologyStrategy',
        'us-east-1': 3
    };

USE metrics;

CREATE TABLE events (
    device_id   BIGINT,
    event_ts    TIMESTAMP,
    metric      TEXT,
    value       DOUBLE,
    tags        MAP<TEXT, TEXT>,
    PRIMARY KEY ((device_id), event_ts, metric)
) WITH CLUSTERING ORDER BY (event_ts DESC, metric ASC)
  AND default_time_to_live = 2592000;    -- 30 days, in seconds

# Python driver code — high-throughput insert
from cassandra.cluster import Cluster
from cassandra.policies import DCAwareRoundRobinPolicy
from datetime import datetime

cluster = Cluster(
    ['yb-tserver.prod.internal'],
    port=9042,
    load_balancing_policy=DCAwareRoundRobinPolicy(local_dc='us-east-1'),
)
session = cluster.connect('metrics')

insert_stmt = session.prepare("""
    INSERT INTO events (device_id, event_ts, metric, value, tags)
    VALUES (?, ?, ?, ?, ?)
""")

def emit_event(device_id: int, metric: str, value: float, tags: dict):
    session.execute_async(insert_stmt, [
        device_id, datetime.utcnow(), metric, value, tags,
    ])

-- Typical read — most recent 100 events for one device
SELECT event_ts, metric, value, tags
FROM   metrics.events
WHERE  device_id = 42
ORDER  BY event_ts DESC
LIMIT  100;

-- Or: events in a specific time window
SELECT event_ts, metric, value
FROM   metrics.events
WHERE  device_id = 42
  AND  event_ts >= '2026-07-27 00:00:00'
  AND  event_ts <  '2026-07-27 01:00:00';

Step-by-step explanation.

1. The partition key ((device_id)) (note the double parens) declares device_id as the single partitioning value. All rows for a given device end up on the same DocDB tablet (hashed from device_id). This is the load-balancing primitive; 100k devices distribute evenly across all tablets.
2. Clustering columns event_ts, metric order rows within a partition. WITH CLUSTERING ORDER BY (event_ts DESC, metric ASC) means the newest events come first — a SELECT ... LIMIT 100 returns the most recent 100 events without scanning the whole partition.
3. default_time_to_live = 2592000 sets 30-day TTL on every INSERT that doesn't specify its own TTL. YugabyteDB stamps each cell with expiry; the compaction daemon removes expired cells during background compaction. No cron job needed.
4. Python driver uses execute_async for fire-and-forget writes. At 1M writes/sec across 9 tservers, each tserver handles ~110k writes/sec — well within YCQL's per-node capacity (typically 200-300k writes/sec on modern hardware).
5. The read query hits exactly one partition (one device_id) so exactly one tablet leader. The clustering order + LIMIT means the query is bounded — even if the partition holds millions of rows, the LIMIT 100 stops after the first 100 clustering positions.

Output.

Query	Tablets touched	Latency (p50)	Throughput
INSERT (single row)	1 (partition leader)	~2 ms	~1 M/sec cluster-wide
SELECT one device, recent 100	1 (partition leader)	~3 ms	~50k/sec cluster-wide
SELECT time window	1 (partition leader)	~5-20 ms	~20k/sec cluster-wide
Full-table scan	all tablets	seconds-to-minutes	avoid

Rule of thumb. Design YCQL tables around the read pattern: pick the partition key so single-partition reads answer the top-N queries, add clustering columns for ordering within a partition, and add TTL for time-series data. Never scan across all partitions in a query — if that's the pattern, YCQL is the wrong tool.

Worked example — LWT for uniqueness enforcement

Detailed explanation. Lightweight transactions (LWT) — INSERT ... IF NOT EXISTS, UPDATE ... IF condition — compile to a Paxos round across the tablet's replicas. This is the mechanism for uniqueness enforcement in YCQL (which has no native UNIQUE constraint). Walk through registering user accounts by email with LWT.

• Pattern. INSERT ... IF NOT EXISTS — succeeds only if the primary key doesn't already exist.
• Cost. ~3-5x slower than a plain INSERT (Paxos consensus round).
• When. Rare writes where uniqueness matters — user registration, order-idempotency guards.
• Not for. High-throughput inserts where the collision rate is <0.1% — the LWT cost isn't worth it.

Question. Write the LWT-based user registration and quantify its cost versus a plain INSERT.

Input.

Metric	Plain INSERT	LWT (INSERT IF NOT EXISTS)
Latency (p50)	~2 ms	~8 ms
Latency (p99)	~10 ms	~30 ms
Throughput per node	~200k/sec	~50k/sec
Consistency	quorum (Raft)	Paxos (stronger)

Code.

-- Users table with email as partition key (enforces one row per email)
CREATE TABLE metrics.users (
    email       TEXT,
    user_id     UUID,
    display_name TEXT,
    created_at  TIMESTAMP,
    PRIMARY KEY (email)
);

# Registration handler with LWT
from cassandra import InvalidRequest
from cassandra.query import SimpleStatement
import uuid
from datetime import datetime

register_stmt = session.prepare("""
    INSERT INTO metrics.users (email, user_id, display_name, created_at)
    VALUES (?, ?, ?, ?)
    IF NOT EXISTS
""")

def register_user(email: str, display_name: str):
    """Register a user; return the user_id, or raise if email already used."""
    new_uuid = uuid.uuid4()
    now = datetime.utcnow()

    result = session.execute(register_stmt, [
        email, new_uuid, display_name, now,
    ])

    # LWT returns a result row with [applied] column
    row = result.one()
    if row.applied:
        return new_uuid
    else:
        # Email already taken — return existing user_id from the result
        raise ValueError(f"Email {email} already registered as user {row.user_id}")

LWT protocol on the tablet's Raft group:

1. Client sends INSERT ... IF NOT EXISTS to any tserver.
2. Tserver routes to the partition's tablet leader.
3. Leader initiates Paxos prepare phase across all replicas.
4. Each replica reads current state of the row.
5. Leader collects prepare responses; determines if row exists.
6. If not exists → leader initiates Paxos accept phase to write.
7. Replicas acknowledge; leader returns {applied: true, ...} to client.
8. If exists → leader skips accept; returns {applied: false, existing_row}.

Total round trips vs plain INSERT:
  Plain INSERT: 1 gRPC (client→tserver) + 1 Raft round (leader→followers) = 2 hops
  LWT INSERT:   1 gRPC + Paxos prepare + Paxos accept + Raft commit  = 4-5 hops

Step-by-step explanation.

1. The PRIMARY KEY (email) on users makes email the partition key. All operations on a given email land on the same tablet, so the LWT round-trip stays within one Raft group.
2. IF NOT EXISTS is the CQL syntax for LWT. Under the hood, YugabyteDB implements this via Paxos consensus across the tablet replicas — not plain Raft, because the "test and set" semantics require the stronger Paxos protocol.
3. The result row of an LWT always includes an [applied] boolean and (if not applied) the current state of the row. This lets the client know both "did my write win" and "what's there instead."
4. Latency and throughput trade-offs: LWT is 3-5x slower than plain INSERT because of the Paxos round trip. On modern hardware you get ~50k LWT/sec per node versus ~200k plain INSERT/sec. Use LWT only where uniqueness matters; use plain INSERT elsewhere.
5. Idempotency use case: INSERT ... IF NOT EXISTS on an order table keyed by client_order_id gives you idempotent order creation for free. Client retries a failed insert; the second attempt returns applied: false and the existing order — no duplicate.

Output.

Scenario	Correct pattern	Why
User registration by email	LWT INSERT IF NOT EXISTS	Uniqueness must be enforced
Idempotent order creation	LWT INSERT IF NOT EXISTS	Client retries must not create duplicates
High-volume metric ingest	plain INSERT	No uniqueness needed; LWT too slow
Conditional status update	LWT UPDATE ... IF status = ?	State-machine transitions

Rule of thumb. Use LWT for the rare cases where uniqueness or conditional-update semantics matter (registration, idempotent inserts, state-machine transitions). Never use LWT for high-throughput writes — the 3-5x latency cost adds up. If you find yourself using LWT everywhere, YSQL with a UNIQUE constraint is probably the better fit.

Senior interview question on YCQL

A senior interviewer might ask: "You inherit a Cassandra cluster on 15 nodes serving a wide-column IoT event workload at 800k writes/sec. The team wants to consolidate onto YugabyteDB. Design the YCQL migration, quantify the cluster-sizing change, and pick which of the existing Cassandra data-model features you can port directly and which need a redesign."

Solution Using ycqlsh schema import + dsbulk data load + cluster right-sizing

#!/usr/bin/env bash
# YCQL migration — schema + data + cutover
set -euo pipefail

CASSANDRA_HOST="cassandra-seed.prod.internal"
YB_HOST="yb-tserver.prod.internal"
KEYSPACE="iot_metrics"

# ─────────────────────────────────────────────────────────────
# Phase 1 — export schema from Cassandra
# ─────────────────────────────────────────────────────────────
cqlsh "$CASSANDRA_HOST" -e "DESCRIBE KEYSPACE $KEYSPACE;" \
      > /tmp/schema.cql

# Manual review: check for User-Defined Types (UDTs) — supported;
# check for materialized views — YCQL doesn't fully support these,
# rebuild as separate tables + application-side dual write instead.

# ─────────────────────────────────────────────────────────────
# Phase 2 — apply schema on YugabyteDB via ycqlsh
# ─────────────────────────────────────────────────────────────
ycqlsh "$YB_HOST" -e "SOURCE '/tmp/schema.cql';"

# ─────────────────────────────────────────────────────────────
# Phase 3 — bulk data load via dsbulk (Datastax Bulk Loader)
# ─────────────────────────────────────────────────────────────
# Export from Cassandra
dsbulk unload -k "$KEYSPACE" -t events \
       -h "$CASSANDRA_HOST" \
       -c csv -url /tmp/events.csv

# Load into YugabyteDB (dsbulk speaks CQL, works with YCQL)
dsbulk load -k "$KEYSPACE" -t events \
      -h "$YB_HOST" \
      -c csv -url /tmp/events.csv \
      --executor.maxPerSecond 500000    # rate-limit to avoid melting the cluster

# ─────────────────────────────────────────────────────────────
# Phase 4 — dual-write bridge (T-24h to T-0)
# ─────────────────────────────────────────────────────────────
# Application writes to both Cassandra and YugabyteDB in parallel;
# reads from Cassandra. Watch YB for consistency; verify counts.

-- Schema — direct port from Cassandra, minor tweaks for YCQL
CREATE KEYSPACE IF NOT EXISTS iot_metrics
    WITH replication = {
        'class': 'NetworkTopologyStrategy',
        'us-east-1': 3
    };

USE iot_metrics;

CREATE TABLE events (
    device_id   BIGINT,
    event_ts    TIMESTAMP,
    metric      TEXT,
    value       DOUBLE,
    tags        MAP<TEXT, TEXT>,
    PRIMARY KEY ((device_id), event_ts, metric)
) WITH CLUSTERING ORDER BY (event_ts DESC, metric ASC)
  AND default_time_to_live = 2592000;

CREATE INDEX events_by_metric ON events (metric);   -- YCQL secondary index

# Cluster right-sizing calculation
CASSANDRA_NODES        = 15
CASSANDRA_WRITE_PER_NODE = 800_000 // 15   # ~53k/sec/node

YB_WRITE_PER_NODE      = 200_000            # YCQL throughput per tserver
YB_NODES_NEEDED        = 800_000 // YB_WRITE_PER_NODE
# → 4 nodes for pure write throughput
# → 6 nodes for RF=3 quorum durability + headroom
# → 9 nodes for three-region deployment (3 per region)

print(f"Cassandra: {CASSANDRA_NODES} nodes")
print(f"YugabyteDB: {YB_NODES_NEEDED} nodes (single region) or 9 (three region)")
# Cassandra: 15 nodes
# YugabyteDB: 4 nodes (single region) or 9 (three region)

Step-by-step trace.

Phase	Action	Duration
1	Export Cassandra schema via cqlsh	~10 min
2	Apply schema on YB via ycqlsh	~5 min
3	Bulk load via dsbulk (rate-limited)	hours per table
4	Dual-write bridge (app change)	continuous
5	Cutover reads to YB	flip config
6	Retire Cassandra ring	after 1-week stable

After the plan, YugabyteDB serves the full 800k writes/sec workload on 9 nodes (down from 15 Cassandra nodes), same-region latency is comparable, and the ops team has one database technology to run instead of two. Materialized views (which YCQL doesn't fully support) get rebuilt as dual-written derived tables at the app level.

Output:

Metric	Cassandra (before)	YugabyteDB YCQL (after)
Nodes	15	9 (three regions) or 4-6 (single)
Consistency	eventually consistent by default	strongly consistent by default
Read repair	Merkle-tree background	not needed (Raft quorum reads)
TTL	native	native (compatible)
LWT	Paxos	Paxos (compatible)
Materialized views	supported	dual-write derived tables
Wire protocol	CQL v4	CQL v4 (same drivers)
Ops surface	Cassandra ring	YB tserver cluster (shared with YSQL)

Why this works — concept by concept:

• CQL wire compatibility — YCQL speaks CQL v4 on port 9042. Existing Datastax drivers work unchanged. Applications never know they've moved off Cassandra.
• DocDB as shared storage — YCQL tables live on the same DocDB tablets as YSQL tables would. Ops team runs one cluster, one monitoring stack, one backup mechanism. No separate Cassandra ring to maintain.
• Strong consistency by default — Cassandra's eventual consistency means Merkle-tree read repair, hinted handoff, and quorum-tuning per query. YugabyteDB gives you strong consistency (Raft quorum) with no read-repair overhead. Applications that were tolerating eventual consistency get stronger guarantees for free.
• Cluster-sizing win — YugabyteDB's per-node throughput on modern hardware is ~200k writes/sec YCQL versus Cassandra's ~50-80k/sec. A 15-node Cassandra ring collapses to a 6-9 node YugabyteDB cluster serving the same load. Both compute and ops-headcount savings compound.
• Cost — one migration engineer for ~2-4 weeks (planning + dsbulk runs + dual-write bridge + cutover), ~30-40% reduction in database compute (fewer nodes), consolidation of two ops teams into one. Materialized views need application-side dual-write which is one sprint of work per view. Net: 6-12 months of ops-team savings; hardware payback in the first quarter.

SQL
Topic — sql
SQL practice for NoSQL and wide-column workloads

Practice →

Aggregation Topic — aggregation Aggregation problems for time-series workloads

Practice →

---

5. Geo-distribution and when YugabyteDB wins

tablespaces + placement policies + read replicas + xCluster — the four primitives that make multi-region OLTP tractable

The mental model in one line: YugabyteDB's geo-distribution story is a stack of four orthogonal primitives — tablespaces with placement policies pin each table's tablets to specific regions and zones, per-tablet Raft leader preferences steer writes to a preferred region without giving up quorum durability, read replicas (async, non-voting) sit in extra regions for low-latency stale reads, and xCluster ships WAL from one universe to another for DR / active-active cross-cluster patterns — and mastering when to reach for each is the difference between an architecture that shrugs off a regional outage and one that scrambles for a manual failover playbook. Every senior interviewer probes geo-distribution because it separates architects who have designed a multi-region OLTP system from candidates who've only used single-region Postgres.

The four geo-distribution primitives.

• Tablespaces + placement policy. CREATE TABLESPACE ... WITH (replica_placement = '{...}') declares how many replicas of each tablet live in which region/zone. Assign tables to tablespaces at CREATE time; every tablet of that table inherits the placement. This is the primary lever for data residency — pin EU customer data to EU tablespaces, keep US data US-only.
• Preferred leader placement. For tables where writes originate mostly from one region, set preferred_region in the placement policy so tablet leaders default there. Writes get one-hop latency; the other regions still hold quorum replicas for durability. If the preferred region dies, Raft re-elects a new leader in a surviving region — no manual failover.
• Read replicas (async, non-voting). Add a read-replica cluster in a region where you want low-latency stale reads but can't afford a synchronous replica. Read-replica reads are eventually consistent (typically <1s behind primary). Use SET yb_read_from_followers = on; at session level to opt in.
• xCluster (async cross-cluster). Ship WAL from one YugabyteDB universe to another. Use for DR (one-way, cold standby), active-active (bidirectional, requires conflict resolution), or blue-green migrations between clusters. Not a substitute for RF>=3 within a universe — this is between universes.

The three multi-region deployment topologies.

• Single-region, multi-AZ. RF=3, one replica per AZ. Cheapest; survives one AZ loss. Cannot survive a full-region outage. Latency: single-digit ms for writes.
• Three-region synchronous (RF=3). One replica per region. Survives one full region loss with no data loss. Write latency = cross-region round trip (~50-150 ms transatlantic). Read latency depends on where the tablet leader is.
• Region-scoped with async DR. Primary universe in one region (RF=3 across AZs); xCluster async replica in another region for DR. Writes stay cheap (single-region latency); DR failover is not automatic (~minutes of data-loss RPO acceptable).

Preferred-region placement — the common middle ground.

• Setup. RF=3 across three regions but declare one as preferred. Tablet leaders default to that region; followers in the other two.
• Effect. Writes from the preferred region get ~10-20 ms latency (in-region round trip plus quorum ack from at least one distant region). Writes from other regions still work but pay the cross-region round-trip cost.
• Failure mode. Preferred region dies → Raft re-elects new leaders in surviving regions; writes continue with elevated latency until preferred region recovers.

Common interview probes on geo-distribution.

• "How does YugabyteDB do multi-region writes?" — required answer: Raft across regions with tablet leaders on preferred region; quorum with at least one distant replica.
• "How do you enforce EU data residency?" — required answer: tablespace with EU-only placement policy; table (or partition) in that tablespace.
• "What's a read replica versus a follower?" — followers vote in Raft; read replicas don't. Read replicas are async, non-voting, and can be added without changing RF.
• "When would you use xCluster?" — DR (one-way), active-active (bidirectional), blue-green migration between clusters. Not for intra-universe replication.

Worked example — three-region deployment with data residency

Detailed explanation. The canonical multi-region YugabyteDB deployment: RF=3 across three regions with per-region tablespaces for tables that need residency pinning. Walk through a fintech that must keep US customer data in US, EU customer data in EU, and shared reference data globally.

• Regions. us-east-1, us-west-2, eu-west-1.
• Tables. customers_us (US-only), customers_eu (EU-only), products (global), orders (global with partitioned residency).
• Residency. US customer PII cannot leave US soil; EU customer PII cannot leave EU soil (GDPR); products are non-PII and can be global.
• Latency target. In-region writes ~20 ms; cross-region reads ~100 ms max.

Question. Design the tablespaces and table placements to enforce residency.

Input.

Table	Data class	Placement
customers_us	US PII	3 replicas in US regions only
customers_eu	EU PII	3 replicas in EU zones only
products	non-PII global	3 replicas across all regions
orders	mixed; partition by customer residency	shard by customer_region

Code.

-- 1. Create per-residency tablespaces
CREATE TABLESPACE us_only WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1b","min_num_replicas":1},
        {"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1}
    ]
}');

CREATE TABLESPACE eu_only WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1b","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1c","min_num_replicas":1}
    ]
}');

CREATE TABLESPACE global WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
        {"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1}
    ]
}');

-- 2. Assign tables to the right tablespaces
CREATE TABLE customers_us (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    ssn TEXT NOT NULL,           -- US PII — cannot leave US
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
) TABLESPACE us_only;

CREATE TABLE customers_eu (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    national_id TEXT NOT NULL,   -- EU PII — cannot leave EU
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
) TABLESPACE eu_only;

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    sku TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    price_cents BIGINT NOT NULL
) TABLESPACE global;

-- 3. Orders partitioned by customer residency
CREATE TABLE orders (
    id BIGSERIAL,
    customer_region CHAR(2) NOT NULL,   -- 'US' or 'EU'
    customer_id BIGINT NOT NULL,
    total_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (id, customer_region)
) PARTITION BY LIST (customer_region);

CREATE TABLE orders_us PARTITION OF orders
    FOR VALUES IN ('US') TABLESPACE us_only;

CREATE TABLE orders_eu PARTITION OF orders
    FOR VALUES IN ('EU') TABLESPACE eu_only;

Step-by-step explanation.

1. Three tablespaces express the three residency policies: us_only (all 3 replicas in US), eu_only (all 3 replicas in EU), and global (one replica per region). Every tablet inherits its placement from the tablespace of the table (or partition) it belongs to.
2. customers_us TABLESPACE us_only guarantees every tablet of that table has 3 replicas in US regions. No matter how YB rebalances, no EU node ever holds a customers_us row — audit-provably. Same for customers_eu and EU regions.
3. products TABLESPACE global puts one replica in each region. Reads from any region hit the local replica (with yb_read_from_followers for stale-tolerant reads) or the leader (for strong reads). Writes go through Raft across all three regions.
4. orders uses PostgreSQL LIST partitioning: orders_us inherits from orders but lives in us_only; orders_eu similarly in eu_only. Applications write to orders (the parent) with customer_region = 'US' or 'EU'; Postgres routes to the correct partition; the partition's tablespace enforces residency.
5. Auditors can inspect the placement policy via SELECT * FROM pg_tablespace + yb_local_tablets to confirm no tablet violates residency. This is a compliance win — the enforcement is at the storage layer, not the application layer.

Output.

Table	Tablets	Replicas	Regions	Compliance
customers_us	~8	3 each	us-east-1, us-west-2 only	US residency
customers_eu	~8	3 each	eu-west-1 only	EU residency (GDPR)
products	~4	3 each	all three regions	non-PII global
orders_us	~4	3 each	US-only	US residency
orders_eu	~4	3 each	EU-only	EU residency

Rule of thumb. For any multi-region deployment with residency requirements, use tablespaces with replica_placement and pin each table (or partition) to the tablespace matching its residency. Never rely on application-layer routing for compliance — the enforcement must be at the storage layer.

Worked example — xCluster for cross-cluster DR

Detailed explanation. xCluster ships WAL from one YugabyteDB universe to another for cross-cluster replication. Use it for DR (one-way async replica in a far region), active-active (bidirectional writes with conflict resolution), or blue-green migrations. Walk through setting up one-way xCluster from a US primary to an APAC DR replica.

• Source universe. prod-us — 9 tservers across us-east-1, us-west-2, one AZ each.
• Target universe. dr-apac — 3 tservers in ap-southeast-1.
• Direction. One-way (writes only to prod-us; dr-apac is read-only replica).
• RPO. ~5 seconds acceptable data loss on primary loss.
• RTO. ~2 minutes to promote dr-apac to primary.

Question. Configure xCluster replication and describe the failover procedure.

Input.

Component	Value
Source universe	prod-us (9 tservers)
Target universe	dr-apac (3 tservers)
Replication mode	async, one-way
Tables replicated	all in production DB
RPO / RTO	5s / 2min

Code.

#!/usr/bin/env bash
# xCluster setup — one-way async replication from prod-us to dr-apac
set -euo pipefail

PROD_MASTERS="yb-master-1.prod-us:7100,yb-master-2.prod-us:7100,yb-master-3.prod-us:7100"
DR_MASTERS="yb-master-1.dr-apac:7100,yb-master-2.dr-apac:7100,yb-master-3.dr-apac:7100"

# 1. Bootstrap target from source (initial snapshot)
#    Use yb-admin backup_and_restore for the base state
yb-admin -master_addresses "$PROD_MASTERS" \
    create_database_snapshot ysql.production

yb-admin -master_addresses "$PROD_MASTERS" \
    export_snapshot production_snapshot_20260727 /tmp/prod-snapshot.tar

# Transfer /tmp/prod-snapshot.tar to DR site
scp /tmp/prod-snapshot.tar dr-yb-node:/tmp/

yb-admin -master_addresses "$DR_MASTERS" \
    import_snapshot /tmp/prod-snapshot.tar

# 2. Create xCluster replication stream
yb-admin -master_addresses "$DR_MASTERS" \
    setup_universe_replication \
    prod-us_to_dr-apac \
    "$PROD_MASTERS" \
    <comma-separated-table-ids>

# 3. Verify replication is running
yb-admin -master_addresses "$DR_MASTERS" \
    get_replication_status prod-us_to_dr-apac

-- 4. Monitor replication lag from the DR side
SELECT stream_id,
       replication_group_id,
       CAST((now() - checkpoint_time) AS INTERVAL) AS lag_behind_primary
FROM   yb_stats.xcluster_replication;

-- Alert if lag > 30 seconds

# 5. Failover procedure (when prod-us is confirmed dead)
#    a) Pause xCluster replication on DR side
yb-admin -master_addresses "$DR_MASTERS" \
    set_universe_replication_enabled prod-us_to_dr-apac 0

#    b) Promote dr-apac to accept writes
#       (In practice, this is a config change on the connection routing tier —
#        e.g. update Route53 to point apps at dr-apac.)
#       No YB-side promotion needed; DR was already fully writable in theory,
#       but the app tier previously only sent reads there.

#    c) Point application connection strings at dr-apac
#       (via config management: consul/etcd/DNS)

#    d) When prod-us returns, reverse xCluster from dr-apac -> prod-us
yb-admin -master_addresses "$PROD_MASTERS" \
    setup_universe_replication \
    dr-apac_to_prod-us \
    "$DR_MASTERS" \
    <comma-separated-table-ids>

Step-by-step explanation.

1. Step 1 bootstraps the target universe with a point-in-time snapshot of the source. This gives the DR site all the historical data before xCluster starts tailing the WAL. Snapshotting a 2 TB universe takes ~1-2 hours; ship the tarball via secure transfer.
2. Step 2 sets up the xCluster replication stream. The source's WAL is decoded per tablet and shipped to the target, which applies each record in HLC order. Applied data lands in the target's DocDB tablets with the source's original HLC timestamps preserved (so causality is maintained even across universes).
3. Step 3-4 monitor lag. xcluster_replication shows the checkpoint time — how far behind the target is from the source. Alert on lag > 30s so you catch replication stalls before they become RPO violations.
4. Step 5 is the failover procedure. Notably, YugabyteDB doesn't do "promotion" in the Postgres sense — the DR universe is always fully-writable in principle. What changes at failover is where the application sends writes. Pausing xCluster on the DR side prevents any stale writes from prod-us from overwriting DR data during the transition.
5. Reversing xCluster after prod-us returns is a common ask. Rebuild the reverse stream (setup_universe_replication in the other direction) after the app has fully migrated to dr-apac. This gives you a fresh DR position in the original site.

Output.

Metric	Value
Replication lag (steady state)	~1-5 seconds
RPO (data-loss window on prod-us loss)	~5 seconds
RTO (time to point app at DR)	~2 minutes (DNS + connection restart)
DR site cost	3 tservers vs 9 in prod = 1/3x compute
Failback time	~1-2 hours (reverse xCluster + verify)

Rule of thumb. Use xCluster for cross-cluster DR when RPO ≥ 5s is acceptable and you can't afford synchronous cross-region replication for the whole universe. Monitor lag continuously; alert at ≤ 30s of lag. Practice failover drills quarterly — the "we've never tested it" DR plan is not a DR plan.

Senior interview question on geo-distribution + when YugabyteDB wins

A senior interviewer might ask: "You're designing a global payments platform. Writes come from three regions (US, EU, APAC) with strict data residency (payments must stay in the region of origin). The team is deciding between YugabyteDB, CockroachDB, and sharded Postgres. Walk me through why YugabyteDB is a candidate, how you'd design placement, what the failure semantics look like when a region dies, and when you'd pick one of the alternatives instead."

Solution Using per-region tablespaces + xCluster DR + Postgres-compatible SQL layer

-- 1. Universe deployment: RF=3, one replica per region for global tables
-- Three regions: us-east-1, eu-west-1, ap-southeast-1

CREATE TABLESPACE global WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1},
        {"cloud":"aws","region":"ap-southeast-1","zone":"ap-southeast-1a","min_num_replicas":1}
    ]
}');

-- 2. Regional tablespaces for residency-pinned data
CREATE TABLESPACE us_only WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1b","min_num_replicas":1},
        {"cloud":"aws","region":"us-east-1","zone":"us-east-1c","min_num_replicas":1}
    ]
}');

CREATE TABLESPACE eu_only WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1b","min_num_replicas":1},
        {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1c","min_num_replicas":1}
    ]
}');

CREATE TABLESPACE apac_only WITH (replica_placement = '{
    "num_replicas": 3,
    "placement_blocks": [
        {"cloud":"aws","region":"ap-southeast-1","zone":"ap-southeast-1a","min_num_replicas":1},
        {"cloud":"aws","region":"ap-southeast-1","zone":"ap-southeast-1b","min_num_replicas":1},
        {"cloud":"aws","region":"ap-southeast-1","zone":"ap-southeast-1c","min_num_replicas":1}
    ]
}');

-- 3. Payments — LIST partitioned by region, each partition in its region's tablespace
CREATE TABLE payments (
    id BIGSERIAL,
    region CHAR(4) NOT NULL,               -- 'US', 'EU', 'APAC'
    merchant_id BIGINT NOT NULL,
    amount_cents BIGINT NOT NULL,
    currency CHAR(3) NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE payments_us PARTITION OF payments
    FOR VALUES IN ('US') TABLESPACE us_only;
CREATE TABLE payments_eu PARTITION OF payments
    FOR VALUES IN ('EU') TABLESPACE eu_only;
CREATE TABLE payments_apac PARTITION OF payments
    FOR VALUES IN ('APAC') TABLESPACE apac_only;

-- 4. Merchant reference data (non-PII, global)
CREATE TABLE merchants (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country CHAR(2) NOT NULL,
    tier TEXT NOT NULL
) TABLESPACE global;

-- 5. Application routes writes by region
--    (In psycopg2 or JDBC, application code sets partition column
--     based on the payment origin; PG partitioning handles routing.)

# Application code — writes go to correct partition automatically
import psycopg2

def create_payment(conn, region: str, merchant_id: int, amount_cents: int, currency: str):
    """Insert a payment; PG partitioning routes to the region-pinned partition."""
    with conn:
        with conn.cursor() as cur:
            cur.execute("""
                INSERT INTO payments (region, merchant_id, amount_cents, currency, status)
                VALUES (%s, %s, %s, %s, 'pending')
                RETURNING id
            """, (region, merchant_id, amount_cents, currency))
            return cur.fetchone()[0]

Step-by-step trace.

Component	Choice	Rationale
Deployment	RF=3, three regions	Survive full region loss with no data loss
Payments residency	Per-region tablespace	Enforced at storage layer, audit-provable
Reference data	Global tablespace	One replica per region; reads local
Payment routing	PG LIST partitioning	Application sends region column; PG routes automatically
Failure mode	Region dies	Payments for that region unavailable until recovery; other regions unaffected
DR option	xCluster to a fourth region	Async replica for full-cluster DR

After deployment, a US merchant's payment lands in payments_us (3 replicas in us-east-1); an EU merchant's payment lands in payments_eu (3 replicas in eu-west-1); merchant lookups hit the local merchants replica in each region. If eu-west-1 loses all AZs simultaneously, payments_eu becomes unavailable (all 3 replicas in one region = no quorum elsewhere); payments_us, payments_apac, and merchants continue serving because their quorum is intact. The trade-off is explicit: residency requirement > cross-region durability for that partition.

Output:

Failure scenario	Impact
Single AZ in us-east-1 loss	payments_us continues (2 of 3 US AZs remain); merchants continues
Full us-east-1 region loss	payments_us unavailable (all 3 US replicas lost); other regions continue
eu-west-1 loss	Same for payments_eu; other regions continue
Cross-region network partition	Each region continues serving its own residency-pinned data; global tablets may see elevated write latency
Full-universe loss	Failover to xCluster DR site (RPO ~5s, RTO ~2min)

Why this works — concept by concept:

• Per-region tablespaces with replica_placement — the storage-layer enforcement of residency. Every tablet of payments_us has 3 replicas in US zones; no matter how YB rebalances, EU nodes never touch US data. Auditors verify via yb_local_tablets join to pg_tablespace.
• PG LIST partitioning on region column — application sends region = 'US'|'EU'|'APAC'; Postgres routes the INSERT to the correct partition automatically; the partition's tablespace enforces the physical placement. No application-side routing logic; the DBMS does it.
• Global tablespace for reference data — non-PII data that every region needs (merchants) gets one replica per region. Local reads via yb_read_from_followers are sub-millisecond; writes take the cross-region quorum hit but that's rare for reference data.
• RF=3 across zones within a region — for residency-pinned data, all 3 replicas must be in the same region. This trades cross-region durability (residency data is lost if the whole region dies) for compliance (data never leaves the region). Cover the lost-region risk with xCluster to a DR site.
• Cost — 9 tservers (3 per region) + 3 tservers for DR = 12 tservers total. Compared to CockroachDB with the same topology, YugabyteDB saves the "rewrite triggers" phase because YSQL is upstream PG. Compared to sharded Postgres with Citus + Patroni, you save the ongoing Citus + Patroni operational burden. Net: 12 tservers, one database technology, one ops team; residency is enforced at storage; DR RPO/RTO is bounded.

Design
Topic — design
Design problems on multi-region OLTP + data residency

Practice →

SQL
Topic — sql
SQL practice for geo-distributed schema design

Practice →

---

Cheat sheet — YugabyteDB recipes

• Which engine when. YugabyteDB is the default distributed SQL for a Postgres migration where multi-region writes matter and the team refuses to rewrite queries — it reuses the actual upstream Postgres 15 parser, planner, and executor, so pg_dump | ysqlsh moves you across and triggers, stored procedures, and extensions (pg_partman, pg_trgm, pgcrypto) work by inheritance. CockroachDB is the alternative when brand and managed maturity outweigh Postgres depth (and BSL license is acceptable). TiDB is for MySQL shops with HTAP requirements. Postgres+Patroni is the right answer when you don't actually need multi-region writes — don't over-engineer.
• DocDB tablet-split trigger template. Tables auto-split at --tablet_split_size_threshold_bytes = 10 GiB (default). Pre-split fast-growing tables via CREATE TABLE ... SPLIT INTO N TABLETS at create time — rule of thumb N = max(3, number_of_tservers) for any table expected to exceed 100 GB in year one. Monitor split events via yb_local_tablets — any tablet over 20 GB signals threshold is too high for that workload. Manual split via yb-admin split_tablet <tablet-id> for surgical intervention.
• YSQL migration from vanilla Postgres — the recipe. pg_dump --schema-only --no-owner --no-privileges | ysqlsh -f - for DDL (install extensions first: CREATE EXTENSION IF NOT EXISTS pg_partman;). Data via pg_dump --data-only --format=directory --jobs=8 piped to pg_restore --jobs=8. Set yb_disable_transactional_writes = ON during bulk load for ~3x speedup; re-enable after. Run ANALYZE VERBOSE; on every table before pointing traffic — the YSQL planner needs statistics. Total downtime for a ~500 GB migration is ~5 minutes if you use logical replication (YB 2.18+) for the catch-up phase.
• Colocated table DDL. CREATE DATABASE prod_colo WITH COLOCATION = true; at DB creation. All tables in that DB share one tablet's Raft group by default (cheap joins, low metadata overhead). Big tables opt out: CREATE TABLE events (...) WITH (colocated = false) SPLIT INTO 16 TABLETS;. Rule of thumb — colocate tables under 100 GB expected size with < 5000 writes/sec; give everything else its own tablets. Verify colocation via SELECT relname, reloptions FROM pg_class WHERE relnamespace = 'public'::regnamespace;.
• YCQL time-series table template. CREATE TABLE metrics.events (device_id BIGINT, event_ts TIMESTAMP, metric TEXT, value DOUBLE, PRIMARY KEY ((device_id), event_ts, metric)) WITH CLUSTERING ORDER BY (event_ts DESC) AND default_time_to_live = 2592000; — partition by device_id for even sharding, cluster by event_ts DESC for newest-first reads, 30-day native TTL. Queries hit exactly one partition (WHERE device_id = ?); avoid full-table scans at all costs. LWT (INSERT ... IF NOT EXISTS) is 3-5x slower than plain INSERT — use for uniqueness only.
• Tablespace + placement policy template for data residency. CREATE TABLESPACE eu_only WITH (replica_placement = '{"num_replicas": 3, "placement_blocks": [{"cloud":"aws","region":"eu-west-1","zone":"eu-west-1a","min_num_replicas":1}, {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1b","min_num_replicas":1}, {"cloud":"aws","region":"eu-west-1","zone":"eu-west-1c","min_num_replicas":1}]}'); — all replicas in one region for residency. Pin tables (or partitions) via TABLESPACE eu_only. Enforcement is at the storage layer; audit via yb_local_tablets join to pg_tablespace.
• xCluster setup — one-way DR replication. Bootstrap target with yb-admin create_database_snapshot ... export_snapshot ... import_snapshot; then yb-admin setup_universe_replication <name> <source-masters> <table-ids>. Monitor lag via yb_stats.xcluster_replication — alert on lag > 30s. Failover procedure: pause replication (set_universe_replication_enabled ... 0), flip application connection routing to DR site, reverse-setup xCluster from DR → primary once primary returns. RPO ~5s, RTO ~2min (mostly DNS + connection restart).
• Preferred-region leader placement recipe. Add "leader_preference": 1 on one placement_block in the replica_placement JSON to declare that region as preferred for tablet leaders. Writes from the preferred region take one in-region hop + quorum ack (~10-20ms); writes from other regions take cross-region round trip. On preferred-region failure, Raft auto-elects new leaders in surviving regions (no manual failover). Use for asymmetric workloads where most writes originate from one region.
• Read replica cluster for stale-tolerant reads. Add a read-replica cluster (async, non-voting) in a region where you want low-latency reads but can't afford a synchronous replica. Session-level opt-in: SET yb_read_from_followers = on; (and optionally SET yb_follower_read_staleness_ms = 30000; for max acceptable staleness). Read replicas are always eventually-consistent; use for dashboards, analytics, and any read where <1s staleness is acceptable.
• Failure-mode monitoring queries. Slot lag: SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag FROM pg_replication_slots;. Tablet health: SELECT tablet_id, state, num_sst_files, leader_node FROM yb_local_tablets WHERE state != 'RUNNING';. HLC skew: SELECT node_name, hlc_time, physical_time - hlc_time AS skew_ns FROM yb_servers;. xCluster lag: SELECT stream_id, checkpoint_time, now() - checkpoint_time AS lag FROM yb_stats.xcluster_replication;. Wire these into Prometheus; alert at operational thresholds.
• Decision matrix — YugabyteDB vs CockroachDB vs TiDB vs PG+Patroni. PG feature depth: YB (upstream code) > CRDB (re-implemented) > TiDB (MySQL, N/A) < PG+Patroni (100%). Multi-region: YB / CRDB / TiDB all native; PG+Patroni is single-region only. License: YB / TiDB Apache 2; CRDB BSL; PG+Patroni PostgreSQL. Dual API (SQL + NoSQL): YB only (YSQL+YCQL). Best when: YB = PG migration with multi-region; CRDB = brand + managed maturity; TiDB = MySQL + HTAP; PG+Patroni = single-region OK.
• Interview signals ranked. Naming DocDB (not just RocksDB) as the storage layer in sentence one; saying "reuses upstream Postgres 15 parser, planner, executor" for YSQL; naming HLC (hybrid logical clock) as the ordering primitive; distinguishing YSQL and YCQL as two APIs on the same DocDB; naming tablespaces + placement policies as the residency primitive; naming xCluster async as the DR primitive; refusing to over-sell YugabyteDB (naming PG+Patroni as the right answer for single-region). Any three of these in a five-minute answer is a senior signal.

Frequently asked questions

What is YugabyteDB in one sentence?

YugabyteDB is an open-source (Apache 2), horizontally-scalable, strongly-consistent distributed SQL database that reuses the upstream Postgres 15 parser, planner, and executor as its YSQL API, exposes a Cassandra-compatible YCQL API on the same underlying DocDB storage engine, replicates data via Raft consensus per tablet across nodes and regions with hybrid-logical-clock (HLC) ordering, and provides first-class multi-region primitives (tablespaces with placement policies, per-tablet leader preferences, async read replicas, xCluster cross-cluster replication) — making it the default answer for teams migrating Postgres OLTP workloads to multi-region without a query rewrite. It ships as self-hosted OSS and as a managed cloud service called YugabyteDB Aeon. Every senior data-engineering interview probes YugabyteDB because it is the only distributed SQL engine that inherits Postgres feature depth by construction (linking upstream PG source code) rather than re-implementing PG semantics.

YSQL vs YCQL — when do I pick each?

Default to YSQL when the workload is SQL-shaped: multi-table joins, ACID transactions across many rows, foreign keys, triggers, stored procedures, and any Postgres extension (pg_partman, pg_trgm, pgcrypto). This is the vast majority of application workloads and the reason most teams pick YugabyteDB in the first place — YSQL is upstream PG 15 code, so migration from vanilla Postgres is pg_dump | ysqlsh. Default to YCQL when the workload is wide-column / time-series shaped: per-user or per-device activity streams, IoT events, feature stores, session data — anything where a partition key groups related rows and clustering columns order them within the partition. YCQL wins over YSQL when native TTL matters (default_time_to_live = 2592000), when write throughput must exceed ~50k/sec on a single table (YCQL has lower per-op overhead), or when you're migrating from Cassandra and don't want to rewrite the data model. Both can coexist on the same YugabyteDB cluster, so consolidation is real — one cluster serves both APIs, ops team runs one thing. The wrong answer is "always YSQL" (misses wide-column shape) or "always YCQL" (loses ACID cross-table transactions); pick per workload.

YugabyteDB vs CockroachDB — how do I choose?

Default to YugabyteDB when Postgres feature depth matters — YSQL reuses upstream Postgres 15 source code (parser, planner, executor as a library), so triggers, stored procedures, extensions like pg_partman and pg_trgm, JSONB operators, pg_stat_statements, and FDW all work by inheritance. YugabyteDB is Apache 2 licensed (fully OSS); CockroachDB is BSL (source-available but not OSS after 2023, and commercial use of the full feature set requires a license). Default to CockroachDB when brand recognition and managed maturity outweigh Postgres depth — CockroachDB Serverless has a longer track record as a managed distributed SQL, and CockroachDB Labs has been in the space longer. Both offer native multi-region writes, Raft consensus per range/tablet, and Postgres wire compatibility; the deciding architectural question is "how much of my Postgres feature usage will I have to reimplement." For a schema with 60 triggers and 20 stored procedures, YugabyteDB requires zero rewrites; CockroachDB requires porting them all to application logic. Both are viable for greenfield multi-region OLTP; only YugabyteDB is viable for a lift-and-shift Postgres migration.

What is DocDB and how does it relate to RocksDB?

DocDB is YugabyteDB's distributed document-oriented storage engine — the layer that sits underneath both YSQL and YCQL, exposes a document key-value interface upward, and translates every SQL/CQL operation into DocDB key-value operations. Underneath, DocDB uses a heavily-modified fork of RocksDB (called "YB-RocksDB" internally) as its per-tablet on-disk store: each tablet is one RocksDB instance with its own memtable, WAL, and SST files. The modifications on top of stock RocksDB include MVCC (multi-version concurrency control for distributed transactions), a custom SST file layout that supports fast tablet-split without file movement, and integration with the tablet's Raft consensus group for durability. Above the per-tablet RocksDB, DocDB adds tablet sharding (hash or range), Raft consensus per tablet group for replication, hybrid-logical-clock (HLC) timestamping for distributed transaction ordering, and the distributed transaction coordinator for multi-shard transactions. The key mental model: DocDB is the distributed storage engine; RocksDB is the local per-tablet on-disk format DocDB uses. Interviewers who ask "how does YugabyteDB use RocksDB" are testing whether you understand the difference between the two layers.

Can I run YugabyteDB in a single region?

Yes, and it's a common deployment for teams that want the operational benefits of Raft-replicated durability, horizontal scale, and Postgres compatibility without multi-region latency. A single-region deployment typically uses RF=3 with one replica per availability zone (three AZs) — this survives one AZ loss with no data loss and no manual failover (Raft re-elects a new leader from the surviving replicas). Write latency is single-digit milliseconds (in-region round trip for the Raft quorum); read latency is even lower for reads on the tablet leader. The trade-off versus vanilla Postgres + Patroni is: YugabyteDB gives you horizontal scale (add tservers → get more capacity) and no manual failover, at the cost of higher per-operation latency (Raft quorum vs single-primary write) and higher operational complexity (a distributed system vs a Postgres primary). Rule of thumb: single-region YugabyteDB makes sense when you expect to scale beyond one primary's capacity, want automatic failover, or plan to add multi-region later. If you're firmly single-region-forever and single-primary is enough, Postgres + Patroni is simpler.

Is YugabyteDB production-ready in 2026?

Yes, unambiguously. Yugabyte Inc. (the company) has been shipping YugabyteDB since 2016; the ASF-license open-source releases have been stable since 2020; the 2.x release train has been production-hardened for years. Production deployments include Netflix (as documented in engineering blog posts), General Motors, Robinhood, and multiple large fintechs across the US, EU, and APAC — many of them running multi-region deployments with 100+ tservers. The managed offering, YugabyteDB Aeon (formerly Yugabyte Cloud), is generally available on AWS, GCP, and Azure with the same SLA profile as any tier-1 managed database. Operational maturity comes with the standard caveats of any distributed system: you need to run a proper multi-AZ deployment (RF=3 minimum), monitor tablet health and replication lag continuously, plan for backup + restore rehearsals, and staff at least one engineer who understands Raft and HLC well enough to diagnose the rare "leader can't elect" incident. None of these are blockers; they are the standard "run a tier-1 stateful service" concerns. The interview question here is almost always "is it mature enough to bet a business on" — the answer in 2026 is unambiguously yes, provided you run it operationally with the same care you'd give any other primary OLTP system.

Practice on PipeCode

• Drill the SQL practice library → for the joins, aggregations, and window-function problems senior interviewers use to test SQL fluency on distributed-SQL engines like YugabyteDB.
• Rehearse system design against the design practice library → for multi-region OLTP, data-residency, and DR-planning scenarios that mirror the YugabyteDB deployment design interview.
• Sharpen the analytics axis with the aggregation practice library → for the tumble/session/rolling-window shapes that dominate time-series workloads on YCQL.
• Practise the joins practice library → for the fact-dimension patterns typical of colocated YSQL schemas.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the DocDB architecture, the YSQL vs YCQL decision, and the four geo-distribution primitives against real graded inputs.

Lock in yugabytedb muscle memory

Docs explain what YugabyteDB is. PipeCode drills explain when it wins — when DocDB tablets and Raft consensus map to your real workload, when YSQL Postgres reuse saves a query-rewrite project, when YCQL wide-column shape beats a Postgres retrofit, when tablespaces + placement policies enforce residency at the storage layer, when xCluster async is the right DR primitive. 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 →

Comments

[Luis Cruz]

I found the explanation of DocDB's storage layer, which combines RocksDB, Raft consensus, and hybrid logical clocks, to be particularly insightful, as it highlights the trade-offs made to achieve distributed SQL capabilities. The fact that YugabyteDB reuses the upstream Postgres 15 parser, planner, and executor for its YSQL API is also noteworthy, as it allows for a more seamless migration from traditional Postgres setups. The distinction between YSQL and YCQL APIs is crucial, especially when dealing with wide-column and TTL workloads, and I'd love to hear more about how the choice between these two APIs impacts the overall system design and performance in real-world scenarios.