4 ms·
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 spec
by momothereal 6y ago
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
- djk447 6y agoI 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).
- momothereal 6y agoYou make some good points. Thanks for the insights/discussion!