Enforce constraints with unique indexes

Determine the partitioning columns

Before you create a unique index, you need to determine which unique indexes are allowed on your hypertable. Begin by identifying your partitioning columns.

TimescaleDB traditionally uses the following columns to partition hypertables:

• The time column used to create the hypertable. Every TimescaleDB hypertable is partitioned by time.
• Any space-partitioning columns. Space partitions are optional and not included in every hypertable.

Create a hypertable

Create a hypertable for your time-series data using 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:

CREATE TABLE hypertable_example(
 time TIMESTAMPTZ,
 user_id BIGINT,
 device_id BIGINT,
 value FLOAT
) WITH (
 tsdb.hypertable,
 tsdb.segmentby = 'device_id',
 tsdb.orderby = 'time DESC'
);

Copy

When you create a hypertable using CREATE TABLE ... WITH ..., the default partitioning column is automatically the first column with a timestamp data type. Also, TimescaleDB creates a columnstore policy that automatically converts your data to the columnstore, after an interval equal to the value of the chunk_interval, 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 chunks are compressed by up to 98%, and organized for efficient, large-scale queries.

You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the hypertable the policy is acting on, you must remove the columnstore policy and add a new one.

You can also manually convert chunks in a hypertable to the columnstore.

Create a unique index on the hypertable

When you create a unique index on a hypertable, it must contain all the partitioning columns. It may contain other columns as well, and they may be arranged in any order. You cannot create a unique index without time, because time is a partitioning column.

For example:

• Create a unique index on time and device_id with a call to CREATE UNIQUE INDEX:

  CREATE UNIQUE INDEX idx_deviceid_time
   ON hypertable_example(device_id, time);

  Copy

• Create a unique index on time, user_id, and device_id.

  device_id is not a partitioning column, but this still works:

  CREATE UNIQUE INDEX idx_userid_deviceid_time
   ON hypertable_example(user_id, device_id, time);

  Copy

Note

This restriction is necessary to guarantee global uniqueness in the index.

Create a relational table

CREATE TABLE another_hypertable_example(
 time TIMESTAMPTZ,
 user_id BIGINT,
 device_id BIGINT,
 value FLOAT
);

Copy

Create a unique index on the table

For example, on device_id and time:

CREATE UNIQUE INDEX idx_deviceid_time
 ON another_hypertable_example(device_id, time);

Copy

Turn the table into a partitioned hypertable

• On time alone:

  SELECT * from create_hypertable('another_hypertable_example', by_range('time'));

  Copy

• On time and device_id:

  SELECT * FROM create_hypertable('another_hypertable_example', by_range('time'));
  SELECT * FROM add_dimension('another_hypertable_example', by_hash('device_id', 4));

  Copy

You get an error if you try to turn the relational table into a hypertable partitioned by time and user_id. This is because user_id is not part of the UNIQUE INDEX. To fix the error, add user_id to your unique index.