When you are working with continuous aggregates, you can drop a view, or you can drop raw data from the underlying hypertable or from the continuous aggregate itself. A combination of refresh and data retention policies can help you downsample your data. This lets you keep historical data at a lower granularity than recent data.
However, you should be aware if a retention policy is likely to drop raw data from your hypertable that you need in your continuous aggregate.
To simplify the process of setting up downsampling, you can use the visualizer and code generator below.
You can drop a continuous aggregate view using the
DROP MATERIALIZED VIEW
command. This command also removes refresh policies defined on the continuous
aggregate. It does not drop the data from the underlying hypertable.
psqlprompt, drop the view:DROP MATERIALIZED VIEW view_name;
If you drop data from a hypertable used in a continuous aggregate it can lead to problems with your continuous aggregate view. In many cases, dropping underlying data replaces the aggregate with NULL values, which can lead to unexpected results in your view.
You can drop data from a hypertable using
drop_chunks in the usual way, but
before you do so, always check that the chunk is not within the refresh window
of a continuous aggregate that still needs the data. This is also important if
you are manually refreshing a continuous aggregate. Calling
refresh_continuous_aggregate on a region containing dropped chunks
recalculates the aggregate without the dropped data.
If a continuous aggregate is refreshing when data is dropped because of a
retention policy, the aggregate is updated to reflect the loss of data. If you
need to retain the continuous aggregate after dropping the underlying data, set
start_offset value of the aggregate policy to a smaller interval than the
drop_after parameter of the retention policy.
For more information, see the data retention documentation.
Set up downsampling and data retention
Maximize your storage by keeping downsampled historical data and dropping raw data.
Once you've created a continuous aggregate, you can automatically downsample your data. Enter the time bucket from your continuous aggregate definition. Then, adjust the sliders to select how you want to update your continuous aggregate, and how long you want to keep your raw and downsampled data.
RefreshAutomatically keep downsampled data up to date with new data from 30 minutes to 12 hours ago.
Drop raw dataDrop raw data older than 1 year.
Drop downsampled dataDrop downsampled data older than 5 years.
SELECT add_retention_policy('hypertable_name', INTERVAL '1 year');SELECT add_continuous_aggregate_policy('continuous_aggregate_name',start_offset => INTERVAL '12 hours',end_offset => INTERVAL '30 minutes',schedule_interval => INTERVAL '10 seconds');SELECT add_retention_policy('continuous_aggregate_name', INTERVAL '5 years');
Found an issue on this page?Report an issue!