CODEWITHSUNDEEP
postgresqltimescaledb
Ahmed Etefy
Ahmed Etefy

Reputation: 75

Timescale Continuous Aggregates on all time

I wanted to ask if continuous aggregate is the the most optimal solution if I want to get aggregated values from all time, not time bucketed in particular Here is the query I drafted

 CREATE VIEW device_summary
WITH (timescaledb.continuous)
    AS
    SELECT
      time_bucket('1 day', module_timestamp) as bucket,
      sum(CAST(component_data->'common'->'bat' AS INT)) as bat_sum
    FROM
      main_componentmessage
    GROUP BY bucket;

I guess one solution would be to increase the time bucket to a really high number (lets say 999999 weeks to include "beginning of time"), but then again I am not sure that would work as it would be no different than a normal aggregate query as the time bucket refresh interval will be really high, and that defeats the purpose. I might as well just do a sum query on all records

Second solution would be to create sum on top of one day bucketed queries so I can get the total sum but I am not sure if it makes sense as well

SELECT SUM(bat_sum) from device_summary

Any suggestions on how to go about this?

Upvotes: 1

Views: 2079

Answers (1)

Mats Kindahl
Mats Kindahl

Reputation: 2075

Internally, the continuous aggregates in TimescaleDB create a materialization table with partial aggregates for each bucket. These are then updated on a regular basis as new data is added to the raw table.

As a result, having a very wide bucket is unlikely to give you any performance boosts since, as you say, it is the same as a normal aggregate query.

If you have smaller bucket and then compute the sum of all those, you might get a performance improvement since older buckets are not recomputed and instead as new data comes in the partial aggregates for the new data is computed.

Upvotes: 2

Related Questions