2

I have a lot of sensors (around 1000) and want to save their values. The sensors will mostly work around a sampling rate of 10Hz.

Regular queries will be used for a live-view application. It can be selected which sensors to plot (around 1-10 sensors at a time) and the time range (15min, 30min,...). The plot will be updated in an Interval like 10 seconds, possibly accessed by multiple users.

Furthermore, for the future the sensor values will be queried for like a whole day, week, month. But these queries are not very common and don't need to be super fast.

The sensors will generate about 36,000,000 readings per hour. The idea was to keep around 1 week worth of readings in "hot storage". Older readings will be archived and compressed.

The environment is PostgreSQL with TimescaleDB integration.

I have now 2 concepts that will differ vastly from one another.

Single-Table or a distinct table for each sensor.

Single table: These 1000 sensors are saved in a single table with timestamp, value, and 2 foreign keys to identify them. Timescale does great on getting a time range but I will still have to filter the dataset for the wanted entries (sensor-id).

Multi table: Creating a table for each sensor. So I don't need foreign keys (just timestamp and value) and if I query just a subset of sensors, I only need to find the wanted range per sensor in a dedicated table.

I did some performance tests between the two ideas and the result was not that far apart from one another to conclude something. Inserting for example was faster on the multitable approach, however while selecting values a definitive answer couldn't be found.

However I don't have enough database experience to decide which of the two designs holds up better in the long run.

Thank you in advance for any help.

Improve this question

1 Answer 1

Reset to default
3

you might want to take a look at how zabbix handles this. Zabbix is a monitoring app that exactly does what you want and works nice with postgres combined with timescaleDB. It's not exactly the answer to your question but maybe even more than that.

Zabbix uses a single table per data type and retention period. The history* tables keep all measurements and the trends* tables keep the measurements aggregated to a min/max/avg per hour per sensor.

so, a history_uint, history_str etc. A very simple but effective application with a solid data model.zabbix-time-series-data-and-timescaledb

an other thing that popped up in my mind is: do you really need all those measurements or do you want to be able too respond quickly on changes. Also here zabbix can help by filtering duplicate values with a heartbeat. With a heartbeat of say 1 hour it stores at least once an hour but as soon as a value changes, it is stored immediately. A zabbix proxy can do this to take some of the load from the server that does the central work like storing in the database and reporting.

But it's always free to invent a better coloured wheel.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.