title

excerpt

products

keywords

About continuous aggregates

A TimescaleDB continuous aggregate combines your data into analytic summaries and is refreshed in the background when new data is added. Learn how continuous aggregates work and how to use them

cloud

mst

self_hosted

continuous aggregates

import CaggsFunctionSupport from "versionContent/_partials/_caggs-function-support.mdx"; import CaggsIntro from "versionContent/_partials/_caggs-intro.mdx"; import CaggsTypes from "versionContent/_partials/_caggs-types.mdx"; import CreateHypertablePolicyNote from "versionContent/_partials/_create-hypertable-columnstore-policy-note.mdx";

About continuous aggregates

Types of aggregation

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 granularity. 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 support 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 $PG table	❌	✅	✅
Joins between ONE hypertable and MANY standard $PG tables	❌	❌	✅
Join conditions must be equality conditions, and there can only be ONE `JOIN` condition	❌	✅	✅
Any join conditions	❌	❌	✅

JOINS in TimescaleDB must meet the following conditions:

Only the changes to the hypertable are tracked, and they are updated in the continuous aggregate when it is refreshed. Changes to standard $PG 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 clause.

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
) WITH (
  tsdb.hypertable
);

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;