TimescaleDB’s real-time aggregation feature, often called "live query rollups," doesn’t actually recompute aggregates in real-time; instead, it materializes the results of an aggregation query and keeps that materialized view updated incrementally as new data arrives.
Let’s see it in action. Imagine you have a table sensor_data storing temperature readings from various devices.
CREATE TABLE sensor_data (
time TIMESTAMPTZ NOT NULL,
device_id INT NOT NULL,
temperature DOUBLE PRECISION
);
SELECT create_hypertable('sensor_data', 'time');
Now, we want to track the average temperature per device every minute, and we want this to be fast and always up-to-date.
CREATE MATERIALIZED VIEW sensor_avg_temp
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 minute', time) AS bucket,
device_id,
avg(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, device_id;
SELECT add_continuous_aggregate_policy('sensor_avg_temp',
start_offset => INTERVAL '1 hour',
end_offset => INTERVAL '5 minutes',
schedule_interval => INTERVAL '1 minute');
When you query sensor_avg_temp, you get results that look like a pre-computed summary. But the magic is in the timescaledb.continuous option and the continuous_aggregate_policy.
Here’s what happens under the hood: TimescaleDB, upon receiving new data in sensor_data, doesn’t just store it. It also triggers background processes that incrementally update the sensor_avg_temp materialized view. If a new data point arrives that falls into a 1 minute bucket that’s already been processed, TimescaleDB doesn’t re-calculate the entire bucket’s average. Instead, it uses an efficient algorithm to update the existing average based on the new value and the previous count and sum for that bucket. This is significantly faster than re-running the avg() function over potentially millions of rows.
The continuous_aggregate_policy defines how far back and how far forward TimescaleDB should maintain the materialized view. start_offset => INTERVAL '1 hour' means it will ensure the view is up-to-date for data at least one hour in the past relative to the current time. end_offset => INTERVAL '5 minutes' means it will process data up to 5 minutes into the future, which is useful if you have slightly delayed data. schedule_interval => INTERVAL '1 minute' tells TimescaleDB to check for and process updates every minute.
The key benefit here is performance. Querying sensor_avg_temp is orders of magnitude faster than running the equivalent aggregation query directly on sensor_data, especially as sensor_data grows. This is because the aggregation is already done. The "live" aspect comes from the background jobs that constantly refine the materialized results without you needing to manually refresh anything.
The specific aggregation functions supported by continuous aggregates are limited to those that can be computed incrementally. This includes functions like avg(), sum(), count(), min(), max(), stddev_pop(), stddev_samp(), var_pop(), and var_samp(). For more complex or unsupported aggregations, you might need to use a different approach, such as scheduled REFRESH MATERIALIZED VIEW commands, but those wouldn’t be "live."
When you have a continuous aggregate, TimescaleDB will automatically create background "chunks" for the materialized view, mirroring the chunking strategy of the hypertable it’s based on. This allows the incremental updates to be highly efficient, as only relevant chunks of the materialized view need to be modified when new data arrives in the corresponding chunks of the source hypertable.
The most surprising thing about continuous aggregates is that they don’t actually store the raw data; they only store the aggregated results. This means that if you query sensor_avg_temp for a specific bucket and device_id, you get the pre-calculated average for that specific time interval and device, and the system is designed to update that pre-calculated value with minimal overhead.
The next step to explore is how to handle multi-level aggregations or aggregations that aren’t directly supported by continuous aggregates, potentially involving UNION ALL operations between different continuous aggregates.