Convert continuous aggregates to the columnstore

Since 2.20.0

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

Columnstore works in the same way on hypertables and continuous aggregates. When you enable columnstore with no other options, your data is segmented by the groupby columns in the continuous aggregate, and ordered by the time column. Real-time aggregate is disabled by default.

Configure columnstore on continuous aggregates

For an existing continuous aggregate:

1. Enable columnstore on a continuous aggregate

   Set timescaledb.enable_columnstore = true when you alter the view:

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

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

2. Set columnstore policies on the continuous aggregate

   Before you set up a columnstore policy on a continuous aggregate, you first set the refresh policy. To prevent refresh policies from failing, set the columnstore 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. 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');