Thoughts on time series database design

[ICHydro]

Here are some pointers that may be of use for the database design:

• Thingsboard is an IoT platform that handles large sensor time series, and supports different database options, including PostgreSQL as the default option. I have not looked in detail at the database schema but it seems to use a table per time series. At first thought this would seem a suitable option for us as well, as most operations on time series will do so on a single time series. Few need to pull multiple time series together.

• Thingsboard also supports TimescaleDB and Cassandra. This is probably not needed for us on the short term, but staying close to the Thingsboard schema may help adopting these solutions on the longer term if we would choose to do so.

[ICHydro]

Here are the Thingsboard database schemas.

[dalonsoa]

Sorry for missing this. For some reason, I do not receive notifications of new discussions, only issues.

I'll look into this, but just as a quick comment, since Dan took over the project, Paricia has been using TimescaleDB as backend with one table per day (the default in TimescaleDB+Django, but can be changed to a different thing).

I'm not entirely sure what you mean one table per time series. Let's say we have a station uploading data for the precipitation for two months. Is this block of data a time series or does it refer to something else entirely different?

[ICHydro]

A time series is all the data of a single sensor (for example precipitation) on a station. That is a 1-dimensional array varying over time. So a block of 2 months of precipitation data is part of the time series for that sensor, but the time series can be longer than just that block: a use can upload 2 months of data for that station, but later on another block of data. If so, they will become part of the same time series.

[dalonsoa]

We also need to keep in mind that as we have moved away from direct SQL calls, but we use higher level Python, we might need to worry more about the performance of operations in Python - using pandas dataframes - than the operations in the database as we will not be doing much beyond querying data out of it.