> ## 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.
# Hierarchical continuous aggregates
> Create continuous aggregates on top of continuous aggregates to summarize data at different levels of granularity
export const PG = 'Postgres';
export const CAGG = 'continuous aggregate';
export const HYPERTABLE = 'hypertable';
export const TIMESCALE_DB = 'TimescaleDB';
The more data you have, the more likely you are to run a more sophisticated analysis on it. When a simple
one-level aggregation is not enough, {TIMESCALE_DB} lets you create {CAGG}s on top of other {CAGG}s. This
way, you summarize data at different levels of granularity, while still saving resources with
precomputing.
For example, you might have an hourly {CAGG} that summarizes minute-by-minute data. To get a daily
summary, you can create a new {CAGG} on top of your hourly aggregate. This is more efficient than
creating the daily aggregate on top of the original {HYPERTABLE}, because you can reuse the calculations
from the hourly aggregate.
## Create a continuous aggregate on top of another continuous aggregate
Creating a {CAGG} on top of another {CAGG} works the same way as creating it on top of a {HYPERTABLE}. In
your query, select from a {CAGG} rather than from the {HYPERTABLE}, and use the time-bucketed column from
the existing {CAGG} as your time column.
For more information, see the instructions for [creating a continuous aggregate][create-cagg].
## Use real-time aggregation with hierarchical continuous aggregates
Real-time aggregates always return up-to-date data in response to queries. They accomplish this by
joining the materialized data in the {CAGG} with unmaterialized raw data from the source table or view.
When {CAGG}s are stacked, each {CAGG} is only aware of the layer immediately below. The joining of
unmaterialized data happens recursively until it reaches the bottom layer, giving you access to recent
data down to that layer.
If you keep all {CAGG}s in the stack as real-time aggregates, the bottom layer is the source
{HYPERTABLE}. That means every {CAGG} in the stack has access to all recent data.
If there is a non-real-time {CAGG} somewhere in the stack, the recursive joining stops at that
non-real-time {CAGG}. Higher-level {CAGG}s don't receive any unmaterialized data from lower levels.
For example, say you have the following {CAGG}s:
* A real-time hourly {CAGG} on the source {HYPERTABLE}
* A real-time daily {CAGG} on the hourly {CAGG}
* A non-real-time, or materialized-only, monthly {CAGG} on the daily {CAGG}
* A real-time yearly {CAGG} on the monthly {CAGG}
Queries on the hourly and daily {CAGG}s include real-time, non-materialized data from the source
{HYPERTABLE}. Queries on the monthly {CAGG} only return already-materialized data. Queries on the yearly
{CAGG} return materialized data from the yearly {CAGG} itself, plus more recent data from the monthly
{CAGG}. However, the data is limited to what is already materialized in the monthly {CAGG}, and doesn't
get even more recent data from the source {HYPERTABLE}. This happens because the materialized-only {CAGG}
provides a stopping point, and the yearly {CAGG} is unaware of any layers beyond that stopping point. This
is similar to [how stacked views work in {PG}][postgresql-views].
To make queries on the yearly {CAGG} access all recent data, you can either:
* Make the monthly {CAGG} real-time, or
* Redefine the yearly {CAGG} on top of the daily {CAGG}.
```mermaid theme={"dark"}
%%{init: {
'theme':'base',
'themeVariables': {
'primaryColor':'#fff',
'primaryTextColor':'#1a1a1a',
'primaryBorderColor':'#333',
'lineColor':'#666',
'secondaryColor':'#fff',
'secondaryTextColor':'#1a1a1a',
'secondaryBorderColor':'#333',
'tertiaryColor':'#fff',
'tertiaryTextColor':'#1a1a1a',
'tertiaryBorderColor':'#333',
'noteBkgColor':'#fff',
'noteTextColor':'#1a1a1a',
'noteBorderColor':'#333',
'background':'#fff',
'mainBkg':'#fff',
'fontFamily': "'Geist Mono', monospace",
'edgeLabelBackground':'#fff',
'labelColor':'#333',
'labelTextColor':'#333'
},
'flowchart': { 'padding': 30, 'htmlLabels': true, 'curve': 'stepAfter' },
/* Hover tint (best-effort; renderer may ignore themeCSS) */
'themeCSS': `
.node rect:hover,
.node polygon:hover,
.node path:hover {
fill: rgba(244, 255, 97, 0.18) !important;
transition: fill 120ms ease-in-out;
}
`
}}%%
graph TB
A[Source hypertable ] --> B[Hourly CAGG ]
B --> C[Daily CAGG ]
C --> D[Monthly CAGG ]
D --> E[Yearly CAGG ]
A -.->|recent data| B
A -.->|recent data| C
D -.->|materialized only| E
F[Real-time aggregates have
access to recent raw data ] -.-> B
G[Materialized-only CAGG acts
as stopping point ] -.-> D
%% Border-only hierarchy
%% - Source hypertable: thicker border (A)
%% - Real-time CAGGs: normal border (B, C, E)
%% - Materialized-only CAGG: thicker dashed border (D)
%% - Notes: lighter dashed border (F, G)
style A fill:#fff,stroke:#333,stroke-width:3px,color:#1a1a1a,rx:4,ry:4
style B fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4
style C fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4
style D fill:#fff,stroke:#333,stroke-width:3px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4
style E fill:#fff,stroke:#333,stroke-width:2px,color:#1a1a1a,rx:4,ry:4
style F fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4
style G fill:#fff,stroke:#666,stroke-width:1.5px,stroke-dasharray: 5 5,color:#1a1a1a,rx:4,ry:4
%% connectors
linkStyle default stroke:#777,stroke-width:1px
click B "/manage-data/capabilities/continuous-aggregates/real-time-aggregates" "Learn about real-time aggregates"
click D "/manage-data/capabilities/continuous-aggregates/real-time-aggregates" "Learn about real-time aggregates"
```
## Roll up calculations
When summarizing already-summarized data, be aware of how stacked calculations work. Not all calculations
return the correct result if you stack them.
For example, if you take the maximum of several subsets, then take the maximum of the maximums, you get
the maximum of the entire set. But if you take the average of several subsets, then take the average of
the averages, that can result in a different figure than the average of all the data.
To simplify such calculations when using {CAGG}s on top of {CAGG}s, you can use the
[hyperfunctions][hyperfunctions] from TimescaleDB Toolkit, such as the [statistical aggregates][stats-aggs].
These hyperfunctions are designed with a two-step aggregation pattern that allows you to roll them up into
larger buckets. The first step creates a summary aggregate that can be rolled up, just as a maximum can be
rolled up. You can store this aggregate in your {CAGG}. Then, you can call an accessor function as a
second step when you query from your {CAGG}. This accessor takes the stored data from the summary aggregate
and returns the final result.
For example, you can create an hourly {CAGG} using `percentile_agg` over a {HYPERTABLE}, like this:
```sql theme={"dark"}
CREATE MATERIALIZED VIEW response_times_hourly
WITH (timescaledb.continuous)
AS SELECT
time_bucket('1 h'::interval, ts) as bucket,
api_id,
avg(response_time_ms),
percentile_agg(response_time_ms) as percentile_hourly
FROM response_times
GROUP BY 1, 2;
```
To then stack another daily {CAGG} over it, you can use a `rollup` function, like this:
```sql theme={"dark"}
CREATE MATERIALIZED VIEW response_times_daily
WITH (timescaledb.continuous)
AS SELECT
time_bucket('1 d'::interval, bucket) as bucket_daily,
api_id,
mean(rollup(percentile_hourly)) as mean,
rollup(percentile_hourly) as percentile_daily
FROM response_times_hourly
GROUP BY 1, 2;
```
The `mean` function of the TimescaleDB Toolkit is used to calculate the concrete mean value of the rolled
up values. The additional `percentile_daily` attribute contains the raw rolled up values, which can be
used in an additional {CAGG} on top of this {CAGG} (for example a {CAGG} for the daily values).
For more information and examples about using `rollup` functions to stack calculations, see the
[percentile approximation API documentation][percentile_agg_api].
## Restrictions
There are some restrictions when creating a {CAGG} on top of another {CAGG}. In most cases, these
restrictions are in place to ensure valid time-bucketing:
* You can only create a {CAGG} on top of a finalized {CAGG}. This new finalized format is the default for
all {CAGG}s created since {TIMESCALE_DB} 2.7. If you need to create a {CAGG} on top of a {CAGG} in the
old format, you need to [migrate your continuous aggregate][cagg-migrate] to the new format first.
* The time bucket of a {CAGG} should be greater than or equal to the time bucket of the underlying {CAGG}.
It also needs to be a multiple of the underlying time bucket. For example, you can rebucket an hourly
{CAGG} into a new {CAGG} with time buckets of 6 hours. You can't rebucket the hourly {CAGG} into a new
{CAGG} with time buckets of 90 minutes, because 90 minutes is not a multiple of 1 hour.
* A {CAGG} with a fixed-width time bucket can't be created on top of a {CAGG} with a variable-width time
bucket. Fixed-width time buckets are time buckets defined in seconds, minutes, hours, and days, because
those time intervals are always the same length. Variable-width time buckets are time buckets defined in
months or years, because those time intervals vary by the month or on leap years. This limitation
prevents a case such as trying to rebucket monthly buckets into `61 day` buckets, where there is no good
mapping between time buckets for month combinations such as July/August (62 days).
Note that even though weeks are fixed-width intervals, you can't use monthly or yearly time buckets on
top of weekly time buckets for the same reason. The number of weeks in a month or year is usually not an
integer.
However, you can stack a variable-width time bucket on top of a fixed-width time bucket. For example,
creating a monthly {CAGG} on top of a daily {CAGG} works, and is one of the main use cases for this
feature.
[cagg-migrate]: /manage-data/capabilities/continuous-aggregates/migrate
[create-cagg]: /manage-data/capabilities/continuous-aggregates/setup-continuous-aggregates
[hyperfunctions]: /api-reference/timescaledb/hyperfunctions
[percentile_agg_api]: /api-reference/toolkit/hyperfunctions/percentile-approximation/percentile_agg
[postgresql-views]: https://www.postgresql.org/docs/current/rules-views.html
[stats-aggs]: /api-reference/toolkit/hyperfunctions/statistical-and-regression-analysis/stats_agg