Choosing the Right chunk_time_interval for Your Workload

Every time someone creates a TimescaleDB hypertable without specifying chunk_time_interval, they inherit a 7-day default. For some workloads, this is fine. For most, it quietly causes either planning overhead (too many chunks) or operational inflexibility (chunks too large to manage efficiently). The right interval is a function of your ingest rate, and it takes about 30 seconds to calculate.

The Core Tradeoff

A TimescaleDB chunk is a real PostgreSQL table. Each one has its own CHECK constraint, indexes, and storage. The interval you choose determines how many of these tables exist and how large each one is.

Short intervals (like 1 hour) produce many small chunks. This gives you fine-grained retention -- you can drop any single hour of data instantly. But every query forces the planner to evaluate every chunk's constraint. At thousands of chunks, planning time alone can exceed 200ms per query.

Long intervals (like 30 days) produce few large chunks. Planning is nearly instant, but each chunk holds billions of rows. Compression takes longer. Decompression takes longer. Retention drops data in 30-day blocks -- if your compliance says 90 days, you might store 120 days because you cannot drop a partial chunk.

The Math

TimescaleDB recommends targeting approximately 25 million rows per chunk:

chunk_interval_seconds = 25,000,000 / rows_per_second

For different ingest rates:

Measure your actual rate:

SELECT
    count(*) AS total_rows,
    round(count(*) / 86400.0, 1) AS rows_per_second
FROM sensor_readings
WHERE recorded_at >= now() - INTERVAL '1 day';

Real-World Impact

Take 259 million rows (100 rows/sec over 30 days). Same data, three intervals:

With the 1-hour interval, even a simple query that touches a single day of data pays a 200ms+ planning tax because the planner evaluates all 720 chunks. After a year without retention, that becomes 8,640 chunks and planning times well over a second.

The 1-day interval balances chunk count against retention granularity. Thirty chunks per month, daily drop granularity, and planning overhead under 10ms.

Check your own planning overhead:

EXPLAIN ANALYZE
SELECT count(*) FROM sensor_readings
WHERE recorded_at >= now() - INTERVAL '1 hour';
-- Planning Time under 10ms = healthy
-- Planning Time over 50ms = investigate chunk count

Compression Interactions

Chunk interval interacts with compression in two ways.

First, larger chunks compress better. Column encoders have more data per chunk to find patterns in. A 2 MB chunk compresses quickly but may achieve a lower ratio than a 50 MB chunk.

Second, the compress_after setting must be at least 1-2x your chunk interval. Compressing a chunk while it is still receiving inserts causes decompress-modify-recompress cycles. These waste I/O and can block background worker slots.

-- Interval is 1 day, so compress_after should be at least 2 days
SELECT set_chunk_time_interval('sensor_readings', INTERVAL '1 day');
SELECT add_compression_policy('sensor_readings', compress_after => INTERVAL '2 days');

Changing the Interval

set_chunk_time_interval() only applies to new chunks. Existing chunks keep their original interval forever.

SELECT set_chunk_time_interval('sensor_readings', INTERVAL '3 days');

This means you will have a mixed-interval hypertable until old chunks are dropped by retention. The planner handles this correctly, but the chunk count reduction is gradual.

For a full reset -- necessary when old chunks are causing excessive planning overhead -- you need to create a new hypertable and migrate data:

CREATE TABLE sensor_readings_new (LIKE sensor_readings INCLUDING ALL);
SELECT create_hypertable('sensor_readings_new', 'recorded_at',
    chunk_time_interval => INTERVAL '3 days');

INSERT INTO sensor_readings_new
SELECT * FROM sensor_readings
WHERE recorded_at >= now() - INTERVAL '30 days'
ORDER BY recorded_at;

BEGIN;
ALTER TABLE sensor_readings RENAME TO sensor_readings_old;
ALTER TABLE sensor_readings_new RENAME TO sensor_readings;
COMMIT;

DROP TABLE sensor_readings_old;

Do this during a maintenance window. For very large tables, batch the copy in time segments to avoid oversized transactions and WAL disk pressure.

Chunk Count Monitoring

Verify your current state:

SELECT
    hypertable_name,
    count(*) AS total_chunk_count,
    pg_size_pretty(avg(total_bytes)) AS average_chunk_size
FROM timescaledb_information.chunks
GROUP BY hypertable_name
ORDER BY total_chunk_count DESC;

If any hypertable has more than 1,000 chunks, it is worth investigating whether the interval is too small or retention is not configured. Even at the correct interval, unbounded retention eventually produces too many chunks.

Rules of Thumb

• Calculate, don't guess. Measure rows_per_second, divide 25M by it, round to a clean interval.
• Always set a retention policy. It caps total chunk count regardless of ingest rate.
• Revisit when traffic changes. A 10x ingest increase means the interval should shrink by 10x.
• Set compress_after to 1-2x the interval. Avoid compressing active chunks.
• Watch planning time. Over 50ms in EXPLAIN ANALYZE output is the early warning sign.

The default 7-day interval was chosen as a reasonable middle ground. It is correct for ~40-50 rows per second. For everything above or below that range, spend 30 seconds on the calculation and set the interval explicitly.