Hi everyone! Reached out here for a question I have regarding continuous aggregates.
We have a platform where devices report events to us semi-frequently (minutes, not seconds), and want to have an efficient way of finding out whenever a device has stopped posting events to us.
What we really want to is to keep an aggregate of device_id and the latest created_at of events that devices post. I was looking into using continuous aggregates for this, but the requirement of having a time_bucket is somewhat confusing me. We pretty much only want to track the max value of created_at at all times, so basically a time_bucket with infinite size.
I was going to try out having a really large time_bucket, e.g. 100 years to sort of mimic infinty. I was going to try out my solution, but figured I’d post here as well to see if anyone has experience using large time_buckets, or know of any consequences of this that would make it unsuitable.
I not been able to find any information on when the actual materialization of the continuous aggregate happens either. If it’s only materialized when reaching the time_bucket interval, then this immediately disqualifies my solution.
If anyone has more information I’d be really grateful!
Thanks in advance.
Edit: The view I am trying to create is this:
CREATE MATERIALIZED VIEW device_events_last_seen
WITH (timescaledb.continuous) AS
SELECT
device_id,
max(created_at) AS last_seen,
time_bucket(INTERVAL '100 year', created_at) AS century
FROM
device_events
GROUP BY
device_id, century;