Continuous aggregate policies can be configured to support different use cases. For example, you might want to:
These use cases are supported by different configuration to
This function takes three arguments:
start_offsetindicates the start of the refresh window relative to the current time when the policy executes.
end_offsetindicates the end of the refresh window relative to the current time when the policy executes.
schedule_intervalindicates the refresh interval in wall-clock time.
Similar to the
refresh_continuous_aggregate function, providing
end_offset makes the range open-ended
and will extend to the beginning or end of time,
respectively. However, it seldom makes sense to use
NULL for the
end_offset. Instead, it is recommended to set the
that at least the most recent time bucket is excluded. For time-series
data that see mostly in-order writes, the time buckets that still see
lots of writes will quickly have out-of-date aggregates. Excluding
those time buckets will provide better performance.
For example, to create a policy for
keeps the continuous aggregate up to date with the underlying
conditions and runs every hour, you would write:
SELECT add_continuous_aggregate_policy('conditions_summary_hourly', start_offset => NULL, end_offset => INTERVAL '1 h', schedule_interval => INTERVAL '1 h');
This will ensure that all data in the continuous aggregate is up to
date with the hypertable except the last hour and also ensure that we
do not try to refresh the last bucket of the continuous
aggregate. Since we give an open-ended
start_offset, any data that
is removed from
conditions (for example, by using
drop_chunks) will also be removed from
conditions_summary_hourly. In effect, the continuous aggregate will
always reflect the data in the underlying hypertable.
If you instead want to keep data in the continuous aggregate even if
the source data is removed from the underlying hypertable, you also
need to set the
start_offset in way that is compatible with the
data retention policy on the source
hypertable. For example, if you have a retention policy that removes
data older than one month, you need to set
start_offset to one month
(or less) and thereby not refresh the region of dropped data.
SELECT add_continuous_aggregate_policy('conditions_summary_hourly', start_offset => INTERVAL '1 month', end_offset => INTERVAL '1 h', schedule_interval => INTERVAL '1 h');
It is important to consider data retention policies when setting up continuous aggregate policies. If the continuous aggregate policy window covers data that is removed by the data retention policy, the aggregates for those buckets will be refreshed and consequently the data will be removed. For example, if you have a data retention policy that will remove all data older than 2 weeks, the continuous aggregate policy above will only have data for the last two weeks. A more reasonable data retention policy for this case would then be to remove data that is older than 1 month.
You can read more about data retention with continuous aggregates in the _Data retention_ section.
A continuous aggregate may be dropped by using the
VIEW command. It does not affect the data in the hypertable from
which the continuous aggregate is derived (
conditions in the example
DROP MATERIALIZED VIEW conditions_summary_hourly;