> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Hypertables and chunks

> SQL commands and functions for creating and managing hypertables and chunks

export const HYPERCORE = 'hypercore';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERTABLE_CAP = 'Hypertable';

export const CHUNK = 'chunk';

export const PG = 'Postgres';

export const CLOUD_LONG = 'Tiger Cloud';

{TIMESCALE_DB} 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—{PG} tables that automatically partition your time-series
data by time and optionally by other dimensions. When you run a query, {TIMESCALE_DB} identifies the correct partition,
called a {CHUNK}, and runs the query on it, instead of going through the entire table.

![Hypertable structure][hypertable-structure]

{HYPERTABLE_CAP}s offer the following benefits:

* **Efficient data management with [automated partitioning by time][change-chunk-intervals]**: {TIMESCALE_DB} splits your data into {CHUNK}s 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][hypertables-and-unique-indexes]**: 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][chunk-skipping]**: {TIMESCALE_DB} skips the {CHUNK}s 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][hyperfunctions]**: {TIMESCALE_DB} 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 {PG} tables. All the optimization magic happens behind the scenes.

<Note>
  Inheritance is not supported for hypertables and may lead to unexpected behavior.
</Note>

[change-chunk-intervals]: /manage-data/capabilities/hypertables/improve-query-performance#optimize-hypertable-chunk-intervals

[chunk-skipping]: /manage-data/capabilities/hypertables/improve-query-performance#enable-chunk-skipping

[hyperfunctions]: /api-reference/timescaledb/hyperfunctions/index

[hypertable-structure]: https://assets.timescale.com/docs/images/hypertable.png

[hypertables-and-unique-indexes]: /manage-data/capabilities/hypertables/hypertables-and-unique-indexes

For more information about using {HYPERTABLE}s, including {CHUNK} size partitioning, see the [hypertable
documentation][hypertable-docs].

## Create a hypertable

To create a {HYPERTABLE} for your time-series data, use [`CREATE TABLE`][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:

```sql theme={"dark"}
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 ...][hypertable-create-table], the default partitioning
column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
[columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
interval equal to the value of the [chunk\_interval][create_table_arguments], 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} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
`created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
[remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

[alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

[create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

<Note>
  For {TIMESCALE_DB} [v2.23.0][tsdb-release-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,
  {TIMESCALE_DB} throws an error and asks for an explicit definition. For earlier versions, set `partition_column` to a
  time column.

  If you are self-hosting {TIMESCALE_DB} [v2.20.0][tsdb-release-2-23-0] to [v2.22.1][tsdb-release-2-23-0], to convert your
  data to the {COLUMNSTORE} after a specific time interval, you have to call [add\_columnstore\_policy][add_columnstore_policy] after you call
  [CREATE TABLE][hypertable-create-table]

  If you are self-hosting {TIMESCALE_DB} [v2.19.3][tsdb-release-2-19-3] and below, create a [{PG} relational table][pg-create-table],
  then convert it using [create\_hypertable][create_hypertable]. You then enable {HYPERCORE} with a call
  to [ALTER TABLE][alter_table_hypercore].

  [pg-create-table]: https://www.postgresql.org/docs/current/sql-createtable.html

  [create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable

  [alter_table_hypercore]: /api-reference/timescaledb/hypercore/alter_table

  [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

  [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

  [chunk_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval

  [tsdb-release-2-23-0]: https://github.com/timescale/timescaledb/releases/tag/2.23.0

  [tsdb-release-2-20-0]: https://github.com/timescale/timescaledb/releases/tag/2.20.0

  [tsdb-release-2-22-1]: https://github.com/timescale/timescaledb/releases/tag/2.22.1

  [tsdb-release-2-19-3]: https://github.com/timescale/timescaledb/releases/tag/2.19.3
</Note>

## Samples

### Create a hypertable

Create a {HYPERTABLE} using the `CREATE TABLE` syntax with {HYPERCORE} for optimal performance:

```sql theme={"dark"}
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 {CHUNK}s older than 3 months to manage storage:

```sql theme={"dark"}
SELECT drop_chunks('conditions', INTERVAL '3 months');
```

### View chunk information

Get detailed information about {CHUNK}s for a {HYPERTABLE}:

```sql theme={"dark"}
SELECT show_chunks('conditions');
```

### Add a space dimension

Add a second partitioning dimension for multi-dimensional data:

```sql theme={"dark"}
SELECT add_dimension('conditions', 'location', number_partitions => 4);
```

## Available functions

### Table creation

* [`CREATE TABLE`][create_table]: create a {HYPERTABLE} using standard SQL syntax with {HYPERCORE}

### Chunk management

* [`show_chunks()`][show_chunks]: display {CHUNK}s associated with {HYPERTABLE}s
* [`drop_chunks()`][drop_chunks]: remove {CHUNK}s from {HYPERTABLE}s
* [`move_chunk()`][move_chunk]: move a {CHUNK} to a different tablespace
* [`reorder_chunk()`][reorder_chunk]: reorder a single {CHUNK} by an index
* [`merge_chunks()`][merge_chunks]: merge multiple {CHUNK}s into a single {CHUNK}
* [`split_chunk()`][split_chunk]: split a {CHUNK} into multiple {CHUNK}s
* [`attach_chunk()`][attach_chunk]: attach a table as a {CHUNK} to a {HYPERTABLE}
* [`detach_chunk()`][detach_chunk]: detach a {CHUNK} from a {HYPERTABLE}
* [`set_chunk_time_interval()`][set_chunk_time_interval]: set the time interval for {CHUNK} creation
* [`set_integer_now_func()`][set_integer_now_func]: set function to compute current time for integer-based times

### Dimension management

* [`add_dimension()`][add_dimension]: add a space-partitioning dimension to a {HYPERTABLE}

### Size and statistics

* [`hypertable_size()`][hypertable_size]: get the total disk space used by a {HYPERTABLE}
* [`hypertable_detailed_size()`][hypertable_detailed_size]: get detailed disk space usage for a {HYPERTABLE}
* [`hypertable_index_size()`][hypertable_index_size]: get the total size of indexes on a {HYPERTABLE}
* [`hypertable_approximate_size()`][hypertable_approximate_size]: get an approximate total size of a {HYPERTABLE}
* [`hypertable_approximate_detailed_size()`][hypertable_approximate_detailed_size]: get approximate detailed size
  information
* [`chunks_detailed_size()`][chunks_detailed_size]: get detailed size information for {CHUNK}s

### Tablespace management

* [`attach_tablespace()`][attach_tablespace]: attach a tablespace to a {HYPERTABLE}
* [`detach_tablespace()`][detach_tablespace]: detach a tablespace from a {HYPERTABLE}
* [`detach_tablespaces()`][detach_tablespaces]: detach all tablespaces from a {HYPERTABLE}
* [`show_tablespaces()`][show_tablespaces]: show tablespaces attached to a {HYPERTABLE}

### Reordering and policies

* [`add_reorder_policy()`][add_reorder_policy]: add a policy to automatically reorder {CHUNK}s
* [`remove_reorder_policy()`][remove_reorder_policy]: remove an automatic {CHUNK} reordering policy

### Query optimization

* [`enable_chunk_skipping()`][enable_chunk_skipping]: enable {CHUNK} skipping for a {HYPERTABLE}
* [`disable_chunk_skipping()`][disable_chunk_skipping]: disable {CHUNK} skipping for a {HYPERTABLE}

### Legacy functions

For backward compatibility, {TIMESCALE_DB} also provides [`create_hypertable()`][create_hypertable], which was the
original function for creating {HYPERTABLE}s. Use [`CREATE TABLE`][create_table] for new {HYPERTABLE}s.

[add_dimension]: /api-reference/timescaledb/hypertables/add_dimension

[add_reorder_policy]: /api-reference/timescaledb/hypertables/add_reorder_policy

[attach_chunk]: /api-reference/timescaledb/hypertables/attach_chunk

[attach_tablespace]: /api-reference/timescaledb/hypertables/attach_tablespace

[chunks_detailed_size]: /api-reference/timescaledb/hypertables/chunks_detailed_size

[create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable

[create_table]: /api-reference/timescaledb/hypertables/create_table

[detach_chunk]: /api-reference/timescaledb/hypertables/detach_chunk

[detach_tablespace]: /api-reference/timescaledb/hypertables/detach_tablespace

[detach_tablespaces]: /api-reference/timescaledb/hypertables/detach_tablespaces

[disable_chunk_skipping]: /api-reference/timescaledb/hypertables/disable_chunk_skipping

[drop_chunks]: /api-reference/timescaledb/hypertables/drop_chunks

[enable_chunk_skipping]: /api-reference/timescaledb/hypertables/enable_chunk_skipping

[hypertable-docs]: /manage-data/timescaledb/data-management/hypertables/understand-hypertables

[hypertable_approximate_detailed_size]: /api-reference/timescaledb/hypertables/hypertable_approximate_detailed_size

[hypertable_approximate_size]: /api-reference/timescaledb/hypertables/hypertable_approximate_size

[hypertable_detailed_size]: /api-reference/timescaledb/hypertables/hypertable_detailed_size

[hypertable_index_size]: /api-reference/timescaledb/hypertables/hypertable_index_size

[hypertable_size]: /api-reference/timescaledb/hypertables/hypertable_size

[merge_chunks]: /api-reference/timescaledb/hypertables/merge_chunks

[move_chunk]: /api-reference/timescaledb/hypertables/move_chunk

[remove_reorder_policy]: /api-reference/timescaledb/hypertables/remove_reorder_policy

[reorder_chunk]: /api-reference/timescaledb/hypertables/reorder_chunk

[set_chunk_time_interval]: /api-reference/timescaledb/hypertables/set_chunk_time_interval

[set_integer_now_func]: /api-reference/timescaledb/hypertables/set_integer_now_func

[show_chunks]: /api-reference/timescaledb/hypertables/show_chunks

[show_tablespaces]: /api-reference/timescaledb/hypertables/show_tablespaces

[split_chunk]: /api-reference/timescaledb/hypertables/split_chunk