Hypertables are Postgres tables in TimescaleDB that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. Hypertables enable TimescaleDB to work efficiently with time-series data. Each hypertable is made up of child tables called chunks. Each chunk is assigned a rangeof time, and only contains data from that range. When you run a query, TimescaleDB identifies the correct chunk andruns the query on it, instead of going through the entire table.

Hypercore is the hybrid row-columnar storage engine in TimescaleDB used by hypertables. Traditionaldatabases force a trade-off between fast inserts (row-based storage) and efficient analytics(columnar storage). Hypercore eliminates this trade-off, allowing real-time analytics without sacrificingtransactional capabilities.

Hypercore dynamically stores data in the most efficient format for its lifecycle:

• Row-based storage for recent data: the most recent chunk (and possibly more) is always stored in the rowstore,ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as awritethrough for inserts and updates to columnar storage.
• Columnar storage for analytical performance: chunks are automatically compressed into the columnstore, optimizingstorage efficiency and accelerating analytical queries.

Unlike traditional columnar databases, hypercore allows data to be inserted or modified at any stage, making it aflexible solution for both high-ingest transactional workloads and real-time analytics—within a single database.

Because TimescaleDB is 100% Postgres, you can use all the standard Postgres tables, indexes, storedprocedures, and other objects alongside your hypertables. This makes creating and working with hypertables similarto standard Postgres.

Expand image

Each hypertable is partitioned into child hypertables called chunks. Each chunk is assigneda range of time, and only contains data from that range. If the hypertable isalso partitioned by space, each chunk is also assigned a subset of the spacevalues.

When TimescaleDB creates a chunk, the creation time is stored in the catalog metadata. This chunk creationtime is not the same as the partition ranges for the data contained in the chunk. Certainfunctionality can use this chunk creation time metadata in cases where it makes sense.

Note

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

Each hypertable chunk holds data for a specific time range only. When youinsert data from a time range that doesn't yet have a chunk, TimescaleDBautomatically creates a chunk to store it.

By default, each chunk covers 7 days. You can change this to better suit yourneeds. For example, if you setchunk_interval to 1 day, each chunk storesdata from the same day. Data from different days is stored in different chunks.

The following figure shows the difference in structure between a relational table and a hypertable:

Expand image

TimescaleDB divides time into potential chunk ranges, based on thechunk_interval. If data exists for a potential chunk range, that chunk iscreated.

In practice, this means that the start time of your earliest chunk does notnecessarily equal the earliest timestamp in your hypertable. Instead, theremight be a time gap between the start time and the earliest timestamp. Thisdoesn't affect your usual interactions with your hypertable, but might affectthe number of chunks you see when inspecting it.

Best practices for maintaining a high performance when scaling include:

• Limit the number of hypertables in your service; having tens of thousands of hypertables is not recommended.
• Choose a strategic chunk size.

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

Postgres 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 fitinto memory, it is constantly flushed to disk and read back. This wastes IO resources which would otherwisebe used for writing the heap/WAL data to disk.

The default chunk interval is 7 days. However, best practice is to setchunk_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 64GB of memory, if index growth is approximately 2 GB per day, a 1-week chunk interval is appropriate. If index growth isaround 10 GB per day, use a 1-day interval.

You setchunk_interval when youcreate a hypertable, or by callingset_chunk_time_interval on an existing hypertable.

For a detailed analysis of how to optimize your chunk sizes, see theblog post on chunk time intervals. To learn howto view and set your chunk time intervals, see how toOptimize hypertable chunk intervals.

By default, indexes are automatically created when you create a hypertable.

The default indexes are:

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

Hypertables have some restrictions on unique constraints and indexes. If youwant a unique index on a hypertable, it must include all the partitioningcolumns for the table. To learn more, seeEnforce constraints with unique indexes on hypertables.

You can prevent index creation by setting thecreate_default_indexes option tofalse.

This section shows you:

• Optimize time-series data in hypertables
• Improve hypertable and query performance
• Enforce constraints with unique indexes
• Troubleshooting