Goodbye integers, hello UUIDv7 | Hacker News Reader

Goodbye integers, hello UUIDv7

buildkite.com

726 pointsjuanfatas363 comments

This is great for internal distributed systems where having ordered keys is useful, however, it should probably be noted that these probably shouldn't be used as public identifiers (even though this will probably be the defacto standard and used publicly without thought).

Having any information, specifically time information, leaking from your systems may or may not have unanticipated security or business implications. (e.g. knowing when session tokens or accounts are created).

jonhohle, thanks. Do you know of examples of when milliseconds are part of the session tokens or accounts being created has been exploited?

The German tank production capacity was estimated by serial numbers of captured tanks. There are ways to read all kinds of information by observing energy usage. High resolution time and sequence data undoubtedly reveal more than you’d like.

Most of our lives as boring SaaS etc. software developer will not be near as exciting as this, but of course you may never know.

I parsed the EV chargers APIs where I live (using Frida in Android) and one of the fields returned the daily revenue and profit.

Could be quite useful information to competing charger networks or in future M&A discussions.

I've seen a project for a trading firm that inferred all kinds of traffic and revenue numbers for companies before their quarterly earnings were made public. It wasn't perfect, but knowing with a certain confidence level whether the numbers were going to be better or worse than estimate was profitable for them.

Here in Ukraine trying to estimate enemy's drone and missile production capacities by serial numbers of their parts is quite a mundane task these days.

Sure, for many places it doesn't really matter, but if your URLs or user ids can be seen/scraped by others, you might expose some commercially interesting information to competitors.

And the indexing argument isn't really compelling, is it? You lose very little by sticking to fully random UUIDs.

I could imagine using the timestamp segment of publicly observable ids to estimate activity patterns in an organization. Probably not super crucial and there are probably easier ways in most cases but it could be a big deal at the right moment and for the right target. This could be like a more refined version of PIZZAINT (where you can detect impending policy/operational movements by the quantity of food deliveries to a government organization).

Is PIZZAINT real? I thought it was a joke. How do you even find out how much pizza is being delivered?

In ye olden days you had a minion sitting in a car watching the front gate with a notepad and enough cigarettes to last the night.

Pizza itself might be a bit of a joke but looking for non-operational behavioral changes is absolutely real. The Cuban missile crisis was started in part because soviets played soccer and Cubans played baseball and the presence of soccer fields helped confirm soviet presence (in enough numbers to bother making rec centers). A more advanced version might be the public Strava data leaking US base layouts and locations or Strava helping the Ukrainians kill a Russian submarine commander.

Edit to add: you could also just figure out where your target orders pizza from and pay one of the dudes working there to tell you when there’s a spike in deliveries to your target.

> soviets played soccer and Cubans played baseball

It's a Henri Kissinger's quote, but it's not accurate: Cubans do in fact play football. Also this quote wasn't from the 1962 Cuban missile crisis, but to another event in 1970. That being said, it is true that the US intelligence got warned by the construction of football fields (or maybe even more so by the lack of baseball grounds).

https://www.cracked.com/article_31335_that-time-soccer-field...

That's Goodhart's law, if you want to know something instead of asking just say something which is wrong and someone will correct you

lol

iswydt

I know of people who used leaked customer ids in public facing chatbot solutions (like Intercom) to estimate how fast their competitors were growing and/or how many customers they had.

How would you do that with UUIDv7 though? I see how using sequential IDs would obviously leak that information, but if all you leak is the timestamp the ID was generated, how do you then infer anything about the rate of ID generation?

Given that a UUID identifier fits in a single cipher block, and the whole point is that these are unique by construction (no IV needed so long as that holds true), it seems like a single round of ECB-mode AES-128 would enable quickly converting between internal/external identifiers.

128 bits -> 128 bits

That's an interesting idea, how would you deal with the bits in the UUID that are used for the version? Setting them to random bits may cause issues for clients that try to use the identifier in their own database or application, as mentioned in the article.

Is there a way to encrypt 122 bits -> 122 bits? If so – do that and set version to 4.

Alternatively, just say it's a random string ID and not an UUID.

I'm actually working on encrypting database keys like this, and I opted for a string ID with "url safe" base64. It avoids the ambiguity of looking like a UUID when it's not, and I prefer "o3Ru98R3Qw-_x2MdiEEdSQ" in a URL over "a3746ef7-c477-430f-bfc7-631d88411d49". (Not that either one is very beautiful)

Curious on the preference there, especially when characters in base64url encoding can look similar/ambiguous in some fonts.

The preference is purely about the amount of characters. I hope no one will ever actually read or write these URLs, but you never know...

Ah in that case, base58 can be useful because it doesn't use characters that may be ambiguous in some fonts (doesn't use: 0/O/I/l , but does use: 1/i)

For your information, yes, you can [1]. For example if you have a good enough 128-bit block cipher (e.g. AES-128-ECB), start with a block of 128 bits where specific 6 bits are filled out and others are filled with the plain text. Repeatedly encrypt the block until those specific 6 bits are reached again (and do the same thing in reverse for decryption). This is possible because a good block cipher is also a good pseudorandom permutation, so it should have a small number of extremely long cycles (ideally just one) with a 2^-6 probability of allowed values in average.

[1] https://en.wikipedia.org/wiki/Format-preserving_encryption

What a cute observation!

If there's any way for the client to influence the input, it may be prone to DoS attacks: By my calculations, with a million random attempts, you would expect to find a cycle of length at least 435, which is over 13x the average. (Mind you, multiplying the number of attempts by 10 only adds about 72.5 to the expected cycle length, and probably no one has the patience to try more than 100 billion or so attempts.)

The properties of the permutation are dependent upon the encryption key, so a client being able to select malicious inputs to get long cycles implies either that the client knows the AES key, or that the client has broken AES.

In any case, as I mentioned in a sibling comment, with 3 AES encryptions one can construct a 122-bit balanced Feistel cipher with a constant amount of work.

Cycle-walking FPE has a (nearly) unbounded upper-bound on latency.

Breaking the 122 bits into two 61-bit halves and using AES as the round function for a Feistel cipher gives you a constant 3-encryption latency instead of the expected 64-encryption average latency of the cycle-walking format preserving encryption.

Alternatively, use AES in VIL mode ( https://cseweb.ucsd.edu/~mihir/papers/lpe.pdf ).

Neat idea.

I’m afraid you won’t be able to ever rotate that key, would you? Since it’s result is externally used as an identifier, you would have to rotate the external identifiers, too.

What is the use case for rotating uuids? Aren’t they immutable?

i think they meant rotating the encryption key not the internal uuids

I think you could but it would further complicate the id scheme (would need some sort of a version mask to facilitate a rotation window).

Assuming you have a table where the identifiers are stored you'd have the internal one (UUIDv7) and the encrypted version from it (external id).

You could rotate encryption keys whenever you want for new external id calculation, so that older external ids won't change (as they are external, they need to stay immutable).

If you're storing the identifiers then you don't need encryption, you just generate a UUIDv4 and use that as the external identifier. And then we're back at where the blog post started.

No IV, ECB mode... why bother with encryption at all? Just expose the internal id.

You would still use a secret key, so it's impossible for the end user to decrypt it.

Encrypting an internal id with ECB into an external id continues to allow the comparison for equality of 2 ids, to determine whether they are the same or not, but except for this it removes all the information contained in the structure of an UUID.

ECB is perfectly secure when you use it on a single block.

And you have no problem with the same data being encrypted being identifyable (no salt). i see now why it might be useful in this case, though I still don’t like the idea, it feels bad somehow. (Yeah I get that you save storage for some computation)

You are encrypting a single block of unique information. No other encryption mode gives you any advantages whatsoever.

Because the internal ID exposes timing/sequence information, as per jonhohle's comment.

This seems overly complex, and you need some kind of key too.

Why not just hash it with pretty much any hash function?

Because a hash is (by definition) a one-way function. You need to be able to go the other way for incoming ids.

Ah, good point. Hadn't thought about the opposite direction.

A hash is not reversible, so you’d need a database index to recover the original efficient-to-index identifier, which misses the whole point ;)

If you didn’t care about index clustering then just use UUIDv4

Although you could use a hash index to avoid the more deleterious effects of insertion, as long as you don’t need a unique constraint anyway…

Why not just use the AES-128 result as the UUID then? What's the benefit of the internal structure at all?

If AES-128 is an acceptable external UUID (and likely an acceptable internal one), then you might as well just stick with a faster RNG.

That would be the same as using a random identifier (UUIDv4, for example) with the associated indexing issues when stored in a database.

The whole point here would be that you can expose something opaque externally but benefit from well behaved index keys internally.

Storage is cheap, you might as well store the extra integer.

Storage is cheap, updating indexes is not.

This is why I’ll probably just always use a UUIDv7 primary key and a secondary UUIDv4 indexed external identifier… which is extremely close to how I tend to do things today (I’ve been using ULID and UUIDv4)

Why not use snowflake IDs?

Lookup speed. Direct mappings are faster. If something needs an external identifier that can be looked up for URL/API queries and such things…

Internal sorts and index on write with ULID/UUIDv7 which reveal potentially sensitive timestamp information, and so when that’s not appropriate a separate column can be used for…

External opaque identifiers which are UUIDv4, and if indexing speeds become an issue, can be switched to deferred indexing…

It’s a good balance and everything I’ve ever used supports this (I only needed a pretty strong representation wrapper for ULIDs since PG doesn’t validate UUID bit field structures so i can just miss-use UUID columns)

It looks like Snowflake has the same information exposure issues that using UUIDv7 or ULID for a publicly visible identifier.

But you still need an external->internal lookup, so doesn't that mean you still need an index on the fully random id?

> What's the benefit of the internal structure at all?

Purely random identifiers are the bane of DB indices. The internal structure is sequential-ish and therefore indexes well.

Purely random identifiers are the recommended primary key for plenty of databases - eg. spanner.

Random identifiers spread work evenly between shards of something sharded by keyspace. They also don't get 'hotspotting' on more recent records - recent records frequently get more than their fair share of updates and changes, and database query planners have no knowledge of that.

Spanner is also bespoke, and was probably designed with that in mind.

Anything with a clustering index - MySQL (with InnoDB), SQL Server - will absolutely hate the page splits from a random PK.

Postgres also doesn’t like it that much, for a variety of reasons. Tuples are in a heap, but the PK is still a B+tree (ish), so it still suffers from splits. They also use half the default page size as MySQL, AND their MVCC implementation doesn’t lend itself to easy updates without rewriting the entire page.

Go run benchmarks with some decent scale (tens of millions of rows or more) on any DB you’d like between UUIDv4 and UUIDv7 as the PK, and see how it goes.

Right. In a way, these new sequential UUIDs approach the fact that recent records are often accessed more frequently as something that can be exploited, not as an issue that needs to be ironed out by randomness.

For tables this is not such a problem because of reasonably good locality (rows inserted close in time will end up close in the file too), but for indexes that's very difference. In Postgres this is particularly painful for writes, because of the write amplification.

None of this really breaks sharding (IDs are still unique and can be hashed), so no new hotspots. It can't fix the "many rows with the same ID" issue, but neither can any other ID.

Yes, you want your clusters hot, but not too hot.

For large distributed systems like Spanner you want to avoid a hotspot on a single node as that limits throughout.

However for a single-node system like PostgreSQL you want hot spots because they are cache friendly. (Locks aside)

Basically you want a hotspot that is as hot as a single node can manage, but no hotter. Even for things like Spanner you still want good cache hits (which is why they support table interleaving and other methods of clustering related data) but in general avoiding overloading single nodes is more important (as it hurts latency and efficiency but doesn't limit throughput like a hotspot would).

You could use whatever is convenient for a DB index, and then encrypt it as the public ID.

One key for all tokens or one key per token? If it’s the latter a simple XOR would do because it would be the equivalent of a one time pad.

I don't think it can be a key per token, or it will scale appallingly.

One key per token would require a table matching internal tokens to their key for forward conversion, and another table matching external keys to their key for reversing.

Might as well just use randomly generated external keys and have one table if you were doing that.

So, one key per all tokens.

I like the idea, but I think it's not possible to rotate the key with that approach, without introducing a breaking change. Eternal secrets are usually a very bad idea, because at some point they are going to be leaked.

But the private data you are protecting (the user's account creation time) has the same properties as an eternal secret.

Therefore there doesn't seem to be much downside in this specific case.

If you were using something like UUIDv4, you wouldn't be exposing that information at all though, neither in cleartext or ciphertext. It seems weird to say, "the user ID contains secret information so we encrypt it with an eternal fixed pre-shared key then share the ciphertext with the world", when you could've just said "the user ID contains no secret information".

It feels like the right solution here is to pick between: use UUIDv7 and treat the account creation time as public information, or use an identifier scheme which doesn't contain the account creation time.

You could just randomize the timestamp. adding +/- month or two to the UUIDv7 won't break the advantages all that much.

That doesn't fix the info leak though, you're still leaking approximately when the account was created. Knowing whether an account was created in 2003 or 2023 is a pretty significant amount of information even if you just know that it was created some time between June 2003 and August 2003.

I mean it's certainly an improvement over telling everyone the millisecond the account was created, but if account creation times are to be considered non-public info, I would probably just not include any version of it in public facing user IDs. And if you do consider approximate account creation times to be public (such as HN, where anyone can see that my account was created July 1, 2015), then adding some fuzz to the timestamp seems to be a good way to avoid certain cryptographic issues.

With massive amount of data this would negate the performance advantage completely. Related data is no longer stored close to each other, every time-sequential access is a cache miss ...

Sure you can, just prefix the encrypted identifier with a version number.

    https://app.example.org/file/1:abcdef12345

    -> decrypt abcdef12345 with key "1" to yield UUIDv7 key of file (no matter what the latest key is)

An incrementing version number would once again leak time information. Even a not incrementing version number would leak that kind of information, because if you know the timestamp of another ID with the same version.

I think there is no good alternative to random external identifiers.

Oh that's a good point.

Yep have used an approach just like that, worked quite well if you have a strong pattern to easily translate from one to the other. Gives you an id with the right properties for internal use, efficient indexing etc, and in its encrypted form gives you the properties you want from an external identifier being unpredictable etc, all from one source id.

It is true that now your encryption key is now very long lived and effectively part of your public interface, but depending on your situation that could be an acceptable tradeoff, and there are quite a few pragmatic reasons why that might be true as has been described by other comments.

Edit: you can even do 64bit snowflakes internally to 128bit AES encrypted externally, doesn’t have to be 128-128 obvs

>> It is true that now your encryption key is now very long lived and effectively part of your public interface

No need to encrypt, just store the external key in a table. Not that you're likely to change algorithms.

True you could rotate by persisting the old value and complicate your lookup/join process, not my idea of an acceptable solution but yep totally possible and worth it for some set of tradeoffs.

You are basically describing BuildKite's previous solution.

Late edit: I meant to say No need to encrypt on the fly. Do it once and save it.

Thinking that harder-to-guess IDs will mitigate attacks is an example of security by obscurity. It's better to think of any IDs in your database as being public knowledge, because they will leak anyway. Assuming that no one can guess another ID leads to shoddy practices. I generally keep IDs sequential and build security around the basic assumption that IDs are not keys, passwords, sessions, or secrets - they're just the public matching identifier for those things.

To that end, I think it's neat to be able to improve indexing on UUIDs, but it's not a security solution.

Doesn't that still leak (statistical) information?

It may not be technically security, but e.g. knowing your competitor just added N products to their shop, might be a security issue for the business.

You're absolutely right, this is also why you generally encrypt sessionized or "consistent view" pagination tokens for public apis (save for primitives like ddb or Kafka)

The end user should know no details about your internal key space.

It may. Certainly, for instance, sequential invoice numbers do. If a business decides to take measures to obscure that, no problem. All I'm saying is that obscuring a numbering system for data artifacts shouldn't be considered any sort of security as far as keeping your endpoints from being hacked.

The point on invoice-numbers brings another issue to mind.

We model our domain(s) using DDD, and often "The ID" really is best left a thing with meaning. Customer-id, Bank-account-number, invoice-number, email, etc. At least within the domain, it is. The business (and laws etc) already ensure there can only ever be one invoice with this number. Its terribly counterproductive to have two ID's for something. "Hey, can you have a look at invoice 20230233, because it seems the VAT was applied wrong. Hmm, do you have the UUID for that invoice and DM me that? You know, the long one with the hyphens".

I guess there isn't a one-size-fits all solution and that "it depends" very much on what e.g. "public" means.

Security by obscurity is a working solution if implemented with other measures. It increases the cost of attack, which in the presence of unknown vulnerabilities gives you precious time to respond.

Security by obscurity is a necessary step in most software security.

It hardens, completes and complements other measures.

Examples of every day security using obscurity: every password and encryption key

EDIT: Thanks for the replies.

Ignore above!

Obscurity is the low bit of security. But when it’s convenient, it still helps.

passwords and encryption keys are secrets, not obscurity.

Security by obscurity would be hiding your house key under a doormat for your friend to find - depending on the culture you live in you may be more or less safe but it is not security (just like hosting your ssh server on port 9384 will repel 99% of attackers but is not a security measure).

I keep SSH on Port 22. After years, I'm still amazed about the operational model of these attacking hosts.

They are completely dumb. I haven't kept record, but I have the feeling that some IPs in my fail2ban list are practically in there for month or even years now.

I assume they are just sweeping the whole IPv4 range? No state, no cache. Either they successfully attack a host or they go to the next IP. Repeat 2^32 times, start again.

I'm not sure where I wanted to go with this comment. Is it _that cheap_ to constantly sweep the IPv4 range or is it _that profitable_ to do it once you have a successful attack?

Obscurity and secrecy are different things. Though I agree with you. Moderate amount of well implemented obscurity is helpful.

> Moderate amount of well implemented obscurity is helpful.

You're getting that wrong: Everything else being equal, the more obscure system will always be the safer one. It's just that obscurity can easily be lost, so your system should, if in any way possible, still be secure even if fully known. In the end, however, no system is 100% secure, but more obscurity will make it harder to find the inevitably existing issues.

I think the counter argument is, that all else is not equal when obscurity is a goal of security, because it adds a maintenance burden to some greater or lesser degree, and that maintenance burden becomes time taken away from proper security practices, or other value providing work.

I think the main argument is that security by obscurity can easily be circumvented, be it via sidechannel, secret leak, source code leak or a surprisingly small search space (for example the whole range of IPv4 being scanned by now). It's easy to assume something is secure and spend a lot of time on obscurity, which completely falls apart thanks to a small sidechannel attack. It's (usually) just a weak defense overall. Yes, it can also be a maintenance overhead and therefore risk via proxy, but it can actually be easier in other situations.

For a personal anecdote, I used to work in a small webshop and our software was horrible, to the point where minimal effort would have been able to compromise our servers, which were running software roughly as old as I was at the time (I want to note that I worked on improving the situation while I was there). Still, the only time we had a problem was when we took over a Joomla-hosted site, as we were small enough to not get any individual attention and your off-the-shelf WordPress or Joomla-scripts did not work on our home-brewed software.

In the end, I fully agree that security by obscurity is a weak concept and the usual wisdom of not relying on it is completely correct. Still, it's important to acknowledge that obscurity can and does help security and bring actual reasons on why you should not rely on it. Just saying "it's obviously bad" leads to an easily refuted argument and will not convince some developers, leading to worse software overall.

To me, the main reason to avoid obscurity in naming or numbering things, or even in code - rather than view it as a modest addendum to security - is to force yourself to do the mental exercise of what happens when that obscurity is lost.

Not doing that is how small companies seem to get away with terrible security holes for a long time, until suddenly they don't. I've seen too many cases of companies in a position where they built a small, insecure service that's now getting shared more widely than envisioned, who don't want to spend the money to make it right, because no one has compromised it yet (that they know of), and what are the chances of someone stumbling across it - where even pointing out that it's an attack vector can earn you trouble.

Having sequential ID's is more than just a security risk, it's an information risk. Competitors can use them to estimate the size of your business, the number of customers you have, and all sorts of stuff.

This was used in the war to estimate the number of German tanks based on the sequential IDs

https://en.wikipedia.org/wiki/German_tank_problem

So just for business intelligence you don't want to leak your IDs.

Not disagreeing with the general concept - these IDs leak information - but these are sequential IDs, not auto-incrementing IDs. The leak is the time the ID was generated, not the volume of IDs generated.

That's still a competitive risk -- it does things like reveal if a given list of customers from recent orders/posts are all new customers or long-term customers.

Or from a list of most recently added customers/users, you can figure out the rate of signups.

Revealing timestamps is bad because it can reveal way too much information about the health of your business that you prefer to keep private, if a sequential list of ID's ever gets exposed (which is hard to prevent).

They’re not even strongly sequential (is there a term for this?). The gaps between them can be arbitrarily large.

They are sequential, where they are in a sequence where one is clearly before or after another.

They're not monotonic.

Thanks! This is what I was looking for.

I agree just knowing the number is bad, but it also makes it easier to discover far worse problems as well.

My second job was for a company that provided internet enabled phone conferencing solutions (this was years before VoIP became widespread).

The customer ids were sequential. Couple that with an outright idiotic security flaw (the login process set the customer ID in a cookie and the app trusted it on ever subsequent request. Just the ID. Nothing else), I was able to iterate over all the customer ids and hand over a complete list of users to my boss to illustrate the problem, starting with a list of the accounts of the complete upper management.

They could have been used to spin up huge numbers of 30-person long distance conference calls at high costs (this company was building out nodes with 20,000 line pstn switches before they had customers... it was crazy, and they failed but would've failed far faster if that had been abused and they were on the hook for costs from their carriers)

Trusting that cookie was still stupid, but had it been a long random key it'd at least been a bit harder to discover and exploit (their next attempt was to base64 encode it and I had to explain why that didn't help; they then finally blowfish encrypted it, but without any time component, so still subject to replay attacks... I jumped at the first opportunity I got to get out of there)

Huh. That's exactly the same security flaw as Moonpig had. Tom Scott made a video about it.

I’ve heard this argument many times, but I’ve never seen anyone actually post a reference to it happening (as in, a company finding and using this information; not the German tank problem).

To me, it reeks of solving imaginary problems while causing new ones.

It's perhaps embarrassing for a new startup to have a user ID of "10".

That's about the only problem I can discern.

Depends on how far you get, I suppose. Wozniak's apple employee badge has ID #1 on it, and that's cool as heck. I also remember ICQ users sorting themselves based on how many digits their identifier had.

I vaguely remember somebody figuring out from photoshop activation IDs how many sales adobe was making and trading options around their earnings report with that information.

I don’t remember the details, so maybe it was something else and not photoshop/adobe.

Years ago I wrote a library that would exaggerate sequential IDs to make our SaaS platform appear more popular than it actually was to anyone trying to pay attention. Not sure if I’m proud of the hack or embarrassed. But of both I suppose.

but UUIDv7 isn’t sequential (unless I’m getting it mixed up). There’s just a time-based component which can make sorting really nice & some random bits at the end.

If you don’t let an attacker iterate your data, all they can tell is when the ID was created.

> If you don’t let an attacker iterate your data, all they can tell is when the ID was created.

The ordering means that you can reconstruct the sequence if you have enough of them, though.

I was responding in a sub-thread about risks/opportunities associated with sequential IDs with an anecdote on opportunities.

They can still find out through LinkedIn, ZoomInfo, BuiltWith, and any number of tools.

The tank problem doesn't fit when the incrementing value is time since epoch. Integers yeah, UUIDs, KSUIDS, or any other semi-ordered thing to make your database indexes less fragmented I haven't seen a real leak issue with those.

Just friday I've had a discussion with a colleague about filenames.

We do a lot of computer vision and in his project, each processed object is assigned a UUID and he wanted to save images to files for each one.

So we took some time to go over various timestamp formats to be embedded into the filename to make the files sort chronologically. UUIDv7 is just spot-on solving our problem. In this use case, there are no real security considerations.

You should think of them as public, but that doesn't mean it isn't still helpful to obscure aspects of the information they carry.

Obscurity can be helpful as part of defence in depth, to reduce the impact when someone does something stupid, or to make it more difficult to extract information that might be helpful as a means to attack the system from another angle.

If you're already thinking about the implications, you can likely ensure people doesn't jump to the conclusion that the IDs can be trusted just because they look complex.

I'm a fan of Cuid2[1] for this reason.

They are compact, don't leak information, and make a good case why k-sortable IDs are unnecessary, or even harmful for performance.

I'm using sequential integers and created_at/updated_at timestamps for internal use, and Cuid2 IDs externally.

[1]: https://github.com/paralleldrive/cuid2

What benefice over uuid4 ?

There's a comparison in the README of the project:

https://github.com/paralleldrive/cuid2#the-contenders

Some of the arguments mentioned are explained elsewhere in the README, others are assumed.

One argument standing out for me is the lack of collision-resistance for UUIDv4 which is surprising for me and I didn't spot any sources for that argument.

Another argument is the entropy source where they go about that Math.random is not reliable as a single entropy source but glimpsing at the source code, they sprinkle the CUID with Math.random data.

I am no expert in ID security, so I am not qualified to speak about the validity of their arguments, only that there's insufficient information to validate without prior knowledge about the problem domain.

crypto.randomUUID should generate UUIDv4 with a cryptographically secure RNG (ie not math.random)

Collision of UUIDV4 (which are 122 bits of entropy) are unlikely enough that it should fit most definitions of the word "impossible".

The argument listed in this library README feels like total bullshit to me, I'd avoid using it for this reason alone.

Reading their docs: No real benefits, just misconceptions.

1. Collision resistance / "weak" PRGNs used to generate UUIDv4. Firstly, these are properties of the implementation, not the spec. Secondly, the source for calling the browser `Crypto.getRandomValues()` insecure is an issue that has been fixed back in 2016. I would not trust the developers of this implementation to do a better job than current browsers.

2. "Not URL or name friendly": Fair, but not very strong argument.

3. "Horizontally scalable" and "offline-capable": No argument given for why UUIDv4 does not meet these requirements, apart from point 1 above.

4. "Too fast": No argument given for why having a slower algorithm to generate random ids is more secure. Both UUIDv4 and Cuid2 use a similar number of random bits (122-124). When using a secure PRNG, both are equally difficult to guess, the SHA3 hashing doesn't add anything. You don't have to try and guess the "input" of the Cuid2 - you can just try to guess the "output" and skip the SHA3 hashing. It would be impossible to actually guess a generated ID, but UUIDv4 is just as impossible. Also no argument given for why UUIDv7 is fine but UUIDv4 is not.

I've used UUIDv4 for genering unique IDs for over 10 years now. I have run into collisions, when I hand-rolled my own implementation for J2ME with major bugs many years ago - ended up with around 20 bits of entropy instead of 122. That's not a reason to not use UUIDv4, just a reason to not implement it yourself unless you really know what you're doing.

I once had UUID collisions in Linux Boot IDs when we started developing our own embedded systems at my company.

Found them because systemd-journald isn't very happy when Boot IDs repeat and (apparently, then) stops showing earlier boots once it hits a repeating boot ID. And I wanted to see an earlier boot. Then I started logging the Boot ID in a textfile myself and it took less than 10 reboots to have duplicate Boot IDs.

Long story short, some weeks earlier, I "optimized" the Kernel config for that system and some config flags that didn't sound like something I'd need. As it turns out, an ARCH_ZYNQ target apparently also needs ARCH_VEXPRESS set. Otherwise it works absolutely fine, but with a broken RNG that you will notice weeks later.

That was a valuable "don't take down a fence until you know the reason why it was put up" lesson. Don't unset kernel config flags until you know why they are set.

Aside from breaking RNGs, I've never experienced any UUID collisions either.

No argument given for why having a slower algorithm to generate random ids is more secure.

If the algorithm is too fast it means you can detect when some other part of the system is having a significant impact on how the key is returned. Eg checking a database to see if a user exists and returning their key versus getting null back and generating a new key. That difference can be used to determine if a user exists. You want your key gen process to be slow enough that it's a significant part of the process, which makes timing attacks hard.

This doesn’t make sense. If you’re going to generate a new key if the user doesn’t exist then you’re creating a new user anyway, so there’s no hidden information. Unless you mean that the system should return a bogus id instead of a status 40X when the user doesn’t exist, which makes even less sense.

ID generation should usually only happen when creating new assets, so it should be as fast as possible.

"A couple of years ago we decided to standardize the use of sequential integer IDs as primary keys, due to the significant performance issues of non-time-ordered UUIDs."

From the article. I'd like a lot more exposition on that, since it goes against some of what used to motivate UUID use in the first place. Sequential ordering across distributed nodes isn't a fun thing to do, and even if you navigate the coding, the network agreement makes it really slow.

Do they mean "sequential enough" but some locality of the node that generated it? And I guess that prng can sometimes have some performance bottlenecks, but compared to doing locks on a single incremented integer?

Yeah, I don't really get this, lots of usual "better" "faster" etc without actual numbers to back it up or detailed algorithmic discussions.

As you basically said, wake me up if it's good enough to get into the standard vetted libraries of UUID generation.

> But not too fast: If you can hash too quickly you can launch parallel attacks to find duplicates or break entropy-hiding. For unique ids, the fastest runner loses the security race.

> Cuid2 has been audited by security experts and artificial intelligence, and is considered safe to use for use-cases like secret sharing links.

I'm getting some snake oil vibes from this... There absolutely shouldn't be anything like a random ID that is 'too fast' to compute. You might need a rate limit to stay within your collision bounds, but CPU usage is a poor way to do it.

And there is currently no publicly available "artificial intelligence" that would be useful in a security audit, unless you want to call fuzzers "AI".

Nearly everything in this README about security or performance is wrong. I'd be very wary of using this.

The comments on performance are utterly incorrect, modulo discussions on hotspots, but you shouldn’t be sharding randomly anyway. If you get to the point where you _need_ to shard for anything other than geolocality, doing so randomly will rapidly reveal your hotspots.

> One reason for using sequential keys is to avoid id fragmentation, which can require a large amount of disk space for databases with billions of records.

Disk is cheap but not free at higher tiers. But more importantly, record fragmentation means more pages (unless you take the time to do a full table lock and rewrite it, and who’s doing that?) which means more index bloat. I assure you, that adds up once you’re into the billions of records level.

> the ids will be generated in a sequential order, causing the tree to become unbalanced, which will lead to frequent rebalancing.

Given the width of B+trees used in DBs, I doubt they generally need to go more than one or at most two levels up. I’ll take the ability to rapidly follow the leaf nodes and have a good shot at sequential reads in cache from prefetch, thanks.

Could you explain a bit more how it would be a risk? Maybe for session tokens is understandable. But why leaking account created info is a problem?

Leaking a monotonic ID could allow outside observers to estimate e.g. number of accounts created or products sold over certain timeframe. Competitors (or traders, for a public company) could use this like a form of inside information on the company (e.g. sell the stock if the rate was falling).

UUIDv7 does not leak the allocation rate of UUIDs.

This would really only be possible if you leaked a monotonic sequence; the monotonic clock only would potentially leak only event ordering or absolute time.

IMO it's not the job of the identifier itself to prevent information leakage vulnerabilities though; if thee is sensitivity to this, the solution should be explicit, such as employing a secondary key derived from the UUID using a secure KDF or similar.

It’s as convenient as manipulating IPv6 addresses.

Fyi, the timestamp is already encoded inside uuid4. But for having a good distribution of values, the low bits half of the timestamp is stored before the high bits half.

Here uuidv7 will just re-order that. So the content of the uuid in itself does not change.

No, this was the case in earlier uuids. In v4, there is no timestamp.

Indeed you are right, just the other ones.

Not to speak of the increased risk of key collisions. https://en.wikipedia.org/wiki/Birthday_problem

In the same millisecond, in the same database system, having rolled the same 74 bit (~22 digit) number?

If it is the same database system, you may as well use a SERIAL or similar synchronized datatype. The point of using UUIDs is that any participant in a distributed system should be able to generate them, without having to rely on a centralized authority. If the use is purely local then UUIDs would be pointless.

If you have 74 bits of entropy, the birthday paradox says that after 2^37 keys you will hit a 50% risk of a key collision. Whether that is going to be a problem depends on the use case, and on the quality of your RNG.

But the uuid collision chance only matters if there is even a possibility of colliding writes and lookups.

So what if your microblogging platform's tweet uuid happens to collide with my cocktail recipe generator's ingredient uuid? The likelihood that any one device will ever be running our apps at the same time and trying to read data from one into the other is even smaller than the 1-in-380000000000000000000000000000000000000 (uuidv4) probability of having a collision in the first place.

You're right, but why would either of us be using UUIDs for that purpose, when an auto-incrementing integer would suffice?

Because incrementing integers require a singular central node to track the universal state and even so still have a risk of collision bucause they are not guaranteed to be unique in absence of an externally carefully managed master incrementer.

Applications use uuids to avoid colliding with themselves. Uuids exist so that web-apps can create objects client-side without waiting for a database CREATE, applications can be built with multi-node dbms, operating systems can name hardware compenents.

Just randomise your clock.

Persistent IDs are a security and information risk. If that's a concern, don't persist IDs.

Every access and id token issued by oidc already has an issued at (iat) and expiration (exp) fields.

Don't create them each time a record is created, create a batch in advance in sufficient number, and do the same every time the previous batch has ran out. UUIDv7 is 128 bits, you can store a large number of them without major penalty.

But then you need to have the client communicate with the server to identify it's newly created object or complicate your logic to have incomplete objects in a pending state, which is one of the things people were using UUIDs to avoid.

You don't store the UUIDs in the database as incomplete records. You can put them in a unused_uuids table and store some of the values in memory to minimize the round-trip. You can even store them in a simple file, and remove each used UUID from that file. When the file is empty, you create a million more of them.

The incomplete objects refers to when someone clicks "new" in your UI. Until it's saved back to the server, that "new" object has no ID, since you need to communicate with the server somehow to get that UUID in this approach. So now the client creates objects without IDs, so now all your models need to assume IDs are optional, and you can't create your object references on unsaved objects.

Then we can get a random sample of UUIDs in sufficient number from the batch, send it to the client at the beginning of the session along with the other data, lock that batch until the client releases the session to avoid duplicate use, and have the client use these UUIDs until they run out, at which point it can request a new batch.

They’re also incredibly cheap to create & don’t need knowledge of each other. I mostly see batching of IDs like this when a lock is involved to prevent collisions & maintain performance.

With UUIDv7, you are reasonably sure that there won’t be collisions (check your use-case first), and can just generate them wherever on-demand (no locks required).

I’d argue batching IDs is actually more complicated than UUIDv7 for most use-cases.

This was a solution to the UUIDv7 problem of being time-dependend and therefore may leak information. Create the UUIDv7 in advance by batch of 10,000, use these randomly, and you fix that problem.

Plus ID's often have to be given over the phone for support. Long or complicated ID's will drive both sides bonkers. #BeenThereDoneThat.

>Having any information, specifically time information, leaking from your systems may or may not have unanticipated security or business implications. (e.g. knowing when session tokens or accounts are created).

I don't think this is really true? These are not serially incrementing, they just indicate the time it happened. If you have an ID that you know exists, having the ability to know _when_ it was created is very rarely meaningful.

What could present more of a risk is being able to predict a large part of IDs that will be created. Even then though, you shouldn't depend on your IDs for secrecy - best to ensure the IDs are never used as protection by themselves (ie treat them like they're just a simple autoincrementing number, even if they're not)

One business implication is that third parties can detect whether your sales increase or decrease from sampling those IDs (a variation on https://en.wikipedia.org/wiki/German_tank_problem)

But how?

You would be correct if the ID were an integer being serially increased. I can sign up to your website today and get an ID X and then sign up again in a week and get ID Y, I can then calculate the number of new users you've had by performing Y-X.

If this ID is a timestamp then there's no such information I can get out of it from a small sample. I sign up today and get todays timestamp, then I sign up next week and get next weeks timestamp..?

One real world security problem is the "elder account" problem: as the age of an account increases the likelihood increases that it uses an insecure old password and/or that the account owner isn't paying as much attention to the account in the present. Depending on what the account represents age may also imply more "value" in the account. (Including just "sentimentality" value in the case of ransom operations, not just financial value.) Being able to tell from an ID alone that an account is at least X years older than some other ID in the system can be a handy way to find "potentially high value/low security" accounts to focus on to social engineer.

There are certainly mitigations that can be made and not all things are equally valuable as they age. (Plus many public APIs include created/modified timestamps anyway. The information is often easy to discover even when not embedded in an ID.) I don't find it a strong reason to avoid timestamp-based IDs for the threat models of that many things beyond user accounts and other things susceptible for social engineering, but it is something to keep aware of.

I find it interesting that it’s quoted random IDs are bad for performance, because it’s actually better for distributed storage systems because you don’t hotspot on a single node. For example see: https://stackoverflow.com/a/53901549 and https://medium.com/google-cloud/cloud-spanner-choosing-the-r...

They likely mean it’s good for latency and not necessarily for throughput.

I still think that graph databases are way better for this sort of thing.

They later note most of this traffic is going to a single postgres instance. Having all the keys go to the same range probably helps throughput because they can do a better job of grouping fsync. But that probably depends on the type of drives they are using (even fast NVMe benefit from locality).

In all the distributed systems I’ve built I hashed the keys to ensure good distribution. A nice thing of ordered keys is you can use part of the ordering to distribute keys with a tunable amount of key locality in each node for efficiency.

UUIDv7: Timestamp up front, random in the back.

Truly, the mullet of unique identifiers.

I know HN doesn't like jokes, but this is really funny. And the subcomment about mullets too.

(for folks who don't get it, mullets are a 1980s haircut (think MacGyver) with a short front but a long tail in the back. A funny description of them is "business in the front, party in the back")

Sitting on a tram in Sheffield, early noughties, a chap with a magnificent unreconstructed ROCK mullet gets off. I see two women chatting on the street, they are stunned into slack-jawed amazement as he struts past -- one of the women makes a scissor-snipping hand gesture behind him as he passes, the whole tram erupts in laughter.

They can be bad for performance. It all depends on your access patterns. A common caching pattern is called "temporal locality" which means that theres a high likelihood that data created at the same time will be accessed at the same time. Therefore, if these pieces of information are on the same machine, they can be queried / returned much faster than if they were both on separate machines. This is doubly true if theres a data dependency between them. E.g. SELECT x + y or SELECT x WHERE y = 'foo'.

Yes but if that machine with sequential data receives 100x the traffic of other machines, it can be worse than splitting this traffic evenly across all available machines.

If your database simply shards keys sequentially, it's going to get hotspots in a lot of use cases, like plain old integer keys and timestamps, not just UUIDv7. In that case it would be fair to say that your database is doing it wrong.

Fortunately, there's no rule that says you should shard your keys using the sequential part up front.

One of the rules for generating randomness from environmental sources is to throw away the high bits and only use the low bits. Distributed databases should do the same if they want a good distribution.

As I understood it, a big part of the premise of the post was that they see sequential storage (either in db or cache layer) as desirable

What distributed databases shard on the low bits? How do they do something like a range query?

The closest I’ve ever heard of is sharding based on a hash (e.g. CockroachDB can do this on request[1]) but most distributed databases with strong consistency (Spanner descendants in particular) default to “doing it wrong”.

[1]: https://www.cockroachlabs.com/docs/stable/hash-sharded-index...

It depends if you have a request covers a lot of sequential data, or if you have a lot of requests of sequential data.

Correct, it speeds up latency in best case scenario, and falls over in worst case scenario. Randomly sharded keys give a more consistent performance.

You're painting with way too broad of a brush. It is not always better and not always worse. E.g. "give me a list of users who made two posts where both posts were created within 1 second of each other" This query would likely blow up on a system which has all the data completely randomly sharded (because you'd have to aggregate all the data centrally, unless you had a complicated shuffle setup (which most dbs dont)) whereas would work fine on a system which has posts sharded by time.

If you're working on a multi-user system, particularly one with hundreds of requests per second, there is no locality of ids. Two of my actions are separated by a sea of actions by other users.

We solved that with UUIDS and updated_at and created_at columns. The latter the default sort in all views and queries. So the btree/indexing issues were hardly an issue. Whenever you fetch a set of rows, they will be bounded by these timestamps.

We even sharded on these columns, because of this (our business case made it so that hardly ever did people need data over multiple months)

But we never encountered distribution issues. I don't think the locality issue will be solved, as postgres doesn't consider other columns when distributing data, only the primary key IIRC. I don't know why we never saw this, though.

Agree, but the solution is easy peasy with a simple hash function.

(Or just reverse the bits, take the last n, etc)

It's bad for performance if you frequently access large consecutive sets of records.

UUIDs are good for data where I want either lots of different users being able to insert without collision, or lots of users who I want to keep their peepers off of other user's metadata (eg, how many X they add to the system per day).

In both cases I'm melding highly disjointed data into a single schema. There are no large consecutive sets of records.

If you're using UUIDs, there's probably a reason. And that reason invalidates the justifications for not using them.

> If you're using UUIDs, there's probably a reason.

Not really; think of how many architectural decisions are made purely based on imaginary scaling problems, or what the latest blog said.

I would wager that if you polled 100 backend devs, very few of them could correctly articulate the pros and cons of a randomized primary key.

Depends on the use case. If, for example, you store things on disk ordered by these IDs, and access patterns to your dataset are related to time (e.g. more recently created data is accessed more frequently), it will help a lot to have this data ordered by time.

This is especially useful when your underlying database stores data in large "chunks", such as LSM-trees you find with e.g. rocksdb.

It can be bad for performance due to how b-trees work in databases, and more pronounced when you have a clustered index.

It Depends(tm).

If you're using a system which is built for distribution, random is great.

When you're leaning on a Postgres database which has powered your startup through scaling but expects right-leaning btree indexes, it's a bad time.

Rearchitecting to use a new data store is ideal, but often impractical as an immediate step. UUIDv7 is a great increment walking that road via sharding etc.

Depends how you query it. In a lot of systems, recently added data is also the most queried data or data typically gets pulled out sorted by time. Having that data on disk in more or less the order it is going to be queried makes sorting it a bit easier. Even in a sharded system, each of the shards would have less work to do for sorting. Of course a lot of these systems would have an append only write model which would effectively sort things by time anyway, even with completely random ids.

Somebody posted an interesting article for the instagram ids, which do something similar. They use 41 bits for a time from a custom epoch followed two more groups of bits for a shard id and a sequential number. Each shard has an incrementing sequence for the sequential bit, which guarantees that things on a shard are sorted by time.

This UUIDv7 is slightly weaker than that but sorting things published in the same millisecond is mostly going to be very light work. The lack of a dedicated sharding group of bits is not that important as you could just take the n least significant bits at the end for that without too much effort. Those are random so you end up with nice consistent hashing. Having 48 instead of 41 bits for the time means we won't run out of time any time soon (nearly 9K years vs. 70 years).

An explicit shard id can ensure all related data across all tables can be on the same shard. Helpful for SQL JOIN operations.

Picking the N least significant bits only a single table has good distribution and sort qualities, no cross-table properties.

As far as I understand, you want to have a random shard position, but once you have found a shard you want that index operation to be cache friendly. When choosing a shard, you can always use the last N bits or use some consistent hashing strategy[1]

[1]: https://en.m.wikipedia.org/wiki/Consistent_hashing

There are many other options that usually scale better than random distribution. For example distribution by user or tenant id.

It's great for performance, up until you reach the point where a single device becomes a bottleneck, at which point it's terrible for performance.

As a sibling comment says, you ideally want to shard on some other key to get "just enough" distribution that all your machines/disks have work to do, but you are still only hitting a limited number of hot sectors on each disk that can be effectively cached. But that requires active monitoring and rebalancing of your data as it grows. Totally random keys are a safe default that will scale with any kind of data distribution and access patterns.

Is there some reason new versions of UUID keep appearing? It seems like the desired properties are never quite achieved so new ones appear later. Is there a table with UUID version across the top and characteristics down the side, so I can see the differences and pick one that fits my needs? That might also help to explain why there are so many variants.

https://en.wikipedia.org/wiki/Universally_unique_identifier#...

Unless you have specific needs, the only type of UUID you should care about is v4.

v1: mac address + time + random

v4: completely random

v5: input + seed (consistent, derived from input)

v7: time + random (distributed sortable ids)

As someone who only cares about v4, I periodically wonder why don't I just use fully random 128-bit identifiers instead (without the version information).

That'd be perfectly fine. You need to take some care to avoid duplicates or patterns though. Databases might not come with crypto-random out of the box but usually do have UUID support. Similarly, you need to use the crypto-random routines in your favorite standard library.

Depending on the implementation you still might have to worry about seeding issues. That's probably moot though since the UUID library would probably be compromised by something like that under the hood too.

UUID v4 isn't large enough to prevent collisions, that is why segment.io created https://github.com/segmentio/ksuid which is 160bit vs the 128bit of a UUIDv4.

I think you vastly overestimate the likelihood of collisions. If all of the 2 billion computers in the world produced a new uuidv4 every millisecond, we still wouldn't expect a collision for the next 5 quintillion years.

Or if the same number of bits are used in a more structured manner, like uuidv1 which combines a 48 bit MAC address, 60 bit 100-nanosecond timestamp, and a 14 bit uniquifier with an effective resolution of 6.5 femtoseconds, you could have 300 quadrillion computers make 160 billion uuidv1s per second with guaranteed zero collisions until the year 3400 (plus an extra 500 or so years because the timestamp is referenced to 1542 for some silly reason), after which point any collisions that do happen will be completely irrelevant because they're guaranteed to be colliding with db entries created over 2000 years prior.

> If all of the 2 billion computers in the world produced a new uuidv4 every millisecond, we still wouldn't expect a collision for the next 5 quintillion years.

This would generate 2^127.8 UUIDs.

First, no, the collision would be expected in less than one year, approximately after exhausting square root of the available space (2^64 generated UUIDs): https://en.wikipedia.org/wiki/Birthday_problem

Second, no, the UUIDv4 has 122 random bits, not 128 as you thought: https://en.wikipedia.org/wiki/Universally_unique_identifier#...

Sure, the presence of some collision somewhere is still likely to happen, but the chances that that collision will actually matter for anything is still vanishingly small.

In the real world, we do not spend 100% of our entire species's computing capacity generating uuids and doing nothing else. In the real world, we aren't burning through uuids at a rate of 2 billion per millisecond, and even if we were it wouldn't matter because the true denominator is the scope of the data system the uuid will be referenced in: if your hard drive partition and my webapp user entry happen to get the same uuid, we will never know, and if for some reason it does matter, then we can use uuidv1 or uuidv7 which guarantee no collisions for thousands of years by embedding a timestamp.

No number of bits is large enough to _prevent_ collisions.

That doesn't help me. My code may generate a whole bunch of IDs in a microsecond, so using a 32bit time isn't going to keep them time sorted. I may as well just use a UUIDv4 at that point.

Looks like you got things a bit mixed up. You’ll not see a v4 collision in your lifetime, that is not why they built ksuid. At the time, UUIDv1 was the only standard alternative that includes a time component, but in way less bits, not sortable, and the fixed mac address takes a lot of space from the random bits.

ksuid is similar to Twitter Snowflake, the main goal is distributed generation of collision-free sortable IDs. The UUIDv7 proposal is meant to address the same use case. You don’t need to worry about collisions as much here, as the timestamp is monotonically increasing, there is a 42-bit counter for every millisecond + the random 32 bits at the end. You’d have to be generating trillions of IDs per second to have the chance of a collision.

Because the rest of the world uses uuidv4 and the extra couple bits doesn’t really buy you anything

It would seem sequential keys for database performance is more than a 'specific' need.

Those are not helpful for database performance in a general sense. Last product I worked on used v4 uuids as primary keys without any issues - single master database, sorting by creation time only used in admin panels. You can index on created_at if needed. The IDs being sortable would be a non-feature.

Generating sortable IDs in very high volume, in a distributed architecture, is a problem very specific to systems like social networks, metrics collection etc. You won’t need that for your average e-commerce app or machine parts database.

v4 being completely random has terrible properties even on a non distributed database. Probably better to use v7.

> We use sequential primary keys for efficient indexing, and UUID secondary keys for external use. The upcoming UUIDv7 standard offers the best of both worlds

Unless you consider users being able to extract the generation time from the id to be an issue, of course.

I've been seeing a few different vendors do this already. MongoDB's ObjectIds are inherently timestamps (so you can actually generate generic MongoDB IDs to query based on time). There's also Discord's Snowflakes as well. I'm sure there's loads of others. All it tells you is when something was generated, not much else. I do love how MongoDB has it stored in such a way that it is easy to query against. I wonder if any RDBMS' will allow you to query these timestamps as well.

There are definitely many cases where it isn't an issue since you were going to tell the user the time anyway (like sent time on a message)

I think Twitter also does it as well. I think its really nice honestly.

FWIW, Snowflake came from Twitter https://blog.twitter.com/engineering/en_us/a/2010/announcing...

Discord uses Twitter's Snowflake.

Can’t agree with that logic. Unless it’s specifically documented leaking timestamp data is going to get totally forgotten. So when you add (e.g.) the ability to change the sent timestamp on a message you’re going to inadvertently leak when a timestamp has been changed. Could cause embarrassment in a lot of scenarios.

This reminds me of that recent investigation regarding fudged data in those Harvard studies. The fields stored the "original" ID, solidifying their creation sequence, which differed to the displayed sequence - Implying that the fields were updated out-of-sequence, thereby they were tampered with.

This sounds like a completely arbitrarily invented argument against a technology that's perfectly useful in many scenarios.

Inadvertently leaking private information is not an arbitrary argument.

Sure but what are cases where knowing when a random GUID was created is actually an issue of privacy? Every platform I've seen using Snowflakes that exact same data is publicly available.

It’s a case by case thing. I don’t disagree that in the vast majority of cases it won’t matter at all. But once it becomes the norm/default it’s inevitable that it’ll lead to inadvertent leaks.

And if you consider knowledge of the id sufficient for access.

Which, despite the fact that it really shouldn't be, still seems to occur every so often. Even in situations where the ids are very much not random.

Honestly if I have to read one more article about a 'hacker' who 'leaked' some secret government piece ahead of time because they thought to increment the date in the url of some yearly report, I'm going to lose my mind.

I don't understand. What part of this requires that one considers knowledge of the ID sufficient for access? And what kind of access are you talking about?

The performance benefits of index friendly user IDs seem like they would apply even if all user info is secret and requires a token to access... The application still has to look up the user by ID after all?

If I imagine a basic authenticated "get information about me" style endpoint, that would take a user ID and an authentication token. Checking if the token is valid is faster if the user ID is index friendly. Getting the requested information is faster if the user ID is index friendly. Yet a user of the API still needs both the user ID and a token to access anything.

> Yet a user of the API still needs both the user ID and a token to access anything.

Ideally yeah.

In practice, it varies...

And you can use it today with Postgres uuid type. Postgres doesn’t care what you store in it as long as it has the correct length. So you can generate a uuidv7 and store it natively

Wouldn’t the index types need to be updated to support ordering on UUIDs?

No, because the ordering is purely byte level which will work perfectly fine here.

Neat

What are the benefits of using the Postgres uuid type (versus using TEXT or VARCHAR)?

It's 16 bytes versus 36 bytes.

totally tangential to this but just have to share

learned this last week that md5's are also 128 bits and so will fit perfectly in a uuid type in postgres, saving space in the physical table and indexes, and giving better index performance. So if youre storing md5 hashes, use UUID!

It’s stored in binary format (16 bytes) instead of text (36 bytes)

Stored in binary format, validation, more efficient due to non-cast, faster access due to non char*, being able to split the high-low, indexing and uniqueness at the byte level.

What validation? "Postgres doesn’t care what you store in it as long as it has the correct length."

I mean, if(len(O) == 32) technically is a validation but you’re right, by default it just fits for size.

Yup this is one of the reasons I put together a light extension for this:

https://github.com/VADOSWARE/pg_idkit

There are a lot of options for UUID extensions (lots of great pure SQL ones!), but I wanted to get as many ID generation strategies in one place

Also note that native UUID v7 is slated to land in pg17:

https://commitfest.postgresql.org/44/4388/

Similar to the old situation in the article, we are using sequential 64 bit primary keys, but we use an additional random 64 bit key for external usage (instead of 128 bit).

The external key is base64 encoded for use in URLs which results in an 11 byte string.

This hides any information about the size of the data, the creation date of customer accounts (which would be sort of visible with UUIDv7) and prevents anyone from attempting to enumerate data by changing the integer in URLs.

I thought about using UUIDs as external keys but the only compelling use case seems to be the ability to generate keys from many decoupled sources that have to be merged later.

64 bit should be enough for most things https://youtu.be/gocwRvLhDf8?si=QBheJCG21bAAV0Z7

It sounds like you basically just made your own 64 bit UUID. If you’re exposing this ID for manual use by a human (like URLs) then that sounds pretty helpful to be shorter!

What's the risk of collisions with your external ID in this scenario?

I would imagine that they enforce this using e.g. a unique constraint in their database.

Yes, a unique index per table, not globally. Although the probability of a global colission is still extremely small.

I am using a variant of SnowflakeId [^1] in order to have 64 bit keys too.

It's similar to UUIDv7 (it leaks the creation time), but it's not an issue for me.

So I am able to have a single 64 bit key, which can easily be formatted into a small string for user-facing urls.

[^1]: https://instagram-engineering.com/sharding-ids-at-instagram-...

Discussion here a couple months ago :

Analyzing New Unique Identifier Formats (UUIDv6, UUIDv7, and UUIDv8) (2022) https://news.ycombinator.com/item?id=36438367

It seems insane to me to “validate” GUIDs/UUIDs.

Half the point of these things is that they’re treated as opaque identifiers.

I’d assume the validation is parsing the uuid with a uuid library (to decode it), and the library eagerly validates the version field, either to check for garbage or because it wants to yield a different subtype for each version.

but why decode it at all, if it's meant to be opaque?

I think there are a couple minor problems:

- if the ID is intended to be opaque then the vendor shouldn't document it as a UUID, as this changing to a different format would be a breaking change

- if the customer isn't going to process the subcomponents of the UUID then they should process it as an opaque string

- if the UUID library encounters a version number in a UUID it doesn't understand, it shouldn't reject the UUID but present it as an unstructured string.

After this blog post it seems likely that even Kite more customer will parse the IDs to extract time, since this has been documented.

Probably because a typed UUID avoids treating it like a random string, and decoding the UUID means you have 16 bytes per in memory rather than 36 (assuming usual 8-4-4-4-12 representation over the wire).

If UUIDv4 was all that ever existed, there would be no need to validate anything apart of the fact that it's supposed to contain 32 hexadecimal characters.

All other versions, including the new v7, attach meaning to certain bits of the identifier. That cat has been out of the bag for a long time, so now everyone needs to maintain code to ensure that some rogue node doesn't spew back-dated identifiers belonging to the wrong department.

UUIDv4 attaches meaning to certain 6 or 7 bits (depending on a variant) of the identifier. UUIDv4 is a UUID after all.

UUIDv4 also contains some bits with meaning. But joke's on them, I tend to even randomize the version bits and call it UUIDv0.

Because in SPAs if a user creates new entities it can be easier to generate the UUIDs client side.

So then just a simple validation server side to ensure the data isn't malicious.

Never trust the client.

Yeah, you can require them to generate a UUID if it helps for your app (e.g. bulk create objects with relationships between them) but then on the server you can generate new UUIDs and return a mapping from old->new to the client.

Why use UUIDv7 over ULIDs?

As Lazare points out in this thread they're basically the same thing, except with ULIDs you get those 6 extra bits of randomness back that UUIDs have to use for metadata.

https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc...

ULID isn't an "official" standard like UUID. Having a real standard usually promotes interoperability and makes it easier to use. Additionally as others have pointed out you can already use UUIDv7 with some databases since it's just 16 opaque bytes and the database doesn't care what's actually in the UUID field.

How much of a standard do ULIDS need? 6 byte timestamp, 10 bytes crypto randomness, stringify it using crockfords base 62 - and off we go.

Base 32, not base 62. Ironic.

I'm not the author, but I work at the same company. ULIDs are nice, but we're a 10 year old company with many TBs of data across multiple logical databases and most rows had UUIDv4 ids.

Maybe if we were starting from scratch ULIDs would have been an option, but given where we were UUIDv7 was a much easier transition.

If it helps anyone, at work, I open sourced the UUID v7 postgresql function that I wrote: https://github.com/Betterment/postgresql-uuid-generate-v7

We've seen some amazing benefits, especially around improving the speed of batch inserts.

A useful/horrifying pattern on this topic: you can use UUIDv1 as a prefixed id, giving you a way to generate tagged IDs in a system that uses UUIDs.

You set the node field to a broadcast MAC address, and use that as a namespace/prefix. This inches close to the boundary of the RFC, but is arguably compliant.

As an example, you may generate demo or “canary” data items that are UUIDv1s with a well known node field, which then lets you do distributed “isDemoData()” checks by just looking at the UUID.

Can you take the first portion of the UUIDv7 string, and decode it to figure out the exact date and time that record was created? I'm wondering if there might be security/privacy concerns in some situations if the UUID codes are visible in your app?

I just commented the same thing. I can't imagine most applications would want to leak time information in their identifiers but these undoubtedly will be used most placed out of convenience. In a year or so we'll read about an attack and everyone will migrate back to v4 or have to maintain a cryptographic identifier in addition to their temporal identifier.

Most application may not want it, but will it hurt them?

I mean this is a similar concern to sequential IDs: many apps do not want to leak them, and in some cases it might cause issues, but in general it doesn't matter.

Yeah, years from now we’re going to see some story about how a company fudged their timestamps in order to get away with X, only to be given away by the timestamp hidden in public UUIDs.

UUIDv7 is a nice idea, and should probably be what people use by default instead of UUIDv4 for internal facing uses.

For the curious:

* UUIDv4 are 128 bits long, 122 bits of which are random, with 6 bits used for the version. Traditionally displayed as 32 hex characters with 4 dashes, so 36 alphanumeric characters, and compatible with anything that expects a UUID.

* UUIDv7 are 128 bits long, 48 bits encode a unix timestamp with millisecond precision, 6 bits are for the version, and 74 bits are random. You're expected to display them the same as other UUIDs, and should be compatible with basically anything that expects a UUID. (Would be a very odd system that parses a UUID and throws an error because it doesn't recognise v7, but I guess it could happen, in theory?)

* ULIDs (https://github.com/ulid/spec) are 128 bits long, 48 bits encode a unix timestamp with millisecond precision, 80 bits are random. You're expected to display them in Crockford's base32, so 26 alphanumeric characters. Compatible with almost everything that expects a UUID (since they're the right length). Spec has some dumb quirks if followed literally but thankfully they mostly don't hurt things.

* KSUIDs (https://github.com/segmentio/ksuid) are 160 bits long, 32 bits encode a timestamp with second precision and a custom epoch of May 13th, 2014, and 128 bits are random. You're expected to display them in base62, so 27 alphanumeric characters. Since they're a different length, they're not compatible with UUIDs.

I quite like KSUIDs; I think base62 is a smart choice. And while the timestamp portion is a trickier question, KSUIDs use 32 bits which, with second precision (more than good enough), means they won't overflow for well over a century. Whereas UUIDv7s use 48 bits, so even with millisecond precision (not needed) they won't overflow for something like 8000 years. We can argue whether 100 years is future proof enough (I'd argue it is), but 8000 years is just silly. Nobody will ever generate a compliant UUIDv7 with any of the first several bits aren't 0. The only downside to KSUIDs is the length isn't UUID compatible (and arguably, that they don't devote 6 bits to a compliant UUID version).

Still feels like there's room for improvement, but for now I think I'd always pick UUIDv7 over UUIDv4 unless there's an very specific reason not to. Which would be, mostly, if there's a concern over potentially leaking the time the UUID was generated. Although if you weren't worrying about leaking an integer sequence ID, you likely won't care here either.

Second precision is too coarse for many (most?) use cases.

How so? It seems like the only real use case for these timestamps is to get data from around the same time together. A second is fine for that. It's not about concurrency or avoiding collisions. A second can't handle that, but neither can a millisecond.

> It seems like the only real use case for these timestamps is to get data from around the same time together.

Yep.

> A second is fine for that.

Not when you're doing O(1k-1M) operations per second, it isn't!

I’d think that the locality would only matter at the scale of your query. I’m sure someone has queries with a window less than a second and so much traffic, but it seems niche enough to not optimize the standard for it.

I could definitely be off. I work at a company that gets those levels of traffic but don’t deal with it directly.

For me the whole value prop for ULIDs is that they can be generated by any node in a distributed system without coordination, while roughly preserving time order. "Roughly" meaning: all IDs will be globally ordered at millisecond precision, subject to the accuracy of each node's system clock; and IDs from a specific node will be locally ordered, subject to the details of the monotonicity part of the ID generator. This is important for me, because most of the things I attach IDs to will happen many many many times per second.

If you need more than second precision then millisecond doesn't get you much further. The fact that the epoch ends in 120 years is a bit more worrying, but is also just about non-critical enough that it will be ignored for at least the next century.

Also, to all future historians of 2150, sorry about the mess, but yes we knew this was going to happen. Whatever it was.

> If you need more than second precision then millisecond doesn't get you much further.

It gets you precisely 100x further.

*1000x

Oops, yes, this one.

100 years sounds short-sighted for something that's supposed to be "universally" unique. We're already having problems with the 32-bit Unix timestamp not being large enough. If you're willing to use 160-bit (or longer) identifiers, you might as well give a few more bits to the timestamp. Round it up to an even number of base-62 characters, too. That part of KSUID has always struck me as a weird decision.

I wish UUIDv7 pulled the version/variant bits up front, though, just to make sure that the identifiers don't all start with null bytes.

The timestamp is first.

https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...

If the version bits were up front, then switching to a hypothetical UUIDv8 in several years would be guaranteed to break the sortability. So I see that decision as a bit of future proofing.

Apparently, humanity is damned to repeat it's mistakes over and over again.

"100 years should be enough" is what led us to a mountain of Y2K issues, because when would a two digit year ever be ambigious?

But I guess it's a psychological issue. Unless you're a megalomaniac, it's just natural to assume that your decisions won't matter much outside of your life and lifetime. And in that case, 100 years totally is enough because I probably won't live that long. And even more, in a lot of cases, it's also the correct assumption and the project won't live longer than a few years.

So, thinking about it, unless you are developing a novel standard or something that you want the world to adopt, 100 years probably IS fine. Unfortunately, KSUID wants to be a novel standard, so there's an issue.

> first component (prefix) of the identifier is a sortable timestamp

> values generated are practically sequential

These statements aren’t strict enough to be relied on. Maybe you have engineered the hell out of your distributed clock scheme, and your IDs actually are completely monotonic, which is great. But you probably haven’t done that, which means conflicts will surely happen and you must handle them gracefully.

But the timestamp is less than half the bits. The rest are random. So timestamp conflicts don't matter.

By “conflict” I don’t mean a UUID collision, I agree with the logic that 2^128 is so much entropy that memory corruption is the more likely culprit.

I mean that you can’t rely for correctness on time(X) < time(Y) when X happened before Y. It’s damn hard to keep two commodity server clocks within ±1 ms of each other even within a single LAN, and across production you’re more likely to see ±10 ms, or worse if your sysadmins don’t realize you intend to bet the farm on no clock skew.

It's not designed for this use case. It's for cases where events in the same epoch may as well of happened concurrently.

ULID on the other hand does address this case by providing monotonicity within an epoch for a given producer. So would still need to treat each producer as a separate partition of the key space but you could order X > Y as long as both were produced by same producer (which effectively acts like a sequencer in this case).

EDIT: nvm, the UUIDv7 spec -allows- for arbitrary allocation of the remaining 62 bits which can be used as a counter: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u... All points for ULID can also apply to UUIDv7 depending on generation algorithm.

When I'm using integer surrogate keys to manipulate subsets of a table, they usually correspond to some kind of out-of-band predicate. Like this is the data that appeared today, or this is everything after the bug happened, etc.

Maybe there are applications where the monotonicity matters, but in my experience reasoning by surrogate key is rather coarse grained and you manually scrutinize the boundaries, so unless your clocks are quite wrong, your worries are probably better placed elsewhere.

I’ve seen too many people write a query like

  where ts between txn_start and txn_end
  order by ts

and not even realize that what they’re seeing is incomplete and misleading. Clock skew is very common, and we shouldn’t sweep that under the rug to promote time ordering, because people want to believe this works the way they think.

Clock skews in a single node database? Actual question.

Usually Hive or Dremel, with rows ingested from frontend instance logs. I get that it would actually work on a smaller system with a single server assigning times (or a quorum, if leader election ensures a monotonic clock).

Thank you, I was just wondering if it was something I missed out on simpler systems and not distributed ones.

Nah, places I’ve worked are just choosing commodity hardware and geographically distributed systems over (very expensive) centralized ones.

I feel like it's well beyond the scope of UUIDs to get into "are your clocks really monotonic?". They give you 48 bits for a timestamp, what that timestamp signifies (transactional time, valid time...) and how that's generated is up to you.

Out of curiosity, are you into hybrid logical clocks?

I only raise this when I see promises that IDs are ordered and can be sorted, not merely grouped by approximate time for storage locality.

Yeah, though I’m more likely to go with a region ID and monotonic version number to compare-and-set and verify gapless data, where versions from different regions aren’t comparable. Actually I think earlier UUID RFCs talk about a “clock sequence” to distinguish timestamps from separate monotonic sources, but this paper doesn’t bring that up (or mention multiple clocks at all).

The reason I ask is because I'm always looking for material around this kind of thing. I read the Hybrid Logical Clocks paper from the university of Buffalo, but then found it if one of your clocks is inaccurate all your timestamps get dragged forward forever.

So any thing you can link, let me know.

This was my first reaction as well. The keys use a unix timestamp, which are clearly not going to be synchronized by default so for event ordering purposes across a distributed system this is dodgy.

For providing better query locality it probably doesn't matter significantly though which seems to be the main benefit here while preserving the other benefits UUIDs provide.

Well yes, but were they even implying that? Even if it was infinitely strict, clocks being perfect, two server processes can touch the same data at the same time.

In other words: Sorting by millisecond-or-so is just as good as sorting by picosecond in most situations. The reason you have to deal with conflicts gracefully isn't particularly because timestamps can be imperfect.

https://datatracker.ietf.org/doc/html/draft-ietf-uuidrev-rfc...

I'm not the author but I work at the same company.

None of our systems require perfect ordering of IDs generated across our distributed system. Most of the system was built with random UUIDv4 identifiers so no code assumes the ID ordering is significant.

However, in much of our system recent data is frequently accessed while old data is rarely accessed. In that world, just having the IDs *approximately* clustered in creation order has been a huge performance boost for many queries, and we've seen significant reduction in postgres Write Ahead Log rates, because writes to UID indexes happen in a smaller number of pages.

Just curious, if there is no ordering of the data (no sequential ID), how is it even "ordered" on the file system? Is it random? Or, even without a sequential ID can you know which data was written earlier and which later?

> In that world, just having the IDs approximately clustered in creation order has been a huge performance boost for many queries, and we've seen significant reduction in postgres Write Ahead Log rates, because writes to UID indexes happen in a smaller number of pages.

Thank you. I’m so tired of seeing the same groupthink on UUIDv4 trotted out - “it only matters if you have a clustered index, Postgres is immune!” The hell it is.

A few years back, I wrote some code that generates a sortable 128-bit UUID-like identifier starting with a milliseconds-since-epoch timestamp, a node number and a random byte tail. It has been working fine in Postgresql, using its builtin UUID type. I suppose downstream system have been using the string representation though. The main reason for going such an identifier was being able to generate them from different, non-centralised places. A nice side effect is that you can't accidentally get an erroneous ID that happens to work the way you can with a sequential integer primary key.

For another project, I've also used sortable 64-bit snowflake-like identifiers; they have the added benefit of being able to use 64-bit integer representation in code and database identifiers, even if you might want to externally represent them in base58 or similar encoding.

The original UUID types aren't as useful as they once were, so it'd be worth writing a new RFC and extending those original types.

Isn’t that almost the same as v1?

IMO the benefit of UUIDs over integers is that they can be generated client-side without clashing. But you cannot trust timestamps generated by clients and therefore the order. So what is the benefit over UUID4?

Would using the timestamp in the UUID be possible for date-range queries?

For me the central benefit is that you can create them in a distributed manner and are not reliant on a central system as a single source of truth for creating your identifiers.

I can therefore easily generate a new UUID in a trusted backend service which just accepts the command received from the untrusted client and then forwards the request for asynchronous processing while returning the UUID to the client. This is a typical architecture and the only change is that I can now create UUIDs which may have performance benefits, depending on the data storage technology of my read models.

If you need to create the UUIDs on the client side to support specific requirements such as offline-first, then I would indeed consider adding some reconciliation which replaces the IDs provided by the client-side by new ones generated by a trusted component as soon as synchronizing takes place.

In any case regardless of UUIDv4, v7 or any other format you should not allow the untrusted client to determine the real ID - as long as there is at least one trusted component in the architecture which would take over this role. This should help eliminate a whole set of possible security issues.

Am I the only one instinctively upset by the communication/bandwith/storage overhead of the dashes as well as the version and variant bits of UUIDs?

It might be insignificant, but to me it makes UUID feel tainted, dirty. 11.1% of a UUID are dashes. 15.3% of a UUID are wasted bits if you count version and variant bits.

Anecdote: I worked for a company that used numeric primary ids internally and externally and increased the primary key by TWO to THREE for each new customer to make it appear to the outside world we had twice to three times the rate of customer growth.

The dashes are just there to help separate the groups visually. You can write code to remove/add the dashes if you want to shorten the URL. If you use the uuid data type when storing it in a database, it only uses 128 bits.

The dashes do not take up any space, they are not encoded. I think it's fairly common to reserve some space to versions and ECC in uuids, packets etc. That's a price to pay to avoid many bugs and compatibility issues.

UUIDs are often pushed around in JSON and as string types, including the none random parts.

Do those dashes cause any meaningful performance impact, or is this just a microoptimization obsession?

It’s nice for front end state. You post the new entity, the front provides the ID, and as long as you get a 200 you can update your state, or update optimistically and roll it back. You don’t need to wait for the API to figure out what your ID is.

Not specifically the topic, but I looked for a library for golang and it is not that common, there is a library in <20 stars, too experimental for me. Also, not sure the postgresql extension is in the main distribution, couldn't find it if it does. For example, GCP only supports this one IIUC https://www.postgresql.org/docs/current/uuid-ossp.html Java has something, but again not really clear how tested. So using this is a bit iffy...

As a beginner I treated and understood (SQL) databases as something I have to use in order to store stuff.

Later I was excited about the power and expressiveness of SQL and its extensions. There is a ton of leverage and you can make it so that interfacing with it directly becomes much more useful.

However now I’m in a different phase. I see it as a durable data structure. I think in terms of “what does it provide to make the overall system better?”

The issues around indexing and uuids that is discussed in the article fits nicely into this line of thinking.

In web development, database access and performance often dominates and infects the whole system.

As a beginner I thought of the database as a backend for the app. Now I think of the app as a frontend for the database. :-D

Crud, you're right!

Is it not the case that having 128 bit primary keys take up 4 times as much memory as 32 bit integers when keeping the indices in RAM? I guess if you need the index to be clustered by time and also need a unique identifier in most queries then UUIDv7 fits your use-case but I still think having integer for the primary key will fit most use cases and be more efficient

The first time I heard about ordered string IDs was Firebase’s push IDs. They had an interesting solution to also address time skew to get better ordering for drivers: https://firebase.blog/posts/2015/02/the-2120-ways-to-ensure-...

Haha this is what we came up with for our home brewn unique ID's in a GIS application since decades ago. For the same reasons.

It’s 2023. Why aren’t we using more characters from the utf-8 keyspace to make things like UUIDs use less characters?

UUIDs are 128-bits, not characters. The string representation is just for humans.

I think the parent is saying that we can make UUIDs more human-readable by displaying the underlying 128 bits with a larger set of characters.

Base58 and Base64 exist in pure-ASCII space. Expanding into non-ASCII characters would probably just be confusing.

We could make the string representation more compact with more characters, but I’m not sure compactness and readability are the same, especially any compactness that takes more than the ASCII character set (sure, just the hexadecimal digits may be fewer than ideal.)

I think it's a fair question, because yes you should store them as numbers, but they are still often sent in text formats and urls. Wanting a shorter representation is reasonable.

The easiest is probably to just base64 the binary representation of the 128 bit number, which results in a 128/6=22 character string, which is a bit smaller.

If glyph-length and not byte-length is more important you could go even smaller but I'm less sure if that's a good idea.

This is the way. Look not at the characters but at the hex.

You could, if you are only optimizing for display size. UUIDs are very infrequently presented as user-facing data, and pretty much every sensible system will be storing them as a 128-bit value, not the ASCII representation you see.

So really, what are you trying to optimize?

UUIDs show up absolutely everywhere as strings in logs.

They're also often used as part of a URL parameter:

http://myservice/orders/<uuid> etc etc

Firstly, "it's current year" is never a good argument, but you seem to be confusing things (UTF-8 vs Unicode). UTF-8 can take as much as 6 bytes to encode a Unicode codepoint.

If you want to store UUIDs as compactly as possible you'd use 16 bytes.

If you want to store them as text, mapping them to Unicode would be a terrible idea because: many characters are from scripts you've never heard of, many characters look identical (Α vs A), many characters are decomposed and it can change the encoding if they're decomposed[1], &c.

[1]: https://en.wikipedia.org/wiki/Precomposed_character

Because it's 2023. You're looking at the UUID through a giant pile of interpreters and renderers and buffers... plenty of opportunity to give each one a tartan without changing it in the data.

Or better yet, only decorate one after it has been clicked by the user, that way when it appears again elsewhere, it stands out. If you make each one pretty you'll have made all of them ugly when viewed together.

we should display them as a monochrome block of 8*16 pixels.

UUIDs are 128 bits, or 16 bytes. They have infinitely many possible string representations. Those strings are not the value, they're a transformation of the value.

The strings are how HUMANS not machines interact with the UUID. When your java stacktrace spits out a log with an UUID it's going to spit out a STRING because it's written for you and not the computer.

When you take that UUID and go start sniffing around internal systems you're going to copy the UTF-8 string representation.

OK, but you don't store the string representation in your DB, or on disk, etc.

Base8192: https://alicecengal.github.io/uuid-hangul/

That's awesome. "Aesthetic, Cosmopolitan" LOL

Not very useful. Always decodes to 00NaN-0NaN-0NaN-0NaN-000000NaN.

The decode input needs to be exactly ten hangul character, and must be the first 8192 hangul characters in the unicode set. Obviously I didn't put too much effort into making this robust, it was just a fun toy project.

> the random nature of standard non-time-ordered UUIDs (such as v4) can create database performance problems when used as primary keys. This problem is often referred to as poor database index locality.

Couldn't that be solved with incremented serial numbers, rather than leaking time data?

Yes. But I think part of the design requirements is minimizing coordination required among distributed nodes.

This solution attempts to solve the sort-ability issue of current uuids by moving the timestamp to the most significant bits.

Incremented serial numbers can leak things like eg. volume of sales in your shop.

So increasing serial numbers with random gaps?

> The nature of Buildkite's products mean recent data is accessed more frequently than old data. With non-sequential identifiers, the most recent data will be randomly dispersed within an index and lack clustering

I would assume that `serial` would solve this problem too.

Feels like a spiritual successor to the ksuid [1] lib which I first heard of used in conjunction with DynamoDB

[1]: https://github.com/segmentio/ksuid which has very similar use cases.

This is neat. I've been using a custom snowflake cluster for years. Having this in the language/DB would be great for smaller projects.

For bigger/public projects I'd like to be able to add a sequence, node, and data center id to the UUID too.

Speaking of RDBMs, how good are UUIDs when making joins and fetching a certain record?

More or less identical to integer ids - they're stored and referenced as a 16-byte integer.

Unless you're manually storing them as strings... (Not ideal, but most dbs are pretty good at dealing with that too)

so I have to pick a certain MySQL type?

Anyone ever try encrypting a database ID (IE, a sequential int,) and use that as a public key?

IE, take a 32 or 64 bit int that's the primary key, encrypt it, and then use that as the public ID in a web application, URL, API, ect.

To me it sounds like a corner case. Example:

a) UUID4, CreatedTime/UpdatedTime.

b) Bigint, CreatedTime/UpdatedTime.

c) UUID7 internal (which also includes time badly), UUID4 external/whatever short ID.

How exactly this helps if you need external ids (which you usually do today)? It doesn't even make it a short ID.

Even if there is a corner case, are we just saving a few bytes while adding more complication?

Clustered Index is a myth in PostgreSQL, not practical since you have to run a special program to reorder. So, a regular index might suffer but not really. Why? Because I am not ordering by the ID most of the time, I am ordering by "Created Date/Updated Date" or Name or whatever. Who cares about ordering IDs?

WAIT!!! But what about Next Tokens? ok, these are painful, but easily solved: Next can be (>=Created Date,>ID). Same result. Pagination, stays the same since it is sorted by Created Date.

I understood it as c) only UUID7, no secondary external UUID.

The external Id is used instead of Bigint because you don't want your external users to query 1, then 2, then 3 (IDOR)... But the random part of the Uuid7 makes this impossible.

Uuid7 isn't a substitute for Created/Updated, but a substitute for the dual field Uuid4/Bigint.

I have been using ULID for years. Using digits now would feel very strange.

Frustrating, I looked up MS/C#'s implementation and they don't get stored in a proper semisequential fashion in MS SQL Server because MS stores UUIDs in an odd binary format.

Always wondered what the point of dash-separating uuid if the separated parts are unreadable anyway just like in this version, just makes it harder to select as a single blob of text

So, how do you guys use UUIDs for real? I worked in a company in which they were using UUIDs in Mongo, and of the most painful things were implementing API endpoints that filter resources. Imagine you have an endpoint in which you are filtering by resources A, B, C and D. Ideally you would end up with something like this:

    GET /filter?a_id=X&b_id=Y&c_id=Z&d_id=w

But in practice we were using POST and passing the ids in the body payload. Why Because my old team said "the UUIDs are long, so we may reach the maximum URL length if we pass them as parameters". I didn't like it, and I still don't like it at all.

The maximum url length is typically quite long.

Another thing is that you don’t necessarily need to encode uuids canonically. They are just u128’s. It’s relatively straightforward to find a url friendly string representation that is shorter.

> It’s relatively straightforward to find a url friendly string representation that is shorter.

Are we talking about shortening the whole URL or shortening specific UUIDs? If the latter then I imagine one would still need to keep track of the mapping UUID <-> shorten version, somewhere, right? If so, why not just add yet another field/column for an old good numeric integer that can be used for filtering? Would that work?

I think the point is for URLs you can consistently represent the 128 bits of UUIDs with shorter strings by using a higher base. I.e. more characters.

So a nonstandard but isomorphic shorter string representation.

You could simply base32, base36 or base64 encode the UUID's (as an example).

Other than that; in IE6 times URL's had a limitation of 1K or 4K or something around that lenght IIRC, so unless you're using dozens of UUID's in a URL this hasn't been a problem for ages.

Do you dislike the use of POST because of its effect on caching, or because it doesn’t feel semantically correct? If you don’t need the caching I wouldn’t mind using a POST to filter.

Note that you can also use GET with a body, it’s not spec compliant (a body is allowed but not supposed to have any meaning) but is used by products such as Elasticsearch. If you control both clients and servers that’s something you can safely do (and use an etag header for idempotency).

I am confused how this is new. UUIDv1 is time based, you just need to be careful about entropy, and in MySQL 8 you can for a longish time use it as an ordered field.

The use of a MAC address and fine grained timestamp are challenges of UUIDv1.

https://blog.devgenius.io/analyzing-new-unique-identifier-fo...

And the crazy epoch instead the more known Unix epoch. Why would anyone want to create UUIDs around year 1500?

Sure, hence why I said entropy, but it's not like you can't use it :)

One benefit of an epoch is that it's easily readable (or comparable, at the very least). I am not sure I can read epoch in hexadecimal format though.

Need a new clock? https://retr0.id/stuff/2038/

I chose ULIDs for a recent project.

Hope it won’t bite me in the future.

I am just about to wrap up some prototyping comparing snowflake, typeids, uuidv4 and ulid. Why did I not bump into uuidv7 earlier?!?

Don't know, because uuidv7 has been coming for ages... https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u...

Relying on timestamps to be sortable, when clock skew and ntd guarantee that they won't always be, strikes me as poor design.

If you need to sort by insert order, use an autoincrementing integer, if you need uniqueness, UUIDv4 is fine, if you need both use both.

Use timestamps when you need to record the time, just don't commit the sin of presuming that clock time will never run backwards, I assure you, it does.

The problem they describe is not about sorting: it’s about data locality. And for the latter, clock skew should not be a problem. Even with significant clock skew, data will end up clustered anyway, much better than with a random spread.

You get index locality with an autoincrement also, and it will actually reflect insert order. My point is that a timestamp won't do that, and worse, it will appear to most of the time. The failure can be fairly spectacular, a Unix clock can be set to any time at all, and it's good when the resulting bugs are limited to time. Having an actual insert order can be a real boon to figuring out what happened.

I hold to the principle that relational data should be normal, and combining uniqueness with a timestamp doesn't do that. To do any of the calculations we use timestamps for, you have to strip off the entropy, this complicates pushing it down to the database level, where the libraries don't expect such conflation.

You're going to have a bad time writing something like a join across tables with a restricted range of time if your time is embedded in UUIDv7.

I maintain this is good advice: if you need index locality and insert order, use an autoincrement. If you need to record and work with time, use a timestamp. If you need global uniqueness, you can use any of the UUIDs, but v4 is the one that doesn't conflate uniqueness with unrelated properties, and should be preferred.

If you think your need data locality but not insert order, think long and hard about what you're doing, because odds are you're wrong. If it turns out you're right, and the OP might be in that situation, sure, go ahead and use UUIDv7.

Just, please, for the sake of your future self and everyone you work with, don't use a timestamp for insert order. Ever.

That's still fine. Because even if there's skew and other such things going on, it's more likely to take advantage of cache locality since the page in the index that key would be stored in, is much more likely to still be in memory.

How long will it be before the “milliseconds since epoch” part of the uuid overflows or repeats?

  $ date -ud @$(( 256 ** 6 / 1000 ))
  Tue Aug  2 05:31:50 AM UTC 10889

Well, at least it's not a Friday.

Interesting - have beem using uuidv4 for a long time. Will explore further on uuidv7

why bother with any version of the uuid standard? just generate a random 128-bit number and use it. that's all the newer ones are anyway

> why bother with any version of the uuid standard? just generate a random 128-bit number and use it. that's all the newer ones are anyway

Good question.

Won't random 128-bit numbers actually be superior to UUIDs in every way except predictability?

Sorry to be this guy, but did you read the article? Only UUIDv4 is "just generating a random 128-bit number" (almost) -- and there are valid reasons that's not a good choice. Which the article explains. :) Like database insertion performance. Sequential IDs have benefits.

I wonder who this article is written for. Who would be reading about UUIDs but not know about cache hit rates?

> As a result, retrieving the most recent data from a large dataset will require traversing a large number of database index pages, leading to a poor cache hit ratio (how many requests a cache is able to fill successfully, compared to how many requests it receives).

Reply on news.ycombinator.com