Continuous aggregate of a continuous aggregate

TimescaleDB and PostgreSQL
1

Hello!
I’m currently looking at trying to replicate the functionality that we’re used to with Ganglia using TimescaleDB and Telegraf.
We would like to keep raw host metrics for 30 days, 10 minute aggregates for 6 months and 60 minute aggregates for 3-5 years.
I have set up the CAGGs as follows:

CREATE MATERIALIZED VIEW cpu_10m WITH (timescaledb.continuous) AS 
SELECT tag_id, 
	time_bucket(INTERVAL '10 minutes', time) AS bucket, 
	AVG(usage_guest) AS usage_guest, 
	AVG(usage_guest_nice) AS usage_guest_nice, 
	AVG(usage_idle) AS usage_idle, 
	AVG(usage_iowait) AS usage_iowait, 
	AVG(usage_irq) AS usage_irq,
	AVG(usage_nice) AS usage_nice, 
	AVG(usage_softirq) AS usage_softirq, 
	AVG(usage_steal) AS usage_steal, 
	AVG(usage_system) AS usage_system, 
	AVG(usage_user) AS usage_user
FROM cpu 
GROUP BY tag_id, bucket;

SELECT add_continuous_aggregate_policy('cpu_10m',
									  start_offset => INTERVAL '6 months',
									  end_offset => INTERVAL '30 days',
									  schedule_interval => '1 day');

SELECT add_continuous_aggregate_policy('cpu_60m',
									  start_offset => INTERVAL '5 years',
									  end_offset => INTERVAL '6 months',
									  schedule_interval => '1 day');

for just average CPU metrics for example.
However, I think that this would require us to keep raw metrics for 6 months (well, actually 7 months) for the cpu_60m policy to work right? Is there a way I can do this to only keep raw metrics for 30 days at the maximum and have averages over 10 and 60 minutes otherwise please? Could this be done by making the cpu_60m CAGG select from cpu_10m?
Thank you,
Will.

1 Like
2

Hi @willfurnell, nested continuous aggregates is in progress, but you can use stats_aggs combined with a rollup and keep only the 10 minutes and reuse the pre-processed 10 minutes data.

3

Thank you!
So just to clarify would I want to do something like this for my 60m aggregate using the rollup function?
Or do I not create another materialized view at all?

CREATE MATERIALIZED VIEW cpu_60m WITH (timescaledb.continuous) AS 
SELECT tag_id, 
	time_bucket(INTERVAL '60 minutes', time) AS bucket, 
	AVG(rollup(usage_guest)) AS usage_guest, 
	AVG(rollup(usage_guest_nice)) AS usage_guest_nice, 
	AVG(rollup(usage_idle)) AS usage_idle, 
	AVG(rollup(usage_iowait)) AS usage_iowait, 
	AVG(rollup(usage_irq)) AS usage_irq,
	AVG(rollup(usage_nice)) AS usage_nice, 
	AVG(rollup(usage_softirq)) AS usage_softirq, 
	AVG(rollup(usage_steal)) AS usage_steal, 
	AVG(rollup(usage_system)) AS usage_system, 
	AVG(rollup(usage_user)) AS usage_user
FROM cpu_10m 
GROUP BY tag_id, bucket;

Thanks,
Will.