Behavior of continuous aggregate refresh policies

[FrancescEthon]

Hi,

I’m trying to understand the exact behavior of continuous aggregate refresh policies, and I’m running into a mismatch between the docs and the code.

For the following policy:

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');

The docs say:

“Since the policy runs once every hour (schedule_interval) while also excluding data within the most recent hour (end_offset), it takes up to 2 hours for data written to the hypertable to be reflected in the continuous aggregate.”

This suggests that for an hourly continuous aggregate with end_offset => INTERVAL '1h', data will appear in the aggregate at most 2h later.

Looking at the code, though, it seems that the refresh window is adjusted using compute_inscribed_bucketed_refresh_window. This function “shrinks” the raw refresh window so that only buckets fully enclosed by the window are refreshed. Buckets that partially overlap the exclusion window are skipped.

Imagine that the refresh job runs at :59 past the hour and a data point at 12:01 which belongs to bucket [12:00,13:00).

At the 13:59 run:

• Raw window end = 13:59 – 1h = 12:59.
• Inscribed window = buckets ending ≤ 12:00.
• The [12:00,13:00) bucket overlaps the exclusion window, so it is not refreshed.

At the 14:59 run:

• The bucket [12:00,13:00) is finally included.

Result: data at 12:01 appears at 14:59 → almost 3h delay, not 2h.

Question

Is this the intended behavior (i.e. buckets are excluded if they overlap the end_offset window at all)?

If so, should the docs be clarified to say that the “up to 2 hours” figure assumes jobs run exactly on bucket boundaries (e.g. :00 for hourly buckets)? Otherwise the effective delay can approach 3h.

Thanks!

[blakemartz]

If you are on version 2.22.0, please see issue #8621.

Yesterday the team rolled back the latest release 2.22.0 to 2.21.4 due to issues with continuous aggregate refresh policies.

[philkra]

Yesterday the team rolled back the latest release 2.22.0 to 2.21.4 due to issues with continuous aggregate refresh policies.

Thank you @blakemartz for helping folks out. One correction, the release 2.21.4 is not a rollback. The newly released minor version of Postgres have some planner changes which can cause performance regressions.

[FrancescEthon]

Thanks for the clarification on version 2.22 and the rollback. Just to clarify my situation: I’m not on 2.22, I’m currently running TimescaleDB 2.19.

What I’d like to confirm is the expected behavior for continuous aggregate refresh policies with end_offset => '1h'.

From my testing, it looks like a bucket such as [12:00,13:00) is excluded at the 13:59 run if the policy runs at :59 past the hour, and only included at the 14:59 run. That means data from 12:01 is only visible nearly 3 hours later, not “up to 2 hours” as the documentation currently states.

So my question is: Is it the intended behavior that buckets overlapping the end_offset window are always excluded, and only fully enclosed buckets are refreshed?

[blakemartz]

@philkra right, thank you for the clarification. yes rollback is the wrong term to use

- Pinning or re-pointing :latest to a prior stable version.
- Sometimes also called a “yank” (in the sense of removing a bad version from circulation without erasing it) or “revert of the distribution tag.”

so it was a yank! 😅

[mkindahl]

Thanks for the clarification on version 2.22 and the rollback. Just to clarify my situation: I’m not on 2.22, I’m currently running TimescaleDB 2.19.

What I’d like to confirm is the expected behavior for continuous aggregate refresh policies with end_offset => '1h'.

From my testing, it looks like a bucket such as [12:00,13:00) is excluded at the 13:59 run if the policy runs at :59 past the hour, and only included at the 14:59 run. That means data from 12:01 is only visible nearly 3 hours later, not “up to 2 hours” as the documentation currently states.

Yes, your computations seems to be correct and it seems to be a bug in the documentation.

So my question is: Is it the intended behavior that buckets overlapping the end_offset window are always excluded, and only fully enclosed buckets are refreshed?

Yes, the intention is that only fully enclosed buckets are refreshed.

The reasoning is that the buckets that are not enclosed on the "near" side (in time) are still accumulating data and refreshing them would mean refreshing them each time you run a refresh (because it would always contain invalidations). This would do a lot of unnecessary refreshes that a real-time aggregate is better at handling.

This assumption is not true if you have a lot of reads of this "near" bucket, very infrequent refreshes (like in your case, every hour), and are OK with "outdated" data (any data added to this bucket since the last refresh will not be used), so then you might want to not set an end offset and instead "waste" CPU to refresh this bucket each time you do a refresh.

[pnthao]

@FrancescEthon Thanks for pointing this out. Yes it is intended that we only refresh buckets that fit entirely into the refresh window. So the behavior in your example is expected.
We will see how we can adjust the doc to avoid confusion. We could think about "data" here as a whole bucket (because we are aggregating data per bucket), and as such, the bucket [12:00 - 13:00] take up to 2hrs to appear after the bucket ends.