You created a TimescaleDB continuous aggregate for hourly metrics, pointed the dashboard at it, and it's fast. But the last few hours are always missing, or the numbers for today are stuck at whatever they were this morning.
Continuous aggregates aren't broken. They materialize data only up to a watermark, and since TimescaleDB 2.13 they don't fill in the rest on the fly by default.
How continuous aggregates work
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
device_id,
avg(value) AS avg_value,
max(value) AS max_value
FROM metrics
GROUP BY bucket, device_id;
A continuous aggregate is a materialized view over a hypertable that is refreshed incrementally: only buckets whose raw data changed are recomputed. It stores results up to a materialization watermark. What happens to newer data depends on one setting:
materialized_only = true, the default since TimescaleDB 2.13: queries return only materialized buckets. Anything newer than the watermark is invisible until the next refresh.materialized_only = false(real-time aggregation, the default before 2.13): queries combine materialized buckets with an on-the-fly aggregation of raw rows newer than the watermark.
So an aggregate created on 2.13 or later shows "stale" data by design, and one created earlier behaves differently. That explains why the same SQL gives different results on two servers.