About continuous aggregates

Time-series data usually grows very quickly. And that means that aggregating the data into useful summaries can become very slow. Continuous aggregates makes aggregating data lightning fast.

If you are collecting data very frequently, you might want to aggregate your data into minutes or hours instead. For example, if you have a table of temperature readings taken every second, you can find the average temperature for each hour. Every time you run this query, the database needs to scan the entire table and recalculate the average every time.

Continuous aggregates are a kind of hypertable that is refreshed automatically in the background as new data is added, or old data is modified. Changes to your dataset are tracked, and the hypertable behind the continuous aggregate is automatically updated in the background.

You don't need to manually refresh your continuous aggregates, they are continuously and incrementally updated in the background. Continuous aggregates also have a much lower maintenance burden than regular PostgreSQL materialized views, because the whole view is not created from scratch on each refresh. This means that you can get on with working your data instead of maintaining your database.

Because continuous aggregates are based on hypertables, you can query them in exactly the same way as your other tables, and enable compression or tiered storage on your continuous aggregates. You can even create continuous aggregates on top of your continuous aggregates.

By default, querying continuous aggregates provides you with real-time data. Pre-aggregated data from the materialized view is combined with recent data that hasn't been aggregated yet. This gives you up-to-date results on every query.

Types of aggregation

There are three main ways to make aggregation easier: materialized views, continuous aggregates, and real time aggregates.

Materialized views are a standard PostgreSQL function. They are used to cache the result of a complex query so that you can reuse it later on. Materialized views do not update regularly, although you can manually refresh them as required.

Continuous aggregates are a Timescale only feature. They work in a similar way to a materialized view, but they are updated automatically in the background, as new data is added to your database. Continuous aggregates are updated continuously and incrementally, which means they are less resource intensive to maintain than materialized views. Continuous aggregates are based on hypertables, and you can query them in the same way as you do your other tables.

Real time aggregates are a Timescale only feature. They are the same as continuous aggregates, but they add the most recent raw data to the previously aggregated data to provide accurate and up to date results, without needing to aggregate data as it is being written.

Continuous aggregates on continuous aggregates

You can create a continuous aggregate on top of another continuous aggregate. This allows you to summarize data at different granularities. For example, you might have a raw hypertable that contains second-by-second data. Create a continuous aggregate on the hypertable to calculate hourly data. To calculate daily data, create a continuous aggregate on top of your hourly continuous aggregate.

For more information, see the documentation about continuous aggregates on continuous aggregates.

Continuous aggregates with a `JOIN` clause

Continuous aggregates supports the following JOIN features:

Feature	TimescaleDB < 2.10.x	TimescaleDB <= 2.15.x	TimescaleDB >= 2.16.x
INNER JOIN	❌	✅	✅
LEFT JOIN	❌	❌	✅
LATERAL JOIN	❌	❌	✅
Joins between ONE hypertable and ONE standard PostgreSQL table	❌	✅	✅
Joins between ONE hypertable and MANY standard PostgreSQL tables	❌	❌	✅
Join conditions must be equality conditions, and there can only be ONE `JOIN` condition	❌	✅	✅
Any join conditions	❌	❌	✅

JOINS in TimescaleDB must that meet the following conditions:

Only changes to the hypertable are tracked, they are updated in the continuous aggregate when it is refreshed. Changes to standard PostgreSQL table are not tracked.
You can use an INNER, LEFT and LATERAL joins, no other join type is supported.
Joins on the materialized hypertable of a continuous aggregate are not supported.
Hierarchical continuous aggregates can be created on top of a continuous aggregate with a JOIN clause, but cannot themselves have a JOIN clauses.

JOIN examples

Given the following schema:


CREATE TABLE locations (
    id TEXT PRIMARY KEY,
    name TEXT
);

CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    location_id TEXT,
    name TEXT
);

CREATE TABLE conditions (
    "time" TIMESTAMPTZ,
    device_id INTEGER,
    temperature FLOAT8
);

SELECT create_hypertable('conditions', by_range('time'));

See the following JOIN examples on Continuous Aggregates:

INNER JOIN on a single equality condition, using the ON clause:


CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
FROM conditions
JOIN devices ON devices.id = conditions.device_id
GROUP BY bucket, devices.name
WITH NO DATA;

INNER JOIN on a single equality condition, using the ON clause, with a further condition added in the WHERE clause:


CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
FROM conditions
JOIN devices ON devices.id = conditions.device_id
WHERE devices.location_id = 'location123'
GROUP BY bucket, devices.name
WITH NO DATA;

INNER JOIN on a single equality condition specified in WHERE clause:


CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
FROM conditions, devices
WHERE devices.id = conditions.device_id
GROUP BY bucket, devices.name
WITH NO DATA;

INNER JOIN on multiple equality conditions:


CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
FROM conditions
JOIN devices ON devices.id = conditions.device_id AND devices.location_id = 'location123'
GROUP BY bucket, devices.name
WITH NO DATA;