4 ms·
I maintain that people are too eager to use UUIDv7 to begin with. It's a dessert topping and a floor wax. Let's say you need an opaque unique handle, and a ti
by samatman 2y ago
I maintain that people are too eager to use UUIDv7 to begin with. It's a dessert topping and a floor wax.
Let's say you need an opaque unique handle, and a timestamp, and a monotonically increasing row ID. Common enough. Do they have to be the same thing? Should they be the same thing? Because to me that sounds like three things: an autoincrementing primary key, a UUIDv4, and a nanosecond timestamp.
Is it always ok that the 'opaque' unique ID isn't opaque at all, that it's carrying around a timestamp? Will that allow correlating things which maybe you didn't want hostiles to correlate? Are you 100% sure that you'll never want, or need, to re-timestamp data without changing its global ID?
Maybe you do need these things unnormalized and conflated. Do you though? At least ask the question.
- user3939382 2y agoRe-timestamp would be a new one for me. What’s a conceivable use case? An NTP fault?
- fastball 2y agoAlso if you have a nanosecond timestamp, do you actually need a monotonically increasing row ID? What for?
- peferron 2y agoPerhaps as a tie breaker if you insert multiple rows in a table within one transaction? In this situation, the timestamp returned by e.g. `now()` refers to the start of the transaction, which can cause it to be reused multiple times.
- fastball 2y agoI meant in a context with a random ID and a timestamp. If you need to retrieve rows by time/order, you use the timestamp. If you need a row by ID, you use the ID. The use-cases where you actually need to know which row was inserted first seem exceedingly rare (mostly financial / auditing applications), and even then can probably be handled with separate transactions (as you touch on).
- peferron 2y agoYou can keep all three and still use UUIDv7 as a performance improvement in certain contexts due to data locality.