> ## Documentation Index > Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # add_columnstore_policy() > Set a policy to automatically move chunks in a hypertable to the columnstore when they reach a given age. export const CAGG = 'continuous aggregate'; export const ROWSTORE = 'rowstore'; export const CHUNK = 'chunk'; export const HYPERTABLE = 'hypertable'; export const HYPERCORE = 'hypercore'; export const PG = 'Postgres'; export const COLUMNSTORE = 'columnstore'; export const TIMESCALE_DB = 'TimescaleDB'; Since [2.18.0][tsdb-2.18.0] `add_columnstore_policy()` replaces `add_compression_policy()`, deprecated in 2.18.0. Create a [job][job] that automatically moves {CHUNK}s 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 {CAGG}, 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 ...][hypertable-create-table], the default partitioning column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_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} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries. You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy]. You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy When {COLUMNSTORE} is enabled, [bloom filters][bloom-filters] are enabled by default, and every new {CHUNK} has a bloom index. Bloom indexes are not retrofitted, existing {CHUNK}s need to be fully recompressed to have the bloom indexes present. If you converted {CHUNK}s to {COLUMNSTORE} using {TIMESCALE_DB} [v2.19.3][tsdb-2.19.3] or below, to enable bloom filters on that data you have to convert those {CHUNK}s 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][informational-views]. 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 {CHUNK}s that have not yet been converted to {COLUMNSTORE}. The existing {CHUNK}s in the {COLUMNSTORE} remain unchanged. This means that {CHUNK}s with different {COLUMNSTORE} settings can co-exist in the same {HYPERTABLE}. ## Samples To create a {COLUMNSTORE} job: 1. **Enable {COLUMNSTORE}** For [efficient queries][secondary-indexes] 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}][hypertable-create-table]. The columnstore policy is created automatically. ```sql theme={"dark"} 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 {TIMESCALE_DB} [v2.23.0][tsdb-release-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, {TIMESCALE_DB} throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a time column. If you are self-hosting {TIMESCALE_DB} [v2.20.0][tsdb-release-2-23-0] to [v2.22.1][tsdb-release-2-23-0], to convert your data to the {COLUMNSTORE} after a specific time interval, you have to call [add\_columnstore\_policy][add_columnstore_policy] after you call [CREATE TABLE][hypertable-create-table] If you are self-hosting {TIMESCALE_DB} [v2.19.3][tsdb-release-2-19-3] and below, create a [{PG} relational table][pg-create-table], then convert it using [create\_hypertable][create_hypertable]. You then enable {HYPERCORE} with a call to [ALTER TABLE][alter_table_hypercore]. [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval [tsdb-release-2-23-0]: https://github.com/timescale/timescaledb/releases/tag/2.23.0 [tsdb-release-2-20-0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0 [tsdb-release-2-22-1]: https://github.com/timescale/timescaledb/releases/tag/2.22.1 [tsdb-release-2-19-3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3 * [Use `ALTER MATERIALIZED VIEW` for a {CAGG}][compression_continuous-aggregate] ```sql theme={"dark"} ALTER MATERIALIZED VIEW assets_candlestick_daily SET ( timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol'); ``` 2. **Add a policy to move {CHUNK}s to the {COLUMNSTORE} at a specific time interval** For example: * 60 days after the data was added to the table: ```sql theme={"dark"} CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '60d'); ``` * 3 months prior to the moment you run the query: ```sql theme={"dark"} CALL add_columnstore_policy('crypto_ticks', created_before => INTERVAL '3 months'); ``` * With an integer-based time column: ```sql theme={"dark"} CALL add_columnstore_policy('table_with_bigint_time', BIGINT '600000'); ``` * Older than eight weeks: ```sql theme={"dark"} CALL add_columnstore_policy('cpu_weekly', INTERVAL '8 weeks'); ``` * Control the time your policy runs: When you use a policy with a fixed schedule, {TIMESCALE_DB} uses the `initial_start` time to compute the next start time. When {TIMESCALE_DB} 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`: ```sql theme={"dark"} 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** ```sql theme={"dark"} SELECT * FROM timescaledb_information.jobs WHERE proc_name='policy_compression'; ``` See [timescaledb\_information.jobs][informational-views]. ## Arguments The syntax is: ```sql theme={"dark"} 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 {CAGG} to run this [job][job] on. | | `after` | INTERVAL or INTEGER | - | ✖ | Add {CHUNK}s 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][set_integer_now_func].
`after` is mutually exclusive with `created_before`. | | `created_before` | INTERVAL | NULL | ✖ | Add {CHUNK}s with a creation time of `now() - created_before` to the {COLUMNSTORE}.
`created_before` is
  • Not supported for {CAGG}s.
  • Mutually exclusive with `after`.
| | `schedule_interval` | INTERVAL | 12 hours when [chunk\_time\_interval][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 | `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. [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job/#samples [bloom-filters]: https://en.wikipedia.org/wiki/Bloom_filter [chunk_time_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval [compression_alter-table]: /api-reference/timescaledb/hypercore/alter_table [compression_continuous-aggregate]: /api-reference/timescaledb/continuous-aggregates/alter_materialized_view [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table/#arguments [hypercore]: /manage-data/capabilities/hypercore/ [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table [hypertables-section]: /manage-data/capabilities/hypertables [informational-views]: /api-reference/timescaledb/informational-views/jobs [job]: /api-reference/timescaledb/jobs-automation/add_job [next-start]: /api-reference/timescaledb/informational-views/jobs/#arguments [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy [secondary-indexes]: /manage-data/capabilities/hypercore/secondary-indexes/ [set_integer_now_func]: /api-reference/timescaledb/hypertables/set_integer_now_func [tsdb-2.18.0]: https://github.com/timescale/timescaledb/releases/tag/2.18.0 [tsdb-2.19.3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3