Convert continuous aggregates to columnstore

Convert a continuous aggregate to columnstore to save storage space while keeping data available for analytical workloads

To save on storage costs, you use to downsample historical data stored in s. After you enable on a MATERIALIZED VIEW, you set a policy. This policy defines the intervals when chunks in a are compressed as they are converted from the to the .

works in the same way on s and s. When you enable with no other options, your data is segmented by the groupby columns in the , and ordered by the time column. Real-time aggregation is disabled by default.

Since 2.20.0. For the old API (pre-2.20.0), see legacy compression documentation.

Configure columnstore on continuous aggregates

For an existing :

1. Enable on a

   To enable the compression on a , set timescaledb.enable_columnstore = true when you alter the view:

   ALTER MATERIALIZED VIEW <cagg_name> set (timescaledb.enable_columnstore = true);
   

   To disable the compression, set timescaledb.enable_columnstore = false.

2. Set policies on the

   Before you set up a policy on a , you first set the refresh policy. To prevent refresh policies from failing, you set the policy interval so that actively refreshed regions are not compressed. For example:

   1. Set the refresh policy

      SELECT add_continuous_aggregate_policy('<cagg_name>',
        start_offset => INTERVAL '30 days',
        end_offset => INTERVAL '1 day',
        schedule_interval => INTERVAL '1 hour');
      

   2. Set the columnstore policy

      For this refresh policy, the after parameter must be greater than the value of start_offset in the refresh policy:

      CALL add_columnstore_policy('<cagg_name>', after => INTERVAL '45 days');