add_columnstore_policy()

Create a job 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 ..., the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk_interval, 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 chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

  You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

  You can also manually convert chunks in a hypertable to the columnstore.

When columnstore is enabled, bloom filters 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 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.

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.

To create a columnstore job:

• Enable columnstore

  For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data.

  • Use ALTER MATERIALIZED VIEW for a continuous aggregate

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

    Copy

  • Use CREATE TABLE for a hypertable. 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'
    );

    Copy

    For TimescaleDB v2.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 to v2.22.1, to convert your data to the columnstore after a specific time interval, you have to call add_columnstore_policy after you call CREATE TABLE

    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.

• 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');

    Copy

  • 3 months prior to the moment you run the query:

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

    Copy

  • With an integer-based time column:

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

    Copy

  • Older than eight weeks:

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

    Copy

  • 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');

    Copy

• View the policies that you set or the policies that already exist

  SELECT * FROM timescaledb_information.jobs
  WHERE proc_name='policy_compression';

  Copy

  See timescaledb_information.jobs.

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