Why TiDB Is the Next‑Gen NewSQL Database for Scalable, ACID‑Compliant Applications

What Is NewSQL

NewSQL is the third generation of database technology that retains the ACID guarantees and relational model of traditional SQL databases while providing the horizontal scalability and high availability of NoSQL systems.

Problems of Traditional SQL

With the rapid growth of internet applications, single‑node relational databases become performance bottlenecks. Typical solutions such as upgrading hardware eventually hit a ceiling, and sharding across multiple databases introduces complex middleware and cross‑shard transaction challenges.

Problems of NoSQL

NoSQL sacrifices strong consistency and the relational model to achieve high availability and easy scaling. While it offers great performance, it lacks full SQL support, foreign‑key constraints, stored procedures, and many advanced MySQL features.

NewSQL Characteristics

NewSQL provides the same scalability as NoSQL while keeping the relational model and full SQL support, guaranteeing ACID transactions and isolation levels. It is designed for cloud environments and supports both OLTP and OLAP workloads.

TiDB Overview

TiDB is an open‑source distributed NewSQL database developed by PingCAP. It supports horizontal elastic expansion, strong consistency, MySQL 5.7 protocol compatibility, and real‑time HTAP through TiSpark and TiFlash. TiDB targets both OLTP (online transaction processing) and OLAP (online analytical processing) scenarios.

TiDB Core Features

Horizontal elastic scaling – add or remove nodes without downtime.

Distributed ACID transactions – 100% ACID compliance across the cluster.

Financial‑grade high availability – Raft‑based replication with automatic failover.

Real‑time HTAP – TiDB stores data in row‑oriented TiKV and column‑oriented TiFlash, enabling simultaneous OLTP and OLAP queries without ETL.

Cloud‑native design – deep integration with Kubernetes, supporting public, private, and hybrid clouds.

MySQL compatibility – supports MySQL 5.7 syntax, protocol, and most ecosystem tools.

TiDB Architecture

The TiDB cluster consists of three core components:

TiDB Server – stateless SQL processing nodes that receive queries, locate data via PD, and interact with TiKV.

Placement Driver (PD) – stores metadata, schedules region placement, and allocates global transaction IDs using Raft.

TiKV Server – a distributed key‑value store that holds data in Region units, each replicated via Raft.

Additional components include:

TiSpark – a Spark plugin that runs Spark SQL directly on TiKV for large‑scale analytical workloads.

TiFlash – a column‑store node that provides fast analytical queries.

High Availability

All three components are designed to tolerate node failures. TiDB servers are stateless; a minimum of two instances behind a load balancer ensures service continuity. PD runs an odd number of nodes (recommended three) to maintain Raft quorum; if the leader fails, a new leader is elected within seconds. TiKV stores data with three replicas by default; loss of a leader triggers Raft re‑election, and prolonged node loss triggers region migration to healthy nodes.

Use Cases

MySQL sharding and consolidation – TiDB can act as a MySQL slave to synchronize data, enabling real‑time cross‑shard queries without custom middleware.

Direct MySQL replacement – applications can switch to TiDB with minimal code changes, benefiting from automatic scaling and strong consistency.

Data warehouse – TiDB 2.0 delivers sub‑10‑second query times on TPC‑H workloads; TiSpark extends analytical capabilities.

Backend for other systems – TiKV can serve as a distributed key‑value store (e.g., Redis‑compatible) for high‑throughput services.

MySQL Compatibility and Limitations

TiDB fully supports the MySQL 5.7 protocol and most syntax, allowing existing MySQL clients and tools to connect without modification. However, several MySQL features are not supported or behave differently, including stored procedures, triggers, events, custom functions, foreign‑key constraints, temporary tables, full‑text and spatial indexes, certain character sets, system schemas, optimizer tracing, XML functions, X‑Protocol, savepoints, column‑level privileges, XA transactions, CREATE TABLE AS, CHECK/TABLE, CHECKSUM, GET_LOCK/RELEASE_LOCK, and view‑based DML.

Default Configuration Differences

Character set – TiDB defaults to utf8mb4 with utf8mb4_bin collation; MySQL 5.7 defaults to latin1, MySQL 8.0 defaults to utf8mb4 with utf8mb4_0900_ai_ci. lower_case_table_names – TiDB only supports value 2 (store names as given, compare case‑insensitively). MySQL defaults vary by OS (0 on Linux, 1 on Windows, 2 on macOS). explicit_defaults_for_timestamp – TiDB forces ON (timestamp columns are not auto‑updated); MySQL 5.7 defaults to OFF, MySQL 8.0 defaults to ON. foreign_key_checks – TiDB forces OFF; MySQL 5.7 defaults to ON.

Auto‑Increment Behavior

TiDB guarantees uniqueness of auto‑increment values within a single TiDB server but does not guarantee global sequential ordering across multiple servers. The system variable tidb_allow_remove_auto_inc controls whether the AUTO_INCREMENT attribute can be removed.

SELECT Limitations

TiDB does not support SELECT ... INTO @var, SELECT ... GROUP BY ... WITH ROLLUP, and the ordering semantics of GROUP BY expr differ from MySQL 5.7 (TiDB follows MySQL 8.0 behavior, returning rows without guaranteed order).

View Limitations

TiDB does not allow UPDATE, INSERT, or DELETE operations on views.

Summary

TiDB combines the relational strengths of MySQL with the scalability of distributed systems, offering strong consistency, high availability, and native HTAP capabilities. While it provides extensive MySQL compatibility, developers must be aware of unsupported features and configuration differences when migrating existing workloads.