Are Primary Keys Recommended on a Timescale Hypertable?

Question

I am about to receive timeseries data for some devices placed remotely and have decided to use Timescale hypertables to store the data.

The data consists of:

• device_id
• timestamp
• value

None of the fields can be expected to be unique (The same device could make two measurements at the same time). The queries would normally be filtered by device_id + timestamp.

In a traditional relational database I would never create a table without a primary key, but in the documentation Creating your first hypertable they do not create a primary key. Would there be any benefit/drawback in adding an id column backed by a Postgres sequence?

I have searched the documentation, but could not find any specific discussion on hypertables and primary keys.

Answer 1

If you have no requirement to find or operate on individual readings (which it sounds like you don’t) then adding a serial PK for the sake of it is just a waste of space.

The non primary key index you have should be influenced by your query patterns - by default you’ll get timestamp DESC but you’ll probably want to add device_id to that.

Answer 2

Timescale hypertables do not support primary keys.

This is because the underlying data must be partitioned to several physical PostgreSQL tables. Partitioned look-ups cannot support a primary key.

In your case you can use a "synthetic" primary key of together unique columns (device_id, timestamp).

Answer 3

create_hypertable will per default automatically create indexes: see Hypertable Indexes

• in the doc example, they pass time as index column

When you also have a partition column (device_id), you should Create a unique index on the hypertable