> ## 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.

# Understand hypertables

> Hypertables are Postgres tables with special features that power real-time analytics on time-series and event data

export const SERVICE_SHORT = 'service';

export const HYPERTABLE = 'hypertable';

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

## Partition by time

Each {HYPERTABLE} is partitioned into child {HYPERTABLE}s called chunks. Each chunk is assigned
a range of time, and only contains data from that range.

### Time partitioning

Typically, you partition {HYPERTABLE}s on columns that hold time values.
[Best practice is to use `timestamptz`][postgresql-timestamp] column type. However, you can also partition on
`date`, `integer`, `timestamp` and [UUIDv7][uuidv7_functions] types.

By default, each {HYPERTABLE} chunk holds data for 7 days. You can change this to better suit your
needs. For example, if you set `chunk_interval` to 1 day, each chunk stores data for a single day.

{TIMESCALE_DB} divides time into potential chunk ranges, based on the `chunk_interval`. Each {HYPERTABLE} chunk holds
data for a specific time range only. When you insert data from a time range that doesn't yet have a chunk, {TIMESCALE_DB}
automatically creates a chunk to store it.

In practice, this means that the start time of your earliest chunk does not
necessarily equal the earliest timestamp in your {HYPERTABLE}. Instead, there
might be a time gap between the start time and the earliest timestamp. This
doesn't affect your usual interactions with your {HYPERTABLE}, but might affect
the number of chunks you see when inspecting it.

## Best practices for scaling and partitioning

Best practices for maintaining a high performance when scaling include:

* Limit the number of {HYPERTABLE}s in your {SERVICE_SHORT}; having tens of thousands of {HYPERTABLE}s is not recommended.
* Choose a strategic chunk size.

Chunk size affects insert and query performance. You want a chunk small enough
to fit into memory so you can insert and query recent data without
reading from disk. However, having too many small and sparsely filled chunks can
affect query planning time and compression. The more chunks in the system, the slower that process becomes, even more so
when all those chunks are part of a single hypertable.

{PG} builds the index on the fly during ingestion. That means that to build a new entry on the index,
a significant portion of the index needs to be traversed during every row insertion. When the index does not fit
into memory, it is constantly flushed to disk and read back. This wastes IO resources which would otherwise
be used for writing the heap/WAL data to disk.

The default chunk interval is 7 days. However, best practice is to set `chunk_interval` so that prior to processing,
the indexes for chunks currently being ingested into fit within 25% of main memory. For example, on a system with 64
GB of memory, if index growth is approximately 2 GB per day, a 1-week chunk interval is appropriate. If index growth is
around 10 GB per day, use a 1-day interval.

You set `chunk_interval` when you [create a {HYPERTABLE}][hypertable-create-table], or by calling
[`set_chunk_time_interval()`][chunk_interval] on an existing hypertable.

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

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

For a detailed analysis of how to optimize your chunk sizes, see the
[blog post on chunk time intervals][blog-chunk-time]. To learn how
to view and set your chunk time intervals, see
[Optimize hypertable chunk intervals][change-chunk-intervals].

## Hypertable indexes

By default, indexes are automatically created when you create a {HYPERTABLE}. The default index is on time, descending.
You can prevent index creation by setting the `create_default_indexes` option to `false`.

{HYPERTABLE_CAP}s 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
[Enforce constraints with unique indexes on hypertables][hypertables-and-unique-indexes].

You can prevent index creation by setting the `create_default_indexes` option to `false`.

## Partition by dimension

Partitioning on time is the most common use case for {HYPERTABLE}, but it may not be enough for your needs. For example,
you may need to scan for the latest readings that match a certain condition without locking a critical {HYPERTABLE}.

<Info>
  The use case for a partitioning dimension is a multi-tenant setup. You isolate the tenants using the `tenant_id` space
  partition. However, you must perform extensive testing to ensure this works as expected, and there is a strong risk of
  partition explosion.
</Info>

You add a partitioning dimension at the same time as you create the hypertable, when the table is empty. The good news
is that although you select the number of partitions at creation time, as your data grows you can change the number of
partitions later and improve query performance. Changing the number of partitions only affects chunks created after the
change, not existing chunks. To set the number of partitions for a partitioning dimension, call [`set_number_partitions()`][set_number_partitions].
For example:

1. **Create the hypertable with the 1-day interval chunk interval**

   ```sql theme={"dark"}
   CREATE TABLE conditions(
      "time"      timestamptz not null,
      device_id   integer,
      temperature float
   )
   WITH(
      timescaledb.hypertable,
      timescaledb.chunk_interval='1 day'
   );
   ```

2. **Add a hash partition on a non-time column**

   ```sql theme={"dark"}
   SELECT * FROM add_dimension('conditions', by_hash('device_id', 3));
   ```

   Now use your {HYPERTABLE} as usual, but you can also ingest and query efficiently by the `device_id` column.

3. **Change the number of partitions as you data grows**

   ```sql theme={"dark"}
   SELECT set_number_partitions('conditions', 5, 'device_id');
   ```

[blog-chunk-time]: https://www.tigerdata.com/blog/timescale-cloud-tips-testing-your-chunk-size

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

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

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

[postgresql-timestamp]: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

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

[uuidv7_functions]: /api-reference/timescaledb/uuid-functions