Create a TimescaleDB continuous aggregate with an incremental refresh policy and enable real-time aggregation for sub-interval queries

Steps

1. Create a continuous aggregate as a materialized view over the hypertable: CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, device_id, avg(value) AS avg_val FROM metrics GROUP BY bucket, device_id
2. Add an automatic refresh policy to keep the aggregate current: SELECT add_continuous_aggregate_policy('hourly_metrics', start_offset => INTERVAL '3 hours', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour')
3. The start_offset/end_offset define the refresh window relative to now(); end_offset should be at least one bucket width to avoid refreshing the still-writing current bucket repeatedly
4. Enable real-time aggregation (default on since TimescaleDB 2.x) so queries against the continuous aggregate automatically union the materialized data with uncompressed live data: the view behaves as if fully up-to-date without waiting for the refresh job
5. Query the continuous aggregate like a regular table: SELECT bucket, avg_val FROM hourly_metrics WHERE device_id = 'dev1' AND bucket > now() - INTERVAL '24 hours'
6. Monitor refresh job health: SELECT * FROM timescaledb_information.job_stats WHERE job_id = (SELECT job_id FROM timescaledb_information.continuous_aggregates WHERE view_name = 'hourly_metrics')