Confused about MQTT > InfluxDB

One of the things I found initially annoying about InfluxDB (apart from how difficult it is to type correctly!) was the similarity of the syntax to SQL. But it really isn't SQL, the similarities make it much harder to get your head around if you are used to SQL - well, that's my personal experience anyway.

Honestly, I still trip over the syntax and I do everything I can to avoid actually writing the stuff, that's where Grafana comes in :slight_smile:

4 Likes

I concur, I would love to know why the Influx people decided to depart from the relative simplicity of the SQL syntax, a syntax that was being used by a huge user base, for an outback version of the language.

1 Like

So, just to be clear, there's nothing wrong with doing things as per my original example? It's important I get this right from the start; I want to keep an unbroken history of what's been happening from the day the system is commissioned...

1 Like

While I am not a professional InfluxDB consultant, my own assessment is that your approach is fine and it is the one I've been using for a few years without worrying about it.

Indeed, it would appear (though I've not explicitly tested) that if you reduce the precision of the timestamp, you are more likely to get timestamp/tag clashes but that InfluxDB seems to do the sensible thing in that case and it merges the data - that would appear to mean that you might not get as sparse a data-set as you think. It would be an interesting experiment to reduce the precision to 1 hr to see if that is actually correct :grinning:

2 Likes

I have been using InfluxDB since its early days. The storage engine they have developed (TSM) is very efficient in terms of disk usage. All sorts of interesting compression and diff algorithms involved.:nerd_face:

The lower the precision of the timestamps, the more efficient even. For most of my sensor readings I am using full seconds.

Another cool feature are these retention policies in combination with continuous queries. You probably won't need your data in 1s resolution for a longer timespan, say a year at max. So I would use a retention policy of 1 year for the raw data (1s) and downsample the data into a second set of measurements with a resolution of 1 hour or so with a continuous query. One could even think of multiple aggregation tiers using this principle. Influx takes care of cleaning up old data depending on your retention policy duration.

2 Likes

In short, yes. :grinning:

Here's the long answer to that:

Absolutely, I keep my 1 minute detail data for 7 days and aggregate to hourly which I keep indefinitely (I'll have to fix that one day).

Yes, I'd read that, I just haven't tested it :slight_smile:

1 Like