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