Overview
Distributed databases provide IoT platforms with the capability to store business data such as device metadata, product definitions, user information, and orders, complementing time-series databases (which store sensor data). IoT business data requires strong consistency, transactional support, and relational query capabilities: device registration, product data models, user permissions, and multi-tenant isolation all require ACID guarantees; associations between device tables and product or group tables are queried frequently; at the scale of millions of devices, a standalone MySQL cannot support the load. Distributed databases such as TiDB, CockroachDB, and OceanBase achieve horizontal scaling and high availability through sharding, replication, and distributed transactions, meeting IoT platform demands for business data storage.
IoT platforms usually adopt a "dual-database" architecture: time-series data (such as temperature and humidity reported by devices, usage, etc.) is stored in time-series databases like InfluxDB or TDengine; business data (such as device profiles, products, users, rules) is stored in distributed relational databases. The two are linked via device ID, and queries may require cross-database aggregation (for example, "the most recent data for all devices under a certain product"), which can be implemented through application-layer assembly or materialized views.
Core Features
Distributed Transactions
Cross-node ACID to ensure the atomicity of composite operations such as "device registration + product association + permission assignment". Protocols like two-phase commit (2PC) and Percolator are used. In IoT scenarios, operations such as device activation, order creation, and bulk import often involve writing to multiple tables and require transaction guarantees.
Horizontal Scaling
Sharding by device ID, tenant ID, product ID, etc., to support linear scalability. When new nodes are added, data automatically migrates without downtime. The replication mechanism ensures that data is not lost in the event of a single-point failure, and read requests can be distributed to replicas.
MySQL Compatibility
TiDB, OceanBase, and others are compatible with the MySQL protocol. Existing MySQL-based applications can be migrated smoothly, reducing modification costs. Supports common SQL syntax, indexes, and foreign keys (partially implemented).
High Availability
Automatic failover, with standby nodes promoted when the primary node fails. Multi-availability zone deployment to avoid single data center failures. RTO (Recovery Time Objective) and RPO (Recovery Point Objective) are configurable.
Typical IoT Table Structure
Devices Table (devices): Device ID, Product ID, Device Name, Key, Status, Last Online Time, Tenant, Group, etc. Sharded by Product ID or Tenant ID.
Products Table (products): Product ID, Thing Model, Communication Protocol, Data Format, etc. The number of products is generally much smaller than the number of devices, can be sharded or a single table.
Users and Permissions Table: Users, Roles, Permissions, Tenants. Works with multi-tenant architecture to achieve data isolation.
Rules and Configurations Table: Rule engine rules, alert configurations, OTA tasks, etc. High read/write ratio, can be used with caching.
Selection Considerations
Data volume scale, read/write ratio, transaction requirements, compatibility with existing tech stack. TiDB is suitable for MySQL migration and HTAP hybrid workloads; CockroachDB is suitable for globally distributed deployments; OceanBase is suitable for financial-grade strong consistency. In IoT platform practices, business data often uses TiDB or cloud vendor-managed MySQL (such as Alibaba Cloud PolarDB), and time-series data uses TDengine or InfluxDB, achieving efficient division of the storage layer.