Hypertables and chunks

SQL commands and functions for creating and managing hypertables and chunks

TimescaleDB supercharges your real-time analytics by letting you run complex queries continuously, with near-zero latency. Under the hood, this is achieved by using hypertables—Postgres tables that automatically partition your time-series data by time and optionally by other dimensions. When you run a query, TimescaleDB identifies the correct partition, called a chunk, and runs the query on it, instead of going through the entire table.

Hypertables offer the following benefits:

• Efficient data management with automated partitioning by time: TimescaleDB splits your data into chunks that hold data from a specific time range. For example, one day or one week. You can configure this range to better suit your needs.

• Better performance with strategic indexing: an index on time in the descending order is automatically created when you create a hypertable. More indexes are created on the chunk level, to optimize performance. You can create additional indexes, including unique indexes, on the columns you need.

• Faster queries with chunk skipping: TimescaleDB skips the chunks that are irrelevant in the context of your query, dramatically reducing the time and resources needed to fetch results. Even more—you can enable chunk skipping on non-partitioning columns.

• Advanced data analysis with hyperfunctions: TimescaleDB enables you to efficiently process, aggregate, and analyze significant volumes of data while maintaining high performance.

To top it all, there is no added complexity—you interact with hypertables in the same way as you would with regular Postgres tables. All the optimization magic happens behind the scenes.

Inheritance is not supported for hypertables and may lead to unexpected behavior.

For more information about using hypertables, including chunk size partitioning, see the hypertable documentation.

Create a hypertable

To create a hypertable for your time-series data, use CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data. For example:

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,
  tsdb.segmentby = 'device',
  tsdb.orderby = 'time DESC'
);

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

You can also manually convert chunks in a hypertable to the columnstore.

For TimescaleDB v2.23.0 and higher, the table is automatically partitioned on the first column in the table with a timestamp data type. If multiple columns are suitable candidates as a partitioning column, TimescaleDB throws an error and asks for an explicit definition. For earlier versions, set partition_column to a time column.

If you are self-hosting TimescaleDB v2.20.0 to v2.22.1, to convert your data to the columnstore after a specific time interval, you have to call add_columnstore_policy after you call CREATE TABLE

If you are self-hosting TimescaleDB v2.19.3 and below, create a Postgres relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.

Samples

Create a hypertable

Create a hypertable using the CREATE TABLE syntax with hypercore for optimal performance:

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,
  tsdb.segmentby = 'device',
  tsdb.orderby = 'time DESC'
);

Drop old chunks

Remove chunks older than 3 months to manage storage:

SELECT drop_chunks('conditions', INTERVAL '3 months');

View chunk information

Get detailed information about chunks for a hypertable:

SELECT show_chunks('conditions');

Add a space dimension

Add a second partitioning dimension for multi-dimensional data:

SELECT add_dimension('conditions', 'location', number_partitions => 4);

Available functions

Table creation

• CREATE TABLE: create a hypertable using standard SQL syntax with hypercore

Chunk management

• show_chunks(): display chunks associated with hypertables
• drop_chunks(): remove chunks from hypertables
• move_chunk(): move a chunk to a different tablespace
• reorder_chunk(): reorder a single chunk by an index
• merge_chunks(): merge multiple chunks into a single chunk
• split_chunk(): split a chunk into multiple chunks
• attach_chunk(): attach a table as a chunk to a hypertable
• detach_chunk(): detach a chunk from a hypertable
• set_chunk_time_interval(): set the time interval for chunk creation
• set_integer_now_func(): set function to compute current time for integer-based times

Dimension management

• add_dimension(): add a space-partitioning dimension to a hypertable

Size and statistics

• hypertable_size(): get the total disk space used by a hypertable
• hypertable_detailed_size(): get detailed disk space usage for a hypertable
• hypertable_index_size(): get the total size of indexes on a hypertable
• hypertable_approximate_size(): get an approximate total size of a hypertable
• hypertable_approximate_detailed_size(): get approximate detailed size information
• chunks_detailed_size(): get detailed size information for chunks

Tablespace management

• attach_tablespace(): attach a tablespace to a hypertable
• detach_tablespace(): detach a tablespace from a hypertable
• detach_tablespaces(): detach all tablespaces from a hypertable
• show_tablespaces(): show tablespaces attached to a hypertable

Reordering and policies

• add_reorder_policy(): add a policy to automatically reorder chunks
• remove_reorder_policy(): remove an automatic chunk reordering policy

Query optimization

• enable_chunk_skipping(): enable chunk skipping for a hypertable
• disable_chunk_skipping(): disable chunk skipping for a hypertable

Legacy functions

For backward compatibility, TimescaleDB also provides create_hypertable(), which was the original function for creating hypertables. Use CREATE TABLE for new hypertables.