Hacker News Reader

Comment by momothereal | Hacker News Reader

OT: Every time I see Timescale on here I'm excited to use it. After some preliminary testing I always run into the same issue: can't create a hypertable if there's a unique index / primary key that doesn't include the 'time' field.

For example my data looks like (row_id, user_id, time, data), where row_id is a unique ID, and time is a non-unique time field. Timescale will refuse to create a hypertable like this because it causes partition issues.

View on HN

You can create a hypertable with a UNIQUE composite key on (row_id, time).

Otherwise, you are correct in that we require your partitioning keys to be at least _part_ of unique constraints; otherwise, we'd need to build global indexes across all your chunks (which would inhibit scalability)...this would only be worse with multi-node =)

Wouldn't that just make the pair unique? That would still allow you to insert rows with the same row_id, but different time.

Why not create an index on user_id, time? Why do you need a unique reference back to the row? We find that they're often (though certainly not always) kind of meaningless and/or unnecessary for timeseries workloads.

Yes that's what the reply suggested. However the business requirement is that the row_id should be unique, such that looking up a row by ID is guaranteed to have 0-1 results. A unique constraint on (row_id, time) doesn't satisfy that.

Sorry, the previous reply suggested it was on row_id, time I thought, anyway. I think the question is why should row_id be unique? What looks it up by row_id? And can they look it up by user_id/time instead? It may be impossible. On the other hand, if row_id is really what you're searching by, not by time, then just use our partitioning on row_id, assuming it's a bigint or something like that, you can just partition on that...

No problem, my fault I misread your comment. `row_id` is a unique identifier for the row, for example my API will need it when the user wants to delete the specific row. Since it's possible for multiple rows to have the same `time`, even for the same `user_id`, I cannot assume uniqueness there.

Partitioning on the row_id by making it an ordinal instead of a UUID could work, however I feel I would be missing out on TimescaleDB's advantages for querying based on the `time` filter?

Consider that my main queries are:

* DELETE FROM t WHERE row_id = xyz AND user_id = xyz

* INSERT INTO t (...)

* SELECT FROM t WHERE user_id = xyz AND time > xyz and time < xyz

I mean, I'm not sure this is the place to get into detailed schema discussions, but I think I wouldn't use a row_id here at all, certainly, there's no need for this to be unique across all of the rows, you could make a unique constraint on user_id, time, row_id and then provide all 3 of them for the deletes, if the only reason is for the deletes, then that should be fine. If there are other reasons, then you probably have a different unique constraint that has some real world meaning (and that likely involves time).

You make some good points. Thanks for the insights/discussion!

If row_id is unique, then (row_id,time) is also unique.

Reply on news.ycombinator.com