About hypertables

In TimescaleDB, hypertables exist alongside regular PostgreSQL tables. Use hypertables to store time-series data. This gives you improved insert and query performance, and access to useful time-series features. Use regular PostgreSQL tables for other relational data.

When you create and use a hypertable, it automatically partitions data by time, and optionally by space.

Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. If the hypertable is also partitioned by space, each chunk is also assigned a subset of the space values.

Each chunk of a hypertable only holds data from a specific time range. When you insert data from a time range that doesn’t yet have a chunk, TimescaleDB automatically creates a chunk to store it.

By default, each chunk covers 7 days. You can change this to better suit your needs. For example, if you set to 1 day, each chunk stores data from the same day. Data from different days is stored in different chunks.

note

TimescaleDB divides time into potential chunk ranges, based on the chunk_time_interval. If data exists for a potential chunk range, that chunk is created.

Chunk size affects insert and query performance. You want a chunk small enough to fit into memory. This allows you to insert and query recent data without reading from disk. But you don’t want too many small and sparsely filled chunks. This can affect query planning time and compression.

We recommend setting the chunk_time_interval so that 25% of main memory can store one chunk, including its indexes, from each active hypertable. You can estimate the required interval from your data rate. For example, if you write approximately 2 GB of data per day and have 64 GB of memory, set the interval to 1 week. If you write approximately 10 GB of data per day on the same machine, set the time interval to 1 day.

note

If you use expensive index types, such as some PostGIS geospatial indexes, take care to check the total size of the chunk and its index. You can do so using the function.

For a detailed analysis of how to optimize your chunk sizes, see the blog post on chunk time intervals. To learn how to view and set your chunk time intervals, see the section on .

Space partitioning is optional. It is not usually recommended for regular hypertables, except in very particular circumstances. It is recommended for distributed hypertables, to balance inserts between nodes. For more information, see the sections on and distributed hypertables.

When space partitioning is on, 2 dimensions are used to divide data into chunks: the time dimension and the space dimension. You can specify the number of partitions along the space dimension. Data is assigned to a partition by hashing its value on that dimension.

For example, say you use as a space partitioning column. For each row, the value of the device_id column is hashed. Then the row is inserted into the correct partition for that hash value.

Space partitioning dimensions can be open or closed. A closed dimension has a fixed number of partitions, and usually uses some hashing to match values to partitions. An open dimension does not have a fixed number of partitions, and usually has each chunk cover a certain range. In most cases the time dimension is open and the space dimension is closed.

If you use the create_hypertable command to create your hypertable, then the space dimension is open, and there is no way to adjust this. To create a hypertable with a closed space dimension, create the hypertable with only the time dimension first. Then use the command to explicitly add an open device. If you set the range to 1, each device has its own chunks. This can help you work around some limitations of normal space dimensions, and is especially useful if you want to make some chunks readily available for exclusion.

Space partitioning is not usually recommended for non-distributed hypertables. It’s only useful if you have multiple physical disks, each corresponding to a separate tablespace. Each disk can then store some of the space partitions. If you partition by space without this setup, you increase query planning complexity without increasing I/O performance.

note

A more recommended way to increase I/O performance is to use RAID (redundant array of inexpensive disks). RAID virtualizes multiple physical disks into a single logical disk. You can then use this single logical disk to store your hypertable, without any space partitioning.

By default, indexes are automatically created when you create a hypertable. You can prevent index creation by setting the create_default_indexes option to .

The default indexes are:

• On all hypertables, an index on time, descending
• On hypertables with space partitions, an index on the space parameter and time

Hypertables have some restrictions on unique constraints and indexes. If you want a unique index on a hypertable, it must include all the partitioning columns for the table. To learn more, see the section on .