enable_chunk_skipping()

Early access 2.16.0

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 added to the columnstore using the convert_to_columnstore 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 convert_to_rowstore 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.

Samples

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

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 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.

Arguments

The syntax is:

SELECT enable_chunk_skipping(
    hypertable = '<hypertable_name>',
    column_name = '<column_name>',
    if_not_exists = true | false
);

Name	Type	Default	Required	Description
column_name	NAME	-	✔	Column to track range statistics for
hypertable	REGCLASS	-	✔	hypertable that the column belongs to
if_not_exists	BOOLEAN	false	✖	Set to true so that a notice is sent when ranges are not being tracked for a column. By default, an error is thrown

Returns

Column	Type	Description
column_stats_id	INTEGER	ID of the entry in the TimescaleDB internal catalog
enabled	BOOLEAN	Returns true when tracking is enabled, if_not_exists is true, and when a new entry is not added