enable_chunk_skipping()

Enable range tracking for columns of chunks from a hypertable

Early access Since 2.16.0

Enable range statistics for a specific column in a compressed . This tracks a range of values for that column per . Used for skipping during query optimization and applies only to the s created after 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 belonging to this 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- level. These min/max range values do not participate in partitioning of the data. These ranges are used for 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 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 .

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

Samples

In this sample, you create the conditions 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 using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , 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 conversion, s 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 the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert s in a to the .

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	-	✔	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 internal catalog
enabled	BOOLEAN	Returns true when tracking is enabled, if_not_exists is true, and when a new entry is not added