Complex Data Rollup in TimescaleDB

user_1276624 0
Viewed 138

I have a system collecting counter data every 5s to the TimescaleDB.

Rollup requirements are:

  1. For data older than 7 days the granularity changes from 5s to 1 hour
  2. For data older than 30 days the granularity drops from 1 hour to 1 day

I can get (1) by using a continuous aggregate from the 5s hypertable and using data retention.
However, for (2) I can't create a second materialized view from the 1h materialized view of (1) becauase I can only do it from a hypertable.

How can I fill the rollup requirements?
Can I somehow leverage the fact that my measurements are counters (can simply delete rows to reduce granularity)?

timescaledb
2 Answers

You can define multiple continuous aggregates on the same hypertable, so the typical approach is to have a separate 5s, 1h, 1d continuous aggregate, with different data retention policies defined on each.

user_84651660

Continuous aggregates can only be defined on top of a hypertable. You cannot define a continuous aggregate from another one or change it.

I see two approaches to achieve desired functionality with current version of TimescaleDB 2.2:

  1. Both continuous aggregate policies materializes data after 7 days. One retention policy drops original data after 1 week and another retention policy drops the data from the first continuous aggregate with 1 hour bucket after 30 days.
  2. The first continuous aggregate policies materialized after 7 days and the second - after 30 days. This means that the original data needs to be kept until the second continuous aggregate materializes the data. Thus both retention policies will be after 30 day. While the original data is kept longer, the hypertable can be compressed to reduce disk usage.

Here is possible implementation. Continuous aggregates will be the same in both approaches:

CREATE MATERIALIZED VIEW cagg_1h WITH (timescaledb.continuous) AS
SELECT time_bucket('1h', my_time_column), ...
FROM my_hypertable
WHERE ...
GROUP BY 1, ...;

CREATE MATERIALIZED VIEW cagg_1d WITH (timescaledb.continuous) AS
SELECT time_bucket('1d', my_time_column), ...
FROM my_hypertable
WHERE ...
GROUP BY 1, ...;

The policies for the first approach:

SELECT add_continuous_aggregate_policy('cagg_1h', INTERVAL '7d', INTERVAL '6d', INTERVAL '4h');
SELECT add_continuous_aggregate_policy('cagg_1d', INTERVAL '8d', INTERVAL '5d', INTERVAL '1h');
SELECT add_retention_policy('my_hypertable', INTERVAL '8d');
SELECT add_retention_policy('cagg_1h', INTERVAL '30d');

I tried to define the refresh windows for continuous aggregates, i.e., start and end offset intervals, and the interval for retention of original data in such way that the data will be materialized in both continuous aggregates before dropping.

For the second approach:

SELECT add_continuous_aggregate_policy('cagg_1h', INTERVAL '7d', INTERVAL '6d', INTERVAL '4h');
SELECT add_continuous_aggregate_policy('cagg_1d', INTERVAL '30d', INTERVAL '27d', INTERVAL '1d');
SELECT add_compression_policy('my_hypetable', INTERVAL '7d');
SELECT add_retention_policy('my_hypertable', INTERVAL '30d');
SELECT add_retention_policy('cagg_1h', INTERVAL '30d');

Same here, the continuous aggregates contain refresh windows, so that data will materialized to the desired interval and will not be dropped by retention policies. The second approach includes a compression policy assuming that no updates will arrive to original data after 7 days, which seems to be true from the question description.

Before adding a compression policy it is necessary to enable it by altering the original hypertable.

user_8403400
Related