Adjusting your hypertable chunk interval can improve performance in your database. This applies to both regular and distributed hypertables. For more information, see best practices for time partitioning.

Check the current setting for chunk intervals by querying the Timescale catalog:

SELECT h.table_name, c.interval_length
FROM _timescaledb_catalog.dimension c
JOIN _timescaledb_catalog.hypertable h
ON h.id = c.hypertable_id;

The result looks like this:

table_name | interval_length
-----------+-----------------
metrics | 604800000000
(1 row)
Note

Time-based interval lengths are reported in microseconds.

The default chunk interval is 7 days. To change this when creating a hypertable, specify a different chunk_time_interval when you create the hypertable. In this example, the table to convert is named conditions, and it stores time values in a column named time:

SELECT create_hypertable(
'conditions',
by_range('time', INTERVAL '1 day')
);

To change the chunk interval on an already existing hypertable or distributed hypertable, use the function set_chunk_time_interval. In this example, the hypertable is named conditions:

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');

When you change the chunk_time_interval, the new setting only applies to new chunks, not to existing chunks. In practice, this means setting an overly long interval might take a long time to correct. For example, if you set chunk_time_interval to 1 year and start inserting data, you can no longer shorten the chunk for that year. If you need to correct this situation, create a new hypertable and migrate your data.

While chunk turnover does not degrade performance, chunk creation does take longer lock time than a normal INSERT operation into a chunk that has already been created. This means that if multiple chunks are being created at the same time, the transactions block each other until the first transaction is completed.

Keywords

Found an issue on this page?

Report an issue!