0

I'm working on a project where experimental data is collected from a bunch of sensors. There are several ways the data is gathered and used:

  • Some metrics are collected on regular basis. For instance, electrical consumption may be saved once per hour. If the value is the same, it is still getting saved in the database.

    Those metrics are then compared to see discrepancies and anomalous patterns, such as an extremely high or low usage compared to other days/hours, as well as studied for trends, for instance to see that there is a steady 10% increase of power usage every month.

  • Some other metrics are collected when they actually change. For instance, pressure change would be recorded, but if the pressure doesn't change for a few hours, there are no records.

    Those metrics are also used to spot problems, as well as for the alerts (i.e. value reaching a threshold). Later on, it may be seen in an aggregated form: for instance, one can be interested to see that between 2 a.m. and 4 a.m., the pressure was between 3.11 and 3.42 bar, with an average of 3.19 bar.

  • Some other metrics are purely binary. For instance, a valve is either open or closed. The database records the change in the value.

    Those metrics can be compared to the other metrics to understand why something happened, or how the system behaved in a given set of circumstances (for instance was the safety valve opened when the pressure reached a specified threshold).

  • Finally, some metrics indicate an event, such as a human intervention intended to change something in the system. For instance, an event may indicate that a pipe was replaced, or that someone modified the configuration of the system.

    Same as the previous type, the goal here is to understand how the system was influenced. For instance, when displaying the pressure chart for the last 24 hours, the GUI would indicate that there was a maintenance performed on valve 6 at 10:26 a.m., and that the threshold was modified from 4 bar to 3.5 bar at 5:01 p.m.

Currently, the data is stored in PostgreSQL. The set is very small, with about 20 MB of data arriving every month (which gives less than 500 MB of historical records), and it all works very well. There are, however, two problems:

  • There is a plan to increase the number of sensors, take more precise measurements, and to raise the frequency used to collect the data, expecting to see fifty to one hundred times the current data usage, ideally without buying new hardware.

  • It is quite challenging to work with a relational model to get the sort of information one needs to display the charts. Very often, SQL requests end up querying more information than they need, and very often, aggregations are slow. The overall structure is way too complex.

Is there a better suited database for this sort of scenarios?

I mean, with all the different types of databases ranging from MongoDB to Elasticsearch, there is probably some better alternative which is made specifically to store data from sensors and keep track of a bunch of events. I imagine that the very same type of database could be used by system administrators to keep track of the servers (storing their CPU load and memory usage, and also saving the list of maintenance events and other things like that), but I don't know what are the popular tools for this task among system administrators either.

Requirements:

  • Compatible with Linux.
  • Open source.
  • Relatively popular, that is, it would be possible to find a programmer who have already worked with it.
  • Non-cloud, i.e. can be hosted locally.

2 Answers 2

2

What about RRDtool? github

What RRDtool does

RRDtool is the OpenSource industry standard, high performance data logging and graphing system for time series data. RRDtool can be easily integrated in shell scripts, perl, python, ruby, lua or tcl applications.

It's fairly old-school, and well-known among sysadmins, but it is not really an SQL database.

1

PostgreSQL

which gives less than 500 MB of historical records (...) expecting to see fifty to one hundred times the current data usage, ideally without buying new hardware

500 MB is a tiny database, I would not expect PostgreSQL to be a limitation here

It is quite challenging to work with a relational model to get the sort of information one needs to display the charts. Very often, SQL requests end up querying more information than they need, and very often, aggregations are slow. The overall structure is way too complex.

Overall it sounds like a limitation of database design and written code, not of an underlying engine, that will not be magically changed by changing database engine.

Personally I would focus on refactoring/fixing code using it and doing things like enabling indexes and rewriting queries to stop pulling data that will be thrown out.

You can try changing to SQLite or something, but from examples you have given I would discourage MongoDB (for years unsecure by default, they lied about passing Jepsen tests).

Similarly second mention, the Elasticsearch security features are disabled when you have a basic or trial license ( https://www.elastic.co/guide/en/elasticsearch/reference/current/security-settings.html ).

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.