Core concepts of the Supertable
A Supertable (STable) is a core data structure in TDengine. It groups data collection points of a specific type into a single logical table. These data collection points share the same table schema, but their static attributes (Tags) can differ.
Create a Supertable with: CREATE STABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location varchar(64), group_id int);. This statement contains a Timestamp column (the first column must be a Timestamp), Metric columns (current, voltage, phase), and Tag columns (location, group_id) defined through the TAGS clause.
Key Supertable characteristics: it defines a template and does not store data or Tag values itself. Tags can be added, modified, or removed flexibly. A Supertable must contain at least one Timestamp column, one or more Metric columns, and one or more Tag columns.
Subtable design principles
A Subtable is a logical representation of a data collection point. It is a concrete table belonging to a Supertable. To create a Subtable through a Supertable: CREATE TABLE d1001 USING meters (location, group_id) TAGS ("California.SanFrancisco", 2);.
Automatic Table Creation: INSERT INTO d1002 USING meters TAGS ("California.SanFrancisco", 2) VALUES (NOW, 10.2, 219, 0.32);. If the Subtable exists, the data is written directly. If it does not exist, TDengine creates it automatically and then writes the data. This simplifies ingestion logic.
Relationship between Supertables and Subtables
| Aspect | Supertable | Subtable |
|---|---|---|
| Data storage | Does not store actual data | Stores actual time-series data |
| Tag information | Defines the Tag schema | Holds specific Tag values |
| Schema modification | Columns and Tags can be modified | Structure cannot be modified directly |
| Cardinality | One Supertable contains many Subtables | N/A |
Schema inheritance: one Supertable contains many Subtables. All Subtables share the same table schema but have different Tag values. Subtable schemas cannot be modified directly, but any change to the Supertable’s columns or Tags takes effect on all Subtables immediately.
Query mechanisms
Querying a Subtable: SELECT * FROM d1001 WHERE ts >= "2022-01-01T00:00:00+08:00";
Querying a Supertable: TDengine treats all Subtable data as a single logical dataset. It first filters for Subtables whose Tags match the query conditions, then runs the time-series query on each matching Subtable, and finally merges the results. SELECT groupid, avg(voltage) FROM meters WHERE ts >= "2022-01-01T00:00:00+08:00" GROUP BY groupid;
Design advantages
Management efficiency: for large device fleets, you define the table schema once. New devices inherit the schema automatically. Schema changes take effect globally in a single operation.
Query convenience: query all devices of the same type in one statement. Group and aggregate by Tags without writing complex UNION statements across tables.
Storage optimization: each Subtable’s data is stored independently without interference. Data for each Subtable is stored contiguously, which improves compression ratios. Tags are stored once per Subtable, saving space.
Basic Tables vs. Subtables
| Aspect | Basic Table | Subtable |
|---|---|---|
| Tag extensibility | No Tags | Has flexible Tags |
| Table affiliation | Standalone | Belongs to a Supertable |
| Conversion | N/A | Cannot be converted to or from a Basic Table |
A Basic Table is similar in function to a table in a traditional relational database. A Subtable, through its Tag mechanism, is better suited for IoT and industrial scenarios.
Best practices
Supertable design principles: group devices of the same type into a single Supertable. Design Tags thoughtfully to support query filtering. Define Metric columns based on actual needs to avoid redundancy.
Subtable naming conventions: use device IDs as Subtable names for easy identification. Keep naming rules consistent. Avoid special characters.
Tag design recommendations: choose stable, unchanging attributes as Tags. Prioritize attributes that are frequently used in filter conditions as Tags. Do not create an excessive number of Tags.
Summary
Supertables and Subtables are TDengine’s core model for managing time-series data at scale. The template-based Supertable mechanism handles millions of devices through a single schema definition, and the independent storage of each Subtable ensures consistent per-device performance. Together, they reduce operational overhead and support high-throughput data processing in IoT and industrial environments.
