create_hypertable()

Replace a standard Postgres relational table with a hypertable that is partitioned on a single dimension. To create a new hypertable, best practice is to call CREATE TABLE.

A hypertable is a Postgres table that automatically partitions your data by time. A dimension defines the way your data is partitioned. All actions work on the resulting hypertable. For example, ALTER TABLE, and SELECT.

If the table to convert already contains data, set migrate_data to TRUE. However, this may take a long time and there are limitations when the table contains foreign key constraints.

You cannot run create_hypertable() on a table that is already partitioned using declarative partitioning or inheritance. The time column must be defined as NOT NULL. If this is not already specified on table creation, create_hypertable automatically adds this constraint on the table when it is executed.

This page describes the generalized hypertable API introduced in TimescaleDB v2.13. The old interface for create_hypertable is also available.

Before you call create_hypertable, you create a standard Postgres relational table. For example:

CREATE TABLE conditions (
   time        TIMESTAMPTZ         NOT NULL,
   location    text                NOT NULL,
   temperature DOUBLE PRECISION    NULL
);

Copy

The following examples show you how to create a hypertable from an existing table or a function:

• Time partition a hypertable by time range
• Time partition a hypertable using composite columns and immutable functions
• Time partition a hypertable using ISO formatting
• Time partition a hypertable using UUIDv7

The following examples show different ways to create a hypertable:

• Convert with range partitioning on the time column:

  SELECT create_hypertable('conditions', by_range('time'));

  Copy

• Convert with a set_chunk_time_interval of 24 hours: Either:

  SELECT create_hypertable('conditions', by_range('time', 86400000000));

  Copy

  or:

  SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day'));

  Copy

• with range partitioning on the time column, do not raise a warning if conditions is already a hypertable:

  SELECT create_hypertable('conditions', by_range('time'), if_not_exists => TRUE);

  Copy

Note

If you call SELECT * FROM create_hypertable(...) the return value is formatted as a table with column headings.

The following example shows how to time partition the measurements relational table on a composite column type using a range partitioning function.

1. Create the report type, then an immutable function that converts the column value into a supported column value:

   CREATE TYPE report AS (reported timestamp with time zone, contents jsonb);
   
   CREATE FUNCTION report_reported(report)
     RETURNS timestamptz
     LANGUAGE SQL
     IMMUTABLE AS
     'SELECT $1.reported';

   Copy

2. Create the hypertable using the immutable function:

   SELECT create_hypertable('measurements', by_range('report', partition_func => 'report_reported'));

   Copy

The following example shows how to time partition the events table on a jsonb (event) column type, which has a top level started key that contains an ISO 8601 formatted timestamp:

CREATE FUNCTION event_started(jsonb)
    RETURNS timestamptz
    LANGUAGE SQL
    IMMUTABLE AS
  $func$SELECT ($1->>'started')::timestamptz$func$;

SELECT create_hypertable('events', by_range('event', partition_func => 'event_started'));

Copy

1. Create a table with a UUIDv7 column:

2. Partition the table based on the timestamps embedded within the UUID values:

   SELECT create_hypertable(
       'events',                                      
       by_range('id', INTERVAL '1 month')
   );

   Copy

Subsequent data insertion and queries automatically leverage the UUIDv7-based partitioning.

Name	Type	Default	Required	Description
create_default_indexes	BOOLEAN	TRUE	✖	Create default indexes on time/partitioning columns.
dimension	DIMENSION_INFO	-	✔	To create a _timescaledb_internal.dimension_info instance to partition a hypertable, you call by_range and by_hash.
if_not_exists	BOOLEAN	FALSE	✖	Set to TRUE to print a warning if relation is already a hypertable. By default, an exception is raised.
migrate_data	BOOLEAN	FALSE	✖	Set to TRUE to migrate any existing data in relation in to chunks in the new hypertable. Depending on the amount of data to be migrated, setting migrate_data can lock the table for a significant amount of time. If there are foreign key constraints to other tables in the data to be migrated, create_hypertable() can run into deadlock. A hypertable can only contain foreign keys to another hypertable. UNIQUE and PRIMARY constraints must include the partitioning key. Deadlock may happen when concurrent transactions simultaneously try to insert data into tables that are referenced in the foreign key constraints, and into the converting table itself. To avoid deadlock, manually obtain a SHARE ROW EXCLUSIVE lock on the referenced tables before you call create_hypertable in the same transaction. If you leave migrate_data set to the default, non-empty tables generate an error when you call create_hypertable.
relation	REGCLASS	-	✔	Identifier of the table to convert to a hypertable.

To create a _timescaledb_internal.dimension_info instance, you call add_dimension to an existing hypertable.

Hypertables must always have a primary range dimension, followed by an arbitrary number of additional dimensions that can be either range or hash, Typically this is just one hash. For example:

SELECT add_dimension('conditions', by_range('time'));
SELECT add_dimension('conditions', by_hash('location', 2));

Copy

For incompatible data types such as jsonb, you can specify a function to the partition_func argument of the dimension build to extract a compatible data type. Look in the example section below.

By default, TimescaleDB calls Postgres's internal hash function for the given type. You use a custom partitioning function for value types that do not have a native Postgres hash function.

You can specify a custom partitioning function for both range and hash partitioning. A partitioning function should take a anyelement argument as the only parameter and return a positive integer hash value. This hash value is not a partition identifier, but rather the inserted value's position in the dimension's key space, which is then divided across the partitions.

Create a by-range dimension builder. You can partition by_range on it's own.

Samples

• Partition on time using CREATE TABLE

  The simplest usage is to partition on a time column:

  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.partition_column='time'
  );

  Copy

  If you are self-hosting TimescaleDB v2.19.3 and below, create a Postgres relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.

  This is the default partition, you do not need to add it explicitly.

• Extract time from a non-time column using create_hypertable

  If you have a table with a non-time column containing the time, such as a JSON column, add a partition function to extract the time:

  CREATE TABLE my_table (
     metric_id serial not null,
     data jsonb,
  );
  
  CREATE FUNCTION get_time(jsonb) RETURNS timestamptz AS $$
    SELECT ($1->>'time')::timestamptz
  $$ LANGUAGE sql IMMUTABLE;
  
  SELECT create_hypertable('my_table', by_range('data', '1 day', 'get_time'));

  Copy

Arguments

If the column to be partitioned is a:

• TIMESTAMP, TIMESTAMPTZ, or DATE: specify partition_interval either as an INTERVAL type or an integer value in microseconds.

• Another integer type: specify partition_interval as an integer that reflects the column's underlying semantics. For example, if this column is in UNIX time, specify partition_interval in milliseconds.

The partition type and default value depending on column type is:

The main purpose of hash partitioning is to enable parallelization across multiple disks within the same time interval. Every distinct item in hash partitioning is hashed to one of N buckets. By default, TimescaleDB uses flexible range intervals to manage chunk sizes.

You use Parallel I/O in the following scenarios:

• Two or more concurrent queries should be able to read from different disks in parallel.
• A single query should be able to use query parallelization to read from multiple disks in parallel.

For the following options:

• RAID: use a RAID setup across multiple physical disks, and expose a single logical disk to the hypertable. That is, using a single tablespace.

  Best practice is to use RAID when possible, as you do not need to manually manage tablespaces in the database.

• Multiple tablespaces: for each physical disk, add a separate tablespace to the database. TimescaleDB allows you to add multiple tablespaces to a single hypertable. However, although under the hood, a hypertable's chunks are spread across the tablespaces associated with that hypertable.

  When using multiple tablespaces, a best practice is to also add a second hash-partitioned dimension to your hypertable and to have at least one hash partition per disk. While a single time dimension would also work, it would mean that the first chunk is written to one tablespace, the second to another, and so on, and thus would parallelize only if a query's time range exceeds a single chunk.

When adding a hash partitioned dimension, set the number of partitions to a multiple of number of disks. For example, the number of partitions P=N*Pd where N is the number of disks and Pd is the number of partitions per disk. This enables you to add more disks later and move partitions to the new disk from other disks.

TimescaleDB does not benefit from a very large number of hash partitions, such as the number of unique items you expect in partition field. A very large number of hash partitions leads both to poorer per-partition load balancing (the mapping of items to partitions using hashing), as well as much increased planning latency for some types of queries.

Samples

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.partition_column='time',
   tsdb.chunk_interval='1 day'
);

SELECT add_dimension('conditions', by_hash('location', 2));

Copy

Arguments

by_range and by-hash return an opaque _timescaledb_internal.dimension_info instance, holding the dimension information used by this function.