Using and setting up continuous aggregate policies
You now have a continuous aggregate, but you have not updated any data or created an automatic policy. There are two ways to update a continuous aggregate:
- Automatic continuous aggregate refresh policy
- Manual refresh
These methods enable you to refresh materialized data in your continuous aggregates when it's most convenient. For example, you can perform refreshes during low query-load times on your database.
To refresh your continuous aggregate on a schedule, set up an automatic refresh policy. Automatic refresh policies are considered a best practice in TimescaleDB and should be created for most continuous aggregates. Using the automated continuous aggregate policy to update continuous aggregate data allows you to "set it and forget it," ensuring that new hypertable data is materialized over time as it is inserted into the database.
Creating a continuous aggregate refresh policy
Use this SQL command to create an auto-updating policy for the continuous aggregate
SELECT add_continuous_aggregate_policy('stock_candlestick_daily', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 days');
This policy runs once a day, as set by
schedule_interval. When it runs, it materializes data from between 3 days ago and 1 hour ago, as set by
end_offset. Offset times are calculated relative to query execution time. The executed query is the one defined in the continuous aggregate
You can manually refresh a continuous aggregate using a one-time refresh.
This is most useful when data is inserted or modified that is outside of the
end_offset interval. This is common in edge
IoT systems where devices lose their internet connection for long periods of time
and eventually send historical readings once they are reconnected.
Manually refreshing a continuous aggregate
Refresh the continuous aggregate
stock_candlestick_dailyfor data within the past week:
CALL refresh_continuous_aggregate( 'stock_candlestick_daily', now() - INTERVAL '1 week', now() );
This manual refresh updates your continuous aggregate only once. It doesn't keep the aggregate up to date automatically. To set up an automatic refresh policy, see the preceding section on continuous aggregate refresh policies.
Now that you have a continuous aggregate set up and refreshing automatically, see how TimescaleDB can save you up to 96% on storage costs while speeding up historical queries using native compression.
See how real TimescaleDB users leverage continuous aggregates in the blog posts How FlightAware fuels flight prediction models for global travelers with TimescaleDB and Grafana and How I power a (successful) crypto trading bot with TimescaleDB.
For detailed information on continuous aggregates and real-time aggregation, see the continuous aggregates section.
Found an issue on this page?Report an issue!