How TiDB’s NewSQL Design Solves Traditional Database Limits

What is NewSQL

Database development has gone through three generations: traditional relational SQL databases (e.g., MySQL), NoSQL databases (e.g., MongoDB, Redis), and the emerging NewSQL.

SQL – traditional relational databases.

NoSQL – non‑relational, highly scalable systems.

NewSQL – combines the scalability of NoSQL with the strong consistency and SQL support of relational databases.

Problems with Traditional SQL

With the rapid growth of Internet applications, the scale of users and data has exploded, demanding 24/7 availability. Traditional relational databases become bottlenecks, and two common remedies are used:

Upgrade hardware

Improves performance but eventually hits a ceiling.

Data sharding

Use a distributed cluster structure.

Sharding spreads data across cheap machines, improving scalability, but introduces new complexities such as middleware for cross‑shard joins and transactions, which are often painful to manage.

NoSQL Issues

NoSQL sacrifices strong ACID guarantees and relational modeling for high availability and scalability.

Advantages

High availability and easy horizontal scaling.

Performance boost by relaxing strong consistency.

Flexibility without relational constraints.

Disadvantages

Lack of strong consistency, which many enterprise applications require.

Incompatible SQL syntax; each NoSQL system has its own API.

NewSQL Features

NewSQL provides NoSQL‑level scalability while retaining the mature relational model and SQL query language, guaranteeing ACID transactions.

In short, NewSQL integrates the scalability of NoSQL into traditional relational databases.

Main NewSQL Characteristics

Full SQL support, enabling complex queries and big‑data analytics.

ACID transactions with isolation levels.

Elastic scaling that is transparent to the application layer.

High availability with automatic failover.

Three‑SQL Comparison

How TiDB Originated

Huang Dongxu, co‑founder and CTO of PingCAP, was inspired by Google’s F1/Spanner papers in 2012, which described a massive relational database with horizontal scalability and global distribution. This vision led to the creation of TiDB.

TiDB Community and Enterprise Editions

TiDB is offered in a free community edition and a paid enterprise edition that provides additional services and security support.

TiDB Core Features

Horizontal Elastic Scaling

Adding new nodes enables TiDB to scale horizontally, adjusting throughput or storage on demand.

The separation of storage and compute allows independent scaling of each component without affecting the other.

Distributed Transaction Support

TiDB fully supports standard ACID transactions.

Financial‑Grade High Availability

Based on the Raft consensus algorithm, TiDB provides 100% data consistency and automatic failover without manual intervention.

Data is replicated across multiple nodes; the majority must acknowledge a write before it is committed.

Real‑time HTAP

TiDB combines OLTP row storage (TiKV) with OLAP column storage (TiFlash) to deliver a unified HTAP solution without ETL.

TiFlash replicates data from TiKV via the Multi‑Raft Learner protocol, ensuring strong consistency between the two engines.

Cloud‑Native Distributed Database

TiDB is tightly integrated with Kubernetes and can run on public, private, or hybrid clouds, simplifying deployment, configuration, and maintenance.

High MySQL Compatibility

TiDB implements the MySQL 5.7 protocol and most MySQL syntax, allowing most applications to migrate with little or no code changes.

OLTP & OLAP (Self‑Study)

OLTP (Online Transaction Processing)

OLTP handles high‑volume, low‑latency transactions such as banking operations, typically processing hundreds to thousands of transactions per second.

OLAP (Online Analytical Processing)

OLAP focuses on complex analytical queries over large data sets, emphasizing throughput (MB/s) rather than low latency.

Feature Comparison

Key differences include real‑time requirements, data volume, user focus, and database design models.

---------------------------------------------------------------
|          | OLTP                              | OLAP          |
|----------|-----------------------------------|---------------|
| Real‑time| High, transaction‑oriented        | Lower, batch |
| Data size| Tens‑to‑hundreds of rows per txn| Hundreds of GB |
| Users    | Customers, transactional apps    | Analysts, managers |
| Design   | ER model, application‑centric    | Star/snowflake schema |
---------------------------------------------------------------

TiDB Overall Architecture

The TiDB cluster consists of three core components: TiDB Server (stateless SQL layer), Placement Driver (PD) Server (metadata and scheduling), and TiKV Server (distributed key‑value storage). Additional components such as TiSpark and TiFlash address OLAP workloads.

TiDB Server

Receives SQL requests, parses them, and forwards data access to TiKV via PD. It is stateless and can be scaled horizontally behind a load balancer.

PD (Placement Driver) Server

Manages cluster metadata, schedules TiKV region placement, and allocates globally unique transaction IDs. PD uses Raft for consensus and recommends an odd number of nodes for reliability.

TiKV Server

Provides distributed transactional key‑value storage. Data is divided into Regions, each replicated via Raft. PD balances Regions across nodes.

TiSpark

Runs Spark SQL directly on TiKV storage, enabling real‑time big‑data analytics without data movement.

TiFlash

Column‑store nodes that accelerate analytical queries by replicating TiKV data in a columnar format.

TiKV Architecture

TiKV splits data into Regions (≈144 MB each). Regions can split when they grow or merge when they shrink, ensuring balanced load distribution.

Region Split & Merge

When a Region exceeds the size limit, TiKV splits it; when it becomes too small, adjacent Regions merge.

Region Scheduling

PD adds a Learner replica to a target node, promotes it to Follower, then transfers leadership as needed, ensuring balanced distribution.

Distributed Transactions

TiKV supports two‑phase commit across multiple keys, guaranteeing ACID properties even when keys span different Regions.

High‑Availability Architecture

TiDB HA

TiDB is stateless; deploying at least two instances behind a load balancer ensures service continuity. Failed instances can be restarted or replaced.

PD HA

PD forms a Raft cluster; loss of a non‑leader node is transparent, while leader loss triggers a brief election (~3 s). Deploy at least three PD nodes.

TiKV HA

TiKV stores data with configurable replica count (default three). If a node fails, Raft elects a new leader for affected Regions, and PD migrates data after a timeout.

Application Scenarios

MySQL Sharding & Merging

TiDB can act as a MySQL slave, enabling real‑time cross‑shard queries without complex middleware.

Direct MySQL Replacement

TiDB’s MySQL compatibility allows a drop‑in replacement, eliminating the need for manual sharding and providing seamless horizontal scaling.

Data Warehouse

TiDB 2.0 delivers strong OLAP performance, handling TPC‑H‑style queries in under 10 seconds; TiSpark further extends analytical capabilities.

Module for Other Systems

TiKV can replace HBase as a key‑value store, offering ACID transactions and a raw API for high‑performance workloads.

TiDB & MySQL Compatibility Comparison

TiDB supports the MySQL protocol and most MySQL syntax, allowing existing connectors and tools to work without modification.

Supported MySQL version: 5.7 (compatible with many MySQL tools).

Some MySQL features are not supported or behave differently (e.g., stored procedures, triggers, foreign keys, certain character sets).

Unsupported MySQL Features

Stored procedures and functions

Triggers

Events

Custom functions

Foreign key constraints

Temporary tables

Full‑text and spatial indexes

Various character sets (ascii, latin1, binary, utf8, utf8mb4)

SYSTEM schema

MySQL optimizer trace

XML functions

X‑Protocol

Savepoints

Column‑level privileges

XA syntax (internal two‑phase commit is not exposed)

CREATE TABLE … AS SELECT

CHECK TABLE, CHECKSUM TABLE

GET_LOCK / RELEASE_LOCK functions

Auto‑Increment ID

TiDB guarantees uniqueness within a single TiDB server but does not guarantee global sequentiality across servers; mixing default and custom values may cause Duplicated Error. The system variable tidb_allow_remove_auto_inc controls whether AUTO_INCREMENT can be removed.

SELECT Limitations

Does not support SELECT ... INTO @var.

Does not support SELECT ... GROUP BY ... WITH ROLLUP.

Result ordering for SELECT ... GROUP BY expr follows MySQL 8.0 semantics, not MySQL 5.7.

Views

TiDB does not support write operations (UPDATE, INSERT, DELETE) on views.

Default Setting Differences

Character Set

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

utf8mb4

Collation

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

utf8mb4_0900_ai_ci

Case Sensitivity

Configuration of lower_case_table_names differs across operating systems.

TiDB supports only value 2 (store names as given, compare case‑insensitively).

Linux default: 0 (case‑sensitive).

Windows default: 1 (store lower‑case, compare case‑insensitively).

macOS default: 2 (store as given, compare case‑insensitively).

Timestamp Field Update

By default, TiDB updates timestamp columns on row modification ( ON ), while MySQL 5.7 defaults to OFF and MySQL 8.0 defaults to ON .

Foreign Key Support

TiDB default: OFF (cannot be enabled).

MySQL 5.7 default: ON.