Wide-table model

TimescaleDB easily supports wide-table models. Queries across multiple metrics are easier in this model, since they do not require JOINs. Also, ingest is faster since only one timestamp is written for multiple metrics.

A typical wide-table model would match a typical data stream in which multiple metrics are collected at a given timestamp:

timestampdevice_idcpu_1m_avgfree_memtemperaturelocation_iddev_type
2017-01-01 01:02:00abc12380500MB7242field
2017-01-01 01:02:23def45690400MB6442roof
2017-01-01 01:02:30ghi7891200MB5677roof
2017-01-01 01:03:12abc12380500MB7242field
2017-01-01 01:03:35def45695350MB6442roof
2017-01-01 01:03:42ghi789100100MB5677roof

Here, each row is a new reading, with a set of measurements and metadata at a given time. This allows us to preserve relationships within the data, and ask more interesting or exploratory questions than before.

Of course, this is not a new format: it's what one would commonly find within a relational database.

JOINs with relational data

TimescaleDB's data model also has another similarity with relational databases: it supports JOINs. Specifically, one can store additional metadata in a secondary table, and then utilize that data at query time.

In our example, one could have a separate locations table, mapping location_id to additional metadata for that location. For example:

location_idnamelatitudelongitudezip_coderegion
42Grand Central Terminal40.7527° N73.9772° W10017NYC
77Lobby 742.3593° N71.0935° W02139Massachusetts

Then at query time, by joining our two tables, one could ask questions like: what is the average free_mem of our devices in zip_code 10017?

Without joins, one would need to denormalize their data and store all metadata with each measurement row. This creates data bloat, and makes data management more difficult.

With joins, one can store metadata independently, and update mappings more easily.

For example, if we wanted to update our "region" for location_id 77 (e.g., from "Massachusetts" to "Boston"), we can make this change without having to go back and overwrite historical data.

Found an issue on this page?

Report an issue!

Keywords

Related Content