> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Understand continuous aggregates

> Continuous aggregates automatically precompute and store aggregate data for lightning-fast queries on time-series data in TimescaleDB

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const PG = 'Postgres';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

In modern applications, data usually grows very quickly. This means that aggregating it into useful
summaries can become very slow. If you are collecting data very frequently, you might want to
aggregate your data into minutes or hours instead. For example, if an IoT device takes temperature
readings every second, you might want to 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.
{TIMESCALE_DB} makes aggregating data lightning fast, accurate, and easy with continuous
aggregates.

![Reduced data calls with continuous aggregates][reduced-data-calls-with-caggs]

Continuous aggregates in {TIMESCALE_DB} 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.

Continuous aggregates have a much lower maintenance burden than regular {PG} 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. This includes continuous aggregates in the rowstore, compressed into the
[columnstore][hypercore], or [tiered to object storage][data-tiering]. You can even create
[continuous aggregates on top of your continuous aggregates][hierarchical-caggs], for an even more
fine-tuned aggregation.

[Real-time aggregation][real-time-aggregates] enables you to combine pre-aggregated data from the
materialized view with the most recent raw data. This gives you up-to-date results on every query.

[data-tiering]: /manage-data/capabilities/data-tiering/data-tiering

[hierarchical-caggs]: /manage-data/capabilities/continuous-aggregates/hierarchical-continuous-aggregates

[hypercore]: /manage-data/capabilities/hypercore/understand-hypercore

[real-time-aggregates]: /manage-data/capabilities/continuous-aggregates/real-time-aggregates

[reduced-data-calls-with-caggs]: https://assets.timescale.com/docs/images/continuous-aggregate.png

## Types of aggregation

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

* [Materialized views][pg-materialized views] are a standard {PG} 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][caggs] are a {TIMESCALE_DB}-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][real-time-aggregates] are a {TIMESCALE_DB}-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.

[pg-materialized views]: https://www.postgresql.org/docs/current/rules-materializedviews.html

[caggs]: /manage-data/capabilities/continuous-aggregates/understand-continuous-aggregates

[real-time-aggregates]: /manage-data/capabilities/continuous-aggregates/real-time-aggregates

<Info>
  In {TIMESCALE_DB} v2.13 and later, real-time aggregates are DISABLED by default. In earlier versions, real-time
  aggregates are ENABLED by default; when you create a {CAGG}, queries to that view include the results from the most
  recent raw data.
</Info>

## 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 [hierarchical continuous aggregates][hierarchical-caggs].

## Continuous aggregates with JOINs

{CAGG_CAP}s 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 {TIMESCALE_DB} must meet the following conditions:

* Only the changes to the {HYPERTABLE} are tracked, and they are updated in the {CAGG} when it is
  refreshed. Changes to standard {PG} tables are not tracked.
* You can use `INNER`, `LEFT`, and `LATERAL` joins; no other join type is supported.
* Joins on the materialized {HYPERTABLE} of a {CAGG} are not supported.
* Hierarchical {CAGG}s can be created on top of a {CAGG} with a `JOIN` clause, but cannot themselves
  have a `JOIN` clause.

### JOIN examples

Given the following schema:

```sql theme={"dark"}
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 (
  timescaledb.hypertable
);
```

When you create a {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning
column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
[columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_after` in the
policy. This columnar format enables fast scanning and
aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the
{COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
`created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
[remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

[alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

[create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

See the following `JOIN` examples on {CAGG}s:

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

  ```sql theme={"dark"}
  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:

  ```sql theme={"dark"}
  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:

  ```sql theme={"dark"}
  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:

  ```sql theme={"dark"}
  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;
  ```

  {TIMESCALE_DB} v2.16.x and higher.

* `INNER JOIN` with a single equality condition specified in `WHERE` clause can be combined with
  further conditions in the `WHERE` clause:

  ```sql theme={"dark"}
  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
  AND devices.location_id = 'location123'
  GROUP BY bucket, devices.name
  WITH NO DATA;
  ```

  {TIMESCALE_DB} v2.16.x and higher.

* `INNER JOIN` between a {HYPERTABLE} and multiple {PG} tables:

  ```sql theme={"dark"}
  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name AS device, locations.name AS location,
    MIN(temperature), MAX(temperature)
  FROM conditions
  JOIN devices ON devices.id = conditions.device_id
  JOIN locations ON locations.id = devices.location_id
  GROUP BY bucket, devices.name, locations.name
  WITH NO DATA;
  ```

  {TIMESCALE_DB} v2.16.x and higher.

* `LEFT JOIN` between a {HYPERTABLE} and a {PG} table:

  ```sql theme={"dark"}
  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
  LEFT JOIN devices ON devices.id = conditions.device_id
  GROUP BY bucket, devices.name
  WITH NO DATA;
  ```

  {TIMESCALE_DB} v2.16.x and higher.

* `LATERAL JOIN` between a {HYPERTABLE} and a subquery:

  ```sql theme={"dark"}
  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,
  LATERAL (SELECT * FROM devices WHERE devices.id = conditions.device_id) AS devices
  GROUP BY bucket, devices.name
  WITH NO DATA;
  ```

  {TIMESCALE_DB} v2.16.x and higher.

## Function support

In {TIMESCALE_DB} v2.7 and later, {CAGG}s support all {PG} aggregate functions. This includes both
parallelizable aggregates, such as `SUM` and `AVG`, and non-parallelizable aggregates, such as `RANK`.

In {TIMESCALE_DB} v2.10.0 and later, the `FROM` clause supports `JOINs`, with some restrictions. For more
information, see the [JOIN support section](#continuous-aggregates-with-joins).

In older versions of {TIMESCALE_DB}, {CAGG}s only support [aggregate functions that can be parallelized by
{PG}][postgres-parallel-agg]. You can work around this by aggregating the other parts of your query in the
{CAGG}, then [using the window function to query the aggregate][cagg-window-functions].

The following table summarizes the aggregate functions supported in continuous aggregates:

| Function, clause, or feature                               | TimescaleDB 2.6 and earlier | TimescaleDB 2.7, 2.8, and 2.9 | TimescaleDB 2.10 and later |
| ---------------------------------------------------------- | --------------------------- | ----------------------------- | -------------------------- |
| Parallelizable aggregate functions                         | ✅                           | ✅                             | ✅                          |
| [Non-parallelizable SQL aggregates][postgres-parallel-agg] | ❌                           | ✅                             | ✅                          |
| `ORDER BY`                                                 | ❌                           | ✅                             | ✅                          |
| Ordered-set aggregates                                     | ❌                           | ✅                             | ✅                          |
| Hypothetical-set aggregates                                | ❌                           | ✅                             | ✅                          |
| `DISTINCT` in aggregate functions                          | ❌                           | ✅                             | ✅                          |
| `FILTER` in aggregate functions                            | ❌                           | ✅                             | ✅                          |
| `FROM` clause supports `JOINS`                             | ❌                           | ❌                             | ✅                          |

DISTINCT works in aggregate functions, not in the query definition. For example, for the table:

```sql theme={"dark"}
CREATE TABLE public.candle(
symbol_id uuid                     NOT NULL,
symbol    text                     NOT NULL,
"time"    timestamp with time zone NOT NULL,
open      double precision         NOT NULL,
high      double precision         NOT NULL,
low       double precision         NOT NULL,
close     double precision         NOT NULL,
volume    double precision         NOT NULL
);
```

* The following works:
  ```sql theme={"dark"}
  CREATE MATERIALIZED VIEW candles_start_end
  WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 hour', "time"), COUNT(DISTINCT symbol), first(time, time) as first_candle, last(time, time) as last_candle
  FROM candle
  GROUP BY 1;
  ```
* This does not:
  ```sql theme={"dark"}
  CREATE MATERIALIZED VIEW candles_start_end
  WITH (timescaledb.continuous) AS
  SELECT DISTINCT ON (symbol)
  symbol,symbol_id, first(time, time) as first_candle, last(time, time) as last_candle
  FROM candle
  GROUP BY symbol_id;
  ```

[postgres-parallel-agg]: https://www.postgresql.org/docs/current/parallel-plans.html#PARALLEL-AGGREGATION

If you want the old behavior in later versions of {TIMESCALE_DB}, set the `timescaledb.finalized`
parameter to `false` when you create your {CAGG}.

## Components of a continuous aggregate

{CAGG_CAP}s consist of:

* **Materialization hypertable** to store the aggregated data
* **Materialization engine** to aggregate data from the raw, underlying table to the
  materialization {HYPERTABLE}
* **Invalidation engine** to determine when data needs to be re-materialized due to changes in the
  data
* **Query engine** to access the aggregated data

### Materialization hypertable

{CAGG_CAP}s take raw data from the original {HYPERTABLE}, aggregate it, and store the aggregated data in a
materialization {HYPERTABLE}. When you query the continuous aggregate view,
the aggregated data is returned to you as needed.

Using a temperature example, the materialization table looks like this:

| day        | location  | chunk | avg temperature |
| ---------- | --------- | ----- | --------------- |
| 2021/01/01 | New York  | 1     | 73              |
| 2021/01/01 | Stockholm | 1     | 70              |
| 2021/01/02 | New York  | 2     |                 |
| 2021/01/02 | Stockholm | 2     | 69              |

The materialization table is stored as a {TIMESCALE_DB} {HYPERTABLE}, to take advantage of the
scaling and query optimizations that {HYPERTABLE}s offer. Materialization tables contain a column
for each group-by clause in the query, and an `aggregate` column for each aggregate in the query.

For more information, see [materialization hypertables][cagg-mat-hypertables].

### Materialization engine

The materialization engine performs two transactions. The first transaction blocks all INSERTs,
UPDATEs, and DELETEs, determines the time range to materialize, and updates the invalidation
threshold. The second transaction unblocks other transactions, and materializes the aggregates. The
first transaction is very quick, and most of the work happens during the second transaction, to
ensure that the work does not interfere with other operations.

### Invalidation engine

Any change to the data in a {HYPERTABLE} could potentially invalidate some materialized rows. The
invalidation engine checks to ensure that the system does not become swamped with invalidations.

Fortunately, time-series data means that nearly all INSERTs and UPDATEs have a recent timestamp, so the
invalidation engine does not materialize all the data, but to a set point in time called the
materialization threshold. This threshold is set so that the vast majority of INSERTs contain more
recent timestamps. These data points have never been materialized by the continuous aggregate, so
there is no additional work needed to notify the continuous aggregate that they have been added.

When the materializer next runs, it is responsible for determining how much new data can be
materialized without invalidating the continuous aggregate. It then materializes the more recent
data and moves the materialization threshold forward in time. This ensures that the threshold lags
behind the point-in-time where data changes are common, and that most INSERTs do not require any
extra writes.

When data older than the invalidation threshold is changed, each transaction logs the minimum and
maximum timestamps of the rows it modified. The continuous aggregate then identifies which complete
time buckets are affected based on this per-transaction tracking. The range of buckets that are
recalculated depends on transaction boundaries:

* If you modify rows in the 10:00 bucket and rows in the 15:00 bucket within a **single
  transaction**, all buckets from 10:00 to 15:00 (including intermediate buckets 11:00, 12:00,
  13:00, and 14:00) are recalculated during refresh.
* If you modify rows in the 10:00 bucket in one transaction and rows in the 15:00 bucket in a
  **separate transaction**, only the 10:00 and 15:00 buckets are recalculated. The intermediate
  buckets (11:00, 12:00, 13:00, 14:00) are not affected.

This logging does cause some write load. However, the threshold lags behind the area of data that
is currently changing, so the writes are small and rare.

[cagg-compression]: /manage-data/capabilities/continuous-aggregates/compression-on-continuous-aggregates

[cagg-drop-data]: /manage-data/capabilities/continuous-aggregates/drop-data

[cagg-joins]: /manage-data/capabilities/continuous-aggregates/setup-continuous-aggregates#create-a-continuous-aggregate-with-joins

[cagg-mat-hypertables]: /manage-data/capabilities/continuous-aggregates/materialized-hypertables

[cagg-time]: /manage-data/capabilities/continuous-aggregates/time

[cagg-window-functions]: /manage-data/capabilities/continuous-aggregates/setup-continuous-aggregates#use-continuous-aggregates-with-window-functions

[create-cagg]: /manage-data/capabilities/continuous-aggregates/setup-continuous-aggregates

[hierarchical-caggs]: /manage-data/capabilities/continuous-aggregates/hierarchical-continuous-aggregates

[postgres-parallel-agg]: https://www.postgresql.org/docs/current/parallel-plans.html#PARALLEL-AGGREGATION

[refresh-policy]: /manage-data/capabilities/continuous-aggregates/refresh-policies