simple-postgres | a minimal postgres interface for node | Runtime Evironment library

You understand correctly that UNIQUE (pair_id, entry_id ) doesn't allow to create hypertable from the table, since unique constraints need to include the partition key, i.e., event_time in your case.

1. I don't follow how the first option, where records with the same timestamp are packed into single record, will help with the uniqueness.

2. Removing the unique constraint will allow to create hypertable and as you mentioned you will lose possibility to check the constraint.

3. Adding the time column, e.g., UNIQUE (pair_id, entry_id, event_time) is quite common approach, but it allows to insert duplicates with different timestamps as you mentioned. It will perform worse than option 2 during inserts. You can replace index on event_time (which you need, since you query on this column, and it is created automatically by TimescaleDB) with unique index, so you save a little bit e.g.,