A time-series database (TSDB) is a specialized database designed for processing time-series data. Its data model design directly determines storage efficiency and query performance. This guide covers the core data model concepts every developer should know: metrics, tags, data collection points, tables, Supertables, and Subtables.
1. Basic concepts of time-series data
To illustrate these concepts clearly, we use a smart meter as an example. Consider a particular smart meter model that collects three analog measurements: current, voltage, and phase. It also has static attributes such as location and group ID.
| Device ID | Timestamp | Current | Voltage | Phase | Location | Group ID |
|---|---|---|---|---|---|---|
| d1001 | 1538548685000 | 10.3 | 219 | 0.31 | California.SanFrancisco | 2 |
| d1002 | 1538548684000 | 10.2 | 220 | 0.23 | California.SanFrancisco | 3 |
| d1003 | 1538548686500 | 11.5 | 221 | 0.35 | California.LosAngeles | 3 |
The table above shows the physical measurements collected by smart meters at specific timestamps, including current, voltage, and phase. This data forms a typical time-series data structure.
2. Metrics: the core elements of time-series data
A metric is a physical quantity collected by sensors, devices, or other types of collection points. Examples include current, voltage, temperature, pressure, and GPS coordinates. Because these quantities change over time, metrics can be of various data types, including integers, floating-point numbers, booleans, and strings.
As time passes, stored metric data grows continuously. In the smart meter example, current, voltage, and phase are typical metrics. Metrics have the following characteristics:
- Time-varying: metric values change over time
- Continuous: data is produced continuously in chronological order
- High volume: data accumulates to enormous volumes over long periods
3. Tags: carriers of static attributes
A tag is a static attribute attached to sensors, devices, or other types of collection points. These attributes do not change over time. Examples include device model, color, and location. Tags can be of any data type.
Unlike metrics, the volume of tag data remains relatively stable over time and does not show significant growth. In the smart meter example, Location and Group ID are typical tags.
Although tags are static by nature, users may need to modify, delete, or add tags in practice, which provides flexibility in data management.
4. Data collection points: the source of data
A data collection point is a hardware or software device that collects physical measurements at preset time intervals or when triggered by specific events. A single data collection point can collect one or more metrics simultaneously, but all metrics are captured at the same moment and share the same timestamp.
For complex equipment, there are usually multiple data collection points, each with potentially different collection intervals. They operate independently without interfering with one another. Take a car as an example:
- GPS data collection point: collects location information
- Engine monitoring collection point: monitors engine status
- Cabin environment collection point: focuses on cabin environment monitoring
5. Tables: the basic storage unit for time-series data
Since the collected data is typically structured, time-series databases use a relational database model to manage data, lowering the learning curve for users. To fully take advantage of the characteristics of time-series data, time-series databases adopt a “one table per data collection point” design strategy.
5.1 Design advantages of one table per collection point
This design approach offers several major advantages:
- Improved write performance: since different collection points produce data independently, each collection point has a single data source. A table therefore has only one writer, enabling lock-free writing and much faster data ingestion.
- Append-only writes: for a given collection point, data arrives in increasing timestamp order. Writes can therefore be implemented as append operations, further improving write speed.
- Faster reads: a collection point’s data is stored contiguously in blocks. Reading data for a time range greatly reduces random read operations, improving read and query speed by orders of magnitude.
- High compression ratio: each data block uses columnar storage internally. Different compression algorithms can be applied to different data types to increase the compression ratio. Because metric values typically change gradually, compression ratios are even higher.
5.2 Table structure design
In a time-series database, the table is typically named after the data collection point. Each collection point can have multiple metrics, and each metric corresponds to a column in the table. The first column must be the timestamp, with the data type Timestamp.
6. Supertables: a unified template for similar data
As the number of devices grows, the number of tables also grows rapidly, creating significant challenges for table management and cross-table aggregation. To solve this problem, time-series databases introduce the concept of the Supertable (or STable).
A Supertable is a data structure that groups data collection points of a specific type into a single logical table. These collection points share the same table structure, but their static attributes (tags) may differ.
When creating a Supertable, you define the tag schema in addition to the metric column structure. A Supertable contains at least:
- A timestamp column
- One or more metric columns
- One or more tag columns
7. Subtables: concrete instances of a Supertable
A Subtable is a logical abstraction of a data collection point. It is a concrete table that belongs to a Supertable. Users can use the Supertable definition as a template and create Subtables by specifying tag values.
The relationship between Supertables and Subtables can be summarized as follows:
- A Supertable contains multiple Subtables that share the same table structure but have different tag values
- Subtable structures cannot be modified directly, but Supertable columns and tags can be modified, and the change takes effect immediately on all Subtables
- A Supertable defines a template and does not store any data or tag information itself
8. Databases: logical containers for data management
A database is a collection of tables managed together in a time-series database. A running instance can contain multiple databases, and each database can have different storage policies configured.
Since different types of data collection points typically have different data characteristics (such as collection frequency, data retention period, replica count, and data block size), it is recommended to create Supertables with different data characteristics in separate databases to ensure the database operates at maximum efficiency.
Summary
The data model design of time-series databases fully accounts for the characteristics of time-series data. By combining concepts such as metrics, tags, tables, Supertables, and Subtables, these databases achieve efficient data storage and querying. The “one table per data collection point” design philosophy, combined with the Supertable template mechanism, keeps individual collection points fast while making large fleets of devices manageable. TDengine implements these core concepts, making it a strong fit for IoT and industrial data workloads.
