enable_chunk_skipping()

Enable range statistics for a specific column in a compressed hypertable. This tracks a range of values for that column per chunk. Used for chunk skipping during query optimization and applies only to the chunks created after chunk skipping is enabled.

Best practice is to enable range tracking on columns that are correlated to the partitioning column. In other words, enable tracking on secondary columns which are referenced in the WHERE clauses in your queries.

TimescaleDB supports min/max range tracking for the smallint, int, bigint, serial, bigserial, date, timestamp, and timestamptz data types. The min/max ranges are calculated when a chunk belonging to this hypertable is compressed using the compress_chunk function. The range is stored in start (inclusive) and end (exclusive) form in the chunk_column_stats catalog table.

This way you store the min/max values for such columns in this catalog table at the per-chunk level. These min/max range values do not participate in partitioning of the data. These ranges are used for chunk skipping when the WHERE clause of an SQL query specifies ranges on the column.

A DROP COLUMN on a column with statistics tracking enabled on it ends up removing all relevant entries from the catalog table.

A decompress_chunk invocation on a compressed chunk resets its entries from the chunk_column_stats catalog table since now it's available for DML and the min/max range values can change on any further data manipulation in the chunk.

By default, this feature is disabled. To enable chunk skipping, set timescaledb.enable_chunk_skipping = on in postgresql.conf. When you upgrade from a database instance that uses compression but does not support chunk skipping, you need to recompress the previously compressed chunks for chunk skipping to work.

In this sample, you create the conditions hypertable with partitioning on the time column. You then specify and enable additional columns to track ranges for.

CREATE TABLE conditions (
   time        TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   device      TEXT              NOT NULL,
   temperature DOUBLE PRECISION  NULL,
   humidity    DOUBLE PRECISION  NULL
) WITH (
   tsdb.hypertable
);

SELECT enable_chunk_skipping('conditions', 'device_id');

Copy

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.