> ## 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.
# Refresh continuous aggregates
> Set up automatic refresh policies or refresh your aggregates manually to ensure you have the latest aggregation data
export const SERVICE_SHORT = 'service';
export const CONSOLE = 'Tiger Console';
export const HYPERTABLE = 'hypertable';
export const CAGG_CAP = 'Continuous aggregate';
export const CAGG = 'continuous aggregate';
export const SERVICE_LONG = 'Tiger Cloud service';
export const SELF_LONG = 'self-hosted TimescaleDB';
{CAGG_CAP}s can have a range of different refresh policies. In addition to refreshing the {CAGG} automatically
using a policy, you can also refresh it manually.
## Prerequisites
To follow the steps on this page:
* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.
You need [your connection details][connection-info]. This procedure also
works for [{SELF_LONG}][enable-timescaledb].
[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services
[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted
[connection-info]: /integrations/find-connection-details
## Change the refresh policy
{CAGG_CAP}s require a policy for automatic refreshing. You can adjust this to suit different use cases. For
example, you can have the {CAGG} and the {HYPERTABLE} stay in sync, even when data is removed from the
{HYPERTABLE}. Alternatively, you could keep source data in the {CAGG} even after it is removed from the
{HYPERTABLE}.
You can change the way your {CAGG} is refreshed by calling
[`add_continuous_aggregate_policy`][add-cagg-policy].
Among others, `add_continuous_aggregate_policy` takes the following arguments:
* `start_offset`: the start of the refresh window relative to when the policy runs
* `end_offset`: the end of the refresh window relative to when the policy runs
* `schedule_interval`: the refresh interval in minutes or hours. Defaults to 24 hours.
Note the following:
* If you set the `start_offset` or `end_offset` to `NULL`, the range is open-ended and extends to the
beginning or end of time.
* If you set `end_offset` within the current time bucket, this bucket is excluded from materialization.
This is done for the following reasons:
* The current bucket is incomplete and can't be refreshed.
* The current bucket gets a lot of writes in the timestamp order, and its aggregate becomes outdated
very quickly. Excluding it improves performance.
To include the latest raw data in queries, enable [real-time aggregation][real-time-aggregates].
See the [API reference][add-cagg-policy] for the full list of required and optional arguments and use
examples.
The policy in the following example ensures that all data in the {CAGG} is up to date with the
{HYPERTABLE}, except for data written within the last hour of wall-clock time. The policy also does not
refresh the last time bucket of the {CAGG}.
Since the policy in this example 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 {CAGG}. Backfills, which are usually outside the most recent hour of data, will be
visible after up to 1 hour depending on when the policy last ran when the data was written.
Because it has an open-ended `start_offset` parameter, any data that is removed from the table, for
example with a `DELETE` or with [`drop_chunks`][drop-chunks], is also removed from the {CAGG} view. This
means that the {CAGG} always reflects the data in the underlying {HYPERTABLE}.
To change a refresh policy to use a `NULL` `start_offset`:
1. **Connect to your {SERVICE_LONG}**
In [{CONSOLE}][services-portal] open an [SQL editor][in-console-editors]. You can also connect to your
{SERVICE_SHORT} using [psql][connect-using-psql].
2. **Create a refresh policy**
Create a new policy on `conditions_summary_hourly` that keeps the {CAGG} up to date, and runs every
hour:
```sql theme={"dark"}
SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
start_offset => NULL,
end_offset => INTERVAL '1 h',
schedule_interval => INTERVAL '1 h');
```
If you want to keep data in the {CAGG} even if it is removed from the underlying {HYPERTABLE}, you can
set the `start_offset` to match the [data retention policy][data-retention] on the source {HYPERTABLE}.
For example, if you have a retention policy that removes data older than one month, set `start_offset` to
one month or less. This sets your policy so that it does not refresh the dropped data.
3. **Keep data after removal from hypertable**
Create a new policy on `conditions_summary_hourly` that keeps data removed from the {HYPERTABLE} in
the {CAGG}, and runs every hour:
```sql theme={"dark"}
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 your data retention policies when you're setting up {CAGG} policies. If the
{CAGG} policy window covers data that is removed by the data retention policy, the data will be removed
when the aggregates for those buckets are refreshed. For example, if you have a data retention policy
that removes all data older than two weeks, the {CAGG} policy will only have data for the last two weeks.
## Add concurrent refresh policies
You can add concurrent refresh policies on each {CAGG}, as long as their start and end offsets don't
overlap. For example, to backfill data into older chunks you set up one policy that refreshes recent
data, and another that refreshes backfilled data.
The first policy in this example keeps the {CAGG} up to date with data that was inserted in the past day.
Any data that was inserted or updated for previous days is refreshed by the second policy.
1. **Connect to your {SERVICE_LONG}**
In [{CONSOLE}][services-portal] open an [SQL editor][in-console-editors]. You can also connect to your
{SERVICE_SHORT} using [psql][connect-using-psql].
2. **Create a refresh policy for recent data**
Create a new policy on `conditions_summary_daily` to refresh the {CAGG} with recently inserted data
which runs hourly:
```sql theme={"dark"}
SELECT add_continuous_aggregate_policy('conditions_summary_daily',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '1 h',
schedule_interval => INTERVAL '1 h');
```
3. **Create a concurrent policy for backfilled data**
Create a concurrent policy on `conditions_summary_daily` to refresh the {CAGG}
with backfilled data:
```sql theme={"dark"}
SELECT add_continuous_aggregate_policy('conditions_summary_daily',
start_offset => NULL,
end_offset => INTERVAL '1 day',
schedule_interval => INTERVAL '1 hour');
```
## Manually refresh a continuous aggregate
If you need to manually refresh a {CAGG}, you can use the [`refresh_continuous_aggregate`][refresh-cagg]
command. This recomputes the data within the window that has changed in the underlying {HYPERTABLE} since
the last refresh. Therefore, if only a few buckets need updating, the refresh runs quickly.
If you have recently dropped data from a {HYPERTABLE} with a {CAGG}, calling `refresh_continuous_aggregate`
on a region containing dropped chunks recalculates the aggregate without the dropped data. See
[drop data][cagg-drop-data] for more information.
The `refresh_continuous_aggregate` command takes three arguments:
* The name of the {CAGG} view to refresh
* The timestamp of the beginning of the refresh window
* The timestamp of the end of the refresh window
Only buckets that are wholly within the specified range are refreshed. For example, if you specify
`2021-05-01', '2021-06-01` the only buckets that are refreshed are those up to but not including
2021-06-01. It is possible to specify `NULL` in a manual refresh to get an open-ended range, but we do
not recommend using it, because you could inadvertently materialize a large amount of data, slow down
your performance, and have unintended consequences on other policies like data retention.
To manually refresh a {CAGG}, use the `refresh_continuous_aggregate` command:
```sql theme={"dark"}
CALL refresh_continuous_aggregate('example', '2021-05-01', '2021-06-01');
```
Follow the logic used by automated refresh policies and avoid refreshing time buckets that are likely to
have a lot of writes. This means that you should generally not refresh the latest incomplete time bucket.
To include the latest raw data in your queries, use [real-time aggregation][real-time-aggregates] instead.
[add-cagg-policy]: /api-reference/timescaledb/continuous-aggregates/add_continuous_aggregate_policy
[cagg-drop-data]: /manage-data/capabilities/continuous-aggregates/drop-data
[connect-using-psql]: /getting-started/integrate-tiger/psql
[data-retention]: /manage-data/capabilities/data-retention
[drop-chunks]: /api-reference/timescaledb/hypertables/drop_chunks
[in-console-editors]: /getting-started/run-queries-from-console
[real-time-aggregates]: /manage-data/capabilities/continuous-aggregates/real-time-aggregates
[refresh-cagg]: /api-reference/timescaledb/continuous-aggregates/refresh_continuous_aggregate
[services-portal]: https://console.cloud.timescale.com/dashboard/services