Data model

As a relational database supporting full SQL, TimescaleDB supports flexible data models that can be optimized for different use cases. This makes TimescaleDB somewhat different from most other time-series databases, which typically use "narrow-table" models.

Specifically, TimescaleDB can support both wide-table and narrow-table models. Here, we discuss the different performance trade-offs and implications of these two models using an Internet of Things (IoT) example.

Imagine a distributed group of 1,000 IoT devices designed to collect environmental data at various intervals. This data could include:

  • Identifiers: device_id, timestamp
  • Metadata: location_id, dev_type, firmware_version, customer_id
  • Device metrics: cpu_1m_avg, free_mem, used_mem, net_rssi, net_loss, battery
  • Sensor metrics: temperature, humidity, pressure, CO, NO2, PM10

For example, your incoming data may look like this:

2017-01-01 01:02:00abc12380500MB72335field
2017-01-01 01:02:23def45690400MB64335roof
2017-01-01 01:02:30ghi7891200MB5677roof
2017-01-01 01:03:12abc12380500MB72335field
2017-01-01 01:03:35def45695350MB64335roof
2017-01-01 01:03:42ghi789100100MB5677roof

Now, let's look at various ways to model this data.