TiDB Explained: The Complete Guide to Distributed SQL and HTAP | JusDB
TiDB Explained: The Complete Guide to Distributed SQL and HTAP
TiDB is an open-source distributed SQL database designed to combine the best of OLTP (transactional) and OLAP (analytical) workloads — making it a true HTAP (Hybrid Transactional and Analytical Processing) platform. Built by PingCAP, TiDB provides horizontal scalability, MySQL compatibility, and real-time analytics, making it one of the most versatile next-generation open-source databases.
At JusDB, we help enterprises adopt and scale TiDB with services ranging from TiDB consulting to performance optimization, migrations, and high availability design.
1) What is TiDB?
TiDB (short for Ti = Titanium, DB = Database) is a distributed SQL database inspired by Google Spanner and F1. Its core design goals are:
- Horizontal scalability — scale out seamlessly across nodes.
- Strong consistency — ACID transactions with a distributed architecture.
- MySQL compatibility — drop-in replacement for MySQL in most applications.
- HTAP support — OLTP and OLAP workloads on the same dataset, powered by TiFlash.
2) TiDB Architecture Overview
TiDB’s architecture separates compute from storage, enabling elastic scaling and high availability:
- TiDB Server (SQL Layer): Stateless SQL processing layer, MySQL protocol compatible.
- PD Server (Placement Driver): Manages metadata, cluster topology, and global transactions.
- TiKV (Storage): Distributed key-value store with Raft consensus for durability and consistency.
- TiFlash (Analytical Engine): Columnar storage optimized for OLAP queries, replicating from TiKV in real time.
📖 Reference: TiDB Documentation
3) Key Strengths
- Elastic Scalability: Add nodes to TiDB or TiKV for linear scale.
- MySQL Compatibility: Works with MySQL drivers, ORMs, and tools.
- Real-Time HTAP: TiFlash provides analytics without ETL.
- ACID Compliance: Global transactions powered by Raft + Percolator model.
- Cloud-Native: Built for Kubernetes with TiDB Operator.
4) Limitations & Trade-offs
- Operational Complexity: More components than MySQL/Postgres; requires orchestration.
- Latency Overhead: Distributed consensus adds some latency vs single-node MySQL.
- Community Size: Growing, but smaller than PostgreSQL/MySQL ecosystems.
- Specialized Workloads: Not optimized for small, single-node apps.
5) When to Use TiDB
- Financial transactions + analytics: OLTP + OLAP on the same dataset.
- IoT + time-series: Large-scale sensor data with mixed queries.
- E-commerce platforms: Real-time inventory, transactions, and BI dashboards.
- Multi-region deployments: Global consistency with Raft replication.
6) TiDB vs MySQL
| Aspect | TiDB | MySQL |
|---|---|---|
| Architecture | Distributed (SQL + KV + PD) | Single-node (replication/cluster add-ons) |
| Scalability | Elastic horizontal scale-out | Vertical scaling + limited sharding |
| Transactions | Global ACID with Raft | ACID (single-node, semi-sync replication) |
| Analytics | Native with TiFlash (HTAP) | Requires ETL to OLAP systems |
Related service: MySQL Consulting
7) TiDB vs PostgreSQL
| Aspect | TiDB | PostgreSQL |
|---|---|---|
| Model | Distributed SQL + HTAP | Single-node SQL (distributed via extensions) |
| Scalability | Horizontal scale-out by design | Extensions like Citus for scale-out |
| Analytics | Built-in with TiFlash | Requires FDWs/warehouses |
| Consistency | Raft consensus, strong consistency | ACID locally, weaker in extensions |
Related service: PostgreSQL Consulting
8) Deployment Options
- Self-managed clusters: On-prem or cloud VMs with TiDB binaries.
- Kubernetes-native: TiDB Operator automates deployment, scaling, and failover.
- Managed: PingCAP Cloud (TiDB Cloud) with serverless HTAP.
9) TiDB SQL Examples
🔹 Creating Tables
CREATE DATABASE ecommerce; CREATE TABLE orders ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT, amount DECIMAL(10,2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
🔹 Running Transactions
BEGIN; INSERT INTO orders (customer_id, amount) VALUES (1001, 250.00); INSERT INTO orders (customer_id, amount) VALUES (1002, 100.00); COMMIT;
🔹 HTAP Query (via TiFlash)
SELECT customer_id, SUM(amount) as total_spent FROM orders WHERE created_at > NOW() - INTERVAL 30 DAY GROUP BY customer_id ORDER BY total_spent DESC LIMIT 10;
10) Best Practices
- Use TiDB Operator for Kubernetes deployments.
- Balance load across TiDB servers with HA proxies like HAProxy or ProxySQL.
- Enable TiFlash replicas for analytical workloads.
- Monitor metrics with Prometheus + Grafana.
- Plan backup & DR strategies with PITR.
11) How JusDB Helps with TiDB
At JusDB, we provide:
- TiDB Consulting: Architecture design, workload analysis, performance tuning.
- Migrations: From MySQL, PostgreSQL, Oracle to TiDB.
- Performance Optimization: Query tuning, resource allocation, cluster scaling.
- High Availability: Multi-AZ, multi-region designs with SRE best practices.
- Monitoring & Support: 24/7 operations with emergency response.
Explore also: Database Migrations | Performance Optimization | High Availability
12) Conclusion
TiDB represents the next evolution of open-source databases — combining distributed SQL with HTAP capabilities. For businesses that outgrow single-node MySQL/Postgres, or that need real-time analytics on transactional data, TiDB offers a compelling alternative. Its MySQL compatibility, scalability, and cloud-native design make it ideal for enterprises modernizing their data platforms.
👉 If you’re considering TiDB adoption, migration, or performance tuning, contact JusDB to learn how our Database Reliability Engineering team can accelerate your TiDB journey.
Author: JusDB Database Reliability Engineering Team