Partition a hypertable

Every hypertable is partitioned on at least one column, called a dimension. The primary dimension is almost always time. Each chunk is assigned a range of time and only contains data from that range.

Time partitioning

Partition by a timestamptz, timestamp, date, or UUIDv7 column. Best practice is to use timestamptz.

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

By default, each chunk holds data for 7 days. You can change this with timescaledb.chunk_interval to better suit your workload.

TimescaleDB divides time into potential chunk ranges based on the interval. When you insert data from a time range that doesn’t yet have a chunk, TimescaleDB automatically creates one. This means the start time of your earliest chunk does not necessarily equal the earliest timestamp in your data — there might be a gap. This doesn’t affect queries, but you may notice it when inspecting chunks.

Integer partitioning

For tables where the partition column is an integer (Unix epoch, sequence, or other monotonic key), specify the chunk interval as a number:

CREATE TABLE events (
  epoch_ms  BIGINT NOT NULL,
  device_id INT,
  payload   JSONB
) WITH (
  timescaledb.hypertable,
  timescaledb.partition_column = 'epoch_ms',
  timescaledb.chunk_interval = 86400000
);

If you use background policies like retention or columnstore, set integer_now_func so those policies know what “current” means:

CREATE OR REPLACE FUNCTION epoch_ms_now() RETURNS BIGINT
LANGUAGE SQL STABLE AS $$
  SELECT (extract(epoch FROM now()) * 1000)::BIGINT
$$;

SELECT set_integer_now_func('events', 'epoch_ms_now');

For Unix time, you can reuse the same function across hypertables. For sequence-style keys, you need a dedicated function per hypertable.

The default chunk interval of 7 days works for many workloads, but tuning it can significantly improve performance. See Sizing hypertable chunks for guidance.

Space partitioning (hash dimension)

TimescaleDB supports adding a hash dimension to a hypertable with add_dimension(), which distributes rows across multiple partitions within each chunk. For example, you could partition by tenant_id in a multi-tenant setup to isolate tenants into separate partitions.

Warning

Space partitioning multiplies the number of chunks, increases query planning overhead, and requires all unique constraints to include the hash column. Do not use it without extensive testing and a deep understanding of how it affects your specific workload. For most use cases, time-based chunk sizing and indexing are a better fit.

If you have a specific use case that requires hash partitioning, add a hash dimension after creating the hypertable:

SELECT add_dimension('my_table', by_hash('device_id', 4));

The second argument to by_hash() is the number of partitions. You must specify it explicitly — there is no default.

Learn more

• Sizing hypertable chunks: Choosing the right chunk interval for your workload.
• Create and configure a hypertable: CREATE TABLE options including chunk_interval.
• Primary keys, time columns, and uniqueness: Constraint rules for partitioned tables.
• Improve hypertable performance: Optimize chunk intervals and enable chunk skipping.
• set_integer_now_func() reference: Required for integer-partitioned policies.
• add_dimension() reference: Add hash or range dimensions.
• set_chunk_time_interval() reference: Change the chunk interval on an existing hypertable.