> values generated are practically sequential
These statements aren’t strict enough to be relied on. Maybe you have engineered the hell out of your distributed clock scheme, and your IDs actually are completely monotonic, which is great. But you probably haven’t done that, which means conflicts will surely happen and you must handle them gracefully.
None of our systems require perfect ordering of IDs generated across our distributed system. Most of the system was built with random UUIDv4 identifiers so no code assumes the ID ordering is significant.
However, in much of our system recent data is frequently accessed while old data is rarely accessed. In that world, just having the IDs *approximately* clustered in creation order has been a huge performance boost for many queries, and we've seen significant reduction in postgres Write Ahead Log rates, because writes to UID indexes happen in a smaller number of pages.
Thank you. I’m so tired of seeing the same groupthink on UUIDv4 trotted out - “it only matters if you have a clustered index, Postgres is immune!” The hell it is.
Maybe there are applications where the monotonicity matters, but in my experience reasoning by surrogate key is rather coarse grained and you manually scrutinize the boundaries, so unless your clocks are quite wrong, your worries are probably better placed elsewhere.
where ts between txn_start and txn_end
order by ts
Out of curiosity, are you into hybrid logical clocks?
Yeah, though I’m more likely to go with a region ID and monotonic version number to compare-and-set and verify gapless data, where versions from different regions aren’t comparable. Actually I think earlier UUID RFCs talk about a “clock sequence” to distinguish timestamps from separate monotonic sources, but this paper doesn’t bring that up (or mention multiple clocks at all).
So any thing you can link, let me know.
In other words: Sorting by millisecond-or-so is just as good as sorting by picosecond in most situations. The reason you have to deal with conflicts gracefully isn't particularly because timestamps can be imperfect.
For providing better query locality it probably doesn't matter significantly though which seems to be the main benefit here while preserving the other benefits UUIDs provide.
I mean that you can’t rely for correctness on time(X) < time(Y) when X happened before Y. It’s damn hard to keep two commodity server clocks within ±1 ms of each other even within a single LAN, and across production you’re more likely to see ±10 ms, or worse if your sysadmins don’t realize you intend to bet the farm on no clock skew.
ULID on the other hand does address this case by providing monotonicity within an epoch for a given producer. So would still need to treat each producer as a separate partition of the key space but you could order X > Y as long as both were produced by same producer (which effectively acts like a sequencer in this case).
EDIT: nvm, the UUIDv7 spec -allows- for arbitrary allocation of the remaining 62 bits which can be used as a counter: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u... All points for ULID can also apply to UUIDv7 depending on generation algorithm.