add_columnstore_policy()

Create a job that automatically moves chunks in a hypertable to the columnstore after a specific time interval.

You enable the columnstore a hypertable or continuous aggregate before you create a columnstore policy. You do this by calling ALTER TABLE for hypertables and ALTER MATERIALIZED VIEW for continuous aggregates.

To view the policies that you set or the policies that already exist, see informational views, to remove a policy, see remove_columnstore_policy.

Since TimescaleDB v2.18.0

Samples

To create a columnstore job:

Enable columnstore

Use ALTER TABLE for a hypertable


ALTER TABLE crypto_ticks SET (
   timescaledb.enable_columnstore = true, 
   timescaledb.segmentby = 'symbol');

Use ALTER MATERIALIZED VIEW for a continuous aggregate


ALTER MATERIALIZED VIEW assets_candlestick_daily set (
   timescaledb.enable_columnstore = true, 
   timescaledb.segmentby = 'symbol' );

Add a policy to move chunks to the columnstore at a specific time interval

For example:

60 days after the data was added to the table:


CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '60d');

3 months prior to the moment you run the query:


CALL add_columnstore_policy('crypto_ticks', created_before => INTERVAL '3 months');

With an integer-based time column:


CALL add_columnstore_policy('table_with_bigint_time', BIGINT '600000');

Older than eight weeks:


CALL add_columnstore_policy('cpu_weekly', INTERVAL '8 weeks');

Older than eight weeks and using the Hypercore table access method:


CALL add_columnstore_policy(
  'cpu_weekly', 
  INTERVAL '8 weeks', 
  hypercore_use_access_method => true);

View the policies that you set or the policies that already exist
```
SELECT * FROM timescaledb_information.jobs
WHERE proc_name='policy_compression';
```
See timescaledb_information.jobs.

Arguments

Calls to add_columnstore_policy require either after or created_before, but cannot have both.

Name	Type	Default	Required	Description
`hypertable`	REGCLASS	-	✔	Name of the hypertable or continuous aggregate to run this job on.
`after`	INTERVAL or INTEGER	-	✖	Add chunks containing data older than `now - {after}::interval` to the columnstore. Use an object type that matchs the time column type in `hypertable`: `TIMESTAMP`, `TIMESTAMPTZ`, or `DATE`: use an `INTERVAL` type. Integer-based timestamps : set an integer type using the integer_now_func. `after` is mutually exclusive with `created_before`.
`created_before`	INTERVAL	NULL	✖	Add chunks with a creation time of `now() - created_before` to the columnstore. `created_before` is Not supported for continuous aggregates. Mutually exclusive with `after`.
`schedule_interval`	INTERVAL	12 hours when chunk_time_interval >= `1 day` for `hypertable`. Otherwise `chunk_time_interval` / `2`.	✖	Set the interval between the finish time of the last execution of this policy and the next start.
`initial_start`	TIMESTAMPTZ	The interval from the finish time of the last execution to the next_start.	✖	Set the time this job is first run. This is also the time that `next_start` is calculated from.
`timezone`	TEXT	UTC. However, daylight savings time(DST) changes may shift this alignment.	✖	Set to a valid time zone to mitigate DST shifting. If `initial_start` is set, subsequent executions of this policy are aligned on `initial_start`.
`if_not_exists`	BOOLEAN	`false`	✖	Set to `true` so this job fails with a warning rather than an error if a columnstore policy already exists on `hypertable`
`hypercore_use_access_method`	BOOLEAN	`NULL`	✖	Set to `true` to use hypercore table access metod. If set to `NULL` it will use the value from `timescaledb.default_hypercore_use_access_method`.

add_columnstore_policy()

Samples

Arguments

Related Content