Query relational database and use results to query InfluxDB


I am using Grafana with InfluxDB. i batch measurements resulting in a typical write:
measurement,substation=substation_name,component=transformer,component_name=T1,quantity=voltage,type=magnitude,<other tags here> a=123,b=124,c=125
measurement,substation=substation_name,component=transformer,component_name=T2,quantity=voltage,type=magnitude,<other tags here> a=111,b=112,c=113

This works fine but the batched messages result in a very large bandwidth. A batch contains hundreds/thousand of lines and it is sent every second. The tags are all constant, only the value changes.
At the moment, In Grafana, the user selects a certain tags from pop up menus populated via template variables. This results in a typical query to InfluxDB:
SELECT mean(a), mean(b), mean(c) FROM "$RP"."measurement" WHERE ("substation" =~ /^$sub_name$/ AND "component"='transformer' AND "quantity"='voltage' AND "type" = 'magnitude') AND $timeFilter GROUP BY time($__interval),component_name

My idea is to leverage on the fact that tags are constant and I am thinking of associating a UUID to a specific set of tags so that the write results in a much smaller message
measurement,UUID=xyz1 a=123,b=124,c=125
measurement,UUID=xyz2 a=111,b=112,c=113

I am thinking of using template variables in Grafana to query a PostgreSQL database in which I map
substation=substation_name,component=transformer,component_name=T1,quantity=voltage,type=magnitude,<other tags here> ==> UUID=xyz1 .No idea how to do the PostgreSQL side yet, but I guess it is feasible.

The UUID(s) assigned to the template variables is(are) then used to query the InfluxDB to get the proper values to plot.

  1. Is there a better way to achieve what I am trying to do? My way seems a bit complex to setup and to maintain.

  2. If this is the way to go, is PostgreSQL the database to use?

  3. Am I overestimating the possibility of using the results of a query to a relational database to query a time series database?

Thanks for reading!

IMHO it is over-engineered solution. Did you try to use compression?

Hello. Thanks for the suggestion. I implemented compression in my client and achieved 93% bandwidth savings. Thanks!

1 Like