Hierarchical continuous aggregates

Create continuous aggregates on top of continuous aggregates to summarize data at different levels of granularity

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, lets you create s on top of other s. This way, you summarize data at different levels of granularity, while still saving resources with precomputing.

For example, you might have an hourly that summarizes minute-by-minute data. To get a daily summary, you can create a new on top of your hourly aggregate. This is more efficient than creating the daily aggregate on top of the original , because you can reuse the calculations from the hourly aggregate.

Create a continuous aggregate on top of another continuous aggregate

Creating a on top of another works the same way as creating it on top of a . In your query, select from a rather than from the , and use the time-bucketed column from the existing as your time column.

For more information, see the instructions for creating a continuous aggregate.

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 with unmaterialized raw data from the source table or view.

When s are stacked, each 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 s in the stack as real-time aggregates, the bottom layer is the source . That means every in the stack has access to all recent data.

If there is a non-real-time somewhere in the stack, the recursive joining stops at that non-real-time . Higher-level s don’t receive any unmaterialized data from lower levels.

For example, say you have the following s:

• A real-time hourly on the source
• A real-time daily on the hourly
• A non-real-time, or materialized-only, monthly on the daily
• A real-time yearly on the monthly

Queries on the hourly and daily s include real-time, non-materialized data from the source . Queries on the monthly only return already-materialized data. Queries on the yearly return materialized data from the yearly itself, plus more recent data from the monthly . However, the data is limited to what is already materialized in the monthly , and doesn’t get even more recent data from the source . This happens because the materialized-only provides a stopping point, and the yearly is unaware of any layers beyond that stopping point. This is similar to how stacked views work in .

To make queries on the yearly access all recent data, you can either:

• Make the monthly real-time, or
• Redefine the yearly on top of the daily .

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 s on top of s, you can use the hyperfunctions from TimescaleDB Toolkit, such as the statistical aggregates. 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 . Then, you can call an accessor function as a second step when you query from your . This accessor takes the stored data from the summary aggregate and returns the final result.

For example, you can create an hourly using percentile_agg over a , like this:

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 over it, you can use a rollup function, like this:

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 on top of this (for example a for the daily values).

For more information and examples about using rollup functions to stack calculations, see the percentile approximation API documentation.

Restrictions

There are some restrictions when creating a on top of another . In most cases, these restrictions are in place to ensure valid time-bucketing:

• You can only create a on top of a finalized . This new finalized format is the default for all s created since 2.7. If you need to create a on top of a in the old format, you need to migrate your continuous aggregate to the new format first.

• The time bucket of a should be greater than or equal to the time bucket of the underlying . It also needs to be a multiple of the underlying time bucket. For example, you can rebucket an hourly into a new with time buckets of 6 hours. You can’t rebucket the hourly into a new with time buckets of 90 minutes, because 90 minutes is not a multiple of 1 hour.

• A with a fixed-width time bucket can’t be created on top of a 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 on top of a daily works, and is one of the main use cases for this feature.