Continuous Aggregates Reordering

TimescaleDB and PostgreSQL
1

Hi, I’m planning to create TimescaleDB table for monitoring purposes, and my source is getting the monitoring data (e.g., CPU, Memory data) every minute. I would like to continuously aggregate this table to let’s say, 10 minutes aggregate. I also want to reorder this continuous aggregate view since my query pattern would be filtering based on the application name of the servers based on some aggregate, since showing 1 minute data for a few days seems to be too much. However, the only thing I find from the doc is just reordering the hypertable itself.

So, is it possible to reorder continuous aggregate? Or perhaps do we need to reorder the base hypertable first, then our continuous aggregate will have the same order as our reordered hypertable? Or are there any other way to achieve this?

2

Hi @Ivan_Hosea, I don’t think you need to reorder but you can also be explicit about the order.

The continuous aggregates key is the time_bucket group by clause. You can also add more details like order by later if you want. But the basic order is time and you can set extra ordering after group.

Can you elaborate a bit more on your use-case?