CODEWITHSUNDEEP
timescaledbprimary-key-design
TemporalSpatialDude
TemporalSpatialDude

Reputation: 43

Are Primary Keys Recommended on a Timescale Hypertable?

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:

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.

Upvotes: 4

Views: 2735

Answers (3)

TmTron
TmTron

Reputation: 19371

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

Upvotes: 1

Mikko Ohtamaa
Mikko Ohtamaa

Reputation: 83788

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

Upvotes: 2

James
James

Reputation: 66

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.

Upvotes: 1

Related Questions