Time Series Database?

[TRS-80]

Inspired by this and the few posts following it, I set out yesterday looking into time series databases, something I have long wanted to add to my setup. The end goal being "nice graphs" in a sort of controller independent way. For the "nice graphs" part, I think Grafana is pretty clearly the answer, as it seems very nice project, that you can plug almost anything into. Which leaves the "(time-series) database back-end" part...

Particularly @BearWithBeard and @sundberg84 comments about the limitations of InfluxDB I found very relevant.

I know that InfluxDB gets recommended quite a lot, especially over at OpenHAB forums where I am also pretty active (off and on). But in looking into it, I detected a strong smell of "open core" and these reports from Bear and sundberg appear to corroborate this. Further (admittedly, slight) research led me to similar comments about large amounts of venture capital funding, one single commercial company behind it, etc. which personally add up to me as something to stay away from (regardless of current popularity and even features). This is just my personal approach, before investing a lot of my own valuable time into learning some new ecosystem; you may have a different calculation.

Anyway one alternative mentioned was Graphite, which is (like many of these) a modular thing where "Carbon" would be the actual database storage component. So I started looking into this some more, and as opposed to InfluxDB in particular, it appears to be a much more "open" project and community, with a number of various companies supporting it. Which is a much more positive sign, in my book, the two different models each leading (quite often) to a set of very predictable results (but not always) in my experience.

Anyway so then I thought the next step, since I have moved to MQTT gateway already for MySensors (as well as some other things) was maybe just to implement this at the MQTT level? Now in general, I prefer Unix philosophy, i.e., modular, one tool for one job sort of approach as much as possible, so separating these concerns appealed to me. I already have my MQTT broker (Mosquitto) running on a separate machine from my controller (OpenHAB) and this would move me even further into this "distributed architecture" experiment.

In case anyone is curious, I am running everything on a few SBC (Cubietruck + couple ODROID-XU4) running Armbian. But you could do the same "in containers" if that is more your cup of tea (I am more "old school" I guess, but who knows, maybe I am "doing it wrong").

So I started looking into tools for that approach, and had some success (after a bit of research). But I wanted to perhaps gather other options and discussion before proceeding any further, or sharing my findings thus far.

[Yveaux]

@TRS-80 I've been using the influxdb/grafana combo for around 5 years now and are mostly very happy with it. Back then there was less to choose from, so I didn't research influxdb.
All my home automation goes through mqtt, and a lot of data can be stored directly in influxdb. However, some data does need tweaking, so all is fed into node-red, formatted and stored in influxdb from there.

[TRS-80]

@Yveaux said in Time Series Database?:

I've been using the influxdb/grafana combo for around 5 years now and are mostly very happy with it.

Thanks for input. I have certainly heard a lot of people say this.

When you say "mostly" are / were there any gripes / pain points you have / had in particular?

@Yveaux said in Time Series Database?:

Back then there was less to choose from, so I didn't research influxdb.

Did you mean to say Graphite (/Carbon) here?

@Yveaux said in Time Series Database?:

All my home automation goes through mqtt, and a lot of data can be stored directly in influxdb. However, some data does need tweaking, so all is fed into node-red, formatted and stored in influxdb from there.

Yes, I discovered some other tools as well, which I plan on sharing later. Some for straight ingesting, and others to massage the data if needed. In a good and open ecosystem there should be a lot of different ways to skin the same cat so to speak. Options are good.

[TRS-80]

@Yveaux said in Time Series Database?:

fed into node-red, formatted and stored in influxdb from there.

I continue to think about this topic. Are you using Node Red as your primary "controller"? Or do you use some other controller, and then only use Node Red for data processing?

[Yveaux]

@TRS-80 node red functions as controller in my setup. I'm not using any other home automation controller.

[wergeld]

@TRS-80 I looked at Grafana and InfluxDB a few years ago and found them supremely limited. It may be that I am a MS SQL Server user but the act if trying to do an update/delete of a value in InfluxDB is painful. Running everything through a JSON call to modify data annoys me.
So, all that aside I prefer sending data for long term storage to an instance of SQL Server 2012 - even with 4 sensor nodes + weather queries and some other data that send info every 30 seconds, 5 minutes, 10 minutes, and 30 minutes for a few years. I am not even above 100 MB of storage used. The data types are Date, INT, CHAR(xxxx), and in one crazy case VARCHAR(1000). I use Domoticz's built in sqlLite and hassio's DB to handle the data view from my controllers (yes, I have 2). The split of data occurs in Node-Red where it gets the data from my MQTT broker and sends to controllers (different subscription topics due to C/F fubaring in hassio) and send to SQL Server for long term storage. Efficient storage types is the key so a timestamp as an INT/BIGINT would be nice. As long as all your values are INT as well that is even better. Timeseries DBs do have their use, I have just not found one I like.
If there is a timeseries DB that can be accessed via ANSI SQL that would be awesome.

Other thought - you could use Elasticsearch to send in values as "documents" and then run analysis on them. For dataviz, I use the built in ones in my two controllers and I have written my own to handle long term data analysis. I prefer Highcharts for doing the viz as that is what I use at work. It is clean, efficient, and fully customizable.