Goodbye integers, hello UUIDv7

This is great for internal distributed systems where having ordered keys is useful, however, it should probably be noted that these probably shouldn&#x27;t be used as public identifiers (even though this will probably be the defacto standard and used publicly without thought).<p>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 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: <a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;53901549" rel="nofollow noreferrer">https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;53901549</a> and <a href="https:&#x2F;&#x2F;medium.com&#x2F;google-cloud&#x2F;cloud-spanner-choosing-the-right-primary-keys-cd2a47c7b52d" rel="nofollow noreferrer">https:&#x2F;&#x2F;medium.com&#x2F;google-cloud&#x2F;cloud-spanner-choosing-the-r...</a>

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.

&gt; 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<p>Unless you consider users being able to extract the generation time from the id to be an issue, of course.

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

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).<p>The external key is base64 encoded for use in URLs which results in an 11 byte string.<p>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.<p>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.<p>64 bit should be enough for most things <a href="https:&#x2F;&#x2F;youtu.be&#x2F;gocwRvLhDf8?si=QBheJCG21bAAV0Z7" rel="nofollow noreferrer">https:&#x2F;&#x2F;youtu.be&#x2F;gocwRvLhDf8?si=QBheJCG21bAAV0Z7</a>

Discussion here a couple months ago :<p>Analyzing New Unique Identifier Formats (UUIDv6, UUIDv7, and UUIDv8) (2022) <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=36438367">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=36438367</a>

It seems insane to me to “validate” GUIDs&#x2F;UUIDs.<p>Half the point of these things is that they’re treated as opaque identifiers.

Why use UUIDv7 over ULIDs?<p>As Lazare points out in this thread they&#x27;re basically the same thing, except with ULIDs you get those 6 extra bits of randomness back that UUIDs have to use for metadata.

If it helps anyone, at work, I open sourced the UUID v7 postgresql function that I wrote: <a href="https:&#x2F;&#x2F;github.com&#x2F;Betterment&#x2F;postgresql-uuid-generate-v7">https:&#x2F;&#x2F;github.com&#x2F;Betterment&#x2F;postgresql-uuid-generate-v7</a><p>We&#x27;ve seen some amazing benefits, especially around improving the speed of batch inserts.

A useful&#x2F;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.<p>You set the node field to a broadcast MAC address, and use that as a namespace&#x2F;prefix. This inches close to the boundary of the RFC, but is arguably compliant.<p>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&#x27;m wondering if there might be security&#x2F;privacy concerns in some situations if the UUID codes are visible in your app?

UUIDv7 is a nice idea, and should <i>probably</i> be what people use by default instead of UUIDv4 for internal facing uses.<p>For the curious:<p>* 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.<p>* 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&#x27;re expected to display them the same as other UUIDs, and should be compatible with <i>basically</i> anything that expects a UUID. (Would be a very odd system that parses a UUID and throws an error because it doesn&#x27;t recognise v7, but I guess it could happen, in theory?)<p>* ULIDs (<a href="https:&#x2F;&#x2F;github.com&#x2F;ulid&#x2F;spec">https:&#x2F;&#x2F;github.com&#x2F;ulid&#x2F;spec</a>) are 128 bits long, 48 bits encode a unix timestamp with millisecond precision, 80 bits are random. You&#x27;re expected to display them in Crockford&#x27;s base32, so 26 alphanumeric characters. Compatible with almost everything that expects a UUID (since they&#x27;re the right length). Spec has some <i>dumb</i> quirks if followed literally but thankfully they mostly don&#x27;t hurt things.<p>* KSUIDs (<a href="https:&#x2F;&#x2F;github.com&#x2F;segmentio&#x2F;ksuid">https:&#x2F;&#x2F;github.com&#x2F;segmentio&#x2F;ksuid</a>) 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&#x27;re expected to display them in base62, so 27 alphanumeric characters. Since they&#x27;re a different length, they&#x27;re <i>not</i> compatible with UUIDs.<p>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&#x27;t overflow for well over a century. Whereas UUIDv7s use 48 bits, so even with millisecond precision (not needed) they won&#x27;t overflow for something like 8000 years. We can argue whether 100 years is future proof enough (I&#x27;d argue it is), but 8000 years is just silly. Nobody will ever generate a compliant UUIDv7 with any of the first <i>several</i> bits aren&#x27;t 0. The only downside to KSUIDs is the length isn&#x27;t UUID compatible (and arguably, that they don&#x27;t devote 6 bits to a compliant UUID version).<p>Still feels like there&#x27;s room for improvement, but for now I think I&#x27;d always pick UUIDv7 over UUIDv4 unless there&#x27;s an very specific reason not to. Which would be, mostly, if there&#x27;s a concern over potentially leaking the time the UUID was generated. Although if you weren&#x27;t worrying about leaking an integer sequence ID, you likely won&#x27;t care here either.

&gt; first component (prefix) of the identifier is a sortable timestamp<p>&gt; values generated are practically sequential<p>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 <i>surely happen</i> and you must handle them gracefully.

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&#x27;t accidentally get an erroneous ID that happens to work the way you can with a sequential integer primary key.<p>For another project, I&#x27;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.<p>The original UUID types aren&#x27;t as useful as they once were, so it&#x27;d be worth writing a new RFC and extending those original types.

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?

Am I the only one instinctively upset by the communication&#x2F;bandwith&#x2F;storage overhead of the dashes as well as the version and variant bits of UUIDs?<p>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.<p>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.

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 &lt;20 stars, too experimental for me. Also, not sure the postgresql extension is in the main distribution, couldn&#x27;t find it if it does. For example, GCP only supports this one IIUC <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;uuid-ossp.html" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;uuid-ossp.html</a> 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.<p>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.<p>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?”<p>The issues around indexing and uuids that is discussed in the article fits nicely into this line of thinking.<p>In web development, database access and performance often dominates and infects the whole system.

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: <a href="https:&#x2F;&#x2F;firebase.blog&#x2F;posts&#x2F;2015&#x2F;02&#x2F;the-2120-ways-to-ensure-unique_68&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;firebase.blog&#x2F;posts&#x2F;2015&#x2F;02&#x2F;the-2120-ways-to-ensure-...</a>

Haha this is what we came up with for our home brewn unique ID&#x27;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?

&gt; 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.<p>Couldn&#x27;t that be solved with incremented serial numbers, rather than leaking time data?

&gt; The nature of Buildkite&#x27;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<p>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<p>[1]: <a href="https:&#x2F;&#x2F;github.com&#x2F;segmentio&#x2F;ksuid">https:&#x2F;&#x2F;github.com&#x2F;segmentio&#x2F;ksuid</a> which has very similar use cases.

This is neat. I&#x27;ve been using a custom snowflake cluster for years. Having this in the language&#x2F;DB would be great for smaller projects.<p>For bigger&#x2F;public projects I&#x27;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?

Anyone ever try encrypting a database ID (IE, a sequential int,) and use that as a public key?<p>IE, take a 32 or 64 bit int that&#x27;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:<p>a) UUID4, CreatedTime&#x2F;UpdatedTime.<p>b) Bigint, CreatedTime&#x2F;UpdatedTime.<p>c) UUID7 internal (which also includes time badly), UUID4 external&#x2F;whatever short ID.<p>How exactly this helps if you need external ids (which you usually do today)? It doesn&#x27;t even make it a short ID.<p>Even if there is a corner case, are we just saving a few bytes while adding more complication?<p>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 &quot;Created Date&#x2F;Updated Date&quot; or Name or whatever. Who cares about ordering IDs?<p>WAIT!!! But what about Next Tokens? ok, these are painful, but easily solved: Next can be (&gt;=Created Date,&gt;ID). Same result. Pagination, stays the same since it is sorted by Created Date.

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

Frustrating, I looked up MS&#x2F;C#&#x27;s implementation and they don&#x27;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:<p><pre><code> GET &#x2F;filter?a_id=X&amp;b_id=Y&amp;c_id=Z&amp;d_id=w </code></pre> But in practice we were using POST and passing the ids in the body payload. Why Because my old team said &quot;the UUIDs are long, so we may reach the maximum URL length if we pass them as parameters&quot;. I didn&#x27;t like it, and I still don&#x27;t like it at all.

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.

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

I chose ULIDs for a recent project.<p>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?!?

Relying on timestamps to be sortable, when clock skew and ntd guarantee that they won&#x27;t always be, strikes me as poor design.<p>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.<p>Use timestamps when you need to record the time, just don&#x27;t commit the sin of presuming that clock time will never run backwards, I assure you, it does.

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

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&#x27;s all the newer ones are anyway

I wonder who this article is written for. Who would be reading about UUIDs but not know about cache hit rates?<p>&gt; 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).