TiDB & TiFlash: HTAP on One Cluster — OLTP Rows + OLAP Columns Auto-Synced

tidb is the pick-one architectural decision that finally makes hybrid transactional-analytical processing something you can run on one cluster with one SQL dialect — the PingCAP-built, MySQL-compatible distributed SQL database that pairs a row-oriented OLTP store (TiKV, backed by RocksDB and coordinated by Raft) with a column-oriented OLAP store (TiFlash, kept in sync via Raft learner replicas), and that senior data engineers now evaluate against CockroachDB, YugabyteDB, and pure-analytics warehouses whenever the requirement reads "we need sub-second OLTP writes and sub-second analytical scans on the same data, without a nightly ETL to Snowflake." Every operational transaction your business writes — an order placement, an inventory decrement, a fraud-check row update — has to reach the analytical query layer without a second ETL pipeline, without the eventual-consistency lag of a Kafka + warehouse stack, and without forcing your OLAP queries to compete for TiKV IOPS against OLTP writes. The engineering trade-off does not live in "should we adopt HTAP" — every stack with dashboards on transactional data needs some form of it — but in which HTAP database you pick and how it isolates the two workloads.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through the TiDB architecture and how TiKV and TiFlash stay in sync", or "your app is Postgres-shaped and needs strong CP — is TiDB the right pick?", or "explain the cost-based optimizer that chooses between TiKV point-lookups and TiFlash MPP scans." It walks through why TiDB matters in 2026 (the HTAP pioneer with a Apache 2 license and a managed offering), the four-component architecture (Placement Driver, TiKV, TiFlash, TiDB SQL layer), the HTAP query router and MPP execution engine, the MySQL 5.7/8.0 wire compatibility story with its well-known gaps, and the migration path from MySQL via the DM tool plus the TiCDC change-feed for streaming events out to Kafka. 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 streaming axis with the streaming practice library →.

---

On this page

• Why TiDB matters in 2026
• Architecture — PD, TiKV, TiFlash, TiDB SQL layer
• HTAP query routing + cost-based optimizer
• MySQL compatibility + migration
• When TiDB wins + interview signals
• Cheat sheet — TiDB recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why TiDB matters in 2026

A distributed MySQL-compatible database that runs OLTP rows and OLAP columns on the same cluster — the HTAP pioneer whose bet finally paid off

The one-sentence invariant: TiDB is PingCAP's Apache-2-licensed, MySQL-wire-compatible distributed SQL database whose architectural bet — pair a row-oriented OLTP store (TiKV) with a column-oriented OLAP store (TiFlash) inside one cluster, keep the two auto-synchronised via Raft learner replicas, and let a single cost-based optimizer route each query to the store that answers it fastest — collapses the "OLTP + analytics" problem into a single system, a single SQL dialect, and a single operational surface, so the pattern you pick binds every downstream consumer, dashboard, and reconciliation job for the life of the schema. Every senior data-engineering interviewer in 2026 probes TiDB because the choice of transactional database is the single biggest lock-in decision in a modern data stack, and TiDB is the option that most credibly claims "you do not need a second copy of the data in Snowflake for dashboards to be fast."

The four axes interviewers actually probe.

• HTAP boundary. True HTAP means one cluster, one write path, one query interface, and analytics that never lag OLTP by more than the Raft-learner replication delay (typically sub-second). Not "we run a nightly ETL to a warehouse", not "we cache aggregates in Redis" — the same INSERT that a payment service commits at 12:00:00.123 is visible to an OLAP GROUP BY region at 12:00:00.456. Interviewers open here because the answer separates architects who understand the HTAP claim from those who conflate it with "we have both a database and a warehouse."
• MySQL compatibility surface. TiDB implements the MySQL 5.7/8.0 wire protocol and about 99% of MySQL SQL — the same JDBC driver, the same mysql client, the same connection string. The gaps are specific and well-documented (foreign key constraints with cascading actions were only recently added, stored procedures / triggers / spatial types are not supported, some legacy character sets are absent) and interviewers probe them to see whether you have actually migrated a real MySQL workload versus read the marketing page.
• Scale-out story. TiDB shards data into 96 MB "regions" managed by the Placement Driver, replicates each region 3× via Raft, and scales horizontally by adding TiKV nodes without downtime. Compared to MySQL's read-replica + Vitess-style sharding, this is a step-change simpler operational model. Compared to CockroachDB it is functionally similar (also Raft, also sharded, also NewSQL) but different wire protocol and different HTAP story.
• Managed vs self-hosted. TiDB Cloud offers both Serverless (usage-based, autoscaling, cheap for small workloads) and Dedicated (provisioned, VPC-peered, enterprise-grade). Self-hosted TiUP-managed clusters run on any cloud or bare metal. Interviewers probe deployment story because it signals whether you have picked the right operational tier for your team.

The 2026 reality — TiDB is one of four viable distributed SQL options.

• TiDB. PingCAP-built, ASF-adjacent, Apache 2, MySQL-first, HTAP-native via TiFlash. Production-scale references at Square, Shopee, ByteDance, Pinterest, and dozens of Chinese hyperscalers. The default answer for "we're on MySQL and need horizontal scale-out plus analytics."
• CockroachDB. Cockroach Labs (BSL-licensed, changing gradually), Postgres-compatible wire, strong on CP (Raft-based, strict serialisable). No native HTAP — you pair it with a warehouse. The default answer for "we're on Postgres and need geo-distribution + strong consistency."
• YugabyteDB. Apache 2, dual API (Postgres YSQL + Cassandra YCQL), Raft-based, deep Postgres compatibility. Positioned between CockroachDB and TiDB; less common in HTAP scenarios. The default answer for "we want Postgres + Cassandra shapes from one storage engine."
• Snowflake / Databricks / BigQuery. Pure analytics at petabyte scale, no OLTP story at all. The complement (not competitor) to TiDB — you still copy TiDB into Snowflake for cross-source joins, historical retention, or ML feature stores that exceed TiFlash's practical scale.

What interviewers listen for.

• Do you name TiKV (rows) + TiFlash (columns) as the two storage engines in sentence one? — required answer.
• Do you name Raft learners as the auto-sync mechanism between them? — senior signal.
• Do you distinguish "MySQL compatible" from "MySQL fork" — TiDB reimplements the protocol, it is not a fork? — senior signal.
• Do you name the Placement Driver (PD) as the metadata + region-placement brain? — senior signal.
• Do you describe HTAP as "one query hits either TiKV or TiFlash, decided per-query by the CBO" rather than "two databases synced"? — required answer.

Worked example — the four-axis comparison table

Detailed explanation. The single most useful artifact for a TiDB interview is a memorised 4x4 comparison table. 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 greenfield transactional platform that also needs sub-second dashboards.

• Workload. E-commerce order platform: ~50k writes/sec at peak (orders, order_items, inventory), analytics on top of the same tables (revenue by hour, top-selling SKUs, geo dashboards).
• Requirements. Sub-second OLTP p99, sub-second OLAP scans over the last 30 days of orders, MySQL-shaped schema and SQL, horizontal scale-out without a re-shard project every two years.
• Constraints. ~5 TB working set at launch (growing to 50 TB in year 2), MySQL-native developers on the team, cannot afford a nightly warehouse ETL that lags dashboards by 24 hours.

Question. Build the four-way comparison for the platform and pick the database each requirement drives you toward.

Input.

Database	Wire protocol	HTAP native?	Scale-out model	License
TiDB	MySQL 5.7/8.0	yes (TiKV + TiFlash)	region-based, Raft, PD-managed	Apache 2
CockroachDB	Postgres	no (pair with warehouse)	range-based, Raft, gossip	BSL (source available)
YugabyteDB	Postgres (YSQL) + Cassandra (YCQL)	no	tablet-based, Raft	Apache 2
Snowflake	Snowflake SQL (custom)	analytics only	micro-partitions, warehouses	Proprietary

Code.

# TiDB cluster topology config snippet (tiup cluster deploy)
global:
  user: tidb
  ssh_port: 22
  deploy_dir: /tidb-deploy
  data_dir: /tidb-data

pd_servers:
  - host: 10.0.1.10
  - host: 10.0.1.11
  - host: 10.0.1.12       # PD is 3 nodes for Raft quorum

tidb_servers:              # stateless SQL layer; add more for QPS
  - host: 10.0.2.10
  - host: 10.0.2.11
  - host: 10.0.2.12

tikv_servers:              # row store; add more for OLTP capacity
  - host: 10.0.3.10
  - host: 10.0.3.11
  - host: 10.0.3.12
  - host: 10.0.3.13

tiflash_servers:           # column store; add more for OLAP capacity
  - host: 10.0.4.10
  - host: 10.0.4.11

monitoring_servers:
  - host: 10.0.5.10

grafana_servers:
  - host: 10.0.5.10

Step-by-step explanation.

1. The comparison table forces the four axes into a single view: wire protocol, HTAP nativeness, scale-out model, and license. TiDB wins uniquely on native HTAP inside a MySQL-shaped wire — no other option in the row hits all three of "MySQL wire + horizontal scale + first-class OLAP replicas on the same cluster."
2. CockroachDB is the natural answer only when your application is Postgres-shaped and geo-distribution + strict serialisable consistency is the primary driver; its NON-existence of a native columnar store means analytical queries either scan the row store (slow at scale) or you copy to a warehouse.
3. YugabyteDB is the pragmatic answer when you want dual-API access to the same storage engine (Postgres-shaped analytics plus Cassandra-shaped key-value writes) — a smaller wedge, and no HTAP columnar story.
4. Snowflake (and Databricks, BigQuery, and any pure-warehouse) is not a competitor for the OLTP workload at all; it complements TiDB when you need cross-source joins, ML feature-store scale, or petabyte historical archives that outgrow TiFlash's practical footprint (roughly hundreds of TB per cluster).
5. The greenfield answer for our e-commerce scenario is TiDB: MySQL wire keeps the existing team productive, TiKV handles the 50k writes/sec via region autoscaling, TiFlash serves the dashboards without a second data copy, and the Apache 2 license means no BSL-style vendor risk if PingCAP's business model changes.

Output.

Requirement	Recommended database	Why
MySQL wire + HTAP	TiDB	only option in the class
Postgres wire + geo-CP	CockroachDB	strict serialisable + PG SQL
Postgres + Cassandra dual API	YugabyteDB	YSQL + YCQL from one engine
Petabyte analytics only	Snowflake	out-of-scope for OLTP

Rule of thumb. Never pick a distributed SQL database based on "which vendor pitched us this week." Pick it based on (wire protocol x HTAP need x scale-out model x license). Write the table on a whiteboard first; the database falls out of the constraints.

Worked example — what interviewers actually probe on TiDB

Detailed explanation. The senior data-engineering TiDB interview has a predictable structure: the interviewer opens with an ambiguous question ("what's your take on the distributed SQL landscape in 2026?"), then progressively narrows to test whether you understand TiKV vs TiFlash, Raft learners, and the CBO's per-query routing. The candidates who name TiKV and TiFlash in sentence one score highest; the candidates who describe "just another MySQL replacement" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you design a database for our new order platform that needs sub-second analytics?" — invites you to name TiDB and describe HTAP.
• Follow-up 1. "How does TiKV differ from TiFlash?" — probes the row-vs-column understanding.
• Follow-up 2. "How does data get from TiKV to TiFlash?" — probes Raft learners and async replication.
• Follow-up 3. "How does TiDB decide whether to run my query on TiKV or TiFlash?" — probes the cost-based optimizer.
• Follow-up 4. "What are the MySQL gaps I should worry about?" — probes migration risk.

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

Input.

Interview signal	Weak answer	Senior answer
Storage engines	"distributed database"	"TiKV (rows, RocksDB + Raft) + TiFlash (columns, async Raft-learner replicas)"
Sync mechanism	"replication"	"Raft learners — non-voting followers pull from voters; async so OLTP is not blocked"
Query routing	"the optimizer picks"	"CBO estimates row count, chooses TiKV for point-lookups, TiFlash MPP for aggregations"
MySQL gaps	"mostly compatible"	"no stored procs, no triggers, spatial types absent; FKs recently added; strict mode differences"
HTAP claim	"OLAP + OLTP"	"one cluster, one SQL, one write path; per-query store choice; workload isolation"

Code.

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

Minute 1 — name the storage engines up front
  "TiDB is a distributed MySQL-compatible SQL database with two
   storage engines on one cluster: TiKV for row-oriented OLTP
   backed by RocksDB and coordinated by Raft, and TiFlash for
   column-oriented OLAP replicated asynchronously from TiKV via
   Raft learners. The SQL layer is stateless; the Placement Driver
   (PD) manages metadata and region placement."

Minute 2 — sync + isolation
  "The auto-sync is Raft learners: each TiKV region has a normal
   3-voter Raft group plus zero or more learner replicas on TiFlash
   that pull the log asynchronously. OLTP writes commit on the voter
   quorum in TiKV; the learners catch up in the background, typically
   within tens of milliseconds. OLAP queries hit TiFlash and cannot
   block OLTP writes because TiFlash is a separate physical tier."

Minute 3 — query routing
  "The cost-based optimizer decides per query which store to read.
   A point-lookup by primary key hits TiKV (index seek, ~1ms). A
   GROUP BY over millions of rows hits TiFlash in MPP mode (parallel
   scan and aggregation across TiFlash nodes). The plan is visible
   in EXPLAIN; you can force a store choice with the READ_FROM_STORAGE
   optimizer hint."

Minute 4 — MySQL compatibility
  "The wire is MySQL 5.7 / 8.0 — same JDBC, same mysql-client. About
   99% of MySQL SQL works unchanged. The gaps: stored procedures,
   triggers, and spatial types are not supported; foreign keys with
   cascading actions were added recently but are still lightly used;
   some legacy character sets are absent. Migration is via the DM tool
   which continuously replicates from a MySQL binlog."

Minute 5 — operational surface
  "One tiup cluster deploy for self-hosted, or TiDB Cloud for managed.
   Scale-out is add-a-node; PD rebalances regions automatically. Change
   feed is TiCDC — a native change-data-capture reader that emits to
   Kafka or Pulsar. Observability is Grafana + Prometheus by default."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming TiKV and TiFlash immediately — not just "TiDB" as a monolith — signals you understand the architecture. Weak candidates say "TiDB is like MySQL but distributed" and lose the interviewer within thirty seconds.
2. Minute 2 answers the sync question before it is asked. "Raft learners" is the exact term the docs use; using it (rather than "replication") signals you have actually read the internals. The "OLAP does not block OLTP" claim is the workload-isolation headline of the HTAP story.
3. Minute 3 covers the CBO routing. The READ_FROM_STORAGE hint is the concrete lever — knowing that hint exists is a senior signal because it means you have written a query where you had to override the CBO.
4. Minute 4 pre-empts the compatibility follow-up. Naming the specific gaps (stored procs, triggers, spatial types) is far better than the vague "mostly compatible" — it shows you have migrated a real workload where the gaps mattered.
5. Minute 5 covers the operational surface. Mentioning tiup, TiCDC, and TiDB Cloud in one sentence signals you know both the self-hosted and managed paths and can pick between them for a given team.

Output.

Grading criterion	Weak score	Senior score
Names TiKV + TiFlash	occasional	mandatory
Names Raft learners	rare	senior signal
Names PD	rare	senior signal
Names CBO + hints	rare	senior signal
Names MySQL gaps	occasional	required
Names TiCDC	rare	senior signal

Rule of thumb. The senior TiDB answer is a 5-minute monologue that covers architecture, sync, routing, compatibility, and ops without waiting for the follow-ups. Rehearse it once; deploy it every time.

Interview Question on TiDB positioning

A senior interviewer often opens with: "You inherit a MySQL 5.7 order platform that has hit vertical-scaling limits (largest RDS instance, 90% write IOPS) and a Snowflake pipeline that lags orders by 24 hours. Leadership wants sub-second dashboards on live data and horizontal write scaling without a re-shard project. Walk me through why you would (or would not) migrate to TiDB, the topology you would deploy, and the interview-worthy trade-offs you would flag."

Solution Using TiDB with TiKV OLTP + TiFlash OLAP replicas and DM-based MySQL migration

# 1. tiup cluster topology — production-ready 12-node deployment
global:
  user: tidb
  ssh_port: 22
  deploy_dir: /tidb-deploy
  data_dir:   /tidb-data
  arch:       amd64

server_configs:
  tidb:
    performance.max-procs: 0    # use all CPUs
    log.level: info
  tikv:
    server.grpc-concurrency: 8
    storage.reserve-space: "10GB"
    raftstore.apply-pool-size: 4
    raftstore.store-pool-size: 4
  pd:
    replication.max-replicas: 3
    replication.location-labels: ["zone", "host"]

pd_servers:
  - host: 10.0.1.10
  - host: 10.0.1.11
  - host: 10.0.1.12

tidb_servers:           # scale for QPS; each pod is stateless
  - host: 10.0.2.10
  - host: 10.0.2.11
  - host: 10.0.2.12

tikv_servers:           # OLTP row store; 3 replicas per region
  - host: 10.0.3.10
    config: { server.labels: { zone: "az-a", host: "kv-1" } }
  - host: 10.0.3.11
    config: { server.labels: { zone: "az-b", host: "kv-2" } }
  - host: 10.0.3.12
    config: { server.labels: { zone: "az-c", host: "kv-3" } }
  - host: 10.0.3.13
    config: { server.labels: { zone: "az-a", host: "kv-4" } }

tiflash_servers:        # OLAP column store; add TiFlash replicas per table
  - host: 10.0.4.10
  - host: 10.0.4.11

-- 2. After cluster is up, add TiFlash replicas to the analytical tables
ALTER TABLE orders            SET TIFLASH REPLICA 2;
ALTER TABLE order_items       SET TIFLASH REPLICA 2;
ALTER TABLE inventory_events  SET TIFLASH REPLICA 2;

-- 3. Verify sync progress; wait for AVAILABLE=1
SELECT TABLE_SCHEMA, TABLE_NAME, REPLICA_COUNT, AVAILABLE, PROGRESS
FROM   information_schema.tiflash_replica
WHERE  TABLE_SCHEMA = 'orders_db';

# 4. DM task — continuous MySQL 5.7 -> TiDB replication for zero-downtime cutover
name: migrate-orders
task-mode: all              # dump + load + sync (binlog tail)
target-database:
  host: "tidb-vip.internal"
  port: 4000
  user: "dm_writer"
  password: "${DM_PASSWORD}"

mysql-instances:
  - source-id: "mysql-order-primary"
    block-allow-list: "orders-only"
    mydumper-config-name: "orders-dump"
    loader-config-name: "orders-load"
    syncer-config-name: "orders-sync"

block-allow-list:
  orders-only:
    do-dbs: ["orders_db"]

mydumpers:
  orders-dump:
    threads: 8
    chunk-filesize: 128
    extra-args: "--skip-tz-utc"

loaders:
  orders-load:
    pool-size: 16
    dir: "./dumped_data"

syncers:
  orders-sync:
    worker-count: 16
    batch: 100
    enable-gtid: true

Step-by-step trace.

Step	Before (MySQL + Snowflake)	After (TiDB HTAP)
Write throughput	80% of largest RDS instance	horizontally scalable via + tikv node
Analytics freshness	~24 hours (nightly ETL)	sub-second (TiFlash Raft-learner lag)
Data copies	2 (MySQL + Snowflake)	1 cluster, 2 physical stores
Migration mechanism	none (would require app rewrite)	DM tool: dump + load + binlog sync, near-zero downtime
Query dialect	MySQL for OLTP, Snowflake SQL for OLAP	MySQL for both; CBO chooses the store
Failure surface	RDS instance failover + Snowflake job	region-level Raft failover in TiKV; TiFlash re-sync on failure
Scale-out	vertical (bigger instance)	horizontal (more nodes, no downtime)

After the migration, order writes commit into TiKV via a MySQL-wire client with no schema change; TiFlash replicates the regions asynchronously (typical lag ~50-200 ms); the analytics dashboard queries the same tables and the TiDB CBO routes GROUP BY / aggregation queries to TiFlash automatically. The nightly Snowflake ETL disappears; the largest RDS instance is decommissioned; write capacity becomes a matter of adding TiKV nodes rather than a re-shard project.

Output:

Metric	Before	After
OLTP p99 write latency	~15 ms	~10 ms (Raft quorum)
OLAP freshness	24 h	< 1 s
Peak write throughput	~30k writes/s (RDS ceiling)	~150k writes/s (12-node TiKV, room to grow)
Data copies to manage	2	1
ETL pipeline maintenance	Airflow + Snowpipe + dbt	none
Time-to-cutover	(not possible)	2-4 weeks (DM sync + parity + traffic shift)

Why this works — concept by concept:

• TiKV row store — the OLTP substrate. RocksDB LSM-tree storage engine + Raft consensus per 96 MB region; three replicas per region (one leader, two followers). Point-lookups by primary key are index seeks (~1 ms); writes commit on the two-of-three Raft quorum.
• TiFlash column store — the OLAP substrate. Columnar storage (Delta Tree / ClickHouse-derived) that pulls the same Raft log as TiKV but as a non-voting learner replica. Columnar layout means analytical scans read only the columns the query touches — 5-10x compression + 10-100x scan speed over row-oriented reads.
• Raft learners — the auto-sync primitive. A learner is a Raft peer that receives the log but does not vote on commits. This means TiFlash's presence does not affect TiKV's write latency or quorum size; TiFlash catches up asynchronously, typically within milliseconds under normal load.
• Placement Driver (PD) — the metadata brain. PD tracks every region's location, orchestrates region splits/merges/rebalances, hands out timestamp oracles (TSO) for MVCC, and is itself a 3-node Raft group. Losing 2 of 3 PD nodes stalls new-transaction writes; losing 1 is invisible to clients.
• Cost — one cluster (vs MySQL + Snowflake + ETL infrastructure), 3x replication in TiKV plus 1-2 TiFlash learners (roughly 4-5x storage overhead vs a single copy), 12-node minimum for HA production. Compared to running MySQL + Snowflake + Kafka + Debezium + Airflow, this is dramatically simpler. Compared to running just MySQL, it costs more hardware but eliminates the entire ETL layer.

SQL
Topic — sql
SQL problems on distributed transactional design

Practice →

Design Topic — design Design problems on HTAP databases

Practice →

---

2. Architecture — PD, TiKV, TiFlash, TiDB SQL layer

Four components, one cluster — the SQL layer routes, PD orchestrates, TiKV stores rows, TiFlash stores columns

The mental model in one line: TiDB's architecture is a four-tier stack — the stateless TiDB SQL layer (parses / plans / routes / returns rows to the MySQL client), the Placement Driver PD (a 3-node Raft group that tracks region metadata, hands out MVCC timestamps, and orchestrates rebalancing), the TiKV row store (RocksDB + Raft, sharded into 96 MB regions each replicated 3x), and the TiFlash column store (async Raft-learner replicas of TiKV regions, laid out columnarly for scan) — with the SQL layer as the only entry point clients see and the two storage engines invisible except through the CBO's per-query routing decision. Every senior architect who deploys TiDB must be able to draw this diagram from memory and explain each component's failure mode without hesitation.

The four components in detail.

• TiDB SQL layer. Stateless MySQL-wire servers. Accepts client connections on port 4000, parses the SQL, builds a logical plan, invokes the CBO to build the physical plan (which decides TiKV vs TiFlash per query), issues Coprocessor requests to the storage tier, collects rows, and returns them to the client. Stateless means: kill any TiDB pod at any time; clients reconnect to another one; no data is lost because no data was ever local. Scale horizontally by adding TiDB pods behind a load balancer.
• Placement Driver (PD). The cluster's metadata brain. Runs as a 3-node (or 5-node for very large clusters) Raft group. Responsibilities: (a) region metadata — which region owns which key range, where each region's leader and followers live, when to split a hot region, when to merge two cold ones, (b) TSO — the monotonic timestamp oracle that hands out globally-ordered timestamps for MVCC + snapshot isolation, (c) rebalancing — decides when to move regions between TiKV nodes for load balance or after a new node joins, (d) TiFlash learner placement — decides which TiFlash nodes host which region's learner replica.
• TiKV. The row-oriented OLTP substrate. Each TiKV node runs one or more RocksDB instances holding data on local disk. Data is sharded into "regions" — contiguous key ranges of ~96 MB — and each region is replicated to 3 TiKV nodes as a Raft group. Every read/write to a key routes to the region's leader (via the routing table PD maintains). Writes commit when the Raft log entry is replicated to a majority (2 of 3). MVCC uses PD's TSO to order transactions; snapshot isolation is the default (roughly equivalent to Postgres's read-committed + repeatable-read; strict serialisable is optionally available via SELECT ... FOR UPDATE).
• TiFlash. The column-oriented OLAP substrate. Each TiFlash node runs a columnar storage engine (Delta Tree, derived from ClickHouse's MergeTree). Data enters TiFlash as Raft learner replicas of TiKV regions: for every region whose table has a TiFlash replica configured, one or more TiFlash nodes join that region's Raft group as non-voting followers. The Raft log is replayed into the columnar storage engine, converting row-formatted mutations into columnar batches. TiFlash never accepts direct writes from clients; it only receives Raft log entries from TiKV.

The write path — how one INSERT reaches both stores.

• Step 1. Client sends INSERT to any TiDB SQL pod via the MySQL wire.
• Step 2. TiDB pod parses, plans, acquires a TSO from PD (the transaction's start_ts).
• Step 3. TiDB pod issues 2-phase commit (Percolator-derived) against the TiKV regions holding the target rows: prewrite phase acquires row locks and writes to a Default column family; commit phase writes commit timestamps.
• Step 4. Each TiKV region's Raft group replicates the write to 2-of-3 voter quorum; the write is now durable.
• Step 5. TiFlash's learner replica of the same region receives the Raft log entry asynchronously (typically within tens of milliseconds); the columnar engine replays the mutation.
• Step 6. Any subsequent SELECT that hits TiFlash sees the new row after the learner has caught up; queries hitting TiKV see it immediately.

The read path — how the CBO picks a store.

• Point lookup. SELECT * FROM orders WHERE id = 42 — the CBO knows this is a primary-key seek. TiKV wins: cheap index seek, ~1 ms. TiFlash would be catastrophic (full column scan).
• Small range scan. SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 HOUR — the CBO estimates the row count using stats. If small, TiKV via the index; if large, TiFlash.
• Aggregation over large data. SELECT region, SUM(total) FROM orders GROUP BY region — the CBO estimates GROUP BY over millions of rows. TiFlash MPP wins by orders of magnitude: parallel columnar scan across TiFlash nodes, hash-aggregation on each node, then final aggregation on the coordinator.
• Join. SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.total > 1000 — the CBO considers each join side independently, chooses the cheapest store per side, and picks a join algorithm (broadcast, shuffle, index) based on cardinality.

The failure story — what happens when each component dies.

• A TiDB SQL pod dies. Clients get connection errors; retry hits another pod; no data loss. Stateless tier; kill anytime.
• A PD node dies. If 1 of 3 dies: quorum survives; cluster runs normally. If 2 of 3 die: no quorum; new-transaction writes stall (existing transactions continue) until quorum returns. This is why PD is 3 or 5 nodes and each is on a different failure domain.
• A TiKV node dies. For each region whose leader was on the dead node: Raft election picks a new leader from the remaining 2 followers; writes resume within seconds. For each region whose follower was on the dead node: PD schedules a re-replication onto a healthy TiKV node; region is briefly under-replicated but writes never stall.
• A TiFlash node dies. TiFlash is a learner; it never blocks OLTP. Analytical queries that were routed there fail over to another TiFlash learner (if TIFLASH REPLICA >= 2) or fall back to TiKV (with performance loss). PD schedules a new learner on a healthy TiFlash node; the new replica catches up from the Raft log.

Common interview probes on TiDB architecture.

• "How many nodes minimum for HA production?" — required answer: 3 PD + 2 TiDB + 3 TiKV + 2 TiFlash = 10 minimum; typical starts at 12.
• "Why is PD 3 nodes?" — Raft quorum; tolerates 1 failure.
• "How big is a region?" — 96 MB default; splits when it grows past ~144 MB, merges when adjacent regions are both small.
• "What is Percolator?" — Google's distributed 2PC protocol; TiDB's transaction model is Percolator-derived.
• "How does TiFlash stay in sync?" — Raft learner: non-voting follower on the region's Raft group; replays the log into the columnar engine.

Worked example — walking the write path with one INSERT

Detailed explanation. The canonical write walkthrough: trace a single INSERT from the MySQL client all the way through the four-tier stack, naming every hop and the timestamps involved. This is the "explain a distributed transaction" question every senior interview asks; having a concrete example in your head makes the answer crisp.

• Client. JDBC connection to tidb-vip.internal:4000.
• Statement. INSERT INTO orders(customer_id, total_cents, status) VALUES (7, 1500, 'pending');
• Region ownership. The orders table's id = 42 (auto-generated) falls into region R17, whose leader is TiKV-node-3.
• TiFlash replicas. orders has TIFLASH REPLICA = 2; regions of orders have learners on TiFlash-node-1 and TiFlash-node-2.

Question. Trace the full write path step-by-step and record what each component contributes.

Input.

Stage	Component	Action
Parse	TiDB SQL pod	parse SQL, resolve schema
Timestamp	TiDB -> PD	GetTSO -> start_ts = 435872341
Prewrite	TiDB -> TiKV region R17 leader	lock + tentative write
Raft replicate	TiKV leader -> 2 followers	quorum ack
Commit	TiDB -> TiKV	commit_ts = 435872343
Learner replay	Raft log -> TiFlash learners	async, ~50 ms lag

Code.

-- The statement the client sends
INSERT INTO orders(customer_id, total_cents, status)
VALUES (7, 1500, 'pending');

-- Behind the scenes, TiDB does something conceptually like:
BEGIN;                       -- implicit; acquire start_ts from PD
--  ... resolve schema, plan write ...
--  ... prewrite phase: acquire row lock on (orders, id=42) in region R17
--      write value {customer_id:7, total_cents:1500, status:'pending'} with start_ts=435872341
--  ... commit phase: acquire commit_ts from PD (435872343)
--      write commit record at commit_ts pointing to the prewritten value
COMMIT;                      -- client sees success

Detailed timeline (microseconds)
================================
t=0        Client sends INSERT over MySQL wire to tidb-pod-2
t=100      TiDB parses; resolves 'orders' -> table_id=1005
t=250      TiDB calls PD.GetTSO() -> start_ts=435872341
t=500      TiDB routes write to region R17 leader (TiKV-node-3)
t=1500     TiKV-node-3 prewrite: lock + tentative write in Default CF
t=2000     Raft entry replicated to TiKV-node-1 + TiKV-node-4 (quorum)
t=2200     TiDB calls PD.GetTSO() -> commit_ts=435872343
t=2500     TiDB commits at commit_ts; TiKV writes commit record
t=3000     Raft commit entry replicated to quorum
t=3200     Client receives OK
--- ASYNC FROM HERE ---
t=~50000   TiFlash-node-1 learner receives Raft entry via log stream
t=~55000   TiFlash-node-1 replays entry into columnar Delta Tree
t=~60000   TiFlash-node-2 learner also caught up
--- New row visible on all four physical copies ---

Step-by-step explanation.

1. Client sends the INSERT via the MySQL wire protocol on port 4000. Any TiDB SQL pod accepts it — the load balancer picks tidb-pod-2. The pod parses the SQL and resolves the table name to a table_id via the schema cache (refreshed from PD periodically).
2. TiDB requests a start_ts from PD. This is a strictly-monotonic global timestamp; every transaction gets a unique one. PD's TSO service allocates timestamps in batches of ~200k per second per PD node, so this call is typically sub-millisecond.
3. TiDB routes the write to the leader of the region that owns the target row's key. The routing table is cached on the TiDB pod (also refreshed from PD). The prewrite phase acquires a row-level lock on (orders, id=42) and writes the tentative value into RocksDB with start_ts as the version.
4. TiKV's Raft group replicates the prewrite entry to 2-of-3 followers. Once the quorum acks, the entry is durable. TiDB then requests commit_ts from PD, and issues the commit — which writes a "commit record" pointing to the prewritten value at commit_ts. After commit, the row is visible at read_ts >= commit_ts under snapshot isolation.
5. The Raft log entry is also streamed to the TiFlash learner replicas. This happens asynchronously — the OLTP commit does not wait for TiFlash. The learner replays the entry into its columnar storage engine (Delta Tree), converting the row-format mutation into columnar batches. Typical end-to-end lag: 50-200 ms.

Output.

Timestamp	Event	Component
t=0 μs	Client INSERT	JDBC
t=250 μs	start_ts	PD TSO
t=1500 μs	Prewrite	TiKV region leader
t=2000 μs	Raft quorum	TiKV followers
t=2500 μs	Commit	TiKV region leader
t=3200 μs	Client OK	(~3.2 ms total OLTP path)
t=50000 μs	TiFlash learner replay	TiFlash node

Rule of thumb. The OLTP write path is TiKV-only and completes in ~3-10 ms end-to-end. The TiFlash sync happens asynchronously in the background and does not affect OLTP latency. Never conflate the two: OLTP latency is bounded by Raft quorum in TiKV, not by TiFlash's catch-up.

Worked example — the Placement Driver and region rebalancing

Detailed explanation. PD's job is to keep the cluster balanced as data grows and nodes come/go. When a new TiKV node joins, PD gradually moves regions onto it until region count and disk usage are balanced. When a region gets hot (many reads or writes), PD splits it. When two adjacent regions get cold, PD merges them. Walk through the observability and control surfaces.

• View. SHOW TABLE tikv_region_status to see per-region location, size, and replica count.
• Control. pd-ctl command-line for cluster admin — add/remove nodes, adjust scheduling limits, trigger splits.
• Metrics. Grafana dashboards for region count per store, leader count per store, and scheduling operator queue length.

Question. Show how to inspect region distribution, trigger a manual region split, and use scheduling limits to slow a rebalance during peak hours.

Input.

Component	Value
Cluster	4 TiKV nodes, ~10k regions total
Peak-hour policy	limit region movements to 2/minute
Manual split	for a hot region on the sessions table
Inspection	via pd-ctl and information_schema tables

Code.

-- 1. Inspect region distribution from a MySQL client
SELECT   REGION_ID, TABLE_NAME, PEER_COUNT, WRITTEN_BYTES, READ_BYTES, LEADER_STORE_ID
FROM     information_schema.tikv_region_status
JOIN     information_schema.tikv_region_peers USING (REGION_ID)
WHERE    TABLE_NAME = 'sessions'
ORDER BY WRITTEN_BYTES DESC
LIMIT    10;

-- 2. Show cluster-wide region count per TiKV store
SELECT   STORE_ID, ADDRESS, REGION_COUNT, LEADER_COUNT, AVAILABLE_STORAGE
FROM     information_schema.tikv_store_status;

# 3. Use pd-ctl to slow rebalance during business hours
# (limit leader movements + region movements per second per store)
pd-ctl -u http://pd.internal:2379 config set leader-schedule-limit 2
pd-ctl -u http://pd.internal:2379 config set region-schedule-limit 2

# 4. Trigger a manual split for a hot region
pd-ctl -u http://pd.internal:2379 operator add split-region 17 --policy=scan

# 5. Inspect the operator queue
pd-ctl -u http://pd.internal:2379 operator show

# 6. Overnight, restore normal scheduling
pd-ctl -u http://pd.internal:2379 config set leader-schedule-limit 4
pd-ctl -u http://pd.internal:2379 config set region-schedule-limit 8

# Grafana panels to watch during a rebalance
- pd_scheduler_event_count{type="balance-region"}   -- rebalance activity
- tikv_grpc_msg_duration_seconds_bucket             -- RPC latency
- pd_regions_status{type="miss-peer-region-count"}  -- under-replicated count
- tikv_store_size_bytes                             -- disk usage per store
- tikv_raftstore_apply_log_avg_duration             -- Raft health

Step-by-step explanation.

1. information_schema.tikv_region_status exposes the same data pd-ctl shows but via SQL — convenient because you can join it with other tables in one query. The result set lets you find the largest / hottest regions per table without leaving your SQL client.
2. tikv_store_status gives you the per-node summary: how many regions, how many leaders, how much disk. During a rebalance, watch this table to confirm the distribution is evening out.
3. leader-schedule-limit and region-schedule-limit cap how many scheduling operations PD kicks off per second per store. Lowering them during peak hours reduces the impact of rebalancing on OLTP latency; raising them at night speeds recovery. Both are runtime-changeable — no restart.
4. The manual split operator forces PD to split a specific region. Useful when you know a region is a hot spot (e.g. all sessions inserts land on the last region due to time-ordered keys) but PD's auto-split heuristics have not fired yet. --policy=scan scans the region to pick a mid-point key.
5. Watching the Grafana panels during a rebalance is the operational gold standard. If miss-peer-region-count spikes and does not recover, the rebalance is falling behind — increase the limit. If TiKV RPC latency degrades, the rebalance is too aggressive — decrease it.

Output.

Time	leader-schedule-limit	region-schedule-limit	Effect
09:00 (peak)	2	2	slow, low-impact rebalance
22:00 (off-peak)	4	8	faster convergence
Manual split	(n/a)	(n/a)	region 17 -> 17a + 17b
After split	(auto-balance)	(auto-balance)	new region rebalanced

Rule of thumb. Treat PD as the cluster autopilot: set leader-schedule-limit and region-schedule-limit per your peak-hours policy, watch miss-peer-region-count as the health signal, and trust auto-splits for most cases — reach for manual splits only when a known hot key (time-ordered primary key, monotonic UUID) creates a persistent last-region hotspot.

Worked example — adding a new TiKV node and watching rebalance

Detailed explanation. The horizontal-scaling story of TiDB is "add a node and PD rebalances." Walk through the concrete steps: provision the node, add it to the cluster via tiup cluster scale-out, watch PD drain regions onto it, and verify the balance completes.

• Trigger. Cluster is at 80% disk usage; add a 5th TiKV node.
• Provisioning. New host at 10.0.3.14 with the same disk / CPU spec as existing TiKV nodes.
• Add. tiup cluster scale-out prod-cluster scale-out.yaml.
• Watch. Grafana tikv_store_size_bytes + pd_scheduler_event_count.

Question. Add the 5th TiKV node to an existing cluster and confirm rebalance completes within 24 hours.

Input.

Item	Value
Existing TiKV	10.0.3.10, .11, .12, .13 (4 nodes at 80% disk)
New node	10.0.3.14 (empty)
Cluster name	prod-cluster
Expected final state	~equal region count per node (~2500 regions each if 10k total)

Code.

# scale-out.yaml — new node spec
tikv_servers:
  - host: 10.0.3.14
    config:
      server.labels: { zone: "az-b", host: "kv-5" }

# 1. Add the new node
tiup cluster scale-out prod-cluster scale-out.yaml --user=tidb --identity_file=~/.ssh/tidb_rsa

# 2. Watch node come up; verify it registers with PD
tiup cluster display prod-cluster

# 3. Inspect region distribution over time
watch -n 30 "pd-ctl -u http://pd.internal:2379 store"

-- 4. From a MySQL client, poll region distribution
SELECT   STORE_ID, ADDRESS, REGION_COUNT, LEADER_COUNT
FROM     information_schema.tikv_store_status
ORDER BY STORE_ID;

-- Sample output at T+0 (right after adding):
-- STORE_ID  ADDRESS         REGION_COUNT  LEADER_COUNT
-- 1         10.0.3.10:20160  2500          835
-- 2         10.0.3.11:20160  2500          834
-- 3         10.0.3.12:20160  2500          833
-- 4         10.0.3.13:20160  2500          834
-- 5         10.0.3.14:20160  0             0

-- Sample output at T+6h:
-- 1         10.0.3.10:20160  2200          735
-- 2         10.0.3.11:20160  2200          734
-- 3         10.0.3.12:20160  2200          733
-- 4         10.0.3.13:20160  2200          734
-- 5         10.0.3.14:20160  1200          400

Step-by-step explanation.

1. tiup cluster scale-out is the one-command interface for adding a node. It provisions the binary, wires up the config, starts the tikv-server process, and registers the new node with PD. No manual steps.
2. Once the new node registers, PD begins scheduling region movements onto it based on the balance-region-scheduler. The default rate is a few regions per minute per store — deliberately conservative to avoid impacting OLTP latency.
3. Over the next several hours, region count on the new node grows from 0 toward ~2000 (the fair share for 5 nodes holding 10000 regions is 2000 each). Leader count also rebalances so no single node holds a disproportionate share of leader-side traffic.
4. If the rebalance is too slow (e.g. you added the node to relieve disk pressure and you need it drained faster), raise region-schedule-limit from 4 to 12 or higher. If it is causing OLTP latency spikes, lower it. Both are runtime-changeable.
5. Rebalance is complete when the region counts are within ~5% of each other and PD stops scheduling movements. At that point the disk usage on the existing nodes should also drop to a more comfortable level — for our 4-to-5 example, from 80% down to ~65%.

Output.

Time	Node 1	Node 2	Node 3	Node 4	Node 5 (new)
T+0h	2500	2500	2500	2500	0
T+6h	2200	2200	2200	2200	1200
T+24h	2000	2000	2000	2000	2000
Disk usage	80% -> 64%	80% -> 64%	80% -> 64%	80% -> 64%	0% -> 64%

Rule of thumb. Adding a TiKV node is a single command; rebalance is automatic and takes hours to a day depending on cluster size. Never do it under an active outage where disk is about to hit 100% — plan the add well before you need it, and watch region-schedule-limit to control impact.

Interview Question on TiDB architecture

A senior interviewer might ask: "Draw the TiDB architecture from memory. Cover PD, TiKV, TiFlash, and the SQL layer. Then walk through: what happens on a write, what happens on a read that hits TiFlash, and what happens when a TiKV node dies mid-transaction. Finally, tell me the minimum HA topology and why."

Solution Using a four-tier explanation, a walked write path, and a Raft-failover trace

Layer 1 — Client
    JDBC / mysql-client on port 4000

Layer 2 — TiDB SQL layer (stateless)
    Parse -> Plan -> CBO -> Coprocessor requests

Layer 3 — Placement Driver PD (3-node Raft)
    Metadata + TSO + region placement + rebalancing

Layer 4a — TiKV row store               Layer 4b — TiFlash column store
    RocksDB + Raft (3 voters/region)        Delta Tree (Raft learners)
    Sharded into 96 MB regions              Async pull from TiKV Raft log
    All OLTP writes land here               All OLAP scans land here (when CBO picks)

-- Minimum HA topology (10 nodes)
-- 3 PD (Raft quorum, tolerate 1 loss)
-- 2 TiDB (stateless; can be more)
-- 3 TiKV (region min replication factor 3)
-- 2 TiFlash (learner min for HA reads)

-- The write walkthrough (repeated for interview)
INSERT INTO orders(customer_id, total_cents, status)
VALUES (7, 1500, 'pending');
-- 1) TiDB pod parses + plans
-- 2) TiDB asks PD for start_ts
-- 3) TiDB prewrites to TiKV region leader
-- 4) Raft replicates to 2-of-3 followers
-- 5) TiDB asks PD for commit_ts, commits
-- 6) TiFlash learner replay (async, ~50 ms later)
-- 7) Client sees OK after step 5

The failure trace (TiKV node dies mid-transaction)
==================================================
t=0   Client sends INSERT
t=1   TiDB routes to region R17 leader = TiKV-node-3
t=2   TiKV-node-3 executes prewrite, replicates to Raft
t=3   TiKV-node-3 crashes (kernel panic)
t=4   TiKV-node-1 and TiKV-node-4 detect leader loss (Raft election timeout)
t=5   TiKV-node-1 wins election, becomes new leader for R17
t=6   TiDB retries prewrite against new leader (transparent to client)
t=7   Prewrite succeeds; commit follows normally
t=8   Client sees OK (typical extra latency: 500 ms - 3 s during election)

Meanwhile, PD:
- Detects TiKV-node-3 is down
- Marks all regions whose leader was on node-3 as "leader-lost"
- Watches for Raft re-elections to promote new leaders
- Schedules re-replication of node-3's follower replicas onto other nodes
- New replicas catch up from Raft log; region returns to 3-replica health
- Total time to full recovery: minutes (depends on data volume + rebalance rate)

Step-by-step trace.

Layer	Failure mode	Recovery
TiDB SQL pod dies	connection reset	client reconnects to another pod (<1s)
PD single node dies	invisible	Raft quorum survives; no impact
PD majority dies	new-tx writes stall	restore quorum -> writes resume
TiKV single node dies	region leaders re-elect (~1s)	rebalance re-replicates followers (minutes)
TiFlash node dies	OLAP fails over to another learner	new learner catches up from Raft log
Client mid-tx during TiKV failover	retry against new leader	mostly transparent; occasional 5-10s delays

After walking the interviewer through this, you have covered: architecture (4 tiers), write path (7 steps), read path (CBO picks store), and failure modes (5 components x 5 failure types). This is what a "draw TiDB from memory" answer looks like at senior level.

Output:

Component	Role	Failure blast radius
TiDB SQL pod	stateless request handler	1 pod = 0 downtime
PD (1 of 3)	metadata + TSO	0 downtime
PD (2 of 3)	metadata + TSO	writes stall until quorum returns
TiKV node	region replica	region leaders re-elect; ~1s stall
TiFlash node	learner replica	OLAP re-route; OLTP unaffected

Why this works — concept by concept:

• Stateless SQL tier — TiDB pods hold no local state (schema cache is refreshable from PD). Any pod can serve any query; adding pods is horizontal QPS scale-out. Killing a pod loses only in-flight queries; clients retry transparently.
• PD as the cluster brain — one 3-node Raft group owns metadata + TSO + scheduling. Splitting these responsibilities across the cluster (like Cassandra's gossip) is possible but complicates consistency; PD's centralised approach makes cross-node decisions trivially correct.
• Percolator 2PC + MVCC — Google's Percolator paper is the transaction model. Prewrite acquires locks; commit finalises. MVCC using start_ts + commit_ts gives snapshot isolation without long-held read locks. This is why analytical reads (long scans on TiFlash) never block OLTP writes.
• Raft learners for TiFlash — the learner extension to Raft (non-voting followers) is the key trick that makes HTAP possible without a second write path. TiFlash is "just another Raft peer that happens to not vote" — no separate CDC pipeline, no eventual-consistency drift, no schema translation layer.
• Cost — 3-node PD + 3-node TiKV min + 2-node TiFlash + 2-node TiDB = 10 nodes minimum for HA production, plus monitoring. Storage overhead is 3x (TiKV replication) + 1-2x (TiFlash learners) = 4-5x raw data footprint. Compared to MySQL + Snowflake + ETL infrastructure, this is comparable-to-cheaper at scale. Compared to CockroachDB it is similar node count but adds the TiFlash tier.

Design
Topic — design
Design problems on distributed database internals

Practice →

SQL Topic — sql SQL problems on schema and region layout

Practice →

---

3. HTAP query routing + cost-based optimizer

The CBO picks TiKV or TiFlash per query — point-lookups hit rows, aggregations hit columns via MPP, and workload isolation is a physical-tier property

The mental model in one line: TiDB's cost-based optimizer is the traffic cop that makes HTAP invisible — for every query it enumerates candidate plans (index seek on TiKV, table full scan on TiKV, columnar scan on TiFlash, MPP fan-out on TiFlash), estimates the cost of each using statistics (row count, distinct value count, average row size, index height), picks the cheapest, and dispatches — so from the client's perspective there is one query, one SQL dialect, and one connection string, while under the hood the same table is being served from two completely different physical layouts depending on the shape of the workload. Getting this axis right is what separates a TiDB deployment that delivers on the HTAP promise from one that runs OLAP queries against TiKV and burns OLTP capacity.

The CBO decision tree — what each factor contributes.

• Row count estimate. The single biggest input. TiDB collects table statistics via ANALYZE TABLE; the resulting histograms + CMS (count-min sketches) let the CBO estimate cardinality for any WHERE clause. Small estimates (say < 10k rows) favour TiKV index seeks; large estimates favour TiFlash columnar scans.
• Index availability. If the query's WHERE / JOIN columns have a covering index in TiKV, an index seek is cheap and wins. Without an index, a full scan is required, and TiFlash's columnar scan is 10-100x faster than TiKV's row scan.
• Column projection. If the query touches 3 columns of a 50-column table, TiFlash reads only those 3 columns (columnar layout). TiKV would read the whole row for every match. The narrower the projection relative to the row width, the more TiFlash wins.
• Aggregation shape. GROUP BY, SUM, AVG, COUNT(DISTINCT) all benefit massively from TiFlash's MPP engine, which parallelises the hash-agg across TiFlash nodes. Pure filter-and-project (SELECT-WHERE with no GROUP BY) can go either way.
• Join. For each join side, the CBO independently considers TiKV vs TiFlash. For the join algorithm, it considers index join (TiKV-friendly), hash join (either), broadcast hash join (usually TiFlash with a small side), and MPP shuffle join (TiFlash with two large sides).

MPP — massively parallel processing on TiFlash.

• What it is. When the CBO picks TiFlash for a query with GROUP BY or JOIN over large data, it dispatches an MPP plan: each TiFlash node scans its own region-learner replicas, does local partial aggregation, then exchanges intermediate results with peer TiFlash nodes over a shuffle channel, then does final aggregation. This is architecturally identical to a small Presto or Snowflake execution engine.
• When it fires. Automatically when the CBO estimates the query cost is lower under MPP than under TiKV or single-node TiFlash. You can force it with SET tidb_allow_mpp = ON (default) and SET tidb_enforce_mpp = ON (skips cost check).
• What it costs. More network traffic (shuffle between TiFlash nodes) but far more parallelism. For queries touching 100M+ rows, MPP is typically 10-100x faster than the alternative.
• Constraints. MPP works only when the target table has TIFLASH REPLICA >= 1 for every touched table. Mixed queries (one table on TiKV only, one on TiFlash) fall back to non-MPP execution.

Workload isolation — why OLAP does not slow OLTP.

• Physical tier. TiKV and TiFlash are separate processes on separate nodes (in the standard deployment). A CPU-bound analytical query on TiFlash cannot steal CPU from the TiKV node serving OLTP writes.
• Raft learner asymmetry. TiFlash pulls the Raft log; it never pushes back. Analytical query load on TiFlash creates no back-pressure on TiKV's Raft groups.
• Read-only. All writes go through TiKV; TiFlash is a read-only replica. There is no lock contention between OLAP scans and OLTP writes on the same rows.
• Optimizer safeguards. Even without physical isolation, the CBO's routing decision means the OLAP query goes to the tier best-suited for it — reducing the chance that a bad plan drags down the wrong tier.

Optimizer hints — when to override the CBO.

• READ_FROM_STORAGE(TIFLASH[t]). Force reads of table t from TiFlash. Use when you know the query is analytical but the CBO's stats are stale or wrong.
• READ_FROM_STORAGE(TIKV[t]). Force reads of table t from TiKV. Use when you know point-lookup latency matters and CBO picked TiFlash by mistake.
• USE_INDEX(t, idx_name). Force a specific index on TiKV. Use for queries where CBO picks a worse index.
• STREAM_AGG() / HASH_AGG(). Force aggregation algorithm.
• SET SESSION tidb_enforce_mpp = ON. Skip cost check; force MPP execution when applicable.

Common interview probes on HTAP routing.

• "How does TiDB decide TiKV vs TiFlash?" — required answer: the CBO uses row-count estimates from statistics.
• "What is MPP?" — massively parallel processing across TiFlash nodes; MPP plans fan out scans and aggregations.
• "How do you force a query to TiFlash?" — the READ_FROM_STORAGE(TIFLASH[t]) hint.
• "Why does OLAP not slow OLTP?" — physical tier isolation + learner-only asymmetry.
• "What if the CBO picks the wrong store?" — refresh stats (ANALYZE TABLE); add hint if stats still lie.

Worked example — reading EXPLAIN plans and identifying store choice

Detailed explanation. The single most useful debugging skill on TiDB is reading the EXPLAIN output. Every operator in the plan has a task column that names which physical store executed it — root (TiDB SQL layer), cop[tikv] (TiKV coprocessor task), cop[tiflash] (TiFlash coprocessor task), or batchCop[tiflash] (TiFlash MPP task). Walk through EXPLAIN for three query shapes.

• Query A — point lookup. SELECT * FROM orders WHERE id = 42.
• Query B — small range scan. SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 HOUR.
• Query C — big aggregation. SELECT region, SUM(total_cents) FROM orders GROUP BY region.

Question. Run EXPLAIN on each query, read the plans, identify the store used, and explain why the CBO chose it.

Input.

Query	Rows expected	Best store	Why
Query A (WHERE id=42)	1	TiKV index seek	primary-key lookup
Query B (last hour)	~10k	TiKV index range	idx_created_at seek
Query C (GROUP BY region)	10M scanned -> 50 rows	TiFlash MPP	columnar scan + parallel agg

Code.

-- Query A — point lookup
EXPLAIN SELECT * FROM orders WHERE id = 42;
-- id                task    estRows  operator info
-- Point_Get_1       root    1        table:orders, handle:42
--
-- Verdict: pure TiDB root; TiKV point-get via primary key; ~1 ms

-- Query B — small range scan
EXPLAIN SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 HOUR;
-- id                            task         estRows    operator info
-- IndexRangeScan_5              cop[tikv]    10500      table:orders, index:idx_created_at
--   TableRowIDScan_6            cop[tikv]    10500      table:orders, keep order:false
-- IndexLookUp_7                 root         10500      (index -> row fetch)
--
-- Verdict: TiKV index range scan; ~50 ms

-- Query C — big aggregation
EXPLAIN SELECT region, SUM(total_cents) FROM orders GROUP BY region;
-- id                    task                 estRows       operator info
-- TableFullScan_9       batchCop[tiflash]    100000000     table:orders, keep order:false
-- HashAgg_10 (partial)  batchCop[tiflash]    100000000     group by:region, funcs:sum(total_cents)
-- ExchangeSender_11     batchCop[tiflash]    50            hash partition on region
-- ExchangeReceiver_12   batchCop[tiflash]    50
-- HashAgg_13 (final)    batchCop[tiflash]    50            group by:region
-- TableReader_14        root                 50
--
-- Verdict: TiFlash MPP; parallel scan + hash-agg + shuffle exchange; ~500 ms for 100M rows

-- If you want to force TiFlash for query B (say for a dashboard that always aggregates the last hour)
EXPLAIN SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */ *
FROM   orders
WHERE  created_at > NOW() - INTERVAL 1 HOUR;
-- Now the plan uses cop[tiflash] TableFullScan

-- Verify by running the actual query with runtime info
EXPLAIN ANALYZE
SELECT region, SUM(total_cents) FROM orders GROUP BY region;
-- Adds actual row counts, execution time per operator, and memory used

Step-by-step explanation.

1. Query A shows Point_Get_1 at root task — TiDB knows this is a primary-key lookup and issues a single point-get to the TiKV region holding that key. No scan, no aggregation; the plan is trivially cheap.
2. Query B shows IndexRangeScan on cop[tikv] — the CBO estimated ~10500 rows via the idx_created_at histogram and picked an index range scan. The IndexLookUp wraps it: seek the index, get RowIDs, fetch the rows. Classic OLTP pattern.
3. Query C shows TableFullScan on batchCop[tiflash] — the CBO estimated 100M rows scanned to produce 50 grouped results. This is the MPP fan-out shape: parallel scan on every TiFlash node, local partial aggregation, exchange (shuffle) on region, final aggregation.
4. The ExchangeSender / ExchangeReceiver operators mark the shuffle boundary between TiFlash nodes. Partition function is hash on region — so all rows for the same region end up on the same TiFlash node for the final aggregation, guaranteeing correctness.
5. The /*+ READ_FROM_STORAGE(TIFLASH[t]) */ hint overrides the CBO's choice. Use it sparingly — it is an escape valve for cases where stats are stale or the CBO's cost model has a blind spot for your workload.

Output.

Query	Plan operator	Store	Latency
A (id=42)	Point_Get	root -> TiKV	~1 ms
B (1h range)	IndexRangeScan	cop[tikv]	~50 ms
C (GROUP BY region)	HashAgg + Exchange	batchCop[tiflash] MPP	~500 ms
B forced to TiFlash	TableFullScan	cop[tiflash]	~200 ms (worse; wrong tool)
C without TiFlash replica	TableFullScan	cop[tikv]	~30-60s (catastrophic)

Rule of thumb. Every EXPLAIN in TiDB should tell you which store executed each operator. Learn to read root / cop[tikv] / cop[tiflash] / batchCop[tiflash] at a glance; that skill is the difference between a TiDB deployment you trust and one that surprises you at 3 AM.

Worked example — statistics maintenance and the stale-stats trap

Detailed explanation. The CBO's routing decisions are only as good as its statistics. Stale stats (e.g. table grew from 1M rows to 100M rows since last ANALYZE) can cause the CBO to pick TiKV for a query that should have gone to TiFlash — turning a 500 ms MPP query into a 60-second TiKV scan and consuming OLTP capacity. Walk through the stats lifecycle: how to view, how to trigger, how to schedule.

• View. SHOW STATS_META for table-level; SHOW STATS_HISTOGRAMS for per-column histograms.
• Trigger. ANALYZE TABLE t for full; ANALYZE TABLE t WITH X SAMPLES for sampled.
• Auto-analyze. TiDB auto-analyzes tables whose modification ratio exceeds tidb_auto_analyze_ratio (default 0.5).
• Health. Watch tidb_auto_analyze_partition_batch_size and the auto-analyze log for stragglers.

Question. Diagnose a query that recently regressed from 500 ms to 60 seconds, fix stale stats, and set up ongoing maintenance.

Input.

Component	Value
Table	orders
Rows in stats	1,000,000 (stale)
Actual rows	100,000,000
Query	SELECT region, SUM(total_cents) FROM orders GROUP BY region
Symptom	latency jumped from 500 ms to 60 s

Code.

-- 1. Confirm the current stats vs reality
SHOW STATS_META WHERE table_name = 'orders';
-- table_name  modify_count  row_count   update_time
-- orders      99000000      1000000     2026-01-15 03:00:00
--                    ^^^^         ^^^ stats say 1M rows but ~99M modifications since;
--                                     ratio ~99x means stats are catastrophically stale

-- 2. Confirm with a direct count
SELECT COUNT(*) FROM orders;
-- 100,238,442

-- 3. Trigger a full analyze
ANALYZE TABLE orders;
-- (runs; can be minutes on 100M rows)

-- 4. Verify stats refreshed
SHOW STATS_META WHERE table_name = 'orders';
-- table_name  modify_count  row_count    update_time
-- orders      0             100238442    2026-07-27 10:15:22

-- 5. Re-run EXPLAIN; verify CBO now picks TiFlash MPP
EXPLAIN SELECT region, SUM(total_cents) FROM orders GROUP BY region;
-- Operators back to batchCop[tiflash]

-- 6. Ensure auto-analyze is enabled and thresholds are sensible
SHOW VARIABLES LIKE 'tidb_auto_analyze%';
-- tidb_enable_auto_analyze     ON
-- tidb_auto_analyze_start_time 00:00 +0000
-- tidb_auto_analyze_end_time   06:00 +0000
-- tidb_auto_analyze_ratio      0.5

-- 7. For large tables, run a scheduled manual analyze during off-peak
--    (cron on a lightweight host; tidb-ctl or mysql-client both work)
--    * 3 * * *  mysql -h tidb -e "ANALYZE TABLE orders_db.orders WITH 100000 SAMPLES;"
--
-- The WITH X SAMPLES clause lets very large tables be analyzed quickly
-- with slightly less precise histograms; the trade-off is usually worth it
-- for tables > 1B rows where full analyze becomes expensive.

-- 8. Optional: pin specific columns' histograms to always be current
--    (useful for hot dashboard filter columns)
CREATE TABLE orders_analyze_targets AS
  SELECT 'region' AS col UNION ALL SELECT 'created_at' UNION ALL SELECT 'status';

-- Nightly cron: for each row, ANALYZE INDEX or ANALYZE COLUMN

Step-by-step explanation.

1. SHOW STATS_META reveals the stale-stats signature: row_count is 1M but modify_count is 99M — meaning 99M rows have been inserted / updated / deleted since the last analyze. Ratio > 0.5 means auto-analyze should have fired; if it did not, likely a maintenance window issue or the auto-analyze schedule was disabled.
2. SELECT COUNT(*) confirms the actual row count (100M). This is the "ground truth" that stats should be tracking.
3. ANALYZE TABLE orders triggers a full stats rebuild — samples the table, builds histograms per indexed column, updates CMS sketches. On 100M rows this takes minutes; run it off-peak or use WITH X SAMPLES for a faster approximate analyze.
4. After the analyze, SHOW STATS_META reflects the true row count and modify_count resets to 0. The next call to the CBO will use the fresh stats.
5. SHOW VARIABLES LIKE 'tidb_auto_analyze%' reveals the auto-analyze policy. Confirm it is enabled, that the schedule window covers your off-peak hours, and that the ratio (default 0.5 = 50% modifications) matches your write pattern. For write-heavy tables you may want a stricter ratio like 0.2.

Output.

Metric	Before ANALYZE	After ANALYZE
row_count in stats	1,000,000	100,238,442
modify_count	99,000,000	0
CBO plan for GROUP BY	cop[tikv] TableFullScan	batchCop[tiflash] MPP
Query latency	60 s	500 ms
OLTP impact	high (scan burns TiKV CPU)	none (isolated to TiFlash)

Rule of thumb. Stale statistics are the single most common cause of TiDB query regressions. Set up nightly ANALYZE TABLE for your top 10-20 largest tables regardless of auto-analyze — the belt-and-braces defense costs cluster CPU but prevents the CBO from making bad routing decisions during business hours.

Worked example — MPP plan shapes and the shuffle boundary

Detailed explanation. MPP execution is where TiFlash really earns its keep. Walk through a two-table join with GROUP BY: the CBO builds an MPP plan with scans on both tables, a shuffle-join on the join key, a local partial aggregation, another shuffle on the group key, and a final aggregation. This is a small-scale Presto or Snowflake execution plan.

• Query. Sum total per region per customer segment.
• Tables. orders (id, customer_id, region, total_cents) + customers (id, segment).
• Both tables. TIFLASH REPLICA = 2, so both are MPP-eligible.
• Expectation. MPP plan with two ExchangeSender/Receiver pairs.

Question. Build the query, run EXPLAIN, and walk through each MPP operator.

Input.

Table	Rows	TiFlash replica	Join key	Group key
orders	100M	2	customer_id	region
customers	5M	2	id	segment

Code.

-- The query
EXPLAIN
SELECT   o.region, c.segment, SUM(o.total_cents) AS revenue
FROM     orders   o
JOIN     customers c ON o.customer_id = c.id
WHERE    o.created_at >= '2026-07-01'
GROUP BY o.region, c.segment
ORDER BY revenue DESC
LIMIT    50;

-- Simplified plan (root -> MPP operators)
-- id                              task                estRows     operator info
-- TopN_20                         root                50          revenue DESC, offset:0, count:50
--   TableReader_22                root                50          (from TiFlash)
--     ExchangeSender_23           batchCop[tiflash]   50          hash on nothing (gather)
--       TopN_24                   batchCop[tiflash]   50          local top-n
--         HashAgg_25 (final)      batchCop[tiflash]   500         group by:region,segment
--           ExchangeReceiver_26   batchCop[tiflash]   500
--           ExchangeSender_27     batchCop[tiflash]   500         hash on region,segment
--             HashAgg_28 (partial) batchCop[tiflash]  500         group by:region,segment funcs:sum
--               HashJoin_29        batchCop[tiflash]  20000000    inner join on o.customer_id = c.id
--                 ExchangeReceiver_30 batchCop[tiflash] 100000000
--                 ExchangeSender_31   batchCop[tiflash] 100000000 hash on customer_id
--                   Selection_32      batchCop[tiflash] 100000000 created_at >= 2026-07-01
--                     TableFullScan_33 batchCop[tiflash] 100000000 table:orders
--                 ExchangeReceiver_34 batchCop[tiflash] 5000000
--                 ExchangeSender_35   batchCop[tiflash] 5000000  hash on id
--                   TableFullScan_36  batchCop[tiflash] 5000000  table:customers

-- Force MPP if the CBO picks otherwise
SET tidb_enforce_mpp = ON;

-- Watch runtime info
EXPLAIN ANALYZE
SELECT ... (same query as above);
-- The actual times per operator + memory + disk spill (if any) are visible in the analyze output

Step-by-step explanation.

1. Bottom-up: the two TableFullScan operators run on every TiFlash node in parallel, each scanning its local region-learner replicas. The Selection operator applies the created_at >= '2026-07-01' filter locally, dropping rows early to reduce shuffle volume.
2. The ExchangeSender operators partition the scanned rows by hash — orders by customer_id, customers by id. This is the shuffle boundary: every TiFlash node sends orders keyed by customer_id % N to the peer node responsible for that partition. After the exchange, all orders and customers with the same key live on the same node.
3. The HashJoin performs an inner join locally on each node (since matching keys are colocated after shuffle). This is a "shuffle hash join" — the standard MPP algorithm for two large tables.
4. The HashAgg (partial) does local aggregation on each node — computing partial sums for each (region, segment) group. This dramatically reduces the volume of data crossing the next shuffle.
5. The second ExchangeSender shuffles the partial aggregates by (region, segment). The HashAgg (final) combines partials into finals. Then a local TopN reduces to 50 rows per node, a final gather-exchange collects results at the root, and TiDB SQL layer returns to the client.

Output.

Operator	Purpose	Data flow
TableFullScan	scan region-learners	100M orders, 5M customers per-node local
Selection	filter early	drops rows before shuffle
ExchangeSender/Receiver #1	shuffle by join key	100M + 5M rows shuffled
HashJoin	inner join, colocated	20M joined rows per-node local
HashAgg (partial)	pre-aggregate	~500 partials per-node
ExchangeSender/Receiver #2	shuffle by group key	~2500 partials total shuffled
HashAgg (final)	combine partials	~500 group rows
TopN	top 50 per node	50 per node
Root TableReader + TopN	gather + final top-N	50 rows returned

Rule of thumb. MPP plans are read bottom-up. Every ExchangeSender/Receiver pair is a shuffle boundary; if you see three or more, the plan is expensive on network but massively parallel on compute. For queries touching 100M+ rows with GROUP BY, MPP is almost always the right answer — trust the CBO unless EXPLAIN ANALYZE proves otherwise.

Interview Question on HTAP query routing

A senior interviewer might ask: "You have a dashboard that runs SELECT region, SUM(total_cents) FROM orders GROUP BY region every 30 seconds. The table has 100M rows and TIFLASH REPLICA = 2. It used to take 500 ms, but this morning it started taking 60 seconds and OLTP p99 latency is spiking. Walk me through diagnosis, root cause, fix, and prevention."

Solution Using EXPLAIN diagnosis, stats refresh, hint fallback, and monitoring rules

-- Step 1 — confirm the current plan
EXPLAIN SELECT region, SUM(total_cents) FROM orders GROUP BY region;
-- If the plan shows cop[tikv] TableFullScan, the CBO chose the wrong store.
-- Root cause candidates:
--   (a) stale stats
--   (b) TiFlash replica temporarily unavailable
--   (c) session variable disabled MPP
--   (d) CBO cost model regression

-- Step 2 — check TiFlash replica status
SELECT TABLE_SCHEMA, TABLE_NAME, REPLICA_COUNT, AVAILABLE, PROGRESS
FROM   information_schema.tiflash_replica
WHERE  TABLE_NAME = 'orders';
-- AVAILABLE=1 means TiFlash is ready; AVAILABLE=0 means fall back to TiKV

-- Step 3 — check statistics
SHOW STATS_META WHERE table_name = 'orders';
-- If modify_count is huge relative to row_count, stats are stale

-- Step 4 — fix root cause (stale stats)
ANALYZE TABLE orders;
-- Wait ~2-5 minutes on 100M rows

-- Step 5 — re-run EXPLAIN; confirm CBO chose TiFlash MPP
EXPLAIN SELECT region, SUM(total_cents) FROM orders GROUP BY region;
-- Operators should now be batchCop[tiflash]

-- Step 6 — as a belt-and-braces, add an optimizer hint to the dashboard query
--         so future regressions are prevented
SELECT   /*+ READ_FROM_STORAGE(TIFLASH[orders]) */
         region, SUM(total_cents)
FROM     orders
GROUP BY region;

-- Step 7 — set up alerts on stale stats
--         (Grafana query on modify_count / row_count ratio)
-- Alert: SUM(modify_count) / GREATEST(SUM(row_count), 1) > 0.3 for 30 minutes

# Prometheus / Grafana alert rules
groups:
- name: tidb-stale-stats
  rules:
  - alert: TiDBStaleStats
    expr: tidb_stats_modify_count_ratio{table="orders"} > 0.3
    for: 30m
    annotations:
      summary: "Stats for orders are >30% stale; CBO routing may regress"

  - alert: TiFlashReplicaUnavailable
    expr: tiflash_replica_available{table="orders"} < 1
    for: 5m
    annotations:
      summary: "TiFlash replica for orders is unavailable; queries falling back to TiKV"

  - alert: OLTPLatencyRegression
    expr: histogram_quantile(0.99, tidb_server_handle_query_duration_seconds_bucket) > 0.1
    for: 5m
    annotations:
      summary: "OLTP p99 > 100 ms; likely misrouted analytical query"

Step-by-step trace.

Step	Symptom	Fix
Confirm plan	EXPLAIN shows cop[tikv] on GROUP BY	mis-routing detected
Check replica	AVAILABLE=1	not a replica issue
Check stats	modify_count/row_count = 0.99	stale stats found
Refresh stats	ANALYZE TABLE orders	~5 min run
Re-EXPLAIN	batchCop[tiflash] returned	correct routing restored
Belt-and-braces	add READ_FROM_STORAGE hint	prevents future CBO drift
Alerts	stale-stats + replica-unavailable + oltp-p99	prevents blind spot

After the fix, the dashboard query returns to 500 ms; the OLTP p99 spike disappears within minutes as the misrouted TiKV scan stops burning CPU. The hint on the dashboard query is documented in the codebase; the alerts fire next time stats drift, preventing a repeat.

Output:

Metric	During incident	After fix
Dashboard latency	60 s	500 ms
OLTP p99 latency	200 ms	15 ms
TiKV CPU	85%	40%
TiFlash CPU	5%	30%
Stats freshness	modify/row = 0.99	modify/row = 0
Time to detect	30 min (customer complaint)	5 min (alert-driven)

Why this works — concept by concept:

• CBO routing — the cost-based optimizer makes per-query store decisions. Correct routing needs correct statistics; when stats lie, the CBO makes bad choices. Fresh stats are the foundation of HTAP performance.
• EXPLAIN as the source of truth — the task column (root / cop[tikv] / cop[tiflash] / batchCop[tiflash]) tells you exactly which store served each operator. If EXPLAIN shows the wrong store, no amount of runtime tuning will help.
• Optimizer hints as the escape valve — READ_FROM_STORAGE(TIFLASH[t]) bypasses the CBO's store choice. Use it sparingly (they are debt: every hint is a maintenance burden), but for critical dashboard queries the belt-and-braces protection is worth it.
• ANALYZE + auto-analyze policy — stats freshness is a maintenance concern. Nightly manual analyze on your top-N tables plus auto-analyze during off-peak covers most write patterns. Watch modify_count / row_count as the health signal.
• Cost — one ANALYZE run per table per night (minutes to hours depending on size), a handful of optimizer hints on critical queries (one-line comment additions), three Grafana alerts (stale-stats + replica-availability + OLTP-p99). Compared to the cost of a 60-second dashboard query running every 30 seconds during business hours (thousands of misrouted queries per day burning OLTP capacity), the maintenance overhead is negligible.

SQL
Topic — aggregation
Aggregation and query-planning problems

Practice →

SQL Topic — sql SQL problems on cost-based optimization

Practice →

---

4. MySQL compatibility + migration

TiDB speaks MySQL 5.7 / 8.0 on the wire — same JDBC, same mysql-client, same connection string, with well-documented gaps that senior DEs must know before promising a zero-code-change migration

The mental model in one line: TiDB implements the MySQL 5.7 / 8.0 wire protocol and roughly 99% of MySQL SQL — connections use the standard mysql:// URL, JDBC drivers work unchanged, the mysql command-line client Just Works — but the last 1% (stored procedures, triggers, spatial types, some legacy character sets, cascade-heavy foreign keys until recent versions) is precisely the surface an experienced MySQL team hits during migration, and senior data engineers must be able to enumerate those gaps and design around them before promising a zero-downtime, zero-code-change cutover. Migration itself is handled by PingCAP's DM (Data Migration) tool, which does a consistent dump-and-load followed by continuous binlog tailing; on the downstream side, TiCDC emits change events to Kafka for the rest of the stack that expects a CDC feed.

The compatibility surface — what works, what does not.

• Wire protocol. Full MySQL 5.7 protocol; TiDB advertises server version as 5.7.25-TiDB-v<version> so clients that check the version see MySQL 5.7. TLS is supported. Prepared statements work. Multi-statement queries work.
• SQL surface. SELECT, INSERT, UPDATE, DELETE, JOIN (INNER, LEFT, RIGHT, CROSS), subqueries, CTEs (including recursive), window functions, aggregations, GROUP BY, HAVING, ORDER BY, LIMIT — all standard MySQL SQL works. DDL (CREATE / ALTER / DROP TABLE, indexes) works with the online-DDL story that Vitess users would recognise.
• Data types. Integers (TINYINT through BIGINT, signed/unsigned), floating point, DECIMAL, DATE / DATETIME / TIMESTAMP, VARCHAR / TEXT, BLOB, JSON, ENUM, SET, BINARY / VARBINARY — all supported.
• Transactions. Full BEGIN / COMMIT / ROLLBACK, savepoints, isolation levels (READ COMMITTED, REPEATABLE READ), snapshot isolation via MVCC, optimistic and pessimistic locking modes.
• Missing. Stored procedures (CREATE PROCEDURE — not supported), triggers (CREATE TRIGGER — not supported), events (CREATE EVENT — not supported), spatial types (GEOMETRY, POINT — not supported), some legacy character sets (ucs2, sjis — partial or missing), foreign keys with cascading actions (basic FK support was added in TiDB 6.6+ but is still gaining adoption; older versions did not enforce them at all).

Foreign keys — the recent addition that changed the migration story.

• Historical stance. For years, TiDB parsed FK declarations but did not enforce them. This was fine for greenfield deployments but broke MySQL migrations that relied on RESTRICT / CASCADE / SET NULL behaviour.
• Current stance (TiDB 6.6+). Foreign keys are enforced, including cascading actions. Migration compatibility is dramatically better.
• Caveats. Cross-region FKs (parent and child in different regions) add cross-region write cost — each FK check may go to a different TiKV region. High-fanout cascades (deleting one row cascading to millions) can be slow. Best practice: enforce FKs at application level for hot paths; use DB-level FKs for cold paths where the safety net matters more than the cost.

Migration via DM — the three phases.

• Dump. DM connects to source MySQL, acquires a consistent snapshot (FLUSH TABLES WITH READ LOCK briefly + SHOW MASTER STATUS), dumps all rows via mydumper-derived parallel dump.
• Load. DM loads the dumped files into TiDB via parallel loaders. Typical throughput: TB per hour on a well-provisioned cluster.
• Sync. DM tails the source MySQL binlog from the position recorded during the dump, translates each row event into TiDB DML, applies. Continuous — DM catches up to head, then keeps pace with ongoing writes indefinitely.
• Cutover. When downstream systems are ready, pause application writes to MySQL, let DM catch up to zero lag, redirect application to TiDB, resume writes. Total downtime: seconds to minutes.

TiCDC — the downstream change feed.

• What it is. A native TiDB change-data-capture reader that watches TiKV's Raft log and emits change events. Sinks: Kafka, Pulsar, MySQL (for replicating TiDB back to MySQL), TiDB (for cluster-to-cluster replication), and a few S3-based options.
• When to use. Any downstream consumer that expects a CDC feed (event bus, search index, real-time analytics warehouse) that is not TiFlash. TiFlash is built-in; TiCDC is for everything else.
• Format. Configurable — Debezium-compatible JSON, Canal-style JSON, or Avro. Same shape as Debezium's Postgres/MySQL connectors, so downstream consumers that already handle Debezium work with TiCDC unchanged.
• Ordering. Per-key ordering within a Kafka partition (partition key derived from primary key). Global ordering is not guaranteed; consumers must design for at-least-once, per-key ordered.

Common interview probes on MySQL compatibility.

• "Is it a MySQL fork?" — required answer: no; it is a from-scratch implementation of the MySQL wire protocol.
• "What MySQL features are missing?" — stored procedures, triggers, spatial types, events, some legacy character sets; older versions also lacked FK enforcement.
• "How do you migrate from MySQL?" — DM tool: dump, load, then continuous binlog sync.
• "What is TiCDC?" — native change feed; emits Debezium-compatible events to Kafka.
• "Can you replicate TiDB back to MySQL?" — yes; TiCDC has a MySQL sink for downstream compatibility.

Worked example — DM configuration and end-to-end migration

Detailed explanation. The canonical MySQL → TiDB migration setup: deploy DM, configure the source, define a task, run the initial dump, verify parity, then leave DM running as continuous sync until cutover day. Walk through the config and the cutover playbook.

• Source. MySQL 5.7 primary at mysql-primary.internal:3306, database orders_db.
• Target. TiDB cluster at tidb-vip.internal:4000, same database name.
• DM cluster. 3 dm-worker nodes for HA + 1 dm-master.
• Cutover. Planned Sunday 03:00 UTC; 5-minute app write pause.

Question. Write the DM source config, the task config, the parity check, and the cutover runbook.

Input.

Component	Value
Source	MySQL 5.7 at mysql-primary.internal:3306
Source DB	orders_db (~500 GB, 12 tables)
Target	TiDB at tidb-vip.internal:4000
DM workers	3 nodes for HA
Expected sync lag steady-state	< 1 second
Cutover downtime budget	< 5 minutes

Code.

# 1. Source config — one file per source MySQL
# source1.yaml
source-id: "mysql-order-primary"
from:
  host: "mysql-primary.internal"
  port: 3306
  user: "dm_reader"
  password: "${MYSQL_PASSWORD}"
enable-gtid: true
relay-dir: "/dm/relay"

# Register the source with the DM master
dmctl --master-addr=dm-master:8261 operate-source create source1.yaml

# 2. Task config — the migration definition
# migrate-orders.yaml
name: migrate-orders
task-mode: all               # 'all' = dump + load + sync; 'incremental' = sync only
target-database:
  host: "tidb-vip.internal"
  port: 4000
  user: "dm_writer"
  password: "${TIDB_PASSWORD}"

mysql-instances:
  - source-id: "mysql-order-primary"
    block-allow-list: "orders-only"
    mydumper-config-name: "orders-dump"
    loader-config-name: "orders-load"
    syncer-config-name: "orders-sync"

block-allow-list:
  orders-only:
    do-dbs: ["orders_db"]
    ignore-tables:
      - { db-name: "orders_db", tbl-name: "temp_*" }   # skip temp tables

mydumpers:
  orders-dump:
    threads: 16
    chunk-filesize: 256       # MB per file
    extra-args: "--skip-tz-utc --statement-size=100000000"

loaders:
  orders-load:
    pool-size: 32
    dir: "./dumped_data"
    on-duplicate: replace     # target may have pre-existing rows

syncers:
  orders-sync:
    worker-count: 32
    batch: 200
    enable-gtid: true
    safe-mode: false          # 'true' does INSERT on DUPLICATE UPDATE; safer but slower

# 3. Start the migration
dmctl --master-addr=dm-master:8261 start-task migrate-orders.yaml

# 4. Monitor progress
dmctl --master-addr=dm-master:8261 query-status migrate-orders
# Look for:
#   dump: progress = 100%
#   load: progress = 100%
#   sync: syncerBinlog = current MySQL binlog position; secondsBehindMaster < 5

# 5. Parity check — count(*) + checksum per table
import pymysql

def parity_check(table: str) -> dict:
    src = pymysql.connect(host='mysql-primary.internal', port=3306, user='ro', password='***')
    tgt = pymysql.connect(host='tidb-vip.internal',   port=4000, user='ro', password='***')

    q_count = f"SELECT COUNT(*) FROM orders_db.{table}"
    q_sum   = f"SELECT COALESCE(SUM(id), 0) AS s FROM orders_db.{table}"

    with src.cursor() as c: c.execute(q_count); src_count = c.fetchone()[0]
    with tgt.cursor() as c: c.execute(q_count); tgt_count = c.fetchone()[0]
    with src.cursor() as c: c.execute(q_sum);   src_sum   = c.fetchone()[0]
    with tgt.cursor() as c: c.execute(q_sum);   tgt_sum   = c.fetchone()[0]

    return {
        "table": table,
        "src_count": src_count,
        "tgt_count": tgt_count,
        "count_match": src_count == tgt_count,
        "src_sum": src_sum,
        "tgt_sum": tgt_sum,
        "sum_match": src_sum == tgt_sum,
    }

for t in ["orders", "order_items", "customers", "inventory", "shipments"]:
    print(parity_check(t))

# 6. Cutover runbook (Sunday 03:00 UTC)
03:00  Announce start; disable feature flags that trigger writes
03:02  Pause application writes to MySQL (kubectl scale --replicas=0 on write pods)
03:03  Wait for DM secondsBehindMaster = 0
03:04  Final parity check on top 5 tables
03:05  Update application config: MYSQL_HOST -> TIDB_VIP
03:06  Restart application pods pointing at TiDB
03:08  Smoke-test key writes; verify TiCDC downstream is receiving them
03:10  All-clear; keep DM running as safety net for 48h

Step-by-step explanation.

1. The source config registers each MySQL primary with DM. Using GTID (enable-gtid: true) instead of file+position for binlog resume is dramatically more robust across binlog rotations.
2. The task config combines dump, load, and sync into one lifecycle. task-mode: all runs the full sequence; incremental would skip dump/load and start from a specified binlog position (useful for re-syncing a subset of tables).
3. block-allow-list scopes which databases and tables are migrated. Skipping temp tables (ignore-tables with a pattern) avoids polluting TiDB with per-session junk.
4. mydumpers.threads = 16 parallelises the dump; loaders.pool-size = 32 parallelises the load. Tune both for your source read bandwidth and target write bandwidth. On a 500 GB database with 12 tables, expect dump-and-load to complete in 4-8 hours.
5. Steady-state sync usually stays under 1 second behind master. If it grows persistently, either increase syncer.worker-count or investigate whether a specific DDL / DML is expensive on TiDB (e.g. a schema change that rewrites a huge table).

Output.

Phase	Elapsed	Progress	Comment
Dump	3 h	500 GB dumped	16 threads on source
Load	4 h	500 GB loaded into TiDB	32 loader pool
Initial sync	30 min	catch up to binlog head	continuous from here
Steady state	ongoing	< 1s behind master	32 syncer workers
Cutover downtime	10 min	app restart + smoke test	within 5-min SLA extended slightly

Rule of thumb. For any MySQL -> TiDB migration, use DM with GTID enable, parallel dump-and-load sized to your bandwidth, and a parity harness (count + sum) run per table before cutover. Keep DM running for 48-72 hours post-cutover as a rollback safety net — if something explodes on TiDB, you can flip the application back to MySQL (which has been kept in sync by an opposite-direction DM task, if you set that up).

Worked example — TiCDC change feed to Kafka

Detailed explanation. After the migration, downstream consumers that used to read the MySQL binlog (via Debezium) need to switch to TiCDC. Walk through the setup: deploy TiCDC nodes, create a changefeed, verify events landing in Kafka.

• TiCDC deployment. 3 cdc-server nodes; deployed via tiup cluster alongside TiDB.
• Sink. Kafka cluster at kafka:9092.
• Topic naming. One topic per table (prod.orders_db.orders, etc.).
• Format. Canal JSON (Debezium-compatible variant that many CDC tools already parse).

Question. Create a TiCDC changefeed that streams orders_db to Kafka and verify the event shape.

Input.

Component	Value
CDC nodes	3 (HA)
Sink	Kafka at kafka:9092
Databases in scope	orders_db (all tables)
Format	canal-json
Ordering	per-primary-key

Code.

# 1. Add TiCDC to the cluster (via tiup)
# cdc-scale-out.yaml
# cdc_servers:
#   - host: 10.0.6.10
#   - host: 10.0.6.11
#   - host: 10.0.6.12
tiup cluster scale-out prod-cluster cdc-scale-out.yaml

# 2. Verify CDC nodes are running
tiup cluster display prod-cluster

# 3. Create a changefeed
cdc cli changefeed create \
  --server=http://10.0.6.10:8300 \
  --changefeed-id="orders-to-kafka" \
  --sink-uri="kafka://kafka:9092/prod?protocol=canal-json&partition-num=8&max-message-bytes=10485760" \
  --config=changefeed.toml

# changefeed.toml — filter + routing
[filter]
rules = ['orders_db.*']

[sink]
dispatchers = [
    # Partition by primary key -> per-key ordering
    { matcher = ['orders_db.*'], partition = "index-value" }
]

# Optionally split each table to its own topic
[sink.topics]
# topic-format = "prod.{schema}.{table}"

# 4. Verify changefeed status
cdc cli changefeed query --server=http://10.0.6.10:8300 --changefeed-id="orders-to-kafka"

# Sample output:
# {
#   "id": "orders-to-kafka",
#   "state": "normal",
#   "checkpoint-ts": 435872341234567,
#   "checkpoint-time": "2026-07-27 10:30:45.123",
#   "resolved-ts": 435872341234580,
#   ...
# }

// 5. Sample canal-json event on Kafka topic prod.orders_db.orders
{
  "id": 0,
  "database": "orders_db",
  "table": "orders",
  "pkNames": ["id"],
  "isDdl": false,
  "type": "INSERT",
  "es": 1720051260123,
  "ts": 1720051260200,
  "sql": "",
  "sqlType": {"id": 4, "customer_id": 4, "total_cents": 4, "status": 12},
  "mysqlType": {"id": "BIGINT", "customer_id": "BIGINT", "total_cents": "BIGINT", "status": "VARCHAR(32)"},
  "data": [{"id": "42", "customer_id": "7", "total_cents": "1500", "status": "pending"}],
  "old": null
}

Step-by-step explanation.

1. tiup cluster scale-out adds TiCDC nodes to an existing cluster. Three nodes give HA — if one dies, changefeeds fail over to another. CDC nodes are stateless w.r.t. checkpointing; state lives in the TiDB cluster's PD.
2. cdc cli changefeed create defines the sink URI (Kafka in this case, with canal-json protocol and 8 partitions), plus a config file that scopes which databases/tables are captured and how to route them across Kafka partitions.
3. The partition = "index-value" dispatcher routes each row's changes to a specific partition based on primary key hash. This is the standard "per-key ordering" guarantee — all changes to the same row land on the same partition, so consumers see them in commit order.
4. cdc cli changefeed query shows the checkpoint (last committed TSO the feed has advanced past) and resolved-ts (latest safe timestamp downstream can consume). Lag is now - checkpoint-time; steady-state should be < 1 second.
5. The canal-json event carries database, table, pkNames, type (INSERT/UPDATE/DELETE), data (the new row), and old (the pre-image on UPDATE/DELETE). Consumers that already parse Debezium canal-json need no changes.

Output.

Component	Value	Verified via
CDC nodes	3 (HA)	tiup cluster display
Changefeed	orders-to-kafka	cdc cli changefeed query
Kafka topic	prod.orders_db.orders	Kafka console consumer
Lag steady-state	< 1 second	changefeed.checkpoint-ts vs now
Event format	canal-json	consumer decodes with existing parser
Ordering guarantee	per-primary-key	partition = index-value

Rule of thumb. TiCDC is the drop-in replacement for Debezium against a TiDB source. Use canal-json protocol and index-value partition dispatcher; monitor changefeed checkpoint lag as the health signal. Downstream consumers do not need to know they went from MySQL+Debezium to TiDB+TiCDC — the wire is the same.

Worked example — handling MySQL SQL that TiDB does not support

Detailed explanation. The 1% of MySQL SQL that TiDB does not support usually falls into one of four buckets: stored procedures, triggers, spatial types, and legacy character sets. Walk through diagnosing each, then designing around them.

• Stored procedure. Move logic to application code or to a scheduled job.
• Trigger. Move logic to application code or to an outbox pattern.
• Spatial types. Store as JSON or use approximate lat/long BIGINTs; use PostGIS-adjacent stack (or a specialised geospatial DB) for real spatial workloads.
• Legacy character set. Convert at migration time to utf8mb4.

Question. Diagnose four unsupported constructs in a source MySQL schema and design the TiDB-shaped replacement for each.

Input.

Source construct	Type	TiDB status
sp_add_order_line(...)	stored procedure	not supported
trigger on orders (audit)	trigger	not supported
location POINT NOT NULL	spatial column	not supported
latin1_swedish_ci collation	legacy	partial support

Code.

-- Source MySQL — stored procedure
DELIMITER //
CREATE PROCEDURE sp_add_order_line(IN p_order_id BIGINT, IN p_sku TEXT, IN p_qty INT)
BEGIN
    INSERT INTO order_lines(order_id, sku, qty) VALUES (p_order_id, p_sku, p_qty);
    UPDATE orders SET total_cents = total_cents + (SELECT unit_cents FROM skus WHERE sku=p_sku) * p_qty
     WHERE id = p_order_id;
END//
DELIMITER ;

-- TiDB replacement — move to application code

# TiDB replacement — the same logic in the application layer
def add_order_line(conn, order_id: int, sku: str, qty: int) -> None:
    with conn:
        with conn.cursor() as cur:
            cur.execute(
                "INSERT INTO order_lines(order_id, sku, qty) VALUES (%s, %s, %s)",
                (order_id, sku, qty),
            )
            cur.execute("SELECT unit_cents FROM skus WHERE sku = %s", (sku,))
            unit_cents = cur.fetchone()[0]
            cur.execute(
                "UPDATE orders SET total_cents = total_cents + %s WHERE id = %s",
                (unit_cents * qty, order_id),
            )

-- Source MySQL — trigger (audit)
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO orders_audit(order_id, changed_at, old_status, new_status)
VALUES (NEW.id, NOW(), OLD.status, NEW.status);

-- TiDB replacement — outbox pattern
-- Application writes both the business row AND the audit row in one transaction

# TiDB replacement — outbox pattern
def update_order_status(conn, order_id: int, new_status: str) -> None:
    with conn:
        with conn.cursor() as cur:
            cur.execute("SELECT status FROM orders WHERE id = %s FOR UPDATE", (order_id,))
            old_status = cur.fetchone()[0]
            cur.execute("UPDATE orders SET status = %s WHERE id = %s", (new_status, order_id))
            cur.execute(
                "INSERT INTO orders_audit(order_id, changed_at, old_status, new_status) "
                "VALUES (%s, NOW(), %s, %s)",
                (order_id, old_status, new_status),
            )
    # Both rows committed atomically via MVCC

-- Source MySQL — spatial
CREATE TABLE stores (id BIGINT PRIMARY KEY, location POINT NOT NULL);
INSERT INTO stores VALUES (1, ST_GeomFromText('POINT(-73.98 40.75)'));

-- TiDB replacement — JSON or lat/long BIGINTs (with scaled integers for indexing)
CREATE TABLE stores (
    id           BIGINT PRIMARY KEY,
    lat_micro    BIGINT NOT NULL,       -- lat * 1_000_000; range-scan friendly
    lng_micro    BIGINT NOT NULL,       -- lng * 1_000_000
    location_json JSON                  -- optional richer geometry
);
CREATE INDEX idx_stores_lat_lng ON stores(lat_micro, lng_micro);

-- Radius queries become bounding-box + Haversine post-filter
SELECT id, lat_micro, lng_micro
FROM   stores
WHERE  lat_micro BETWEEN 40740000 AND 40760000
  AND  lng_micro BETWEEN -73990000 AND -73970000;
-- (application computes exact Haversine for the few hundred candidates)

Step-by-step explanation.

1. Stored procedures move to application code. This is usually a straight port — the SQL statements are unchanged, just wrapped in a language-level function. Benefits: the logic is now in version control (was hidden inside the DB), testable via unit tests (was untested), and portable across databases (was TiDB / MySQL specific).
2. Triggers move to either application code (inline the trigger's DML into every application write path) or to an outbox pattern (application writes both business row and event/audit row in one transaction). The outbox path scales better for cross-cutting concerns like audit logging because you can add new consumers without touching every write path.
3. Spatial types are the trickiest. If you truly need geospatial queries at scale, TiDB is not the right store for that specific workload — spin up a specialised store (PostGIS on Postgres, Elasticsearch, or a dedicated geospatial DB) for the geo tables and keep the rest on TiDB. For light usage (nearest-N-stores), scaled BIGINT lat/lng with a bounding-box index works.
4. Legacy character sets get converted at migration time. SET NAMES utf8mb4 on every connection; convert existing data via CONVERT USING utf8mb4 during dump-and-load; test string comparisons post-migration for any collation-sensitive queries.
5. The key insight for all four: TiDB's SQL surface is 99% MySQL-compatible, and the 1% you have to work around has well-known patterns. Never let the 1% derail a migration decision — the compatibility surface is more than enough for the vast majority of MySQL workloads.

Output.

MySQL construct	TiDB replacement	Effort
Stored procedure	Application code	port SQL as-is into a function
Trigger	Outbox pattern (inline in app txn)	1-2 sprint per aggregate type
Spatial column	Scaled BIGINT + JSON + bounding-box	1-2 weeks per geo table
Legacy charset	utf8mb4 conversion at dump time	1-time migration step

Rule of thumb. Before promising a MySQL -> TiDB migration, run pt-config-diff or a manual audit against INFORMATION_SCHEMA to enumerate every stored procedure, trigger, spatial column, and non-utf8 collation. Budget one sprint per non-trivial unsupported construct. The 1% will not derail the migration, but it will surprise you if you have not enumerated it.

Interview Question on MySQL compatibility + migration

A senior interviewer might ask: "You need to migrate a 2 TB MySQL 5.7 order database with 8 stored procedures, 3 triggers, and 2 spatial columns to TiDB, with a 5-minute cutover downtime SLA. Walk me through the assessment, the DM setup, the workaround for the unsupported constructs, the cutover runbook, and the rollback plan."

Solution Using DM sync + application-level replacement + parity harness + reverse-DM rollback safety net

# 1. Source assessment — before writing any config, enumerate unsupported constructs
# audit.sql (run against source MySQL)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM   information_schema.ROUTINES
WHERE  ROUTINE_SCHEMA = 'orders_db';
-- 8 stored procedures found

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE
FROM   information_schema.TRIGGERS
WHERE  TRIGGER_SCHEMA = 'orders_db';
-- 3 triggers found

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM   information_schema.COLUMNS
WHERE  TABLE_SCHEMA = 'orders_db' AND DATA_TYPE IN ('point','geometry','linestring','polygon');
-- 2 spatial columns found

-- Plan: port SPs to application code (sprint 1), replace triggers with outbox (sprint 2),
--       redesign spatial columns as BIGINT lat/lng + JSON (sprint 3)

# 2. DM sync setup — same as the earlier worked example, but with rollback preparation
# Reverse-direction DM task also configured (TiDB -> MySQL) for rollback safety net.
# Both directions run continuously; cutover flips application; DM keeps both in sync 48h.

# migrate-orders.yaml (forward: MySQL -> TiDB)
name: migrate-orders
task-mode: all
target-database: { host: tidb-vip.internal, port: 4000, user: dm_writer, password: ${TIDB_PWD} }
mysql-instances:
  - source-id: mysql-order-primary
    ...

# rollback-orders.yaml (reverse: TiDB -> MySQL, for rollback safety)
name: rollback-orders
task-mode: incremental
target-database: { host: mysql-primary.internal, port: 3306, user: mysql_writer, password: ${MYSQL_PWD} }
tidb-instances:
  - source-id: tidb-order-cluster
    ...

# 3. Parity harness — run per table, both directions, before cutover
import pymysql
from concurrent.futures import ThreadPoolExecutor

def parity(table: str) -> dict:
    src = pymysql.connect(host='mysql-primary.internal', port=3306, user='ro', password='***')
    tgt = pymysql.connect(host='tidb-vip.internal', port=4000, user='ro', password='***')

    src_c = pymysql.cursors.DictCursor(src)
    tgt_c = pymysql.cursors.DictCursor(tgt)

    src.cursor(pymysql.cursors.DictCursor).execute(f"SELECT COUNT(*) n FROM orders_db.{table}")
    ...
    # count + sum + max(id) + min(id) — all four must match

    return { "table": table, "counts_match": True, "sums_match": True }

with ThreadPoolExecutor(max_workers=8) as pool:
    results = list(pool.map(parity, ["orders", "order_items", "customers", "inventory", "shipments"]))
assert all(r["counts_match"] and r["sums_match"] for r in results), "PARITY FAIL"

# 4. Cutover + rollback runbook
Cutover (Sunday 03:00 UTC)
--------------------------
02:30  Freeze DDL; announce migration window
02:45  Run parity harness; abort on any diff
02:55  Pre-scale TiDB pods; warm up connection pool
03:00  Pause application writes to MySQL (scale write pods to 0)
03:01  Wait for DM secondsBehindMaster = 0
03:02  Final parity check
03:03  Flip application config to TIDB_VIP + restart write pods
03:05  Smoke-test key writes; verify TiCDC receiving events
03:10  Green; declare cutover complete

Rollback plan (any point 03:05 - +48h)
--------------------------------------
1. Pause application writes to TiDB
2. Wait for reverse-DM secondsBehindMaster = 0 (TiDB -> MySQL)
3. Final parity check MySQL vs TiDB
4. Flip application config back to MYSQL_HOST + restart write pods
5. Smoke-test writes on MySQL
6. Total rollback time: 5-10 minutes

Step-by-step trace.

Phase	Action	Success signal
Audit	enumerate SPs / triggers / spatial	count matches known schema
Refactor	replace unsupported constructs	3 sprints, app-level tests pass
Dump + load	DM full sync	progress = 100%, all tables loaded
Continuous sync	binlog tail	secondsBehindMaster < 1 s
Reverse DM	TiDB -> MySQL rollback safety	secondsBehindMaster < 1 s both directions
Parity	count + sum + min + max per table	100% match, no diffs
Cutover	app flip + smoke test	writes flow to TiDB; TiCDC receives
Rollback safety net	reverse DM running 48h	any incident -> 5-min rollback

After the cutover, the application writes exclusively to TiDB; TiCDC replaces the old MySQL+Debezium change feed; the reverse-DM task continues for 48 hours so any incident can trigger a 5-minute rollback. At the end of 48 hours (with no incidents), the reverse DM is decommissioned and MySQL is scaled down.

Output:

Metric	Target	Achieved
Cutover downtime	< 5 min	~4 min (writes paused 03:00 -> 03:04)
Parity accuracy	100% (count + sum)	100% on all 12 tables
Rollback time (if triggered)	< 10 min	untested (not needed)
Application code changes	SP + trigger + spatial refactors	3 sprints of dev work
Post-migration compatibility	all app queries work	100%

Why this works — concept by concept:

• DM tool — PingCAP's purpose-built MySQL -> TiDB replicator. Dump + load + continuous binlog sync as one lifecycle, with GTID-based resumption for robustness. Handles the mechanical half of migration; the semantic half (unsupported constructs) is on the application team.
• Application-level replacement of SPs / triggers / spatial — the "1% incompatibility" gets a design pattern per case (SP -> app function; trigger -> outbox; spatial -> BIGINT + JSON). These are one-time refactors, not permanent burdens.
• Reverse-direction DM as rollback safety net — running DM in both directions during the cutover window means any post-cutover incident can flip the application back to MySQL within minutes. This buys enormous psychological safety; teams cut over confidently because they know they can undo.
• Parity harness — count + sum + min + max per table catches almost every silent data-loss scenario at nearly no cost. Run it before and after cutover; alert on any diff.
• Cost — 3-node DM cluster (~2 vCPU + 4 GB per node), 3 sprints of application refactoring, 4-8 hours dump + load, 48 hours rollback safety net. Compared to a big-bang migration ("shut down MySQL over the weekend, hope nothing breaks"), this is orders of magnitude safer. Compared to running MySQL forever, TiDB unlocks horizontal scale-out and native HTAP that the source could never deliver.

SQL
Topic — sql
SQL migration and compatibility problems

Practice →

Streaming Topic — streaming Streaming CDC and change-feed problems

Practice →

---

5. When TiDB wins + interview signals

TiDB wins when the workload is MySQL-shaped, needs horizontal scale-out, and needs sub-second analytics without a second data copy — everything else has a better answer

The mental model in one line: TiDB's sweet spot is a specific quadrant of the distributed-database market — MySQL-compatible wire (so your team keeps its skills and drivers), horizontal scale-out (so you never re-shard), and native HTAP via TiFlash (so dashboards see live data without a warehouse ETL) — and outside that quadrant every alternative (CockroachDB, YugabyteDB, Snowflake, Databricks, or even single-node MySQL for small workloads) is a better pick, so senior data engineers must be able to name the quadrant precisely and defend it against the four canonical alternatives. This decision matrix is the interview question you will get more than any other, and having it internalised is the difference between an architect who picks confidently and one who defers to the last vendor pitch.

The four canonical workload-to-database mappings.

• MySQL-shaped + HTAP + horizontal scale-out. TiDB. This is the sweet spot: your app is already MySQL, you have hit or foresee hitting single-instance write limits, and you have dashboards that need live data. No other database in 2026 hits all three requirements.
• Postgres-shaped + geo-distribution + strong CP. CockroachDB. When your workload is Postgres-first (deep use of Postgres extensions, JSONB, materialised views, complex CTEs), needs multi-region write distribution with strict serialisable consistency, and can tolerate warehouse-based analytics. YugabyteDB is a close second here.
• Postgres-shaped + dual API (Postgres + Cassandra). YugabyteDB. Rare workload — you need Cassandra-style wide-row storage for one part of your data and Postgres-style SQL for another. Yugabyte serves both from one storage engine; TiDB and Cockroach would need two separate stores.
• Pure analytics at petabyte scale. Snowflake / Databricks / BigQuery. When "OLTP" is not part of your requirement at all — analytical warehouse only, over hundreds of TB to multiple PB, feeding BI and ML. TiFlash is not designed for this scale; use a pure warehouse.

Interview probes on database selection.

• "How does TiDB compare to CockroachDB?" — required answer: wire protocol (MySQL vs Postgres), HTAP (native TiFlash vs none), license (Apache 2 vs BSL), and geo-CP story (CockroachDB slightly stronger on multi-region strict serialisable).
• "How does TiDB compare to a MySQL cluster with read replicas?" — required answer: TiDB is horizontally sharded (region-based, PD-managed), MySQL read replicas are vertical + eventual; TiDB writes scale linearly with TiKV nodes.
• "When would you NOT pick TiDB?" — pure OLAP at PB scale; small workload where MySQL RDS suffices; Postgres-first codebase that would need an app rewrite.
• "TiDB vs Vitess?" — Vitess is a sharding layer over MySQL (you still run MySQL primary + replicas); TiDB is a from-scratch distributed database. Vitess is lighter weight but does not do HTAP or native strong consistency.

Operational + team-fit signals.

• Team already knows MySQL. Enormous win for TiDB — same drivers, same client tools, same mental model for indexes and joins. If your team is Postgres-native, TiDB has a steeper learning curve.
• DevOps maturity. TiDB has more moving parts than single-node MySQL. If your team has never run a Raft-based system, budget 1-2 quarters of learning curve.
• Existing observability stack. TiDB ships Grafana + Prometheus + AlertManager by default. If you already run Prometheus, integration is trivial; if you are on Datadog / New Relic exclusively, budget one sprint for exporter setup.
• Managed vs self-hosted. TiDB Cloud Serverless is the fastest way to try TiDB; costs pennies per GB-hour. Dedicated is the enterprise path with VPC peering. Self-hosted via tiup is the maximum-control path.

Common interview probes on TiDB positioning.

• "Is TiDB truly HTAP?" — required answer: yes, one cluster, one write path, TiKV rows + TiFlash columns auto-synced via Raft learners.
• "What is the license risk?" — Apache 2; ASF-adjacent (though not formally an ASF project). No BSL-style vendor lock-in as with CockroachDB.
• "Production references at scale?" — Square, Shopee, Pinterest, ByteDance, hundreds of others; PingCAP publishes case studies.
• "TiDB Cloud vs self-hosted?" — Cloud reduces ops burden; self-hosted maximises control; the software is identical.

Worked example — the four-way decision matrix

Detailed explanation. The canonical interview question: given a workload, pick the database. Walk through four realistic scenarios and show how the four-axis matrix (wire, HTAP, scale-out, license) drives the choice.

• Scenario A. E-commerce team on MySQL 5.7 with 20 TB and 50k writes/sec at peak; dashboards lag 24h behind due to nightly ETL.
• Scenario B. Fintech team on Postgres with multi-region write requirements and strict serialisable transactions.
• Scenario C. Ad-tech team with both key-value (session store, Cassandra-shaped) and relational (campaign management, Postgres-shaped) needs.
• Scenario D. Data science team with 200 TB of historical events needing petabyte-scale aggregation for ML feature engineering.

Question. Pick the right database for each scenario and defend the choice against alternatives.

Input.

Scenario	Wire pref	HTAP?	Scale-out?	Analytics scale	Recommendation
A (e-commerce)	MySQL	yes	yes (writes)	TB	TiDB
B (fintech)	Postgres	no (warehouse OK)	yes (multi-region)	TB	CockroachDB
C (ad-tech)	mixed	no	yes	TB	YugabyteDB
D (data sci)	any	analytics only	no (single warehouse)	PB	Snowflake

Code.

Decision tree — 4 questions
============================

Q1. Is the workload MySQL-shaped?
    YES -> Q2   NO -> Q4

Q2. Do you need sub-second analytics on live data (i.e. HTAP)?
    YES -> TiDB   NO -> Q3

Q3. Are single-node MySQL limits within reach in 2 years?
    YES -> TiDB   NO -> RDS / Aurora / Vitess

Q4. Is the workload Postgres-shaped?
    YES -> Q5   NO -> Q7

Q5. Do you need multi-region strict serialisable + Postgres depth?
    YES -> CockroachDB   NO -> Q6

Q6. Do you need Postgres + Cassandra dual API from one engine?
    YES -> YugabyteDB   NO -> managed Postgres (RDS / Cloud SQL / Neon)

Q7. Is this analytics-only over 100+ TB?
    YES -> Snowflake / Databricks / BigQuery
    NO  -> (rethink; you might be forcing a database into the wrong shape)

# The four-question tree in code
def pick_database(mysql_shaped: bool,
                  postgres_shaped: bool,
                  needs_htap: bool,
                  needs_multi_region_cp: bool,
                  needs_pg_plus_cassandra_dual: bool,
                  analytics_only_pb_scale: bool,
                  scale_out_needed: bool) -> str:
    if analytics_only_pb_scale:
        return "Snowflake / Databricks / BigQuery"
    if mysql_shaped:
        if needs_htap or scale_out_needed:
            return "TiDB"
        return "MySQL RDS / Aurora / Vitess"
    if postgres_shaped:
        if needs_multi_region_cp:
            return "CockroachDB"
        if needs_pg_plus_cassandra_dual:
            return "YugabyteDB"
        return "Managed Postgres (RDS / Cloud SQL / Neon)"
    return "Reconsider — you have not defined the shape yet"


# Walk the four scenarios
print(pick_database(True,  False, True,  False, False, False, True))
# -> TiDB
print(pick_database(False, True,  False, True,  False, False, True))
# -> CockroachDB
print(pick_database(False, True,  False, False, True,  False, True))
# -> YugabyteDB
print(pick_database(False, False, False, False, False, True,  False))
# -> Snowflake / Databricks / BigQuery

Step-by-step explanation.

1. Scenario A (e-commerce on MySQL) hits both HTAP need and scale-out need — TiDB is the only option in the four-way matrix that satisfies both. CockroachDB or YugabyteDB would require a MySQL -> Postgres rewrite; MySQL + Snowflake keeps the ETL lag; MySQL + Vitess sacrifices HTAP.
2. Scenario B (Postgres fintech, multi-region CP) hits Postgres compat + strict serialisable. CockroachDB is designed exactly for this — strong CP with Postgres wire, geo-distribution as a first-class feature. TiDB would require a rewrite; YugabyteDB is a close second.
3. Scenario C (ad-tech, dual API) is the rare case for YugabyteDB — YSQL for the relational side, YCQL for the wide-row session store, one storage engine underneath. TiDB and Cockroach would each need two separate databases; the operational cost of running two is often higher than YugabyteDB's dual API.
4. Scenario D (data sci, 200 TB pure analytics) is out-of-scope for any HTAP database. TiFlash tops out at hundreds of TB in practice; Snowflake / Databricks / BigQuery are designed for PB scale. Pick one of the three based on ecosystem fit and cost model.
5. The four-axis matrix (wire x HTAP x scale-out x analytics scale) covers most real workloads. When a scenario does not fit any quadrant, the honest answer is "you have not defined the shape yet — go back and clarify."

Output.

Scenario	Recommendation	Why
A	TiDB	MySQL wire + HTAP + horizontal scale
B	CockroachDB	Postgres wire + multi-region CP
C	YugabyteDB	dual API from one engine
D	Snowflake	pure analytics at PB scale

Rule of thumb. Never pick a distributed SQL database because "everyone else is using it." Walk the four-question decision tree; the workload shape determines the answer. TiDB is not the right answer for a Postgres workload just because it is a great database; the wire protocol must match your team.

Worked example — TiDB vs CockroachDB deep comparison

Detailed explanation. The most common comparison question is TiDB vs CockroachDB. Both are Raft-based, both are horizontally-sharded NewSQL databases, both are production-mature. But they occupy different quadrants and interviewers probe how well you understand the differences.

• Wire. MySQL vs Postgres.
• HTAP. TiFlash vs "pair with a warehouse."
• License. Apache 2 vs BSL (Business Source License, evolving).
• Isolation default. Snapshot isolation vs strict serialisable.
• Multi-region story. Comparable; both do Raft-based multi-region write; CockroachDB has more mature "geo-partitioning" primitives.

Question. Build a side-by-side comparison and identify which side wins for each dimension.

Input.

Dimension	TiDB	CockroachDB
Wire	MySQL 5.7/8.0	Postgres
HTAP	Native via TiFlash	None; pair with warehouse
License	Apache 2	BSL (source available)
Isolation	Snapshot (SI) default; SERIALIZABLE optional	Strict serialisable default
Multi-region	Yes via Raft; less mature geo-partitioning	Yes; very mature geo-partitioning
Schema changes	Online DDL	Online DDL
Storage engine	RocksDB (TiKV) + Delta Tree (TiFlash)	Pebble (CockroachDB's RocksDB fork)

Code.

-- TiDB (MySQL wire)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT,
    total_cents BIGINT,
    status VARCHAR(32),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders(customer_id, total_cents, status)
VALUES (7, 1500, 'pending');

-- CockroachDB (Postgres wire) — same table, near-identical syntax
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT,
    total_cents BIGINT,
    status VARCHAR(32),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders(customer_id, total_cents, status)
VALUES (7, 1500, 'pending');

# Client code — TiDB (mysql-connector-python)
import mysql.connector

conn = mysql.connector.connect(
    host="tidb-vip.internal",
    port=4000,
    user="app_user",
    password="***",
    database="orders_db",
)
cur = conn.cursor()
cur.execute("INSERT INTO orders(customer_id, total_cents, status) VALUES (%s, %s, %s)",
            (7, 1500, 'pending'))
conn.commit()

# Client code — CockroachDB (psycopg2)
import psycopg2

conn = psycopg2.connect(
    host="cockroach-vip.internal",
    port=26257,
    user="app_user",
    password="***",
    database="orders_db",
)
cur = conn.cursor()
cur.execute("INSERT INTO orders(customer_id, total_cents, status) VALUES (%s, %s, %s)",
            (7, 1500, 'pending'))
conn.commit()

Step-by-step explanation.

1. Wire protocol is the single biggest team-fit dimension. MySQL-shaped teams pick TiDB; Postgres-shaped teams pick CockroachDB. Both databases speak their wire natively — no adapter layer — so the same JDBC / psycopg2 code that worked against the source works against the distributed replacement.
2. HTAP is the clearest architectural difference. TiDB's TiFlash gives you sub-second analytics on live data with no separate ETL. CockroachDB has no columnar tier; analytical queries either hit the row store (slow at scale) or require a warehouse copy.
3. License is the newest axis. TiDB's Apache 2 license is enterprise-friendly; CockroachDB's BSL (Business Source License, converting to Apache 2 after 4 years) has more restrictions on running as a managed service. For most teams this is not a blocker; for teams building competing products it can be.
4. Isolation default matters for correctness-sensitive workloads. CockroachDB's strict serialisable is stronger; TiDB's snapshot isolation is sufficient for most workloads but occasionally lets a phantom read slip through under contention.
5. Multi-region is where CockroachDB has a maturity edge — its geo-partitioning primitives (pin data to specific regions, "follow the workload" semantics) are more polished. TiDB can do multi-region via Placement Rules but the ergonomics are less refined.

Output.

Dimension	Winner	Margin
MySQL wire	TiDB	infinite (Cockroach does not speak MySQL)
Postgres wire	CockroachDB	infinite (TiDB does not speak Postgres natively)
HTAP	TiDB	massive (Cockroach has no columnar tier)
License openness	TiDB	Apache 2 vs BSL
Strict serialisable	CockroachDB	default; TiDB requires opt-in
Multi-region maturity	CockroachDB	polished geo-partitioning primitives
Managed offering maturity	comparable	TiDB Cloud + CockroachDB Cloud both GA

Rule of thumb. TiDB and CockroachDB occupy adjacent quadrants — MySQL vs Postgres, HTAP vs strict-CP. Neither is universally better; the choice is dictated by your wire protocol requirement and whether HTAP is worth more than strict serialisable. When both are viable, MySQL teams pick TiDB and Postgres teams pick CockroachDB; the friction of a wire-protocol switch usually outweighs any secondary consideration.

Worked example — the interview signal checklist

Detailed explanation. Senior interviews on TiDB reliably score candidates on the same set of signals. Walk through the checklist a senior candidate should hit; use it as both a preparation guide and a self-assessment.

• Storage engines named separately. "TiKV for rows, TiFlash for columns." Not "TiDB has a distributed store."
• Raft learners named as the sync mechanism. Not "replication."
• PD named as the metadata brain. Not "the coordinator" or "the master."
• Percolator 2PC named. Not just "distributed transactions."
• CBO + hints named for query routing. Not "the optimizer decides."
• MPP named for analytical execution. Not "parallel scan."
• DM named for MySQL migration. Not "some replication tool."
• TiCDC named for downstream change feed. Not "Debezium equivalent."
• License (Apache 2) named. Not "open source."
• Known gaps named. Not "mostly MySQL compatible."

Question. Rehearse the senior TiDB answer against the 10-item checklist and identify weak spots.

Input.

Signal	Weak candidate	Senior candidate
Storage engines	"distributed store"	"TiKV rows + TiFlash columns"
Sync	"replication"	"Raft learners"
Metadata	"master node"	"Placement Driver PD"
Transactions	"distributed transactions"	"Percolator 2PC + MVCC via TSO"
Query routing	"the optimizer"	"CBO with row-count estimates + hints"
Analytical exec	"parallel scan"	"MPP fan-out on TiFlash nodes"
Migration tool	"some replication"	"DM tool (dump + load + binlog sync)"
Change feed	"CDC"	"TiCDC with canal-json to Kafka"
License	"open source"	"Apache 2, ASF-adjacent"
Compat gaps	"mostly compatible"	"SPs / triggers / spatial / some collations"

Code.

# Rehearsal script for the 10-item senior signal checklist
# ------------------------------------------------------
# Say this out loud. If you cannot fluently produce every named term,
# review the section that introduced it before the interview.

"TiDB is PingCAP's Apache-2 distributed SQL database with two storage
 engines on one cluster: TiKV for row-oriented OLTP, backed by RocksDB
 and coordinated by Raft, and TiFlash for column-oriented OLAP,
 replicated from TiKV via Raft learners. The Placement Driver PD is
 the metadata brain, a 3-node Raft group that tracks region placement
 and hands out MVCC timestamps for Percolator 2PC transactions. The
 cost-based optimizer routes each query to the store the CBO estimates
 will be cheapest — point-lookups to TiKV, aggregations to TiFlash MPP;
 you can override with the READ_FROM_STORAGE hint.

 MySQL migration is via PingCAP's DM tool — dump, load, then continuous
 binlog sync with GTID resumption. The MySQL wire compatibility is 99%
 with known gaps in stored procedures, triggers, spatial types, and a
 few legacy character sets; foreign keys were fully enforced from
 TiDB 6.6 onwards. Downstream change events are emitted by TiCDC in
 canal-json format to Kafka.

 TiDB wins when the workload is MySQL-shaped and needs sub-second HTAP
 without a separate warehouse ETL. For Postgres-shaped workloads with
 strict-CP multi-region needs, CockroachDB is the better pick; for
 pure PB-scale analytics, Snowflake or Databricks; for small MySQL
 workloads that fit on one instance, plain RDS or Aurora."

Step-by-step explanation.

1. Naming TiKV and TiFlash separately in your opening sentence is the single biggest signal — it shows you understand the architecture rather than treating TiDB as a monolith. Practice this until it is automatic.
2. Naming Raft learners (rather than "replication") signals you have read the internals. This is a small vocabulary difference that carries huge information about your depth of knowledge.
3. Naming PD (not "the coordinator") + Percolator (not "distributed transactions") together shows you have read the design papers. Both terms appear in every serious TiDB doc; using them signals fluency.
4. Naming CBO + READ_FROM_STORAGE hint together shows you have written a query where you had to override the optimizer. That is a very specific senior signal — nobody knows about optimizer hints unless they have had to use one.
5. Naming DM + TiCDC together covers the migration + downstream sync story. Interviewers explicitly probe both because they cover different failure modes; a candidate who names both is showing they have thought about the whole data-flow surface.

Output.

Signal	Score	Coverage
TiKV + TiFlash	10/10	required
Raft learners	10/10	required
PD	10/10	required
Percolator	8/10	senior signal
CBO + hints	9/10	strong
MPP	9/10	strong
DM	10/10	required for migration q
TiCDC	9/10	strong
License	7/10	nice to have
Compat gaps	10/10	required for migration q

Rule of thumb. The senior TiDB interview is largely a vocabulary test. Ten specific terms (TiKV, TiFlash, Raft learner, PD, Percolator, MVCC/TSO, CBO, MPP, DM, TiCDC) separate senior candidates from junior ones. If you can produce all ten in a 3-minute unprompted monologue, you have already outscored 90% of the field.

Interview Question on database selection

A senior interviewer might ask: "We're a Postgres shop with 40 TB of transactional data and dashboards that lag 24 hours because we ETL to Snowflake nightly. Someone proposed migrating to TiDB. Walk me through your assessment, whether you would accept the proposal, and what alternatives you would propose."

Solution Using the four-axis decision matrix + honest gap analysis + phased alternative

Assessment
==========

Step 1 — check MySQL vs Postgres compat
  Source is Postgres. TiDB is MySQL-wire. Migration would require:
    - Rewriting every JDBC connection string
    - Rewriting Postgres-specific SQL (JSONB operators, LATERAL joins,
      CTE MATERIALIZED hint, some window function syntax)
    - Rewriting all Postgres extensions (PostGIS, pg_stat_statements,
      pgvector)
    - Rewriting stored procedures / triggers (Postgres has them; TiDB
      does not support either)

  Verdict: HUGE application-side cost. Not zero-code migration.

Step 2 — check HTAP requirement
  Yes, sub-second analytics on live data is a real requirement.
  TiDB's TiFlash would deliver this. But so would:
    - CockroachDB + Snowflake with reduced ETL lag (Fivetran / Airbyte)
    - CockroachDB + Materialize (real-time materialized views)
    - Postgres 16 + Citus (postgres-shaped horizontal scale-out)

Step 3 — check scale-out requirement
  40 TB is at the edge of single-instance Postgres. Vertical scale
  (bigger RDS instance) buys 2-3 years. Horizontal is required
  eventually.

Step 4 — the four-question tree
  Q1 (MySQL-shaped?): NO
  Q4 (Postgres-shaped?): YES
  Q5 (multi-region CP?): probably not; single-region is fine
  Q6 (Postgres + Cassandra dual?): no
  -> Managed Postgres if single instance works
  -> CockroachDB if horizontal scale-out needed
  Neither of those give HTAP natively; both need warehouse pairing.

# Recommendation
def recommend():
    return """
    Recommendation: DO NOT migrate to TiDB.

    Reason: TiDB is MySQL-wire; the source is Postgres-wire. The
    application-side rewrite cost is prohibitive (extensions,
    stored procedures, Postgres-specific SQL), and the HTAP benefit
    can be achieved with a Postgres-native stack.

    Instead, consider:

    A. Short-term (0-6 months): stay on Postgres; replace nightly
       Snowflake ETL with Fivetran/Airbyte real-time CDC (5-min lag);
       add read replicas for dashboard queries.

    B. Medium-term (6-18 months): evaluate CockroachDB for horizontal
       scale-out if Postgres single-instance limits become a concern.
       Continue warehouse pairing for analytics.

    C. If HTAP truly matters (i.e. sub-second not just 5-min):
       Materialize (real-time materialized views on Postgres) or
       ReadySet (query cache on Postgres). Both preserve the Postgres
       wire; both deliver sub-second analytical answers.

    D. Only migrate to TiDB if the entire team is willing to accept
       a MySQL-wire replatform AND the HTAP requirement is the primary
       driver AND options A-C are insufficient. This is a rare
       combination for a Postgres shop.
    """

Step-by-step trace.

Step	Analysis	Verdict
Wire compat	Postgres source vs MySQL TiDB	massive rewrite cost
HTAP requirement	genuine sub-second need	TiDB delivers, but alternatives exist
Scale-out timeline	2-3 years horizon	not urgent
Decision tree	Postgres branch -> CockroachDB / managed PG	TiDB not indicated
Alternatives	Materialize / ReadySet / Fivetran CDC	preserves Postgres, meets HTAP need
Recommendation	Do not migrate to TiDB	Postgres-native path better

After the analysis, the honest recommendation is to not migrate — TiDB is a great database but wrong for a Postgres shop. Instead, run a 6-month experiment with real-time CDC to reduce ETL lag, then evaluate CockroachDB or a Postgres HTAP layer if scale-out becomes urgent. This kind of "no, and here is why" answer is what senior interviewers score highest — it shows you understand the four-axis matrix well enough to defend against a shiny-new-database proposal.

Output:

Path	Feasibility	Cost	HTAP delivered?
Migrate to TiDB	possible but expensive	6-12 months app rewrite	yes
Stay + real-time CDC	trivial	1-2 sprints	5-min lag
Move to CockroachDB	moderate	3-6 months	no; needs warehouse
Add Materialize / ReadySet	easy	1 month	yes; Postgres-native
Do nothing	zero cost	zero	24-hour lag

Why this works — concept by concept:

• Four-axis decision matrix — wire x HTAP x scale-out x license. Applying it honestly to this workload (Postgres, HTAP need, moderate scale) reveals TiDB is a wire mismatch. Never let a shiny-new-database pitch skip the matrix.
• Migration cost is application-side, not database-side — the TiDB migration mechanics (DM tool, parity harness) work great. The cost is rewriting every Postgres-specific piece of the application. For a Postgres shop this is prohibitive.
• Real alternatives exist for HTAP on Postgres — Materialize, ReadySet, Postgres + real-time CDC to a small warehouse. None deliver TiDB's single-cluster elegance but all preserve wire compat and deliver "close enough" HTAP for most dashboards.
• The "no" answer is the senior signal — recommending against a proposal, with evidence and alternatives, is the mark of an architect. Interviewers score this higher than an enthusiastic "yes let's do it" answer that ignores the mismatch.
• Cost — a 1-hour assessment produces a "do not migrate" recommendation that saves 6-12 months of engineering effort. Compared to a rushed "yes" that discovers the wire mismatch after the first sprint, the honest analysis is orders of magnitude cheaper.

Design
Topic — design
Design problems on database selection

Practice →

SQL
Topic — joins
SQL problems on join and query design

Practice →

---

Cheat sheet — TiDB recipes

• Which distributed SQL when. TiDB is the 2026 default for a MySQL-shaped workload that needs horizontal scale-out plus sub-second analytics on live data — no other database in the class hits all three (MySQL wire + horizontal scale + native HTAP via columnar replica). CockroachDB is the answer when the workload is Postgres-shaped, needs multi-region strict serialisable, and can tolerate a warehouse-based analytics story. YugabyteDB fits the rare "Postgres + Cassandra dual API from one storage engine" quadrant. Snowflake / Databricks / BigQuery are pure analytics and complement (never compete with) TiDB. Never pick a distributed SQL database because a vendor rep pitched it; walk the four-axis matrix (wire x HTAP x scale-out x license) with your actual workload and let the choice fall out of the constraints.
• Cluster topology sizing template. Minimum HA production is 3 PD nodes (Raft quorum, tolerates 1 loss) + 2 stateless TiDB SQL pods + 3 TiKV nodes (region min-replication factor 3) + 2 TiFlash nodes (learner min for HA analytical reads) = 10 nodes. Scale TiDB pods for QPS (each is stateless, load balance behind a VIP). Scale TiKV nodes for OLTP capacity (each additional node adds ~25k writes/sec at typical row sizes). Scale TiFlash nodes for OLAP scan throughput (each additional node parallelises MPP). Place PD nodes across three failure domains (AZs or racks); label TiKV nodes with zone and host so PD spreads replicas correctly.
• ALTER TABLE ... SET TIFLASH REPLICA recipe. For each table that needs analytical queries, run ALTER TABLE db.tbl SET TIFLASH REPLICA 2; — this schedules a Raft learner replica of every region of that table onto TiFlash nodes. Monitor progress via SELECT * FROM information_schema.tiflash_replica WHERE TABLE_NAME='tbl'; — the AVAILABLE=1 PROGRESS=1.0 state means the CBO can now route to TiFlash. Use REPLICA 2 for HA; REPLICA 1 is fine for single-node TiFlash but risks OLAP downtime on TiFlash node failure. Never SET TIFLASH REPLICA 0 on a table with active analytical queries without first checking information_schema.processlist — you will drop the learner replicas and future analytical queries fall back to TiKV.
• Optimizer hints for forcing store choice. /*+ READ_FROM_STORAGE(TIFLASH[t]) */ forces the CBO to read table t from TiFlash — use for dashboard queries where CBO drift due to stale stats regresses to TiKV. /*+ READ_FROM_STORAGE(TIKV[t]) */ forces TiKV — use for point-lookup queries where CBO wrongly picks TiFlash. /*+ USE_INDEX(t, idx_name) */ forces a specific TiKV index. SET SESSION tidb_enforce_mpp = ON; disables the cost check and forces MPP execution when at least one table has a TiFlash replica. Treat hints as debt — every hint is a maintenance item, so use them sparingly and comment why.
• ANALYZE TABLE maintenance schedule. Run a nightly cron: ANALYZE TABLE db.tbl; for your top 10-20 largest tables during off-peak hours (typically 00:00-06:00 UTC), even if tidb_enable_auto_analyze = ON. Auto-analyze covers most cases but can lag under write-heavy load. Use ANALYZE TABLE ... WITH X SAMPLES for tables with >1B rows to keep the analyze time bounded. Alert on modify_count / row_count > 0.3 for 30 minutes as an early-warning signal that the CBO is about to make bad routing decisions.
• DM task config template. task-mode: all runs dump + load + sync in one lifecycle; task-mode: incremental skips dump/load and starts from a specified binlog position. Always enable-gtid: true for robust resume across binlog rotations. Size mydumpers.threads (dump parallelism) and loaders.pool-size (load parallelism) for your source read bandwidth and target write bandwidth respectively — typical values are 16 and 32 for a 500 GB migration. Set syncer.worker-count to at least 16 for steady-state lag < 1 second. Always run a reverse-direction DM task (TiDB -> MySQL) during cutover as a rollback safety net for the first 48 hours.
• TiCDC changefeed config. sink-uri="kafka://kafka:9092/prefix?protocol=canal-json&partition-num=8&max-message-bytes=10485760" is the standard shape. Use canal-json for Debezium-compatible payloads that most existing consumers already parse. Use partition = "index-value" in the dispatcher to ensure per-primary-key ordering on Kafka. Monitor cdc cli changefeed query output for checkpoint lag; alert when lag exceeds 5 seconds sustained. Configure separate changefeeds per downstream consumer for isolation — if one consumer's Kafka topic backs up, the others keep flowing.
• Decision matrix: TiDB vs CockroachDB vs YugabyteDB vs Snowflake. Wire: TiDB = MySQL 5.7/8.0, Cockroach = Postgres, Yugabyte = Postgres YSQL + Cassandra YCQL, Snowflake = proprietary SQL. HTAP: TiDB = native via TiFlash, Cockroach = none (pair warehouse), Yugabyte = none, Snowflake = analytics-only. Scale-out: TiDB = region-based via PD, Cockroach = range-based via gossip, Yugabyte = tablet-based, Snowflake = micro-partitions. License: TiDB = Apache 2, Cockroach = BSL (source-available, converting to Apache 2 in 4 years), Yugabyte = Apache 2, Snowflake = proprietary. Isolation default: TiDB = snapshot, Cockroach = strict serialisable, Yugabyte = snapshot (YSQL) / configurable (YCQL), Snowflake = read-committed.
• Failure semantics reminder. TiDB SQL pod dies -> client reconnects to another (< 1s). PD single node dies -> Raft quorum survives, no impact. PD majority dies -> new-transaction writes stall until quorum returns. TiKV node dies -> region leaders re-elect in ~1s, followers re-replicated in minutes. TiFlash node dies -> OLAP queries fail over to another learner (if REPLICA >= 2), OLTP writes unaffected. Client transaction mid-TiKV-failover -> retry against new leader, mostly transparent, occasional 5-10s delays.
• Schema evolution rules. TiDB supports online DDL: CREATE INDEX, ADD COLUMN, DROP COLUMN, MODIFY COLUMN, RENAME TABLE all run non-blocking against the table (using the same online-DDL pattern as MySQL 8+). Foreign key adds require a table lock briefly. When adding TiFlash to an existing table (SET TIFLASH REPLICA 2), monitor AVAILABLE=1 before routing analytical queries — the CBO will fall back to TiKV until the learner replicas are caught up.
• Migration from MySQL — the six-step playbook. (1) Audit the source: stored procedures, triggers, spatial columns, non-utf8 collations. Refactor before migration. (2) Deploy TiDB cluster; verify HA. (3) Deploy DM cluster; register source. (4) Start task-mode: all; monitor dump + load + sync progress. (5) Run parity harness (count + sum + min + max per table). (6) Cutover: pause app writes, wait for zero lag, flip config, restart pods. Keep reverse DM running 48h as rollback safety net. Total downtime target: < 5 minutes.
• Monitoring recipe. Grafana panels: tidb_server_handle_query_duration_seconds (OLTP p99), tikv_grpc_msg_duration_seconds (TiKV RPC latency), pd_regions_status (region health), tiflash_replica_syncing_regions (TiFlash sync progress), ticdc_processor_checkpoint_ts_lag_seconds (CDC lag). Prometheus alerts: OLTP p99 > 100 ms for 5 min; TiKV RPC latency p99 > 200 ms; region miss-peer count > 0 for 15 min; TiFlash sync progress < 1.0 for > 1 h; CDC lag > 5 s sustained.
• What senior interviewers score highest. Naming TiKV and TiFlash as separate storage engines (not "TiDB"); naming Raft learners as the sync primitive (not "replication"); naming the Placement Driver PD as the metadata brain (not "coordinator"); naming Percolator 2PC + TSO for transactions (not "distributed transactions"); naming the CBO + READ_FROM_STORAGE hint for query routing; naming MPP for analytical execution (not "parallel scan"); naming DM for MySQL migration; naming TiCDC for downstream change feed; naming the license as Apache 2 (not "open source"); enumerating specific compat gaps (SPs, triggers, spatial, legacy collations, older-version FK enforcement). These ten specific terms are the vocabulary test that separates senior TiDB candidates from junior ones.

Frequently asked questions

What is TiDB in one sentence?

TiDB is PingCAP's Apache-2-licensed distributed SQL database that speaks the MySQL 5.7/8.0 wire protocol and combines two storage engines on one cluster — TiKV, a row-oriented OLTP store backed by RocksDB and coordinated by Raft, and TiFlash, a column-oriented OLAP store that pulls the same Raft log as TiKV via non-voting learner replicas — so a single SQL layer + cost-based optimizer can route each query to the store that answers it fastest, delivering true hybrid transactional-analytical processing (HTAP) without a separate warehouse or a nightly ETL. The Placement Driver PD provides metadata management, TSO-based MVCC timestamps, and region rebalancing; the entire stack (PD + TiKV + TiFlash + TiDB SQL layer) runs as one deployment, scales horizontally by adding nodes, and is used in production by companies including Square, Shopee, Pinterest, and ByteDance. Every senior data-engineering interview in 2026 probes TiDB because it is the reference implementation of the HTAP-on-one-cluster bet that finally paid off.

TiKV vs TiFlash — how are they different?

TiKV is the row-oriented OLTP storage engine — each TiKV node runs one or more RocksDB instances holding data as row-formatted key-value pairs, data is sharded into ~96 MB "regions" replicated 3x via Raft (one leader, two followers), and every INSERT/UPDATE/DELETE/point-lookup routes to the region leader and commits when the Raft quorum acks. Latencies are OLTP-shape: sub-millisecond point lookups, ~10 ms writes at the p99. TiFlash is the column-oriented OLAP storage engine — each TiFlash node runs a Delta Tree columnar engine (derived from ClickHouse's MergeTree), holds data as column-batches (Parquet-adjacent), and receives updates by joining each region's Raft group as a non-voting learner replica — it pulls the log asynchronously, replays it into columnar batches, and never accepts direct writes from clients. Analytical queries with GROUP BY or table-full-scan patterns run 10-100x faster on TiFlash than on TiKV because columnar scans read only the columns the query touches and TiFlash's MPP engine parallelises the work across TiFlash nodes. The two engines share nothing physically — separate processes, separate nodes, separate CPUs — so OLAP load on TiFlash cannot slow OLTP writes on TiKV. The auto-sync between them (Raft learners) is what makes HTAP a single-cluster property rather than a separate-database concern.

What is HTAP and why does TiDB do it natively?

HTAP (Hybrid Transactional and Analytical Processing) is the architectural pattern where the same cluster serves both OLTP writes and OLAP scans on the same data, with analytical queries seeing changes within a bounded lag of the transactional commit — no separate warehouse copy, no nightly ETL, no eventual-consistency drift. Traditional stacks split OLTP and OLAP into two databases (MySQL / Postgres for OLTP + Snowflake / Redshift for OLAP) connected by CDC or ETL, which introduces lag (minutes to hours), operational overhead (two stores to run, one pipeline to maintain), and consistency headaches (dashboards show yesterday's numbers). TiDB does HTAP natively by pairing TiKV (row-oriented OLTP) with TiFlash (column-oriented OLAP) inside one cluster, kept in sync via Raft learner replicas — the same INSERT you commit at 12:00:00.123 is queryable via GROUP BY at 12:00:00.456. The cost-based optimizer routes each query to the store that answers it fastest, so from the client's perspective there is one SQL dialect, one connection string, and one data model — but the physical layout is per-workload-optimised under the hood. This is the architectural pattern TiDB pioneered in the distributed SQL space and it is why the company (PingCAP) bet the whole database on it.

Is TiDB really MySQL-compatible?

TiDB speaks the MySQL 5.7/8.0 wire protocol and implements roughly 99% of MySQL SQL — the standard JDBC driver, the mysql command-line client, ORMs like Django / SQLAlchemy / Hibernate, and every existing MySQL query in your codebase should Just Work with only a connection string change. The 1% incompatibility is well-documented and specific: stored procedures (CREATE PROCEDURE) are not supported — move the logic to application code; triggers (CREATE TRIGGER) are not supported — replace with application-level or outbox-pattern writes; spatial types (POINT, GEOMETRY, LINESTRING, POLYGON) are not supported — use scaled BIGINT lat/lng columns with bounding-box indexes for light usage, or a specialised geospatial store for heavy usage; some legacy character sets (ucs2, sjis) are absent or partial — convert to utf8mb4 at migration time; foreign key enforcement was added in TiDB 6.6+ (older versions parsed but did not enforce), so if you rely on FK cascades verify you are on a recent version; some MySQL-specific SQL modes, session variables, and system tables are absent or behave slightly differently. For any migration, run a source-side audit against INFORMATION_SCHEMA.ROUTINES, INFORMATION_SCHEMA.TRIGGERS, and INFORMATION_SCHEMA.COLUMNS to enumerate every construct in the incompatible 1% before the migration; budget one sprint per non-trivial refactor. The overall compatibility surface is genuinely enough for the vast majority of MySQL workloads, but the 1% will surprise you if you have not enumerated it.

TiDB vs CockroachDB — when to pick each?

Both are Raft-based, horizontally-sharded, production-mature NewSQL databases, and they occupy adjacent quadrants of the distributed SQL market — the choice is dictated almost entirely by your wire-protocol requirement and whether HTAP is worth more than strict serialisable. Pick TiDB when: your application is MySQL-shaped (existing JDBC, MySQL-native ORM, MySQL SQL dialect), you need sub-second analytics on live data (HTAP via TiFlash is the killer feature), the license is a concern (TiDB is Apache 2; CockroachDB is BSL / source-available with a 4-year Apache 2 conversion clock), and snapshot isolation (with optional strict serialisable) is sufficient for your correctness requirements. Pick CockroachDB when: your application is Postgres-shaped (existing psycopg2, Postgres-specific SQL, JSONB / LATERAL / MATERIALIZED CTE features), multi-region write distribution with strict serialisable consistency is a first-class requirement, you can tolerate warehouse-based analytics (Cockroach has no columnar tier — pair with Snowflake or similar), and mature geo-partitioning primitives ("pin data to region X", "follow the workload") matter. Both databases have comparable operational maturity, comparable managed offerings (TiDB Cloud vs CockroachDB Cloud), and comparable production references at scale. Neither is universally better; the wire-protocol match to your existing team is almost always the deciding factor.

Is TiDB production-ready in 2026?

Yes, unambiguously, for both self-hosted and managed deployments. Self-hosted TiDB (deployed via tiup cluster) has been running in production at Square (payments), Shopee (e-commerce, multiple Southeast Asian markets), Pinterest (analytics workloads), ByteDance (multiple internal platforms), and hundreds of other companies for years; the software has matured through many major releases, the operational tooling (Grafana dashboards, Prometheus exporters, pd-ctl, tikv-ctl, br for backup and restore) is polished, and the community documentation is extensive. TiDB Cloud (PingCAP's managed offering) provides both Serverless (usage-based, cheap for small workloads, autoscaling) and Dedicated (provisioned, VPC-peered, enterprise-grade) tiers, both generally available, and both are used by enterprise customers with production SLAs. The remaining rough edges in 2026 are largely around operational learning curve (Raft-based systems have different failure modes than single-node MySQL; teams need 1-2 quarters to internalise them) and the specific ~1% MySQL incompatibility surface (which is well-documented but must be enumerated per workload). None of these are blockers; they are the standard "run a distributed database in production" concerns. The interviewer's implicit question here is almost always "is it mature enough to bet a business on" — the answer in 2026 is unambiguously yes, with the caveat that you must run it operationally the way you would any other tier-1 distributed system.

Practice on PipeCode

• Drill the SQL practice library → for the distributed-transaction, sharding, and MySQL-wire query problems senior interviewers use to probe TiDB fluency.
• Rehearse system design against the design practice library → for HTAP architecture, Raft-based replication, and multi-tier storage-engine problems that mirror the TiDB design interview.
• Sharpen the aggregation axis on the aggregation practice library → for the GROUP BY / SUM / DISTINCT-window shapes that dominate TiFlash MPP workloads.
• Practise the joins practice library → for the fact-dimension shuffle-join and broadcast-join patterns typical of TiFlash analytical workloads.
• Sharpen the streaming axis with the streaming practice library → for TiCDC + Kafka change-feed problems and the CDC-consumer contracts that show up in senior HTAP interviews.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the TiDB decision matrix, the four-tier architecture, and the Raft-learner sync semantics against real graded inputs.

Lock in tidb muscle memory

Docs explain what TiDB is. PipeCode drills explain when it wins — when TiKV's row layout is the right substrate for a point-lookup and when TiFlash's columnar MPP is the right one for a GROUP BY, when Raft learners keep OLAP and OLTP honest without a second ETL, when the DM tool is the safest cutover path off a MySQL cluster, and when the honest answer to "should we migrate to TiDB" is "no, and here is a Postgres-native alternative." Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face when picking, deploying, and defending a distributed HTAP database.

Practice SQL problems →
Practice design problems →