TiDB: The NewSQL Database That Makes Sharding Obsolete

What Is NewSQL

Databases have evolved through three generations: traditional SQL (e.g., MySQL), NoSQL (e.g., MongoDB, Redis), and NewSQL. NewSQL provides NoSQL-like horizontal scalability while retaining the relational model, standard SQL, and ACID transactions.

Problems with Traditional SQL

Early internet growth made single-node relational databases a bottleneck. Two common fixes were vertical scaling (hardware upgrades) — which hits a ceiling — and data sharding via middleware. Sharding introduces cross-shard join and distributed transaction complexity, often pushed to the application layer.

Problems with NoSQL

NoSQL sacrifices strong consistency and SQL for availability and scalability. It lacks ACID guarantees required by financial-grade applications and uses proprietary APIs instead of standard SQL, increasing integration complexity.

NewSQL Characteristics

Full SQL support for complex queries and analytics

ACID transactions with isolation levels

Elastic scaling transparent to applications

High availability with automatic failover

TiDB Origins

PingCAP co-founder & CTO Huang Dongxu was inspired by Google's 2012 F1/Spanner papers describing a globally distributed relational database with elastic scaling. TiDB was built on those principles.

TiDB Editions

Community Edition (open source) and Enterprise Edition (paid support and security features).

Core TiDB Features

Horizontal Elastic Scaling

Adding nodes scales throughput or storage on demand. Storage-compute separation allows independent online scaling of compute and storage, transparent to applications.

Distributed Transactions

100% standard ACID transaction support.

Financial-Grade High Availability

Uses Raft majority quorum instead of traditional master-slave replication. Data stored in multiple replicas; writes succeed only after majority acknowledgment, ensuring strong consistency. Automatic failover without manual intervention. Replica placement policies configurable for disaster recovery levels.

Real-Time HTAP

TiDB combines row-store TiKV (OLTP) and column-store TiFlash (OLAP). TiFlash replicates data from TiKV via Multi-Raft Learner protocol, guaranteeing strong consistency between engines. TiKV and TiFlash can be deployed on separate machines for resource isolation. TiSpark provides Spark SQL on TiKV for complex OLAP.

Cloud-Native Design

Deep Kubernetes integration; runs on public, private, or hybrid clouds. Targets 100% OLTP and 80% OLAP scenarios; complex OLAP handled by TiSpark. Zero application intrusion; replaces traditional sharding middleware.

High MySQL Compatibility

Compatible with MySQL 5.7 protocol, common features, and ecosystem. Most migrations require zero or minimal code changes. Migration tools support real-time sync from sharded MySQL clusters.

OLTP vs OLAP

OLTP (Online Transactional Processing)

High-concurrency, low-latency transaction processing (e.g., banking, e-commerce). Metrics: transactions per second, SQL executions per second. Typical: hundreds to thousands of transactions/sec.

OLAP (Online Analytical Processing)

Complex analytical queries on large datasets (e.g., dynamic reports). Metrics: disk subsystem throughput (MB/s). Queries scan millions of rows; execution time per query can be long.

Comparison Tables

The article includes detailed tables contrasting OLTP and OLAP across real-time requirements, data volume, user orientation, database design (ER vs star/snowflake), data characteristics (current/detailed vs historical/aggregated), access patterns, concurrency, and database size (100MB–GB vs 100GB–TB).

TiDB Overall Architecture

Three core components plus TiSpark:

TiDB Server

Stateless SQL layer: parses, optimizes, executes SQL; interacts with PD for data location and TiKV for data retrieval. Horizontally scalable behind load balancers (LVS, HAProxy, F5).

PD (Placement Driver) Server

Cluster management module with three roles: (1) stores cluster metadata (key-to-TiKV mapping), (2) schedules and load-balances TiKV (region migration, Raft leader transfer), (3) allocates globally unique monotonic transaction IDs. PD uses Raft for safety; deploy odd number of nodes (recommended ≥3).

TiKV Server

Distributed transactional Key-Value storage engine. Data partitioned into Regions (key ranges, default ~144 MB). Each Region replicated via Raft (default 3 replicas). Regions form Raft Groups across nodes. PD schedules Regions for load balancing.

TiSpark

Runs Spark SQL directly on TiKV, merging big-data ecosystem with TiDB for complex OLAP without data sync.

TiFlash

Columnar storage nodes for analytical acceleration. Replicates from TiKV via Multi-Raft Learner for strong consistency.

TiKV Architecture Details

Data split into key-range Regions (~144 MB default). Each Region has multiple replicas (default 3) with one Leader serving reads/writes. PD schedules Regions and replicas for even distribution.

Region Split and Merge

Regions exceeding size limit split into two or more. Adjacent small Regions (after heavy deletes) merge automatically.

Region Scheduling

Writes go to Leader; require majority replica acknowledgment (2 of 3). To move a replica, PD adds a Learner on target node; once caught up, promotes to Follower, then removes source Follower. Leader transfer follows similar steps with an explicit Leader Transfer election.

Distributed Transactions

Two-phase commit (2PC) across multiple Regions ensures ACID without application awareness of data placement.

High Availability Architecture

TiDB HA

Stateless; deploy ≥2 instances behind load balancer. Single instance failure affects only active sessions; retry on new connection restores service.

PD HA

Raft cluster; non-leader failure transparent. Leader failure triggers new election (~3 seconds unavailability). Deploy ≥3 instances.

TiKV HA

Raft replication (default 3 replicas). Node failure affects its Regions: Leaders re-elect, Followers unaffected. If node unreachable for 10 minutes (default), PD migrates its data to other nodes.

Application Scenarios

MySQL Sharding Consolidation

Existing sharded MySQL clusters face cross-shard query challenges. TiDB speaks MySQL protocol; Syncer tool replicates MySQL master into TiDB as a slave, enabling real-time cross-shard SQL. "From one-master-many-slaves to many-masters-one-slave."

Direct MySQL Replacement

For architectures that never planned sharding, TiDB handles distribution internally. Drop-in MySQL replacement; eliminates sharding middleware overhead. Horizontal scaling with stable latency.

Data Warehouse

TiDB 2.0 runs TPC-H complex queries mostly under 10 seconds. For heavier OLAP, TiSpark runs Spark SQL directly on TiKV.

Embedded KV Module

TiKV can serve as HBase replacement with cross-row transactions. Two APIs: ACID Transaction API (cross-row) and Raw API (single-row, higher performance). Some users implement Redis protocol on TiKV for high-capacity, latency-tolerant caches.

TiDB vs MySQL Compatibility

Supports MySQL protocol and most syntax; existing connectors/clients work unchanged.

Officially targets MySQL 5.7 compatibility.

Most admin tools (phpMyAdmin, Navicat, MySQL Workbench) and backup tools (mysqldump, Mydumper/myloader) work directly.

Some features unsupported or behave differently in distributed context.

Certain MySQL syntax parses but is ignored (e.g., ENGINE in CREATE TABLE).

Unsupported MySQL Features

Stored procedures & functions

Triggers

Events

User-defined functions

Foreign key constraints

Temporary tables

Full-text/spatial functions and indexes

Non-ascii/latin1/binary/utf8/utf8mb4 charsets

SYS schema

MySQL optimizer trace

XML functions

X-Protocol

Savepoints

Column-level privileges

XA syntax (TiDB uses 2PC internally but not exposed via SQL)

CREATE TABLE ... AS SELECT

CHECK TABLE, CHECKSUM TABLE

GET_LOCK / RELEASE_LOCK

Auto-Increment ID Behavior

TiDB auto-increment guarantees uniqueness and monotonic increase per TiDB server, but not across servers nor continuity. Mixing explicit and auto values may cause Duplicated Error. Variable tidb_allow_remove_auto_inc controls dropping AUTO_INCREMENT via ALTER TABLE MODIFY or ALTER TABLE CHANGE; once removed, cannot be re-added.

SELECT Limitations

No SELECT ... INTO @variable No

SELECT ... GROUP BY ... WITH ROLLUP

SELECT ... GROUP BY expr

returns no guaranteed order (matches MySQL 8.0), unlike MySQL 5.7 which implicitly orders by expr.

Views

Views are read-only; UPDATE/INSERT/DELETE on views not supported.

Default Setting Differences

Character Set

TiDB default: utf8mb4 MySQL 5.7 default: latin1 MySQL 8.0 default:

utf8mb4

Collation

TiDB utf8mb4 default: utf8mb4_bin MySQL 5.7 utf8mb4 default: utf8mb4_general_ci MySQL 8.0 utf8mb4 default:

utf8mb4_0900_ai_ci

Case Sensitivity ( lower_case_table_names )

TiDB default: 2 (store as given, compare lowercase); only value supported.

MySQL defaults: Linux 0 (case-sensitive), Windows 1 (store lowercase, compare case-insensitive), macOS 2.

Timestamp Auto-Update ( explicit_defaults_for_timestamp )

TiDB default: ON (timestamp NOT auto-updated on row update); only ON supported.

MySQL 5.7 default: OFF (auto-update).

MySQL 8.0 default: ON.

Foreign Key Checks

TiDB default: OFF; only OFF supported.

MySQL 5.7 default: ON.