Enforce constraints with unique indexes

Having a unique index on your hypertable simplifies lookup, speeds up aggregation, and makes JOINs more efficient. Learn to create a unique index in TimescaleDB and what the related limitations are

You use unique indexes on a to enforce constraints. If you have a primary key, you have a unique index. In , a primary key is a unique index with a NOT NULL constraint.

You do not need to have a unique index on your s. When you create a unique index, it must contain all the partitioning columns of the .

Create a hypertable and add unique indexes

To create a unique index on a :

1. Determine the partitioning columns

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

   traditionally uses the following columns to partition s:

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

2. Create a hypertable

   Create a 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 (
     timescaledb.hypertable,
     timescaledb.segmentby = 'device_id',
     timescaledb.orderby = 'time DESC'
   );
   

   When you create a using CREATE TABLE ... WITH ..., the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , 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 conversion, s 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 the policy is acting on, you must remove the columnstore policy and add a new one.

   You can also manually convert s in a to the .

3. Create a unique index on the hypertable

   When you create a unique index on a , 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);
     

   • 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);
     
   This restriction is necessary to guarantee global uniqueness in the index.

Create a hypertable from an existing table with unique indexes

If you create a unique index on a table before turning it into a hypertable, the same restrictions apply in reverse. You can only partition the table by columns in your unique index.

1. Create a relational table

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

2. 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);
   

3. Turn the table into a partitioned hypertable

   • On time alone:

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

   • 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));
     

   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.