I thought the main reason UUIDv7 was created with a time component is to keep temporally related rows together in the index pages. Randomly distributed row ids seem OK until the entire index no longer fits in RAM.
I suppose it's a problem if the spec actually says they're monotonically incrementing because it's clear that isn't guaranteed. But that was never a property I wanted or expected out of them anyway.
All of the other issues here sound like misunderstandings or misuses of ids, IMHO
https://pypi.org/project/uuid-utils/ is what you want :)
If you are using your primary key for anything other than a unique identifier for rows, you are almost certainly doing something wrong. If you need an index on creation time, or just creation order, have a creation time column and an index on that.
I hope to god no one is actually using the timestamp portion to replace an “updated at” timestamp but I suspect I’ll be disappointed.
If you do anything that creates related records via foreign keys you start to run into lock contention. For example:
If your insert requires a parent record with a single child record, you have to get the next sequential id which involves a non-preemptable single threaded lock, increment it, and send it back to the connection which requested it. In most databases this would mean writing the parent record and getting the sequential id of the parent record back so you can set the foreign key reference on the child record. Stored procedures are one way to mitigate the network transit time, but they don't solve the single threaded lock acquisition time.
The reason most distributed systems use UUIDs or another flavor of distributed id is so you can generate the id for the parent record on the client, set the foreign key there as well, then send both records in as simple inserts. It's not that there aren't ways to work around it or mitigate it. Using a coordination free id, is just much easier to work with and scale in practice.
Some of us don't, so these types of universal proclamations are rather unhelpful.
I didn't assume uuid7 was guaranteed monotonic either. Of course not if you have different clocks. That's not the point of it.
https://www.postgresql.org/docs/current/functions-uuid.html#...
uuid_extract_timestamp ( uuid ) → timestamp with time zone
Extracts a timestamp with time zone from a UUID of version 1 or 7. For other versions, this function returns null. Note that the extracted timestamp is not necessarily exactly equal to the time the UUID was generated; this depends on the implementation that generated the UUID.
uuid_extract_timestamp('019535d9-3df7-79fb-b466- fa907fa17f9e'::uuid) → 2025-02-23 21:46:24.503-05Sure, we could use faster alternatives specifically for indexes - but UUIDs are used throughout all business processes. Setting up a separate "conversion to UUID" step seems like a waste.
I know the conventional wisdom says that the collisions are extremely rare <insert some sort of cosmis analogy>
Then, some reddit users reported encountering a collision: https://www.reddit.com/r/webdev/comments/1kvkxte/comment/mud...
Ultimately, you'd have to design the system to handle collision anyway. Might as well design a system that avoid collision in the first place.
The math is simply not in there favor, by a number so large we cannot conceive of it in our tiny brains.
UUIDs have other useful properties. For example, they don't expose counts (e.g. how many customers you have.) They make URL / resource guessing impossible.
ids in the form of `[prefix]_[uuid stripped of dashes]` is ideal for most use cases. It should be the default the way Postgres is the default. Any deviation have good reason.
the UUID timestamp is not for de-duping purposes. it's avoid expensive re-indexes that happen with truly random ids
if timestamp_ms < _last_timestamp_v7:
timestamp_ms = _last_timestamp_v7 + 1
But if you did want "monotonically increasing IDs", as the article suggests, that is what you want.
“12.4.4 — The Clock Sequence shall be originally (that is, once in the lifetime of a system producing UUIDs) initialized to a random number that is not derived from the Node value. NOTE – This is in order to minimize the correlation across systems, providing maximum protection”
In fairness, he does mention this, but squeezes it into a single sentence near the end. But eg the fact that you can't reliably extract the precise timestamp from the UUIDv7 is a feature, not a bug. It's either that or collisions and you don't want the collisions.
I assume I'm oblivious and naive, but it seems theoretically possible these issues could -- theoretically -- result in TOCTOU or similar issues.
Any thoughts about relying on:
- central time service, with message-queue of inbound requests, resulting in correctly timed + sequenced events [ albeit with hopefully small lag tradeoffs ]?
- know of any databases that implement this type of logic around their own minting of time-stamped UUIDs?
Prior efforts implementing event-driven backend service with UUID7s minted in client-code seemed perfectly fine at low usage/scale. However, as clients + usage + event-volume scaled, team became more curious about time/timestamp/related issues, and any minting client-side quickly fell out of favor.
Any insights greatly appreciated -- thanks
[ edited b/c i don't know how to format... ]
Just like NOW() won't have the current time.
I believe it was Poincare who has proven that it's even theoretically impossible to synchronize clocks exactly.
There are fun conditions under which a quasicrystalline clock could coordinate this in a distributed way. https://en.wikipedia.org/wiki/Time_crystal
v4 is less efficient but more private.
It has never crossed my mind to decode the timestamp from them so I save myself a 12-byte column, neither has it crossed my mind to have them monotonic in a distributed system...
It's almost always better (in my experience) to use auto increment internally, and assign a random hash (or UUID) to each public-facing record. All your SQL will be faster at the cost of one lookup (trivially cached) to match the requested hash/uuid to PK. Of course this requires discipline across the team and, in that sense, "just always use uuidv4" is easier.
Realities are arrows. Time marks flight. UUIDv7 is Time.
(Yes. Yes. No, hell no, not after what Robert just told ya.)
https://www.evalapply.org/posts/poor-mans-time-oriented-data....
The small saving grace for me is that, despite my broken mental model of UUIDv7's time and monotonicity properties, I get to also use SQLite's automatic rowid for ordering and disambiguation. That too, only because I can (still think I can) get away with a "DB per tenant" architecture. Nevertheless, the same breakage potential will return when (not if, when) I have to mix / remix records from two or more DB files.
brb, investigating whether I can reliably construct UUIDv7s using db-time...
This glares for me - I'm not "listing" by creation time per se; I'm taking advantage of locality - the page of data that a given key's data is in should also contain data of other keys created around the same time.
That locality means that I have less disk calls if I want to get the data for a series of rows that were created around the same time.
The blog post seems to be saying that (worst case) I cannot rely on that aspect.
The issue with truly random IDs is that inserts are scattered throughout the B-tree index, which cause page splits, fragmentation, and poor cache locality. Using a timestamp prefix makes new IDs sequential, so inserts tend to land near the end of the index, which avoid all the B-tree trashing and improve write performance.
I think this is good. A proper UUID that has specific significant bits dedicated to (approximately) monotonic values is useful. I don't want it over-specified beyond that, and I don't want to just use UUIDv8.