--- title: add_columnstore_policy() | Tiger Data Docs description: Set a policy to automatically move chunks in a hypertable to the columnstore when they reach a given age. --- Since [2.18.0](https://github.com/timescale/timescaledb/releases/tag/2.18.0) `add_columnstore_policy()` replaces `add_compression_policy()`, deprecated in 2.18.0. Create a [job](/docs/reference/timescaledb/jobs-automation/add_job/index.md) that automatically moves chunks in a hypertable to the columnstore after a specific time interval. - **Continuous aggregates**: You first call `ALTER MATERIALIZED VIEW` to enable the columnstore on a continuous aggregate, then create the job that converts your data to the columnstore with a call to `add_columnstore_policy`. - **Hypertables**: When you create a hypertable using [CREATE TABLE … WITH …](/docs/reference/timescaledb/hypertables/create_table/index.md), the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that automatically converts your data to the columnstore, after an interval equal to the value of the [chunk\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md), defined through `after` in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by up to 98%, and organized for efficient, large-scale queries. You can customize this policy later using [alter\_job](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). However, to change `after` or `created_before`, the compression settings, or the hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md). You can also manually [convert chunks](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md) in a hypertable to the columnstore. When columnstore is enabled, [bloom filters](https://en.wikipedia.org/wiki/Bloom_filter) are enabled by default, and every new chunk has a bloom index. Bloom indexes are not retrofitted, existing chunks need to be fully recompressed to have the bloom indexes present. If you converted chunks to columnstore using TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.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. To view the policies that you set or the policies that already exist, see [informational views](/docs/reference/timescaledb/informational-views/jobs/index.md). 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. ## Samples To create a columnstore job: 1. **Enable columnstore** For [efficient queries](/docs/build/columnar-storage/setup-hypercore/index.md) on data in the columnstore, remember to `segmentby` the column you will use most often to filter your data. - [Use `CREATE TABLE` for a hypertable](/docs/reference/timescaledb/hypertables/create_table/index.md). The columnstore policy is created automatically. ``` CREATE TABLE crypto_ticks ( "time" TIMESTAMPTZ, symbol TEXT, price DOUBLE PRECISION, day_volume NUMERIC ) WITH ( tsdb.hypertable, tsdb.segmentby='symbol', tsdb.orderby='time DESC' ); ``` For TimescaleDB [v2.23.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting TimescaleDB [v2.20.0](https://github.com/timescale/timescaledb/releases/tag/2.23.0) to [v2.22.1](https://github.com/timescale/timescaledb/releases/tag/2.23.0), to convert your data to the columnstore after a specific time interval, you have to call [add\_columnstore\_policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) after you call [CREATE TABLE](/docs/reference/timescaledb/hypertables/create_table/index.md) If you are self-hosting TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.19.3) and below, create a [PostgreSQL relational table](https://www.postgresql.org/docs/current/sql-createtable.html), then convert it using [create\_hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md). You then enable hypercore with a call to [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md). - [Use `ALTER MATERIALIZED VIEW` for a continuous aggregate](/docs/reference/timescaledb/continuous-aggregates/alter_materialized_view/index.md) ``` ALTER MATERIALIZED VIEW assets_candlestick_daily SET ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol'); ``` 2. **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'); ``` 3. **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](/docs/reference/timescaledb/informational-views/jobs/index.md). ## Arguments The syntax is: ``` CALL add_columnstore_policy( hypertable = '', after = , if_not_exists = true | false, schedule_interval = , initial_start = , timezone = '', created_before = ); ``` 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](/docs/reference/timescaledb/jobs-automation/add_job/index.md) 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](/docs/reference/timescaledb/hypertables/set_integer_now_func/index.md).`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](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md) >= `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 | `NULL` | ✖ | Set the time this job is first run. | | `timezone` | TEXT | `NULL` | ✖ | 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` | ## Returns This function returns void.