Rendered at 19:14:42 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
ricardobeat 2 days ago [-]
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 they’re not supposed to be public ids, what advantage do they have over serial integers for a central db (the vast majority of the web runs this way).
tibbar 1 days ago [-]
Others have mentioned their difficulty in distributed systems; but even for traditional enterprise workloads, serial IDs don't scale very well. They force all writes through a single, transactional bottleneck, so you can't shard your data. UUIDv7 and friends lets you guarantee that records get a unique identifier without that coordination cost. There are also scenarios where you need to have access to an ID before the write to the database succeeds.
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.
chaz6 1 days ago [-]
Every time a discussion like this comes up I am reminded of Microsoft's "RID master" used in Active directory. A central server issues out chunks of ID's to each server on demand (usually when a watermark is reached), so each server always has a pool of ID's available to use.
Are there any extensions for PostgreSQL that do this?
tibbar 1 days ago [-]
This is actually a very interesting question. I assume that you are referencing extensions that add shards to Postgres, since it runs on a single server out of the box. You made me curious how those extensions/rewrites handle sequences.
It looks like Citus (one such extension that runs shards on separate workers) does something a little like what you described. Each node gets an ID, which becomes the high bits of the sequences it generates. So serial IDs are monotonic within a shard, but not across shards.
Aurora PostgreSQL Limitless uses different abstractions, but also ends up reserving different chunks of IDs for different routers to use.
rplnt 1 days ago [-]
I use this pattern in mysql. One row, and one update query gets you a block of IDs. Don't want to attempt to write it out here without looking, but the trick is using the last insert id in the query (and on the result).
It's one query, so shouldn't require a plugin I hope.
On the other hand, one modern server is 10,000x faster than a 1990s server with 10,000x the capacity, and modern RDBMSs are better at executing queries and give you more options for index types, so the 1990s single-server approach goes very, very far.
I suppose that over time we'll also see more data sovereignty laws, which gives a pretty natural partition key (i.e. for most data you needn't have a global serial because putting them in one database where you'd be worried about conflicts would be illegal anyway, and if you do have some place where you can do those joins, you can adjoin a partition id).
tibbar 22 hours ago [-]
Postgres/MySQL and friends are incredibly good at handling the kind of workloads they were designed for (transactional reads and writes, and perhaps some aggregate queries if you can plan your indexes in advance.) You can scale that quite far, although if you get big enough you will eventually run out of room there (around 10k writes/sec or so.)
However, relational databases are a poor fit for writing logs, for performing analytical queries, and various other purposes that most reasonably large businesses today need. You can try to use your relational DB for them, but you will create real performance issues very fast; and even if you can get the DB to scale up for the load, you'll end up paying way more money that you would from using the right kind of DB for that workload.
otterley 1 days ago [-]
I think you may be assuming that insertion requests are not being distributed across long distances. Software, servers, and storage may be faster, but nobody’s broken the speed of light yet.
ndriscoll 6 hours ago [-]
I suppose that's fair, though in my experience nobody seems to think it's crazy if the computer takes over 1s to respond to a human, so 200 ms is kind of small potatoes, and for most cases you can have a local read replica, so only writes need be slow.
grey-area 1 days ago [-]
I mean, I'd say for 90% of enterprise or high traffic workloads, a single big db (1990s style as you call it) works very, very well and has done for a long time. Sharding and using distributed dbs is not often actually necessary.
To take one example this high traffic website (HN) uses one db and serial integer ids as you can see in the urls. A single master db with read-only followers can scale very well even for higher workloads than most businesses ever see. There are a lot of businesses trying to solve this problem (distributed ids) that shouldn't be and will in fact never need it.
As we can see from the article UUIDv4 is not suitable as a pk, certainly not where you want good performance, the performance is significantly worse than v7 or integer pks, and v7 isn't really suitable if you want to hide business data because of the time stamps. So distributed dbs are the best use-case, and those are not often necessary IMO.
tibbar 22 hours ago [-]
The killer app for UUIDv7 is logs. High-volume, distributed, non-public data that you probably want to partition by time as well. Log volume typically grows much, much faster than transactional writes, and any reasonably big organization is going to want to keep that data out of their relational database.
As far as relational databases go, another common workload they're a bad fit for is analytical queries. If you employ any data analysts or operations staff, they can bring even the biggest deployment of Postgres/MySQL to its knees pretty fast with relatively simple queries. Read replicas buy you a little runway there, but they're just not a good or cost-effective way to power BI workloads.
> To take one example this high traffic website (HN) uses one db
First of all, HN does not use a single database! E.g. it powers search with Algolia and API queries via Big Query, and that's just for external traffic.
Second, HN is not a high (write) traffic website. It's a forum. We can actually see from those serial IDs that there are about ~50 million all time comments. HN is about 20 years old; that works out to about 5 writes per minute. Of course growth has increased over time, but it's still probably less than 1 write per second.
So, I'd agree with you that lots of interesting websites can power their primary application write traffic with a traditional relational database, but I think you'll find that those properties end up needing several other databases anyway for other purposes.
grey-area 11 hours ago [-]
I agree there are solid use cases for distributed dbs, but they are relatively uncommon and not something most businesses need to think about.
Many businesses outsource distributed logs for example as it’s not a core concern.
10k writes a second is a problem that a very small number of systems have relative to all deployed web servers, the normal case is far closer to HN.
If you grow close to that level you have the resources to think about more complex solutions. Most businesses will never get close to it.
Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia.
Ah, I was using "enterprise" in the colloquial tech/B2B sense as a company of ~1000 people or so. There are about 8,000 businesses of that size in the US, but they employ about half the private workforce. It appears to me that Cassandra adoption is very heavy within that market segment, for instance.
(Keep in mind that pushing relational databases to 10k writes/sec is not optimal! Well before you get to that point, there are much more cost-effective options.)
On to a few other nits...
> 10k writes a second is a problem that a very small number of systems have relative to all deployed web servers, the normal case is far closer to HN.
Hmm ... I would estimate that the majority of deployed servers today use a distributed database, operating out of clusters for e.g. Google, Meta, Amazon, Netflix, Microsoft, CDN operators, and so on.
And, of course, HackerNews is also using distributed databases, so it's not a great counterexample...
> Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia
HN uses Google BigQuery to support a company they VC funded? Anyway, this is "no true Scotsman"-ing databases.
nodamage 44 minutes ago [-]
Uncommon? How many people have a phone in their pocket? Any app that functions offline (for when you don't have cell service) and syncs between devices essentially has distributed nodes minting data.
surye 2 days ago [-]
Two major use cases I've used for this are distributed databases, so no coordination of series is required, and a specialized case of this, offline data creation for syncing at a later time.
grey-area 2 days ago [-]
Those sound sensible uses for them.
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.
cpburns2009 1 days ago [-]
What's bad about using them as opaque public ids? The only information that's leaked is approximate time of creation which is moot for most applications.
WorldMaker 1 days ago [-]
Things like age of an account/record-in-an-account can help find "higher value" targets and/or "less secure"/"more likely to be currently retired/inactive" targets.
grey-area 1 days ago [-]
The article goes into a few reasons.
cpburns2009 1 days ago [-]
I read it. There's no convincing reason for most applications.
nodamage 1 days ago [-]
Indeed. There are a couple billion devices on the planet for which this is a common requirement.
xxs 1 days ago [-]
serialized ids are in most cases a mistake from business point of view revealing the amount of entities created, e.g. the amount of new user registrations each day. Just create a new user to sample.
Some of the bottle neck of having a central system issuing them one by one can be alleviated by high-low schemes (multiple the received seq. by a number, e.g. 1000 and use a local counter), still unnecessary effort.
grey-area 1 days ago [-]
You don’t have to use a pk as public identifiers.
If this is your use-case, private serial pk and fully random public identifiers is a better trade off than uuid. V4 is inefficient as a pk and v7 leaks info too.
Many business don’t really have super secret customers/growth and don’t need to hide that info, but if you feel you do you can transition to uuidv4 (not v7) for public identifiers while keeping an efficient pk.
xxs 12 hours ago [-]
You can be much simpler, you can always one-way-hash your primary keys (along with any pepper), if you wish to hide them. Still, use UUIDs freely.
yencabulator 2 hours ago [-]
One-way hash -> you need to store the result to look up based on it -> likely better off using random identifier, and not worry about hash weakness etc.
globular-toast 1 days ago [-]
UUIDs are one of the most misunderstood things in general, I think. Their entire point is that you don't need a central db to generate them.
l5870uoo9y 2 days ago [-]
Do you then maintain a separate column with a UUID for public ids?
tibbar 1 days ago [-]
There is a good discussion of the tradeoffs here [0]. It is often fine expose UUIDv7 publicly, e.g. if the event time is not a secret and you use good entropy for the non-timestamp bits.
However, sometimes you might decide that you care, and use a random identifier instead. In those cases you probably won't benefit from a second UUIDv7 identifier, since you're already paying the cost for a primary key index, but perhaps if you are also doing certain kinds of range queries and have a certain kind of storage engine you still get something out UUIDv7.
In other words, it depends. And it might not matter at all if you don't have much data, and/or it's not being generated in a distributed way, and/or you're not doing range queries or partitions in a way that can take advantage of it.
I don’t agree with the advice that it should be a public facing ID - that depends on the application
hackingonempty 2 days ago [-]
> 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.
NyxWulf 2 days ago [-]
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.
WorldMaker 1 days ago [-]
Assuming the database uses a UUID sort order that reflects UUIDv1 time-based sorting (the last version with timestamps and even then temporal ordering of UUIDs was only one possible sort order; v1 also included machine IDs and some databases optimized for locality by machine for reasons that made sense with v1 but also relate to why we gave up on v1 because machine IDs were problematic privacy issues in wider usage). Many of the databases implementing explicit v7 support are choosing time orders for v7 sorting, and only time orders.
srcreigh 2 days ago [-]
> Generally
Except for Postgres
thayne 2 days ago [-]
Postgresql uses pages too. The records aren't kept in any kind of order, but the indexes are. So uuidv7 is still useful for postgresql because it means inserts are less likely to be spread across many pages in the primary index.
2 days ago [-]
pkulak 2 days ago [-]
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.
jrs235 1 days ago [-]
Similar experience here. Random guids as primary keys quickly fragment your table and indexes. You'll need to defrag frequently.
zeendo 2 days ago [-]
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
dspillett 2 days ago [-]
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.
computing 2 days ago [-]
you thought right. the author is just plain wrong here.
rrr_oh_man 2 days ago [-]
I thought it was for distributed DBs?
btown 2 days ago [-]
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.
This package should be removed from pypi or somehow give warnings
cpburns2009 2 days ago [-]
> "... 48 bits of randomness"
That's an awful waste for a 128 bit object!
9dev 2 days ago [-]
not python is what you really want I suppose.
kentm 2 days ago [-]
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.
ajb 2 days ago [-]
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.
computing 2 days ago [-]
[dead]
rcoder 2 days ago [-]
If you really need monotonic, secure/unguessable IDs, there are options like CUID2 (github.com/paralleldrive/cuid2).
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.
cpburns2009 1 days ago [-]
ULID is what I use. I convert it to UUID before storing in PostgreSQL. Technically it's an invalid serialization but it works. The system predates UUIDv7. I also like ULID's shorter representation.
WorldMaker 1 days ago [-]
I did that with SQL Server for a time. It was particularly a strange serialization because I tried to match ULID ordering with SQL Server's UUID sort algorithm.
hdjrudni 2 days ago [-]
What is special about Postgres' column type? Is it not just binary(16)?
cpburns2009 1 days ago [-]
PostgreSQL's UUID type is convenient. But yes it's pretty much just a 16 byte type.
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.
mahboi 2 days ago [-]
For a database primary key, yeah. Just use serial.
esafak 2 days ago [-]
That depends on your database. Serial keys can create hot spots in distributed databases because new writes are consistently directed to the same partition.
ndriscoll 2 days ago [-]
Yeah but for nearly everyone, they'd get better performance from not using a distributed database, and benefit from "hotspots" i.e. data locality.
JoshTriplett 2 days ago [-]
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.
NyxWulf 2 days ago [-]
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.
JoshTriplett 2 days ago [-]
> If your insert requires a parent record with a single child record, you have to get the next sequential id
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.
baq 2 days ago [-]
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
mahboi 2 days ago [-]
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.
NyxWulf 2 days ago [-]
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.
ndriscoll 2 days ago [-]
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.
F_r_k 2 days ago [-]
I don't want my competitors to know about my number of clients, etc.
mahboi 2 days ago [-]
Don't expose your PKs
tyre 2 days ago [-]
So you have a second, opaque unique identifier for the frontend to fetch? At that point why not just use a single column (UUID)?
crazygringo 2 days ago [-]
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).
mahboi 2 days ago [-]
Yep, you'll probably join like 20 of those for each public uuid lookup. There are potential reasons not to do it this way, but it's a good default.
ndriscoll 2 days ago [-]
Higher density for indexes you use for joins. Also you can then use UUIDv4 to hide creation times if that's sensitive too.
2 days ago [-]
nextaccountic 2 days ago [-]
It makes more sense to have two columns yes, one autoincrement for the primary key, and another uuidv4. Because uuids are too large and makes indexes slower
spixy 1 days ago [-]
So when public APIs use uuidv4 to fetch item, those uuidv4 still need to be indexed...
mahboi 20 hours ago [-]
Right, but that's one lookup vs the many joins you're probably doing internally
skrtskrt 2 days ago [-]
This was hard enough to enforce when it was just people writing code, and once one of those stupid incrementing integer IDs go in a public API or important codepath, it can be a real pain to rip out.
rplnt 1 days ago [-]
Use snowflake? It leaks some info, but not nearly as much.
nodamage 2 days ago [-]
...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.
woogle 2 days ago [-]
another benefit of not using UUID:
for AI heavy apps/agent they are token expensive
0x20cowboy 2 days ago [-]
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.
mahboi 2 days ago [-]
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.
tredre3 2 days ago [-]
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:
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
mahboi 2 days ago [-]
It makes sense. Since part of the UUID is a timestamp, there should exist a way to extract it.
tanin 2 days ago [-]
I generally avoid using UUID unless there's a strong reason for it.
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.
tyre 2 days ago [-]
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.
grey-area 2 days ago [-]
It’s not the default though is it. I wonder why?
Dylan16807 1 days ago [-]
In what? Many things do default to UUID. If you mean databases, an auto-incrementing integer makes a lot of sense for the median database. It's not trying to hide the number of IDs and traffic is well below the level that central generation is a problem.
NyxWulf 2 days ago [-]
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.
Dylan16807 2 days ago [-]
Without that reddit user saying what kind of UUID or how it was generated, that anecdote is nearly useless.
> 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.
xxs 1 days ago [-]
UUID are amazing and should be the default primary key for pretty much anything. They can be independently generated and require no central shared location.
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.
tossandthrow 2 days ago [-]
You don't have to. You can absorb the risk. Which most orgs do.
nextaccountic 2 days ago [-]
If there's a collision, just generate another uuid
dtjohnnymonkey 2 days ago [-]
It would be hard to know whether there is a collision at generation time. If you did know, it implies a lookup in a global database, then you are back where you started.
nextaccountic 1 days ago [-]
there's some ways around this. for example, if you generate and the db answers this uuid already exists, this obviously is a failure. will it ever happen in your lifetime? if it does, you can be almost 100% sure that there is a problem in your random number generator. but those DO exist, like the CA certs that had to be revoked recently because they had like the number 4 as a random key
now, what to do with such an error? I think you should either log the error and retry with another uuid, or pass the error back to whoever called you (often the user, giving them an error page). Or maybe you will try for N times, and then return an error. what you can't do is to pretend everything is fine
if you do retry then it's just generating the uuid again with extra steps. i think this is fine
that's a new failure mode for sure, but not very different from other failures like connection loss to the db (also a fatal error, which in this case always should be surfaced to the caller). every networking API should have some possibility for error, and in this case this is just a new kind of error
dspillett 2 days ago [-]
> 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.
sbuttgereit 2 days ago [-]
Hear, hear. Just because something -looks- like it has the qualities of a sequence, order, or other meaning outside of that one thing which it is: an identity, doesn't mean that you can reliably use it for that other purpose. I don't care if it's a UUIDv7 or am autoincremented-integer-of-some-sort: you're getting a record ID and nothing more. Mistaking and ID for something more is the best preparation for some poor soul's headache at some point in the (possibly distant) future.
clownstrikelol 1 days ago [-]
> you're getting a record ID and nothing more.
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.
dspillett 1 days ago [-]
> 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.
That to me sounds like a good way to amplify a bug whereby a foreign key has not been defined¹ where it should be. I hope there are copious warnings about it in the docs and when creating a table that way.
--------
[1] Or can't be as the referrer is in an external system, though in that case you probably want a separate external ID, possibly a UUID of some sort.
clownstrikelol 1 days ago [-]
> That to me sounds like a good way to amplify a bug whereby a foreign key has not been defined¹ where it should be.
Good, as it should be.
> I hope there are copious warnings about it in the docs and when creating a table that way.
It is, SQLite’s docs are great and explain how it works in great detail.
Ah, a connoisseur of the semi-automatic foot-gun, I see. Or maybe the foot-RPG?
> It is, SQLite’s docs are great and explain how it works in great detail.
But not the “and when creating a table that way.” part?
1 days ago [-]
mahboi 2 days ago [-]
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.
nitwit005 2 days ago [-]
The Python version simply forces the timestamp forward if needed to preserve ordering:
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.
dpark 2 days ago [-]
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.
Lammy 2 days ago [-]
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”
vbezhenar 2 days ago [-]
So you can't generate more than 1000 UUIDs per second without skewing timestamp portion? That's not a lot. Bulk imports often require a lot more inserts.
ben-schaaf 2 days ago [-]
Reading that code there's a 48-bit counter used for each millisecond, so ~281 quadrillion UUIDs per second.
computing 2 days ago [-]
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
tossandthrow 1 days ago [-]
I have had this conversation with the team. I lead, who started to use properties of the ID for ordering etc.
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.
Xirdus 1 days ago [-]
You can encode extra things in IDs no problem. E.g. Polish civil records system uses PESEL numbers which, on top of being unique IDs, also encode date of birth and gender.
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.
tossandthrow 1 days ago [-]
As long as these are not accidental invariants.
Xirdus 4 minutes ago [-]
As long as they are invariants. Due to clerical errors, the aforementioned PESEL is not really unique and the gender doesn't always match records and even the checksum digit is sometimes incorrect. And the encoded date of birth isn't always the actual date of birth, but this one is intentional (there's only 5000 numbers available for each day, per gender).
skrebbel 2 days ago [-]
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.
roughly 2 days ago [-]
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.
vbezhenar 2 days ago [-]
You probably can't use that for streaming. Moment of UUID generation and moment of transaction commit are different, so it's very possible that even if your code generates UUIDs strictly sequentially; the observable sequence from the database will not be always sequential. I've been bitten by this once when I've used serial IDs and I don't think that UUIDv7 is any different in that regard.
real_faxenoff 2 days ago [-]
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.
phoronixrly 2 days ago [-]
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...
alexchamberlain 2 days ago [-]
The author is in DevSecOps (i understand the Sec part...) - nitpicking is in the job description.
mahboi 2 days ago [-]
Serial PK solves this too, better actually cause it's smaller
phoronixrly 2 days ago [-]
Why would I ever use a UUID PK if I had the option to use a serial one instead?
mahboi 2 days ago [-]
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.
tyre 2 days ago [-]
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.
tredre3 2 days ago [-]
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.
gcbirzan 2 days ago [-]
>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.
awesome_dude 2 days ago [-]
> 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.
NyxWulf 2 days ago [-]
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.
time4tea 1 days ago [-]
Uuidv7 is also not so great for preserving privacy - they give away a lot of info about when an activity happened... account id = when you signed up, order id = when you bought a thing etc etc. So, combined with where a thing happened, at a shop say, puts you at a place and specific time = loss of physical safety.
pphysch 2 days ago [-]
> 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.
pupppet 2 days ago [-]
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.
vbezhenar 2 days ago [-]
When I have enough time, I'm usually using 125-bit ID which I convert to 25 alphanumeric characters (something like [A-Z0-9] excluding [IO01]).
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.
inkyoto 2 days ago [-]
If you are looking for a monotonously increasing, sortable ID, you might then want to consider ULID[0] which predates UUID v7/v8.
It is probably not the right choice if you require a cryptographically strong and unique ID.
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... ]
awesome_dude 2 days ago [-]
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.
simonbuilds 2 days ago [-]
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?
vbezhenar 2 days ago [-]
I prefer to use int16/int32/int64 IDs for primary keys and UUIDv4 for "visible" keys. So database always uses integer fields to refer to other rows and web API uses UUID.
tyre 2 days ago [-]
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.
deepsun 2 days ago [-]
> 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.
Dylan16807 1 days ago [-]
It's not impossible at all, you just need to agree on a reference frame. Which is not hard to do when you're sitting on a planet.
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.
orthoxerox 2 days ago [-]
Wasn't that Einstein with special relativity?
deepsun 2 days ago [-]
Einstein read Poincare papers for sure, and realized that assembling all those pieces together turns the physics upside down. But many of the results of the special relativity were well known before. For example, the great Michelson–Morley experiment was conducted in 1887, 18 years before. Einstein was the first to realize that all the pieces form a totally new puzzle.
ForHackernews 2 days ago [-]
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.
saagarjha 2 days ago [-]
Just use UUIDs as they were meant to be used, as a random identifier. Don't shove random other junk into it!
cpburns2009 2 days ago [-]
The point of shoving the timestamp into it is to make it work well with database indexes. UUIDv4 is awful in that regard.
saagarjha 2 days ago [-]
Sounds like the problem here is that databases need to index on more than one thing at once
cpburns2009 2 days ago [-]
They already do. UUIDv7 neatly solves a common problem: a random, unguessable, public id that works well with databaes. UUIDv4 is infamous for this.
saagarjha 2 days ago [-]
This very much sounds like databases do not, in fact, support indexing on more than one thing at once.
ndriscoll 2 days ago [-]
Yes, you cannot be in one order and also a different order at the same time. You can make another index, but that's something else entirely.
saagarjha 10 hours ago [-]
You should make another index is my point. The database layer properly implements this extra index instead of just being a stupid dictionary that you have to massage with the one key value you have
ndriscoll 5 hours ago [-]
That doesn't do anything to help with the issue being discussed, which is index fragmentation and random IO affecting performance. It just means now you have two indexes to maintain.
debazel 2 days ago [-]
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.
saagarjha 2 days ago [-]
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
debazel 2 days ago [-]
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.
saagarjha 2 days ago [-]
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
ndriscoll 2 days ago [-]
How would it internally sort your index by (timestamp, ID) without you later having to provide the timestamp to locate the ID?
adityaathalye 2 days ago [-]
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.)
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...
jongjong 2 days ago [-]
UUIDv7 is not that useful. I'm still using mostly UUIDv4 and UUIDv5.
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.
piaste 2 days ago [-]
> You can't have idempotency without determinism
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.
8organicbits 2 days ago [-]
Hey author here, thanks for reading the post. I haven't seen UUIDv5 used very often but I'll have to think about the idempotency angle more.
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.
jongjong 3 hours ago [-]
Interesting! That said, these examples appear to be crafted in a special way to cause the collision. In a practical scenario with normal usage, I suspect it would be essentially impossible. Sure, it can be a problem in some niche scenarios with a determined attacker but a collision would just mean that the user is not able to create the resource (the system would falsely indicate that the resource already exists); but that's not too bad. At worst, it would reveal the existence of a resource with a specific UUID, but could the attacker have achieved the collision if they didn't know the target UUID already? It doesn't seem like a viable attack given how little information it gives you.
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
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.
Are there any extensions for PostgreSQL that do this?
It looks like Citus (one such extension that runs shards on separate workers) does something a little like what you described. Each node gets an ID, which becomes the high bits of the sequences it generates. So serial IDs are monotonic within a shard, but not across shards.
Aurora PostgreSQL Limitless uses different abstractions, but also ends up reserving different chunks of IDs for different routers to use.
It's one query, so shouldn't require a plugin I hope.
https://dev.to/aws-heroes/scalable-sequence-for-postgresql-3...
Would interact with sharding?
I suppose that over time we'll also see more data sovereignty laws, which gives a pretty natural partition key (i.e. for most data you needn't have a global serial because putting them in one database where you'd be worried about conflicts would be illegal anyway, and if you do have some place where you can do those joins, you can adjoin a partition id).
However, relational databases are a poor fit for writing logs, for performing analytical queries, and various other purposes that most reasonably large businesses today need. You can try to use your relational DB for them, but you will create real performance issues very fast; and even if you can get the DB to scale up for the load, you'll end up paying way more money that you would from using the right kind of DB for that workload.
To take one example this high traffic website (HN) uses one db and serial integer ids as you can see in the urls. A single master db with read-only followers can scale very well even for higher workloads than most businesses ever see. There are a lot of businesses trying to solve this problem (distributed ids) that shouldn't be and will in fact never need it.
As we can see from the article UUIDv4 is not suitable as a pk, certainly not where you want good performance, the performance is significantly worse than v7 or integer pks, and v7 isn't really suitable if you want to hide business data because of the time stamps. So distributed dbs are the best use-case, and those are not often necessary IMO.
As far as relational databases go, another common workload they're a bad fit for is analytical queries. If you employ any data analysts or operations staff, they can bring even the biggest deployment of Postgres/MySQL to its knees pretty fast with relatively simple queries. Read replicas buy you a little runway there, but they're just not a good or cost-effective way to power BI workloads.
> To take one example this high traffic website (HN) uses one db
First of all, HN does not use a single database! E.g. it powers search with Algolia and API queries via Big Query, and that's just for external traffic.
Second, HN is not a high (write) traffic website. It's a forum. We can actually see from those serial IDs that there are about ~50 million all time comments. HN is about 20 years old; that works out to about 5 writes per minute. Of course growth has increased over time, but it's still probably less than 1 write per second.
So, I'd agree with you that lots of interesting websites can power their primary application write traffic with a traditional relational database, but I think you'll find that those properties end up needing several other databases anyway for other purposes.
Many businesses outsource distributed logs for example as it’s not a core concern.
10k writes a second is a problem that a very small number of systems have relative to all deployed web servers, the normal case is far closer to HN.
If you grow close to that level you have the resources to think about more complex solutions. Most businesses will never get close to it.
Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia.
https://www.ycombinator.com/companies/algolia
(Keep in mind that pushing relational databases to 10k writes/sec is not optimal! Well before you get to that point, there are much more cost-effective options.)
On to a few other nits...
> 10k writes a second is a problem that a very small number of systems have relative to all deployed web servers, the normal case is far closer to HN.
Hmm ... I would estimate that the majority of deployed servers today use a distributed database, operating out of clusters for e.g. Google, Meta, Amazon, Netflix, Microsoft, CDN operators, and so on.
And, of course, HackerNews is also using distributed databases, so it's not a great counterexample...
> Re HN using other systems for search and api, that’s more related to supporting companies the vc funded - Algolia
HN uses Google BigQuery to support a company they VC funded? Anyway, this is "no true Scotsman"-ing databases.
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.
Some of the bottle neck of having a central system issuing them one by one can be alleviated by high-low schemes (multiple the received seq. by a number, e.g. 1000 and use a local counter), still unnecessary effort.
If this is your use-case, private serial pk and fully random public identifiers is a better trade off than uuid. V4 is inefficient as a pk and v7 leaks info too.
Many business don’t really have super secret customers/growth and don’t need to hide that info, but if you feel you do you can transition to uuidv4 (not v7) for public identifiers while keeping an efficient pk.
In other words, it depends. And it might not matter at all if you don't have much data, and/or it's not being generated in a distributed way, and/or you're not doing range queries or partitions in a way that can take advantage of it.
[0] https://news.ycombinator.com/item?id=45622045
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 :)
https://github.com/stevesimmons/uuid7/issues/1
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.
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.
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
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#...
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 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.
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.
now, what to do with such an error? I think you should either log the error and retry with another uuid, or pass the error back to whoever called you (often the user, giving them an error page). Or maybe you will try for N times, and then return an error. what you can't do is to pretend everything is fine
if you do retry then it's just generating the uuid again with extra steps. i think this is fine
that's a new failure mode for sure, but not very different from other failures like connection loss to the db (also a fatal error, which in this case always should be surfaced to the caller). every networking API should have some possibility for error, and in this case this is just a new kind of error
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.
That to me sounds like a good way to amplify a bug whereby a foreign key has not been defined¹ where it should be. I hope there are copious warnings about it in the docs and when creating a table that way.
--------
[1] Or can't be as the referrer is in an external system, though in that case you probably want a separate external ID, possibly a UUID of some sort.
Good, as it should be.
> I hope there are copious warnings about it in the docs and when creating a table that way.
It is, SQLite’s docs are great and explain how it works in great detail.
https://sqlite.org/autoinc.html
https://sqlite.org/lang_createtable.html#rowid
Ah, a connoisseur of the semi-automatic foot-gun, I see. Or maybe the foot-RPG?
> It is, SQLite’s docs are great and explain how it works in great detail.
But not the “and when creating a table that way.” part?
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”
the UUID timestamp is not for de-duping purposes. it's avoid expensive re-indexes that happen with truly random ids
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.
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.
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.
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.
Just like NOW() won't have the current 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.
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.
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.
[0] https://github.com/ulid/spec
There are fun conditions under which a quasicrystalline clock could coordinate this in a distributed way. https://en.wikipedia.org/wiki/Time_crystal
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... ]
v4 is less efficient but more private.
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.
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.