Db design sensor network

TimescaleDB and PostgreSQL
1

Hi,

We run a monitoring program where we have multiple sensors in different locations. The sensors in each location are logged and the data are stored in files. Each sensor might produce multiple variables of different types (bool, integer, float, …). So the logger file consists of a datetime column and several variable columns of different data type. Some loggers measure at 1 measurement/minute while other loggers sample at 20Hz.

Now, I am wondering if it would possible to store our data in a Timescaledb system. A proposed design, I read in earlier topics, consists of a table with sensor readings (columns: timestamp, sensor_id, sensor_value) and a table with sensor metadata (sensor_id, location, manufacturer, more metadata…). But as mentioned earlier, each sensor might measure different metrics (e.g. sensor 1: (pressure, temperature, sensor flag); sensor 2: (wind vector, inclination); sensor 3: etc. ). So, In order to capture this heterogeneity, you could add a column to the “sensor readings” table which specifies the metric plus extra tables for each data type. On one hand this solution would make querying of data and metadata easy. On the other hand it doesnt seem terribly efficient?

In fact it seems I just need unique column names which could be related to metadata over a given valid time range. But I guess that is not possible in a SQL framework?

Thanks!

1 Like
2

Hi @Alme

Thanks for explaining your use case. We at Timescale are building something that matches the solution you are looking for. It’s an automatic TimescaleDB for IoT use cases that includes: HTTP based ingest, automatic schema: creation, management, and TimescaleDB feature(s) enablement, and ease of querying using simple SQL. We are currently in the active development phase and expecting a beta release by end of Q1.

The solution will be Timescale Cloud only by the end of Q1 as a beta offering. If you’re interested in sharing more about your use case and/or discussing joining the early access program for the feature above, we can schedule a call to discuss (https://calendly.com/vineeth97/product-outreach) or, if you prefer, we can also continue the conversation over email at ([email protected]).

Best,
Vineeth
Product Manager at Timescale