They are also not supposed to be used as public IDs, just like you wouldn't use auto-incrementing IDs that way.
(I wrote a library for this 13y ago [1], around the time Twitter, Instagram et al started reaching enough scale to think about this kind of optimization)
Of course, lot of event/log data never hits a relational database at a all.. it gets piped right to a data lake. But those records still need IDs, too.
Many years ago, I was working at a small shop that used Oracle databases. We had different tenants for different customers. However, we still wanted records across different tenants to have unique IDs, so (as I recall) all writes to all tenants phoned out to a central sequence generating function. This became a major source of latency for us. This is jut one of many ways that serial IDs become annoying if you're doing anything other than running a 1990's-style RBDMS on a single server.
Anyway, once you try to solve this problem, there are lots of schemes that give you (almost certainly) unique IDs without coordination. Whether you pick UUIDv4 or v7 etc.etc depends on other factors. But one nice feature of UUIDv7 is that it is kinda, sorta serial, which is helpful for e.g. partitioning to work well.
I think unfortunately the vast majority of people misuse them as opaque public identifiers and pks for a single db, which is what this article is talking about.
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.
Except for Postgres
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
Yes you can shrink it down when things get bad, but if your index is large enough that you care then it is large enough that this reorganisation is going to be costly in time and may slow your system while it happens (or cause a complete lock if your DB doesn't support, or optionally supports and you don't select the option, this as an online operation.
https://pypi.org/project/uuid-utils/ is what you want :)
This package should be removed from pypi or somehow give warnings
That's an awful waste for a 128 bit object!
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.
Why would you need the next sequential ID, rather than just a unique ID? You could have a serial column that uses per-shard preallocations like I was describing, and allocate the IDs for both records.
Some of us don't, so these types of universal proclamations are rather unhelpful.
for AI heavy apps/agent they are token expensive
Older designs like ULIDs (https://github.com/ulid/spec) and Snowflake IDs (https://en.wikipedia.org/wiki/Snowflake_ID) have similar properties to UUIDv7, and potentially some advantages w.r.t. length/encoding/runtime cost/etc.
But UUIDv7 has one key advantage none of those do: you can store them in a UUID-typed column or struct field without any lossy conversion. All of the UUID versions share a common size and serialization format, so it doesn't actually matter if you generate v1/4/7/etc.
Many language standard libraries can generate them interchangeably, and (for example) a Postgres table can store any of the versions in the same column type.
So yes, to the author's point(s): they are not magic, monotonic, un-guessable IDs and you shouldn't treat them as either "secure" or strictly ordered. That doesn't mean they aren't useful.
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-05I 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, we'd have to design the system to handle collision anyway. Might as well design a system to avoid collision in the first place. Then, if we have a system that avoids collision, then whether UUID is used is irrelevant.
Until you have any reason to believe UUIDs (and not bugs or misunderstanding as the blog post) are the critical component in the system, just use them. Look at the odds for collision with a proper setup (ntp + any modern cpus) then look at odds for anything else being an issue.
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.
> Ultimately, we'd have to design the system to handle collision anyway.
It's possible for a bug to create a collision. But it's also possible for a bug to return someone else's ID at any point in the process. So even if you try to "handle" collisions, it might not work. You can't programmatically detect a mistake like that. Detection only works for bugs in a narrow slice of the pipeline. And extra complication in the pipeline, especially with a code path that has never been taken before, adds its own risk and drops the trustworthiness of the entire system.
> Might as well design a system to avoid collision in the first place.
Agreed. But UUID accomplishes the goal of "designed to avoid collisions" by itself.
An ID is an identifiers. It supports exactly the operation of comparing two IDs to establish identity.
When you start to treat them as something else that is not guaranteed, you will see weird things happening.
As long as you have absolute control over every single ID generated, it will work just fine. The problem is that it's way too easy to accidentally generate UUID in a way that ignores whatever rules you set up for it. Also, clocks are hard.
the UUID timestamp is not for de-duping purposes. it's avoid expensive re-indexes that happen with truly random ids
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 like SQLite’s decision to make integer primary keys not marked as auto increment to simply be aliases for its rowid which is not guaranteed to be unique for the table’s lifetime.
i.e. inserts that create rows with id 1,2,3,4 a delete of row 3,4 followed by inserts will create new rows 3,4
> Mistaking and ID for something more is the best preparation for some poor soul's headache at some point in the (possibly distant) future.
Agreed, all IDs should be considered opaque. All user facing IDs should definitely be opaque and leak no information.
I’m not a fan of UUIDv7, and don’t buy any of the arguments in favor of it. v4 is the better ID. My general preference though is 128 random bits encoded as base58 for user display. v4 isn’t even 128 random bits!
Need creation time? Add a column. Need sequential ordering based on when rows were inserted? Add an auto increment column.
“But my indexes” So what? Prove that it realistically impacts performance (spoiler alert, the rest of your app is likely far slower than the user facing ID lookups!)
“But my foreign keys” Add a column. If you’re only in a single database server this can be the real primary key, likely an u32 or u64 if you truly need more than 4 billion rows.
“But my hyper scale!” Then eat the cost of more servers for your distributed system. You can’t have your cake and eat it too.
"But my straw men!" It's writes that are the problem, and it's been proven with hard numbers over and over again.
Sure, 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.
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.
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”
Just like NOW() won't have the current time.
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... ]
There are fun conditions under which a quasicrystalline clock could coordinate this in a distributed way. https://en.wikipedia.org/wiki/Time_crystal
Standard UUID length is 36 characters. So you have identifiers 11 characters less and they're actually a tiny bit more readable and writeable if you decide to type them for some reason.
It is probably not the right choice if you require a cryptographically strong and unique ID.
I believe it was Poincare who has proven that it's even theoretically impossible to synchronize clocks exactly.
And with computers it's fussy to get clocks synchronized below a millisecond but not that fussy. You can keep a single computer synchronized down to a single cycle, making timestamps effectively perfect across different parts of it. And then between computers, if you have n latency between them you should be able to get the clocks within n/10, which might as well be perfect for coordination purposes.
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.
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.
v4 is less efficient but more private.
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...
I'm leaning increasingly heavily on v5 for its determinism which is extremely important for IDs. I would sacrifice every other property to get determinism.
You can't have idempotency without determinism and you can't get reliability without idempotency.
Once you factor in failure and retry scenarios under concurrency, you often need ID determinism. In the rare cases I don't want determinism, I prefer to go all-in on randomness. I prefer my IDs to be well spread out; it scales better, more predictable, less of a security liability (prefer not to have all data clustered in the same place)...
Both UUIDv4 and UUIDv5 outputs are uniformly distributed. IMO this is a desirable property which is well worth a bit of additional compute. v7 feels like an optimization hack, and it comes at a cost.
If you want to get the performance benefits of v7 with the determinism of v5, then you need to have essentially two IDs; the id (v7) and an idempotency key (which is essentially like a second ID you have to maintain; e.g. UUIDv5)... In the end, maintaining a UUIDv7 ID + idempotency key is less performant than just having a single UUIDv5 id which does both jobs for no extra effort... Not to mention, less maintainable and more complicated to use.
You absolutely can. The idempotency nonce must be generated by the client who is responsible for firing potential retries. And a random UUIDv4 is a perfectly fine nonce.
If the client _doesn't_ generate a nonce, it doesn't matter what the server side does, they cannot tell from the request data if it's supposed to be a retry or a new request with the same data. A hash such as a UUIDv5 is more performant but not logically any better than comparing the raw data of the two requests. If you use that UUIDv5 as your primary key for insert/update, it's little more than a natural key, with all its well-known drawbacks.
One thing that concerns me is that UUIDv5 is based on SHA1, which is an insecure hashing algorithm. I generated a pair of colliding UUIDv5s in an earlier post in this series (https://alexsci.com/blog/uuid-oops/#insecure-hash-functions). So I don't think you can use UUIDv5 for security critical functions.
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.
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.