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.
