--- title: Hypertables and chunks | Tiger Data Docs description: SQL commands and functions for creating and managing hypertables and chunks --- Tiger Cloud 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, PostgreSQL tables that automatically partition your time-series data by time and optionally by other dimensions. When you run a query, Tiger Cloud identifies the correct partition, called chunk, and runs the query on it, instead of going through the entire table. ![Hypertable architecture: automatic time-based partitioning into chunks](/docs/_astro/hypertable.DZMukoho_Z2fUoYn.webp) Hypertables offer the following benefits: - **Efficient data management with [automated partitioning by time](/docs/build/performance-optimization/improve-hypertable-performance#optimize-hypertable-chunk-intervals/index.md)**: Tiger Cloud 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](/docs/build/performance-optimization/hypertables-and-unique-indexes/index.md)**: 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](/docs/build/performance-optimization/improve-hypertable-performance/index.md)**: Tiger Cloud 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](/docs/learn/hyperfunctions/about-hyperfunctions/index.md)**: Tiger Cloud 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 PostgreSQL tables. All the optimization magic happens behind the scenes. For more information about using hypertables, including chunk size partitioning, see the [hypertable documentation](/docs/learn/hypertables/understand-hypertables/index.md). ## Create a hypertable To create a hypertable for your time-series data, use [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table/index.md). 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 …](/docs/reference/timescaledb/hypertables/create_table/index.md), the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a [columnstore policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) that automatically converts your data to the columnstore, after an interval equal to the value of the [chunk\_interval](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md), defined through `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](/docs/reference/timescaledb/jobs-automation/alter_job/index.md). However, to change `after` or `created_before`, the compression settings, or the hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md). You can also manually [convert chunks](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md) in a hypertable to the columnstore. Note For TimescaleDB [v2.23.0](https://github.com/timescale/timescaledb/releases/tag/2.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](https://github.com/timescale/timescaledb/releases/tag/2.23.0) to [v2.22.1](https://github.com/timescale/timescaledb/releases/tag/2.23.0), to convert your data to the columnstore after a specific time interval, you have to call [add\_columnstore\_policy](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md) after you call [CREATE TABLE](/docs/reference/timescaledb/hypertables/create_table/index.md) If you are self-hosting TimescaleDB [v2.19.3](https://github.com/timescale/timescaledb/releases/tag/2.19.3) and below, create a [PostgreSQL relational table](https://www.postgresql.org/docs/current/sql-createtable.html), then convert it using [create\_hypertable](/docs/reference/timescaledb/hypertables/create_hypertable/index.md). You then enable hypercore with a call to [ALTER TABLE](/docs/reference/timescaledb/hypercore/alter_table/index.md). ## 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`](/docs/reference/timescaledb/hypertables/create_table/index.md): create a hypertable using standard SQL syntax with hypercore ### Chunk management - [`create_chunk()`](/docs/reference/timescaledb/hypertables/create_chunk/index.md): manually create a chunk with specific dimensional constraints - [`show_chunks()`](/docs/reference/timescaledb/hypertables/show_chunks/index.md): display chunks associated with hypertables - [`drop_chunk()`](/docs/reference/timescaledb/hypertables/drop_chunk/index.md): drop a single chunk from a hypertable - [`drop_chunks()`](/docs/reference/timescaledb/hypertables/drop_chunks/index.md): remove chunks from hypertables by time range - [`move_chunk()`](/docs/reference/timescaledb/hypertables/move_chunk/index.md): move a chunk to a different tablespace - [`reorder_chunk()`](/docs/reference/timescaledb/hypertables/reorder_chunk/index.md): reorder a single chunk by an index - [`merge_chunks()`](/docs/reference/timescaledb/hypertables/merge_chunks/index.md): merge multiple chunks into a single chunk - [`merge_chunks_concurrently()`](/docs/reference/timescaledb/hypertables/merge_chunks_concurrently/index.md): merge multiple chunks without blocking reads - [`split_chunk()`](/docs/reference/timescaledb/hypertables/split_chunk/index.md): split a chunk into multiple chunks - [`chunk_rewrite_cleanup()`](/docs/reference/timescaledb/hypertables/chunk_rewrite_cleanup/index.md): clean up state from an aborted chunk rewrite operation - [`attach_chunk()`](/docs/reference/timescaledb/hypertables/attach_chunk/index.md): attach a table as a chunk to a hypertable - [`detach_chunk()`](/docs/reference/timescaledb/hypertables/detach_chunk/index.md): detach a chunk from a hypertable - [`set_chunk_time_interval()`](/docs/reference/timescaledb/hypertables/set_chunk_time_interval/index.md): set the time interval for chunk creation - [`set_integer_now_func()`](/docs/reference/timescaledb/hypertables/set_integer_now_func/index.md): set function to compute current time for integer-based times ### Dimension management - [`add_dimension()`](/docs/reference/timescaledb/hypertables/add_dimension/index.md): add a space-partitioning dimension to a hypertable ### Size and statistics - [`hypertable_size()`](/docs/reference/timescaledb/hypertables/hypertable_size/index.md): get the total disk space used by a hypertable - [`hypertable_detailed_size()`](/docs/reference/timescaledb/hypertables/hypertable_detailed_size/index.md): get detailed disk space usage for a hypertable - [`hypertable_index_size()`](/docs/reference/timescaledb/hypertables/hypertable_index_size/index.md): get the total size of indexes on a hypertable - [`hypertable_approximate_size()`](/docs/reference/timescaledb/hypertables/hypertable_approximate_size/index.md): get an approximate total size of a hypertable - [`hypertable_approximate_detailed_size()`](/docs/reference/timescaledb/hypertables/hypertable_approximate_detailed_size/index.md): get approximate detailed size information - [`chunks_detailed_size()`](/docs/reference/timescaledb/hypertables/chunks_detailed_size/index.md): get detailed size information for chunks ### Tablespace management - [`attach_tablespace()`](/docs/reference/timescaledb/hypertables/attach_tablespace/index.md): attach a tablespace to a hypertable - [`detach_tablespace()`](/docs/reference/timescaledb/hypertables/detach_tablespace/index.md): detach a tablespace from a hypertable - [`detach_tablespaces()`](/docs/reference/timescaledb/hypertables/detach_tablespaces/index.md): detach all tablespaces from a hypertable - [`show_tablespaces()`](/docs/reference/timescaledb/hypertables/show_tablespaces/index.md): show tablespaces attached to a hypertable ### Reordering and policies - [`add_reorder_policy()`](/docs/reference/timescaledb/hypertables/add_reorder_policy/index.md): add a policy to automatically reorder chunks - [`remove_reorder_policy()`](/docs/reference/timescaledb/hypertables/remove_reorder_policy/index.md): remove an automatic chunk reordering policy ### Query optimization - [`enable_chunk_skipping()`](/docs/reference/timescaledb/hypertables/enable_chunk_skipping/index.md): enable chunk skipping for a hypertable - [`disable_chunk_skipping()`](/docs/reference/timescaledb/hypertables/disable_chunk_skipping/index.md): disable chunk skipping for a hypertable ### Legacy functions For backward compatibility, TimescaleDB also provides [`create_hypertable()`](/docs/reference/timescaledb/hypertables/create_hypertable/index.md), which was the original function for creating hypertables. Use [`CREATE TABLE`](/docs/reference/timescaledb/hypertables/create_table/index.md) for new hypertables.