TDengine Data Modeling Practice Guide

Data modeling is the foundation of time-series database application development. A well-designed model directly affects system performance and maintainability. A smart meter scenario illustrates the complete TDengine data modeling workflow.

1. Modeling design principles

1.1 Core design philosophy

TDengine follows the “one table per device” design philosophy, which brings three advantages:

• Write performance: Single writer per table with lock-free writing
• Query efficiency: Contiguous data storage reduces random I/O
• High compression ratio: Columnar storage compresses slowly changing data effectively

1.2 Modeling steps

1. Analyze the business scenario and identify data collection points
2. Determine metrics and tags
3. Design the Supertable structure
4. Create the database
5. Create Supertables and Subtables

2. Creating a database

2.1 Database creation syntax

CREATE DATABASE power PRECISION 'ms' KEEP 3650 DURATION 10 BUFFER 16;

2.2 Parameter details

Parameter	Description	Example value
PRECISION	Timestamp precision	‘ms’ (milliseconds)
KEEP	Data retention in days	3650 (about 10 years)
DURATION	Time span per data file	10 (days)
BUFFER	Write buffer memory size	16 (MB)

2.3 Parameter selection recommendations

Timestamp precision:

• Millisecond (ms): Suitable for most IoT scenarios
• Microsecond (us): Suitable for finance and high-frequency trading
• Nanosecond (ns): Suitable for scientific experiments and precision measurement

Data retention policy:

• Determined by business needs and storage capacity
• Historical data can be archived periodically
• Tiered storage for hot and cold data

2.4 Switching databases

USE power;

After execution, subsequent insert, query, and other operations run in the current power database.

3. Creating a Supertable

3.1 Supertable creation syntax

CREATE STABLE meters (
    ts timestamp,
    current float,
    voltage int,
    phase float
) TAGS (
    location varchar(64),
    group_id int
);

3.2 Column definition rules

Timestamp column:

• The first column must be a timestamp column
• Format: column_name timestamp

Metric columns:

• Defined starting from the second column
• Data types can be integer, float, string, and others
• Example: current float, voltage int

Tag columns:

• Defined with the TAGS keyword
• Data types can be any type
• Tag names must not duplicate metric column names

3.3 Data type selection

Data type	Use case	Example
TINYINT	Small-range integers	Status codes
SMALLINT	Medium-range integers	Temperature (integer)
INT	Regular integers	Voltage values
BIGINT	Large integers	Device IDs
FLOAT	Single-precision float	Current values
DOUBLE	Double-precision float	High-precision measurements
VARCHAR	Strings	Location info
TIMESTAMP	Timestamps	Collection time

4. Creating Subtables

4.1 Creating Subtables manually

CREATE TABLE d1001
USING meters (
    location,
    group_id
) TAGS (
    "California.SanFrancisco",
    2
);

Parameter description:

• d1001: Subtable name
• USING meters: Uses the Supertable meters as the template
• TAGS is followed by tag values

4.2 Automatic table creation

Subtables can be created automatically when writing data:

INSERT INTO d1002
USING meters
TAGS (
    "California.SanFrancisco",
    2
) VALUES (
    NOW,
    10.2,
    219,
    0.32
);

When Subtable d1002 does not exist, the system creates the Subtable automatically before writing the data.

4.3 Partial tag specification

You can specify only some tag values. Unspecified tags default to NULL:

INSERT INTO d1005
USING meters (location)
TAGS ("London.Westminster")
VALUES ("2018-10-04 14:38:07", 10.15, 217, 0.33);

5. Creating basic tables

5.1 Basic table concepts

A basic table is a table without any tags, similar to tables in a standard relational database.

5.2 Differences between basic tables and Subtables

Feature	Basic table	Subtable
Tag extensibility	No tags	Has configurable tags
Table affiliation	Standalone	Belongs to a Supertable
Conversion	Cannot be converted	Cannot be converted

5.3 Use cases

Basic tables are suitable for:

• Data that does not need tag classification
• Single data source scenarios
• Compatibility with traditional relational database needs

6. Modeling best practices

6.1 Database planning

Separate databases by data characteristics:

• Different collection frequencies go to different databases
• Different retention policies go to different databases
• Different business domains go to different databases

Example:

<em>-- High-frequency collection database</em>
CREATE DATABASE high_freq PRECISION 'ms' KEEP 365 DURATION 1;

<em>-- Low-frequency collection database</em>
CREATE DATABASE low_freq PRECISION 'ms' KEEP 3650 DURATION 30;

6.2 Supertable design

Group similar devices into one Supertable:

• Same metric structure
• Same tag dimensions
• Easier unified querying and management

Tag design principles:

• Choose stable, unchanging attributes as tags
• Prioritize attributes commonly used as filter criteria
• Keep the number of tags manageable (recommended no more than 10)

6.3 Subtable naming conventions

Recommended naming patterns:

• Use device IDs as Subtable names
• Keep naming rules consistent
• Avoid special characters

Examples:

d1001, d1002, d1003...  - Device IDs
meter_london_001        - Region + sequence number
sensor_temp_001         - Sensor type + sequence number

6.4 Metric design

Column count control:

• Limit columns per table (recommended no more than 50)
• Group related metrics together
• Place infrequently used metrics in separate tables

Data type selection:

• Match data types to the data to avoid wasting storage
• Pay attention to precision requirements for floating-point numbers
• Specify reasonable lengths for string types

7. Complete modeling example

7.1 Scenario description

A smart meter system needs to store the following data:

• Device count: 10,000
• Collection frequency: every 10 seconds
• Metrics: current, voltage, phase
• Tags: location, group ID

7.2 Modeling implementation

<em>-- 1. Create the database</em>
CREATE DATABASE power
PRECISION 'ms'
KEEP 365
DURATION 10
BUFFER 96;

<em>-- 2. Switch to the database</em>
USE power;

<em>-- 3. Create the Supertable</em>
CREATE STABLE meters (
    ts timestamp,
    current float,
    voltage int,
    phase float
) TAGS (
    location varchar(64),
    group_id int
);

<em>-- 4. Write data (automatic Subtable creation)</em>
INSERT INTO d1001 USING meters TAGS ("California.SanFrancisco", 2)
VALUES (NOW, 10.3, 219, 0.31);

7.3 Query verification

<em>-- Count all devices</em>
SELECT COUNT(*) FROM meters;

<em>-- Group statistics by region</em>
SELECT location, COUNT(*), AVG(voltage)
FROM meters
GROUP BY location;

<em>-- Time window aggregation</em>
SELECT tbname, _wstart, avg(voltage)
FROM meters
WHERE ts >= NOW - 1h
PARTITION BY tbname
INTERVAL(1m);

8. Common modeling issues

8.1 Too many tables

Problem: Large numbers of devices raise concerns about too many tables.

Solution: TDengine is designed for managing tables at scale. Tens of millions of tables do not affect performance.

8.2 Tag modification needs

Problem: Device tags may need to be modified.

Solution: TDengine supports modifying, adding, and deleting tags.

8.3 Historical data migration

Problem: How to migrate historical data.

Solution:

• Import using batch INSERT statements
• Migrate through third-party tools such as DataX
• Use TDengine’s built-in import functionality

9. Performance optimization recommendations

9.1 Write optimization

• Use batch writes instead of single-row writes
• Use automatic table creation
• Set the BUFFER parameter appropriately

9.2 Query optimization

• Specify a time range in queries
• Use PARTITION BY for parallel queries
• Use SLIMIT sensibly to control results

9.3 Storage optimization

• Choose an appropriate KEEP parameter
• Clean up expired data periodically
• Monitor compression ratio

10. Monitoring and maintenance

10.1 Checking compression ratio

SELECT * FROM INFORMATION_SCHEMA.INS_DISK_USAGE
WHERE db_name = 'power';

10.2 Checking table distribution

SHOW TABLE DISTRIBUTED meters;

10.3 Database status

SHOW DATABASES;
SHOW STABLES;
SHOW TABLES;

Summary

These modeling practices, including database creation, Supertable design, Subtable management, and more, provide a complete guide for building industrial data management platforms and real-time database applications. TDengine’s time-series data modeling capabilities make it well-suited for IoT and industrial scenarios.