> ## 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.

# Continuous aggregates overview

> TimescaleDB reference for calculating continuous aggregates on your data. Includes SQL functions and views related to creating, altering, and dropping continuous aggregates

export const COLUMNSTORE = 'columnstore';

export const ROWSTORE = 'rowstore';

export const PG = 'Postgres';

export const TIMESCALE_DB = 'TimescaleDB';

export const CAGG_CAP = 'Continuous aggregate';

export const CAGG = 'continuous aggregate';

export const HYPERTABLE = 'hypertable';

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
{CAGG}s.

![Reduced data calls with continuous aggregates](https://assets.timescale.com/docs/images/continuous-aggregate.png)

{CAGG_CAP}s 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 {CAGG} is
automatically updated in the background.

{CAGG_CAP}s 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 {CAGG}s are based on {HYPERTABLE}s, you can query them in exactly the same way as your other tables. This
includes {CAGG}s in the {ROWSTORE}, compressed into the [{COLUMNSTORE}][hypercore],
or [tiered to object storage][data-tiering]. You can even create [{CAGG}s on top of your {CAGG}s][hierarchical-caggs],
for an even more fine-tuned aggregation.

[Real-time aggregation][real-time-aggregation] 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.

[hypercore]: /use-timescale/latest/hypercore/

[data-tiering]: /use-timescale/latest/data-tiering/

[hierarchical-caggs]: /use-timescale/latest/continuous-aggregates/hierarchical-continuous-aggregates/

[real-time-aggregation]: /use-timescale/latest/continuous-aggregates/real-time-aggregates/

For more information about using {CAGG}s, see the documentation in [Use {CAGG}s][cagg-docs].

## Samples

### Create a continuous aggregate

Create a {CAGG} that calculates hourly average temperature:

```sql theme={"dark"}
CREATE MATERIALIZED VIEW conditions_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS bucket,
  location,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp
FROM conditions
GROUP BY bucket, location;
```

### Add a refresh policy

Automatically refresh the {CAGG} to keep it up to date:

```sql theme={"dark"}
SELECT add_continuous_aggregate_policy('conditions_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');
```

### Manually refresh a continuous aggregate

Refresh a specific time range in the {CAGG}:

```sql theme={"dark"}
CALL refresh_continuous_aggregate('conditions_hourly',
  '2024-01-01', '2024-02-01');
```

### Query a continuous aggregate

Query the {CAGG} just like a regular table:

```sql theme={"dark"}
SELECT bucket, location, avg_temp
FROM conditions_hourly
WHERE bucket >= NOW() - INTERVAL '7 days'
  AND location = 'office'
ORDER BY bucket DESC;
```

## Available functions

### Create and modify continuous aggregates

* [`CREATE MATERIALIZED VIEW (Continuous Aggregate)`][create_materialized_view]: create a {CAGG} on a {HYPERTABLE} or
  another {CAGG}
* [`ALTER MATERIALIZED VIEW (Continuous Aggregate)`][alter_materialized_view]: change an existing {CAGG}
* [`DROP MATERIALIZED VIEW (Continuous Aggregate)`][drop_materialized_view]: drop a {CAGG} view
* [`cagg_migrate()`][cagg_migrate]: migrate a {CAGG} from the old format to the new format introduced in {TIMESCALE_DB}
  2.7

### Refresh continuous aggregates

* [`refresh_continuous_aggregate()`][refresh_continuous_aggregate]: manually refresh a {CAGG}

### Manage policies

* [`add_continuous_aggregate_policy()`][add_continuous_aggregate_policy]: add policy to schedule automatic refresh of a

{CAGG}

* [`remove_continuous_aggregate_policy()`][remove_continuous_aggregate_policy]: remove a refresh policy from a {CAGG}

### Experimental policy management

* [`add_policies()`][add_policies]: add refresh, compression, and data retention policies on a {CAGG}
* [`alter_policies()`][alter_policies]: alter refresh, compression, or data retention policies on a {CAGG}
* [`remove_policies()`][remove_policies]: remove refresh, compression, or data retention policies from a {CAGG}
* [`remove_all_policies()`][remove_all_policies]: remove all policies from a {CAGG}
* [`show_policies()`][show_policies]: show all policies that are currently set on a {CAGG}

[add_continuous_aggregate_policy]: /api-reference/timescaledb/continuous-aggregates/add_continuous_aggregate_policy

[add_policies]: /api-reference/timescaledb/continuous-aggregates/add_policies

[alter_materialized_view]: /api-reference/timescaledb/continuous-aggregates/alter_materialized_view

[alter_policies]: /api-reference/timescaledb/continuous-aggregates/alter_policies

[cagg-docs]: /use-timescale/latest/continuous-aggregates/

[cagg_migrate]: /api-reference/timescaledb/continuous-aggregates/cagg_migrate

[create_materialized_view]: /api-reference/timescaledb/continuous-aggregates/create_materialized_view

[data-tiering]: /use-timescale/latest/data-tiering/

[drop_materialized_view]: /api-reference/timescaledb/continuous-aggregates/drop_materialized_view

[hierarchical-caggs]: /use-timescale/latest/continuous-aggregates/hierarchical-continuous-aggregates/

[hypercore]: /use-timescale/latest/hypercore/

[real-time-aggregation]: /use-timescale/latest/continuous-aggregates/real-time-aggregates/

[refresh_continuous_aggregate]: /api-reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate

[remove_all_policies]: /api-reference/timescaledb/continuous-aggregates/remove_all_policies

[remove_continuous_aggregate_policy]: /api-reference/timescaledb/continuous-aggregates/remove_continuous_aggregate_policy

[remove_policies]: /api-reference/timescaledb/continuous-aggregates/remove_policies

[show_policies]: /api-reference/timescaledb/continuous-aggregates/show_policies