add_columnstore_policy()

Tiger Cloud: Performance, Scale, Enterprise, Free

Self-hosted products

MST

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 CREATE TABLE for hypertables and ALTER MATERIALIZED VIEW for continuous aggregates. When columnstore is enabled, bloom filters are enabled by default, and every new chunk has a bloom index. If you converted chunks to columnstore using TimescaleDB v2.19.3 or below, to enable bloom filters on that data you have to convert those chunks to the rowstore, then convert them back to the columnstore.

Bloom indexes are not retrofitted, meaning that the existing chunks need to be fully recompressed to have the bloom indexes present. Please check out the PR description for more in-depth explanations of how bloom filters in TimescaleDB work.

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

A columnstore policy is applied on a per-chunk basis. If you remove an existing policy and then add a new one, the new policy applies only to the chunks that have not yet been converted to columnstore. The existing chunks in the columnstore remain unchanged. This means that chunks with different columnstore settings can co-exist in the same hypertable.

Since TimescaleDB v2.18.0

Samples

To create a columnstore job:

Enable columnstore
Create a hypertable for your time-series data using CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data. For example:
- Use CREATE TABLE for a hypertable
```
CREATE TABLE crypto_ticks (
   "time" TIMESTAMPTZ,
   symbol TEXT,
   price DOUBLE PRECISION,
   day_volume NUMERIC
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time',
  tsdb.segmentby='symbol', 
  tsdb.orderby='time DESC'
);
```
  If you are self-hosting TimescaleDB v2.19.3 and below, create a Postgres relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.
- 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');
```
- Control the time your policy runs:
  When you use a policy with a fixed schedule, TimescaleDB uses the initial_start time to compute the next start time. When TimescaleDB finishes executing a policy, it picks the next available time on the schedule, skipping any candidate start times that have already passed.
  When you set the next_start time, it only changes the start time of the next immediate execution. It does not change the computation of the next scheduled execution after that next execution. To change the schedule so a policy starts at a specific time, you need to set initial_start. To change the next immediate execution, you need to set next_start. For example, to modify a policy to execute on a fixed schedule 15 minutes past the hour, and every hour, you need to set both initial_start and next_start using alter_job:
```
select * from alter_job(1000, fixed_schedule => true, initial_start => '2025-07-11 10:15:00', next_start => 
'2025-07-11 11:15:00');
```

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.
`next_start`	TIMESTAMPTZ	-	✖	Set the start time of the next immediate execution. It does not change the computation of the next scheduled time after the next execution.
`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`

add_columnstore_policy()

Samples

Arguments

Related Content