Downsampling Timescale Data with Continuous Aggregations

Published on:

Timescale v1.3 was released recently and the major feature is continuous aggregations. The main use case for these aggregations is downsampling data: which has been brought up several times before, so it’s great that these issues have been addressed.

Downsampling data allows queries spanning a long time interval to complete in a reasonable time frame. I wrote and maintain OhmGraphite, which I have configured to send hardware sensor data to a Timescale instance every couple seconds. Querying one sensor over the course of a week will cause the db to scan over 600k values. This query will take 10 or so seconds to run on penny pinched hardware. A single grafana dashboard can be composed of dozens of queries, which will compound performance degradations. If one could downsample the data from a resolution of 2 seconds to 5 minutes, the db would scan 150x fewer values.

This is why I’m so excited about the new continuous aggregations feature. It’s starting to get to the point where Timescale is checking all the boxes for me. I’ve compared it against graphite before and not downsampling was a caveat preventing me from investing more time with Timescale. To be clear, one could have approximated downsampling prior to this release, by implementing multi-table approach + a system timer to compute aggregations. But in my opinion, downsampling should be a native feature of any time series database.

There are caveats with continuous aggregations but they are documented and we’ll explore them here as well. Here I hope to outline how I’ve already started using them.

So OhmGraphite writes to a table structure like so

CREATE TABLE IF NOT EXISTS ohm_stats (
   time TIMESTAMPTZ NOT NULL,
   host TEXT,
   hardware TEXT,
   hardware_type TEXT,
   identifier TEXT,
   sensor TEXT,
   sensor_type TEXT,
   sensor_index INT,
   value REAL
);

SELECT create_hypertable('ohm_stats', 'time', if_not_exists => TRUE);

Basically it’s time + sensor info + sensor value. Here is how we downsample to 5 minute intervals without losing sensor granularity.

CREATE VIEW ohm_stats_5m
WITH (timescaledb.continuous)
AS SELECT
  time_bucket('5m', time) as time,
  host, hardware, hardware_type, identifier, sensor, sensor_type, sensor_index,
  avg(value) as value_avg,
  max(value) as value_max,
  min(value) as value_min
FROM ohm_stats
GROUP BY 1, host, hardware, hardware_type, identifier, sensor, sensor_type, sensor_index;

Some notes:

The one area where we have to do some heavy lifting is our queries should be rewritten on the client side to utilize the continuous aggregations when a “large” time range is requested. A large time range would be when each data point is 5 or more minutes apart. In grafana, I changed CPU load calculation from:

SELECT
  $__timeGroupAlias("time", $__interval),
  max(value)
FROM ohm_stats
WHERE
  $__timeFilter("time") AND
  hardware_type = 'CPU' AND
  sensor_type = 'Load' AND
  sensor LIKE '%Total%'
GROUP BY 1
ORDER BY 1

to

(SELECT
  $__timeGroupAlias("time", $__interval),
  max(value)
FROM ohm_stats
WHERE
  $__timeFilter("time") AND
  hardware_type = 'CPU' AND
  sensor_type = 'Load' AND
  sensor LIKE '%Total%' AND
  '$__interval' < '5m'::interval
GROUP BY 1
ORDER BY 1)

UNION ALL

SELECT
  $__timeGroupAlias("time", $__interval),
  max(value_max)
FROM ohm_stats_5m
WHERE
  $__timeFilter("time") AND
  hardware_type = 'CPU' AND
  sensor_type = 'Load' AND
  sensor LIKE '%Total%' AND
  '$__interval' >= '5m'::interval
GROUP BY 1
ORDER BY 1

Some notes:

('$__interval' >= '5m'::interval OR
  (NOW() - $__timeFrom()) > '7 days'::interval)

Conclusion

Positives:

More work is needed to make continuous aggregations a clear win-win

Cons:

Result: I will continue porting all timescale queries to continuous aggregations. The feature isn’t perfect but I only expect it to get better with time.

Comments

If you'd like to leave a comment, please email [email protected]