This is a misunderstanding of the purpose of UUIDv7. The whole point is to keep rows physically close, so you can very quickly extract an entire time range from a number of database shards, with minimal reads and compute. And be able to generate them without a centralized server. That's all there was to it, and something you'd only worry about when serving billions of records every hour.
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)
> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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.
It's not just index pages. Generally databases write records into pages, which are then grouped into extents. If the primary key gives an ordering you can create new pages and start filling them up in an orderly fashion. If it's random, you are doing random inserts into pages everywhere all the time, then you start running out of room in pages and have to start splitting them. UUIDv7 solves the most painful parts of this problem in general and is a much better option for a Primary Key than UUIDv1-4.
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
Yeah, this is 99% of the benefit for me. And it's not theoretical. My company saw performance issues using pure random IDs with MySQL way back in 2015; and consequently rolled our own UUIDv7 that we're still using to this day.
As well as keeping rows of similar age roughly together, which may create access efficiencies but should not be relied upon, relatively ordered keys massively reduces page splits as the index grows (this can be particularly significant for clustered indexes as implemented by MS SQL and others) because you are always adding items to the end of the index. This can reduce the size of the index overall, potentially a big problem if working in a situation where your data+indexes do not fit comfortably in RAM (you don't want your precious RAM half full of half-empty index pages).
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.
One of my favorite things about UUIDv7: the package https://pypi.org/project/uuid7/ does not implement compliant UUIDv7s, it's from a wholly incompatible draft format. That was not a fun debugging session.
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.
Kind of funny title since I didn’t think UUIDv7 had any of the properties being disputed here. The reason to use UUIDv7 is because it reduces the space over which the UUID can cover at a given time, which improves write clustering. They don’t need to be strictly ordered for this, just “good enough”.
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.
Yeah this is what pick-up "artists" call a "neg" - a negative judgement made purely so the recipient feels, at some level, that their credibility is on the line. In this case, for clickbait. I always either don't read such an article or come here to read the comments instead.
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.
The biggest advantage I've seen for UUID come with distributed systems imo. The ability to create an id without lock coordination allows for much easier horizontal scaling. UUIDv1 avoids the collision issue by using a portion of the MAC in the id, but v1-4 all have sorting issues. UUIDv7 is a good choice if your use case warrants it. If you can get away with a single integer field like a database primary key and not get bottlenecked that's much easier. When that doesn't work anymore one of the distributed id schemes like UUID are the only real options available.
You don't have to handle collisions and I don't believe that multiple Redditors have experienced this. If they have, it's an issue with the implementation and not UUIDs.
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.
I support this. I wrote a Sqlite plugin for KSUIDs, but at the end I never used them, since all I do can be handled by integer primary keys, which sqlite maps to the rowid automatically. Autoincrement is only required, if you want to prevent the reuse of ids in deleted rows.
Yes, this is standard. And the point is performance. The needs of user-facing ID's (short alphanumeric, randomly ordered) tend to be pretty orthogonal to the needs of internal ID's (raw bits, monotonically increasing, maybe timestamp data).
That depends on your database. Serial keys can create hot spots in distributed databases because new writes are consistently directed to the same partition.
There's no fundamental reason for this. Much as with per-CPU memory allocation, shards could pre-reserve chunks of the next serial space, and then only coordinate when they need a new chunk.
Many schemes do exactly this, if all you are doing is single table inserts that works pretty well.
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.
In my testing the primary bottleneck isn't about writing to the same partition. Lock coordination to increment the serial key, then send it back across a connection was the real culprit. Virtually all databases make updates into RAM and to a write ahead log, which is then spooled to disk in the background. No matter which pages are written or updated, the changes all go sequentially in order into the WAL. For me, getting rid of Serial Keys was the big hot spot in processing. YMMV.
It's been a while since I've touched db stuff, but pg still has to write the indexes before commit completes, no? So it's still having to go load and write a gazillion pages. And then unless your filesystem has atomic page writes like ZFS, it also writes the whole page to WAL to ensure that it doesn't do torn writes. Writes to the same page within a checkpoint don't need full page write, so benefit.
Yeah this was odd: "Superficially, it seems that embedding a timestamp in the ID can remove the need to track the creation time in a separate database column" Who actually tried to do this?
I didn't assume uuid7 was guaranteed monotonic either. Of course not if you have different clocks. That's not the point of it.
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-05
> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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.
Yes, UUIDv7 doesn't have the same charm and texture as tree notches. But it's been a huge help with my tables containing billions of records.
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.
This is perfect if you only have one machine and the process never restarts. But then if you only have one machine you don’t realistically need uuid identifiers.
This is why the oldschool UUIDv1 spec specifies initializing the sequence value at a random number:
“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”
I feel like this article way downplays the entire point of UUIDv7s, which is that it lets you sort (and store!) your data kinda sorta by creation time, no overlap ever. That's great for cursors, for streaming data, for twitter clones, for chat messages, for player moves, and so on and so forth. Nearly everything that's vaguely realtimey that you might like to both persist and broadcast live to other clients, and displayed in a stable & reasonable order, benefits from a property like this.
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.
Yeah, I think the key here is that you have to know what the limitations are. UUIDv7 does _not_ provide you with sorted and ordered IDs by creation time, it provides you with _kinda sorta_ sorted and ordered IDs by creation time. That's probably fine for a lot of use cases, but if it's not OK for yours (forensic accounting, risk mitigation for banks, whatever), you can't use it for that. You're right it's not a technical problem necessarily, but when the marketing material describes something without an asterisk, people who are unaware that what's being described is impossible at the limits can make bad system design decisions based on that promise.
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.
I've never bothered with anything but uuid4. Yes there are special circumstances where a public uuid7 helps because you're sure the timestamp leakage is ok, and you made sure it's not guessable, and you're sure some index performs better with it in a way that matters. There are also certain distributed systems where it has helpful properties. The amount of times blanket advice is given to use uuid7 far outweighs how often I've been in that situation, especially when people are talking about Postgres primary keys.
>Unfortunately, the timestamp embedded in a UUIDv7 ID may not relate to the object creation time. The Postgres docs for uuid_extract_timestamp helpfully warn that “the extracted timestamp is not necessarily exactly equal to the time the UUID was generated”.
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...
Wow... That is some nitpicking... The major issue these UUIDs solve for me is the fact that using a random UUID as a primary key of a relational database leads to horrible 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 because it's opaque. Signing up and seeing a page like `/companies/366` unnecessarily leaks company data. Sometime it's a huge deal, sometimes not; not here to litigate that. But it's trivially avoidable.
The UUIDs discussed here leak the time that the record was created, so one could conceivably extract the growth rate, retention, sequence of event, etc. That's not great either.
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.
> Superficially, it seems that embedding a timestamp in the ID can remove the need to track the creation time in a separate database column, providing marginally more efficient database operations. If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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 generally you can still rely on that aspect, compared to the alternative of UUIDv1-4. Even the worst cases he mentioned would cause minimal page splitting problems in a database compared to even the best cases with UUIDv1-4. The biggest take away I got from the article is that there is some wiggle room in how things can be implemented in the UUIDv7 spec, and it's important to be aware of the implementations you are using.
It doesn’t have anything to do with indexing more than one thing.
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.
Yes, which means that you should probably be able to provide your timestamp to the database so it can use it for optimizing its index, rather than trying to shove it in your ID
Sure but at least you are being honest about it. For what it's worth the database can handle this internally without you needing to hand this back but if you have a bad one then yes you should probably have it give you back a location hint that you use for future queries
> My mental model for UUIDv7 is that it’s a mash-up of a bunch of different ideas which implementations are free to choose as they see fit. There’s a wide variance of properties that UUIDv7 implementations can provide, with varying degrees of certainty they can successfully provide those properties.
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.
This is a misunderstanding of the purpose of UUIDv7. The whole point is to keep rows physically close, so you can very quickly extract an entire time range from a number of database shards, with minimal reads and compute. And be able to generate them without a centralized server. That's all there was to it, and something you'd only worry about when serving billions of records every hour.
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)
[1] https://github.com/ricardobeat/unik
> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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.
It's not just index pages. Generally databases write records into pages, which are then grouped into extents. If the primary key gives an ordering you can create new pages and start filling them up in an orderly fashion. If it's random, you are doing random inserts into pages everywhere all the time, then you start running out of room in pages and have to start splitting them. UUIDv7 solves the most painful parts of this problem in general and is a much better option for a Primary Key than UUIDv1-4.
> Generally
Except for Postgres
Yeah, same.
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
Yeah, this is 99% of the benefit for me. And it's not theoretical. My company saw performance issues using pure random IDs with MySQL way back in 2015; and consequently rolled our own UUIDv7 that we're still using to this day.
As well as keeping rows of similar age roughly together, which may create access efficiencies but should not be relied upon, relatively ordered keys massively reduces page splits as the index grows (this can be particularly significant for clustered indexes as implemented by MS SQL and others) because you are always adding items to the end of the index. This can reduce the size of the index overall, potentially a big problem if working in a situation where your data+indexes do not fit comfortably in RAM (you don't want your precious RAM half full of half-empty index pages).
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.
I thought it was for distributed DBs?
you thought right. the author is just plain wrong here.
One of my favorite things about UUIDv7: the package https://pypi.org/project/uuid7/ does not implement compliant UUIDv7s, it's from a wholly incompatible draft format. That was not a fun debugging session.
https://pypi.org/project/uuid-utils/ is what you want :)
https://github.com/stevesimmons/uuid7/issues/1
not python is what you really want I suppose.
If you really need monotonic, secure/unguessable IDs, there are options like CUID2 (github.com/paralleldrive/cuid2).
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.
Kind of funny title since I didn’t think UUIDv7 had any of the properties being disputed here. The reason to use UUIDv7 is because it reduces the space over which the UUID can cover at a given time, which improves write clustering. They don’t need to be strictly ordered for this, just “good enough”.
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.
Yeah this is what pick-up "artists" call a "neg" - a negative judgement made purely so the recipient feels, at some level, that their credibility is on the line. In this case, for clickbait. I always either don't read such an article or come here to read the comments instead.
I generally avoid using UUID unless there's a strong reason for it.
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, 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.
The biggest advantage I've seen for UUID come with distributed systems imo. The ability to create an id without lock coordination allows for much easier horizontal scaling. UUIDv1 avoids the collision issue by using a portion of the MAC in the id, but v1-4 all have sorting issues. UUIDv7 is a good choice if your use case warrants it. If you can get away with a single integer field like a database primary key and not get bottlenecked that's much easier. When that doesn't work anymore one of the distributed id schemes like UUID are the only real options available.
You don't have to handle collisions and I don't believe that multiple Redditors have experienced this. If they have, it's an issue with the implementation and not UUIDs.
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.
It’s not the default though is it. I wonder why?
You don't have to. You can absorb the risk. Which most orgs do.
You probably don't need a UUID: https://ssg.dev/you-probably-dont-need-a-uuid/
I support this. I wrote a Sqlite plugin for KSUIDs, but at the end I never used them, since all I do can be handled by integer primary keys, which sqlite maps to the rowid automatically. Autoincrement is only required, if you want to prevent the reuse of ids in deleted rows.
For a database primary key, yeah. Just use serial.
I don't want my competitors to know about my number of clients, etc.
Don't expose your PKs
So you have a second, opaque unique identifier for the frontend to fetch? At that point why not just use a single column (UUID)?
Higher density for indexes you use for joins. Also you can then use UUIDv4 to hide creation times if that's sensitive too.
Yes, this is standard. And the point is performance. The needs of user-facing ID's (short alphanumeric, randomly ordered) tend to be pretty orthogonal to the needs of internal ID's (raw bits, monotonically increasing, maybe timestamp data).
That depends on your database. Serial keys can create hot spots in distributed databases because new writes are consistently directed to the same partition.
Yeah but for nearly everyone, they'd get better performance from not using a distributed database, and benefit from "hotspots" i.e. data locality.
There's no fundamental reason for this. Much as with per-CPU memory allocation, shards could pre-reserve chunks of the next serial space, and then only coordinate when they need a new chunk.
Many schemes do exactly this, if all you are doing is single table inserts that works pretty well.
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.
It’s much easier if you can simply not coordinate. Bonus points if that doesn’t mean you need an atomic clock in your rack
If you're designing a distributed DB, sure. If you're using something, you do whatever it wants. Spanner recommends random keys. Citus uses serial.
In my testing the primary bottleneck isn't about writing to the same partition. Lock coordination to increment the serial key, then send it back across a connection was the real culprit. Virtually all databases make updates into RAM and to a write ahead log, which is then spooled to disk in the background. No matter which pages are written or updated, the changes all go sequentially in order into the WAL. For me, getting rid of Serial Keys was the big hot spot in processing. YMMV.
It's been a while since I've touched db stuff, but pg still has to write the indexes before commit completes, no? So it's still having to go load and write a gazillion pages. And then unless your filesystem has atomic page writes like ZFS, it also writes the whole page to WAL to ensure that it doesn't do torn writes. Writes to the same page within a checkpoint don't need full page write, so benefit.
...if you work specifically on web applications with a centralized database.
Some of us don't, so these types of universal proclamations are rather unhelpful.
another benefit of not using UUID:
for AI heavy apps/agent they are token expensive
I dislike UUIDv7 for no other reason than I hate any UUID with a dash. Makes navigating/selecting ids a pain with Ctrl/Cmd left/right arrow.
As others have said, I don’t think the time was meant to be something you relied on for the object time, but more the help with indexing and sharding.
Yeah this was odd: "Superficially, it seems that embedding a timestamp in the ID can remove the need to track the creation time in a separate database column" Who actually tried to do this?
I didn't assume uuid7 was guaranteed monotonic either. Of course not if you have different clocks. That's not the point of it.
I have never tried to do this myself, for the obvious reasons you've stated, but postgres has a function to do it so clearly enough people did do it:
https://www.postgresql.org/docs/current/functions-uuid.html#...
> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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.
Yes, UUIDv7 doesn't have the same charm and texture as tree notches. But it's been a huge help with my tables containing billions of records.
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.
perfect example of missing the forest for the trees
the UUID timestamp is not for de-duping purposes. it's avoid expensive re-indexes that happen with truly random ids
The Python version simply forces the timestamp forward if needed to preserve ordering:
https://github.com/python/cpython/blob/8d119208074fef0f652f1...But if you did want "monotonically increasing IDs", as the article suggests, that is what you want.
This is perfect if you only have one machine and the process never restarts. But then if you only have one machine you don’t realistically need uuid identifiers.
This is why the oldschool UUIDv1 spec specifies initializing the sequence value at a random number:
“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”
I feel like this article way downplays the entire point of UUIDv7s, which is that it lets you sort (and store!) your data kinda sorta by creation time, no overlap ever. That's great for cursors, for streaming data, for twitter clones, for chat messages, for player moves, and so on and so forth. Nearly everything that's vaguely realtimey that you might like to both persist and broadcast live to other clients, and displayed in a stable & reasonable order, benefits from a property like this.
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.
Yeah, I think the key here is that you have to know what the limitations are. UUIDv7 does _not_ provide you with sorted and ordered IDs by creation time, it provides you with _kinda sorta_ sorted and ordered IDs by creation time. That's probably fine for a lot of use cases, but if it's not OK for yours (forensic accounting, risk mitigation for banks, whatever), you can't use it for that. You're right it's not a technical problem necessarily, but when the marketing material describes something without an asterisk, people who are unaware that what's being described is impossible at the limits can make bad system design decisions based on that promise.
Great article.
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... ]
See: NTP, Lamport and Vector clocks. Spanner (Google) does make a good job of using atomic clocks, but it takes a hang of a lot of money.
I've never bothered with anything but uuid4. Yes there are special circumstances where a public uuid7 helps because you're sure the timestamp leakage is ok, and you made sure it's not guessable, and you're sure some index performs better with it in a way that matters. There are also certain distributed systems where it has helpful properties. The amount of times blanket advice is given to use uuid7 far outweighs how often I've been in that situation, especially when people are talking about Postgres primary keys.
>Unfortunately, the timestamp embedded in a UUIDv7 ID may not relate to the object creation time. The Postgres docs for uuid_extract_timestamp helpfully warn that “the extracted timestamp is not necessarily exactly equal to the time the UUID was generated”.
Just like NOW() won't have the current time.
> the problem is that system clocks cannot be synchronized to a suitable precision.
There are fun conditions under which a quasicrystalline clock could coordinate this in a distributed way. https://en.wikipedia.org/wiki/Time_crystal
> not necessarily exactly equal to the time
I believe it was Poincare who has proven that it's even theoretically impossible to synchronize clocks exactly.
Wasn't that Einstein with special relativity?
So where do you actually draw the line for a business and a customers table whether to use uuidv4 vs uuidv7, in terms of privacy and efficiency?
Do you care about users knowing when another business was put into the system (e.g. how long they've been with you"? If yes, v4. If no, v7.
v4 is less efficient but more private.
alexsci destroyed one of my core assumptions in a bitemporal data system project I've been noodling on. Namely, this oh-so-confident presumption:
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...
Wow... That is some nitpicking... The major issue these UUIDs solve for me is the fact that using a random UUID as a primary key of a relational database leads to horrible 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...
Serial PK solves this too, better actually cause it's smaller
Why would I ever use a UUID PK if I had the option to use a serial one instead?
People do it, but seems like you get it. Every time I'm on a new team or job, they have UUID PKs in a single DB for no reason.
It's because it's opaque. Signing up and seeing a page like `/companies/366` unnecessarily leaks company data. Sometime it's a huge deal, sometimes not; not here to litigate that. But it's trivially avoidable.
The UUIDs discussed here leak the time that the record was created, so one could conceivably extract the growth rate, retention, sequence of event, etc. That's not great either.
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.
The author is in DevSecOps (i understand the Sec part...) - nitpicking is in the job description.
> Superficially, it seems that embedding a timestamp in the ID can remove the need to track the creation time in a separate database column, providing marginally more efficient database operations. If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.
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 generally you can still rely on that aspect, compared to the alternative of UUIDv1-4. Even the worst cases he mentioned would cause minimal page splitting problems in a database compared to even the best cases with UUIDv1-4. The biggest take away I got from the article is that there is some wiggle room in how things can be implemented in the UUIDv7 spec, and it's important to be aware of the implementations you are using.
Just use UUIDs as they were meant to be used, as a random identifier. Don't shove random other junk into it!
The point of shoving the timestamp into it is to make it work well with database indexes. UUIDv4 is awful in that regard.
Sounds like the problem here is that databases need to index on more than one thing at once
It doesn’t have anything to do with indexing more than one thing.
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.
Yes, which means that you should probably be able to provide your timestamp to the database so it can use it for optimizing its index, rather than trying to shove it in your ID
If you did that you would also have to provide the timestamp every time you wanted to look up an ID, and now we're back to UUIDv7 again.
Sure but at least you are being honest about it. For what it's worth the database can handle this internally without you needing to hand this back but if you have a bad one then yes you should probably have it give you back a location hint that you use for future queries
They already do. UUIDv7 neatly solves a common problem: a random, unguessable, public id that works well with databaes. UUIDv4 is infamous for this.
This very much sounds like databases do not, in fact, support indexing on more than one thing at once.
> My mental model for UUIDv7 is that it’s a mash-up of a bunch of different ideas which implementations are free to choose as they see fit. There’s a wide variance of properties that UUIDv7 implementations can provide, with varying degrees of certainty they can successfully provide those properties.
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.
Most (all?) of these are also true of UUIDv1. Devs in 2026 should be well versed in using UUIDv4 if they really truly can never leak anything.