Skip to content
Search ⌘K
Get started

Size hypertable chunks

Choose a chunk interval that balances memory usage, query planning, and columnstore effectiveness

Chunk size affects insert and query performance. You want chunks small enough to fit into memory so you can insert and query recent data without reading from disk. However, too many small chunks can slow query planning and reduce columnstore effectiveness.

How the chunk interval works

Section titled “How the chunk interval works”

The chunk interval is the time range each chunk covers. It determines how many chunks you get for a given time range:

  • Default: 7 days for timestamp columns. For integer columns, defaults vary by type (smallint: 10,000, integer: 100,000, bigint: 1,000,000).
  • New chunks only: Changing the interval with set_chunk_time_interval() affects only chunks created after the change. Existing chunks keep their original boundaries.
  • Minimum: For timestamp types, intervals shorter than 1 second trigger a warning.

Sizing guideline

Section titled “Sizing guideline”

Set chunk_interval so that the indexes of chunks currently being ingested into fit within 25% of main memory (shared_buffers).

PostgreSQL builds indexes on the fly during ingestion. When an index doesn’t fit in memory, it is constantly flushed to disk and read back, wasting I/O that would otherwise be used for writes.

Example: On a system with 64 GB of memory, shared_buffers is typically ~16 GB.

  • If index growth is approximately 2 GB per day, a 7-day interval is appropriate (2 GB x 7 = 14 GB, fits in 16 GB).
  • If index growth is around 10 GB per day, use a 1-day interval (10 GB, fits in 16 GB).

Active vs inactive chunks

Section titled “Active vs inactive chunks”
  • Active chunks receive new writes and their indexes should fit comfortably in cache.
  • Inactive chunks are older and mostly read-only — they can rely more on disk, especially after conversion to columnstore.

Too many chunks

Section titled “Too many chunks”

Having too many chunks in the system has costs:

  • Query planning: The planner must evaluate each chunk to decide whether to include or skip it. More chunks means more planning time.
  • Columnstore: Very small chunks compress less effectively because there is less data to find patterns in.
  • System limits: Limit the number of hypertables in your service — tens of thousands of hypertables is not recommended.

Setting the chunk interval

Section titled “Setting the chunk interval”

Set the interval at creation time:

CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  device_id   INT,
  value       DOUBLE PRECISION
) WITH (
  timescaledb.hypertable,
  timescaledb.chunk_interval = '1 day'
);

Or change it for an existing hypertable:

SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

You can also set a global default for all new hypertables:

SET timescaledb.default_chunk_time_interval = '1 day';

When you need to retrofit the chunk interval

Section titled “When you need to retrofit the chunk interval”

set_chunk_time_interval() only affects new chunks. If an undersized chunk_interval has already fragmented your hypertable into thousands of small chunks, calling set_chunk_time_interval() alone does not heal historical data — the old fragments remain and keep dragging on planner time and memory.

Watch for these symptoms, which indicate historical chunks need to be rebuilt:

  • Over 1,000 chunks in a single hypertable. Scanning chunk catalog metadata starts to dominate query planning.
  • Intermittent out-of-memory errors on wide time-range scans, caused by the volume of chunk metadata held in memory.
  • Low columnstore compression ratios on historical data — very small chunks compress poorly because there is less data to find patterns in.

When this happens, the fix is a table-swap migration: create a new hypertable with the correct chunk_interval, atomically swap names so ingestion is never blocked, then backfill historical data in batches. For a step-by-step walkthrough, see Retrofit chunk intervals on a production hypertable.

Learn more

Section titled “Learn more”