The limits of traditional data modeling
The “One Table per Data Collection Point” model and Supertable design address many time-series workloads, but real-world devices often contain multiple sensors with very different collection frequencies. Take a wind turbine as an example: electrical parameters are collected at high frequency (multiple times per second), environmental parameters at medium frequency (several times per minute), and mechanical parameters at low frequency (several times per hour). Because each sensor type operates at a different frequency, a single table cannot adequately describe one device.
The traditional workaround uses multi-level join queries to analyze data across tables. This leads to complex SQL, higher development effort, expensive joins, and lower query efficiency. Adopting a “One Table per Device” design directly creates a different problem: the mismatched collection frequencies leave many columns NULL at any given timestamp, hurting both storage density and query performance.
Core concepts of virtual tables
A Virtual Table (VTable) stores no physical data but can still be queried for analysis and computation. Its data comes from other real tables that do store data, including Subtables and Basic Tables. The VTable is built by sorting, aligning, and merging columns drawn from those source tables by timestamp.
Categories:
- Virtual Supertable: A complete collection of data needed for one device or one analytical task.
- Virtual Subtable: A flexible definition that references the same or different columns from source tables as needed.
- Virtual Basic Table: A standalone virtual table instance.
Key characteristics:
- Stores no data and cannot accept writes or deletes.
- Data is generated dynamically at each query execution.
- Query usage is identical to real tables, with full transparency.
- Only columns referenced in a query are merged; unused columns are ignored.
Core functions of virtual tables
Column selection and composition
Select specific columns from multiple source tables and combine them into one virtual table on demand. This creates a unified data view where measurements collected at different frequencies are presented together. Analysts interact with the virtual table without needing to understand the underlying storage structure, decoupling the business perspective from the storage layer.
Timestamp-based alignment
When multiple source tables have data at the same timestamp, the corresponding column values are combined into a single row. When some tables have no data at a given timestamp, those columns are filled with NULL.
Dynamic update
Virtual tables reflect changes in source data automatically. Computation happens at query time, which saves storage space and keeps data consistent with its sources.
Write first, model later
The traditional workflow follows a rigid sequence: requirements analysis, table structure design, table creation, data writing, and then data analysis. This path carries several problems. The upfront modeling burden is heavy. Changing requirements forces difficult schema modifications. And it is easy to miss measurements during the initial design phase.
Virtual tables change this workflow to: data writing (per the device protocol), data storage, on-demand modeling (via virtual tables), and then data analysis. During the write phase, data is ingested in the form closest to the device protocol, such as the Single-Column Model. After storage, business-oriented data models are created dynamically based on analytical needs.
This approach lowers the complexity of data ingestion, reduces the upfront modeling burden, and makes the overall data pipeline more flexible and efficient.
Application scenarios
Complex device management: A Virtual Supertable is a complete collection of data for one device or one analytical task. Each Virtual Subtable can reference the same or different columns as needed, giving different users and use cases their own tailored views of the same device.
Cross-database and cross-table analysis: Virtual tables can reference data sources across databases and tables arbitrarily and perform aggregation operations on them.
Unified business view: The storage layer keeps data distributed by collection point for performance. The business layer presents data unified by device for ease of use. These two concerns stay independent.
Virtual tables vs. real tables
| Feature | Real table | Virtual table |
|---|---|---|
| Data storage | Actually stores data | Does not store data |
| Write operations | Supported | Not supported |
| Delete operations | Supported | Not supported |
| Query operations | Supported | Supported |
| Data source | Direct collection | Derived from other tables |
| Update mechanism | Manual operations | Dynamically generated at query time |
Best practices
Modeling strategy: At the storage layer, model data in the form closest to the device protocol and prioritize write performance. At the business layer, build business-oriented views through virtual tables. This separates storage optimization from analytical modeling.
Virtual table design: Define the virtual table structure based on analytical requirements. Choose referenced source columns carefully. Use the timestamp alignment mechanism to handle frequency differences between source tables.
Performance optimization: Pay attention to the computational cost of dynamic query generation for virtual tables. Design appropriate indexes on source tables. Avoid overly complex aggregation logic that compounds at query time.
Summary
The virtual table mechanism in TDengine makes “One Table per Device” practical. By inverting the traditional workflow to write first and model later, it lowers the complexity of data ingestion. By generating virtual tables dynamically at query time, it balances storage efficiency with analytical convenience. This makes the device-oriented analytical model practical without forcing the storage layer into an inefficient shape.
