The author comes across one of the issues with using UUIDv4 as a (primary) key — they are not great for btree performance. Particularly for clustered indexes like in MySQL, but also in non clustered indexes.
On the other hand if you ever want to shard based on ID full randomness is fantastic. There is a funny tradeoff where if all of your IDs are sequential then you get very good locality in your storage system. But that backfires if it creates too much of a hotspot.
Maybe the best option is something like a UUIDv7 (time + random) ID and you can tune your shard keys by shuffling the bits. Maybe you want something like moving the last 4 bits to the front of the shard key so that you have 16 hot spots, which is good for performance without any being too hot.
In my experience sharing is generally done on a "technical" key because it's easy to arbitrarily partition into any desired number of shards.
Something like per-year could result in different years having vastly different disk usage requirements, which would be a headache.
While per-region or per-customer isn't really sharding at all in my understanding. It's just giving each customer (or region) their own dedicated/private database.
Sharding is generally about dividing a single table (for business logic purposes) among (usually) equally-sized servers for performance/capacity reasons, according to some function of the primary key (modulus, hash, etc.).
Great article. I ended up using ULIDs recently, since I had an ORM layer that shared two different database providers (SQLite and SQL Server), and for whatever reason ULID worked when the other options didn't. Something about the differences in the blob representation of the Microsoft GUID format, which is a variant of UUID.
You can add some secret sauce on top of the ULID spec to force monotonic behavior if needed.
Curious about the tradeoff between index builds on insert and general hotspotting if you have sequentially increasing primary keys. I had always thought the latter was a bigger problem (to the point where I would need to reverse the bits of some uuid pkey before writing it). I guess scale matters?
Scale definitely matters. You want your caches to be hot, but not too hot. If your primary keys are sequential you will probably have great performance, but will have a hard time sharing when you need to. Although that can probably be solved by shuffling a bit of the high entropy bits to the front of the shard key. Ideally you would be able to dynamically adjust the number of bits shuffled to get the right balance of hot but not overloaded caches. On the other hand if you were using random IDs no amount of shuffling is going to make them group into nice high-hit-rate slices.
I don't know if it's coincidence, or driven by the front page post on Nano IDs but those interested in this may be interested in that: https://news.ycombinator.com/item?id=34172989
UUIDv7 hasn't been standardized yet, but ULID is way worse. Its spec includes a monotonic mode which entirely contradicts the promises of a "universal" ID.
I wouldn't use ULIDs on a new project. The spec doesn't seem like it has much stewardship, the last commit was in 2019 with a bunch of open PRs and issues:
https://github.com/ulid/spec/issues
I went through this exploration a while back for a new project and decided on uuidv7s, which are binary compatible with ULIDs but will likely find more support as they get added to the original UUID RFC.
Either UUIDv7 or XIDs seem like better choices than ULIDs for new projects.
> the last commit was in 2019 with a bunch of open PRs and issues
I don’t see much of a problem with that. It’s a spec, and is probably considered complete by its authors. The issues and PRs are mostly people wanting to add links to more implementations of the spec to the README. That’s understandable but also not really necessary.
I’m not sure if it’s even possible to turn off PR for a repo on GitHub. One of the replies at https://stackoverflow.com/questions/27957454/how-to-disable-... seems to indicate that it is now possible since quite recently, but the linked post does not seem directly about disabling PRs.
Either way, we don’t know if they really don’t want any issues or PRs opened at all just because they have some old issues and PRs still open.
From my experience this is only triggered when generating something like a thousand IDs a second (or more?), something I never run into in the systems I work on.
Since _every_ system has a down side or limitation, I am perfectly happy with ULID's flaws.
Why should there be disdain or dismissal of a system that solves many people's problems?
Finally! I have a question I've been dying to ask for some time.
Most of the time, when I hear people talking about UUIDs being better than big ints, it comes down to big ints leaking data (how many users / posts / comments your site has) and letting an attacker fetch sequential entries (user id 4 works, now try 5). This is even the reasoning in the supabase article. While ULIDs don't reveal the next entry, they do leak data (either creation date or number of entries, as all ULIDs are either prefixed with physical time or logical time).
Why don't we just encrypt / decrypt IDs when going over the wire (most API frameworks even have native methods to transform different types of data)? It suffers from neither issue above, is natively supported by API frameworks, and any time used on compute will be dwarfed by network latency. Is this already a pattern and I just don't know about it?
EDIT: ahh, below they list one advantage of UUIDs / ULIDs which is you can generate client side. That is reasonable.
Great username, however ULIDs look good and I’d consider using them simply because they solve the problems UUID v7 attempts to solve while being considerably better in some regards and slightly worse in others. I think for my next project I’ll try them instead of UUIDv4.
In the end support for something this simple and complete isn’t something I’d have big concerns about.
Thank you for posting pg_idkit. For months I was searching something like it. Supabase doing great things as well. They have couple others great PG extensions.
> While all of these formats can be generated by the client before inserting them into the database, for the purpose of simplicity and consistency, having them be generated within the database engine is preferred.
This very much misses the point of UUID/ULID pkeys. The whole idea is that you can generate them client-side without needing a connection to the database and assume that they will not conflict. This speeds up everything considerably allowing you to just write to the database once when generating new records without holding onto a sequence, without needing to worry about synchronizing ids between client & server, and without needing to worry about synhronizing sequences between multiple shards.
We've been using ULIDs with postgres in production for years without the database even being ULID-aware at all. The only thing you might want is support for a ULID type so you can manually draft queries with ULIDs directly (rather than having to use their binary equivalent as a UUID) and to display results of manual db queries with pkeys cast as Crockford base-32 ULIDs rather than UUID format.
> The whole idea is that you can generate them client-side ...
And this is not precluded in the article. One may, at INSERT, also provide the ID. But if you don't it's not an error - the database chooses a default for you.
One thing to be aware off is that the ULID spec only guarantees ordering up to millisecond precision. Most implementation additionally guarantee ordering of the 80 random bits for absolute ordering but only IN PROCESS. If you have many clients concurrently producing data and generating ULIDs you can run into out-of-order rows. If this matters or not is ofc application specific.
One use case of ULIDs is as event IDs in distributed systems. If the producers are horizontally scaled Microservices you might generate events with reversed ULIDs compared to their logical order.
Ordering in distributed systems is anyway very hard but be aware that ULIDs are also no silver bullet
I think this is a bit of a red herring. ULIDs are not meant as a replacement for a lamport timestamp or something. The accuracy to the millisecond would only be correctly ordered if the clocks were perfectly synchronized anyway. I interpret the purpose of the timestamp based id as allowing denser clustering in b-trees vs random key data, and that’s about it. The timestamp can only be used as a relative ordering when processing multiple events from the same client, and even then not completely (client clock could change).
> PostgreSQL has built in support for UUIDv4 through the pgcrypto or the uuid-ossp extensions.
Do note that postgres supports generating UUIDv4 without any extension, via the `gen_random_uuid ` function, since PG 13. (released sep 2020).
A lot of people still haven't realized this! (Apparently including OP author!)
There are some circumstances where even loading built-in extensions is inconveient. (Some people have told me their container-based setup makes this infeasible, I don't totally understand why, it's not not my use case, but just in case...) But no extension necessary for UUIDv4 in postgres 13 and following!
> Do note that postgres supports generating UUIDv4 without any extension, via the `gen_random_uuid ` function, since PG 13. (released sep 2020).
> A lot of people still haven't realized this! (Apparently including OP author!)
A lot of people still use pre-PG13 versions (maybe including OP author).
I don't understand the OP's case for ULID over UUIDv7.
> The main differences between UUIDv7 and ULID are:
> * UUIDv7
> * UUIDv7 will work until 4,147AD whereas ULID will work until 10,889AD
Is the first bullet point a typo, missing something? So we're just left with the second one.... is that really a concern for anyone?
Maybe the first bullet point was supposed to say that UUIDv7 explicitly exposes the fact that it's a UUIDv7, as the OP does mention that as another significant difference. To me that, and the fact that it is a UUID, seems enough reason to choose UUID7, when they are mostly otherwise pretty similar?
If it's true UUIDv7 spec isn't finalized yet though, it would be nice if they'd finalize it.... ah. It appears to be a "Proposed Standard". I have trouble figuring out what's what in IETF standard-making terminology (starting with "everything's an RFC"!), but wikipedia suggests "Actual practice has been that full progression through the sequence of standards levels is typically quite rare, and most popular IETF protocols remain at Proposed Standard." So sounds like it can be considered a standard. I wonder why eg postgres hasn't implemented it yet in a built-in thing.
You have internet drafts and RFCs, where RFCs are assigned numbers and are immutable. Internet Drafts are time limited and typically used for work in progress.
You have two tracks - informational (for things which are intended to document things but not be standards, including vendors documenting extensions to protocols) and standards track, which has maturity levels like proposed standard.
You could say the goal of both tracks is interoperability, while standards track is motivated by having everyone's comments be heard and respected (to the degree possible).
Labels like "proposed standard" are basically meant to mark the maturity in the standards process. A future revision might clarify or change the RFC, or the proposed standard might be replaced by something different.
A more material example of this progression:
HTTP/1.1 was released as:
1. RFC 2068 (proposed standard, January 1997)
2. RFC 2616 (draft standard, June 1999)
3. Reworked through the BIS process into several RFCs (7230-7235, proposed standards, June 2014)
4. Replaced by RFC 9110 and 9112 (and friends) (Internet Standard, STD 97, June 2022)
But note these are all revisions of HTTP/1.1 - HTTP/2 and HTTP/3 have developed independently and have their own maturity levels.
This is one of the ways that standards differ from software. There have been several documents about HTTP/1.1, all of which aimed to provide maximal backward compatibility with previous deployments, and to clarify and prescribe behavior where there were implementation, interoperability and operational difficulties identified. None of them were HTTP/1.2.
I am struggling understanding the underlying point here - UniqueIDs of some sort are generally great but if You're desperately need some kind of precise monotonic increase I need to have my thinking cap on
- everything is on same machine / database - great ! Use UUID 7 or just grab a long integer ...
- things are on distributed machines but they talk back to a central machine. Now this gets harder (and is kind of actually the same problem as single machine but we ignore that). Network speed really screws with you if you ask for a new monotonic id each time, and if you do anything else you have just sharded and well that's a new design.
- if you want some monotonic id generated distributed-ly then good luck. Not sure I have understood paxos that well.
In the end I strongly want to avoid the need for stric distributed ordering - and suggest it is often the failure of business model not the technology
Surprised to see no mention of UUID v1mc, which Postgres supports today via ossp.
Like v1, it has a 60 or so bits of timestamp at the front. But unlike v1 which ends with the generating node's MAC address, v1mc uses a random MAC, essentially giving it 48 bits of randomness.
47 comments
[ 2.5 ms ] story [ 92.6 ms ] threadMaybe the best option is something like a UUIDv7 (time + random) ID and you can tune your shard keys by shuffling the bits. Maybe you want something like moving the last 4 bits to the front of the shard key so that you have 16 hot spots, which is good for performance without any being too hot.
Something like per-year could result in different years having vastly different disk usage requirements, which would be a headache.
While per-region or per-customer isn't really sharding at all in my understanding. It's just giving each customer (or region) their own dedicated/private database.
Sharding is generally about dividing a single table (for business logic purposes) among (usually) equally-sized servers for performance/capacity reasons, according to some function of the primary key (modulus, hash, etc.).
But yes, in what dimension you shard will usually depend on the application.
A brief history of the UUID (2017) - https://news.ycombinator.com/item?id=19069553 - Feb 2019 (21 comments)
A Brief History of the UUID - https://news.ycombinator.com/item?id=14508413 - June 2017 (66 comments)
You can add some secret sauce on top of the ULID spec to force monotonic behavior if needed.
Understanding UUIDs, ULIDs and string representations - https://news.ycombinator.com/item?id=29794186 - Jan 2022 (100 comments)
Going Deep on UUIDs and ULIDs - https://news.ycombinator.com/item?id=28948815 - Oct 2021 (2 comments)
https://en.wikipedia.org/wiki/Universally_unique_identifier#...
I went through this exploration a while back for a new project and decided on uuidv7s, which are binary compatible with ULIDs but will likely find more support as they get added to the original UUID RFC.
Either UUIDv7 or XIDs seem like better choices than ULIDs for new projects.
* Supabase on different primary key considerations: https://supabase.com/blog/choosing-a-postgres-primary-key
* Postgres extension for generating various kinds of IDs: https://github.com/VADOSWARE/pg_idkit
I don’t see much of a problem with that. It’s a spec, and is probably considered complete by its authors. The issues and PRs are mostly people wanting to add links to more implementations of the spec to the README. That’s understandable but also not really necessary.
If you do not believe a PR or issue to be valid, you close it. If you are literally not willing to accept any PRs or issues, turn the feature off.
Either way, we don’t know if they really don’t want any issues or PRs opened at all just because they have some old issues and PRs still open.
(fyi, I am very happy user of ULID for years, I don't need libraries to be updated to keep using it)
I am wanting UUIDv7 but I can use ULID right now.
Since _every_ system has a down side or limitation, I am perfectly happy with ULID's flaws.
Why should there be disdain or dismissal of a system that solves many people's problems?
Most of the time, when I hear people talking about UUIDs being better than big ints, it comes down to big ints leaking data (how many users / posts / comments your site has) and letting an attacker fetch sequential entries (user id 4 works, now try 5). This is even the reasoning in the supabase article. While ULIDs don't reveal the next entry, they do leak data (either creation date or number of entries, as all ULIDs are either prefixed with physical time or logical time).
Why don't we just encrypt / decrypt IDs when going over the wire (most API frameworks even have native methods to transform different types of data)? It suffers from neither issue above, is natively supported by API frameworks, and any time used on compute will be dwarfed by network latency. Is this already a pattern and I just don't know about it?
EDIT: ahh, below they list one advantage of UUIDs / ULIDs which is you can generate client side. That is reasonable.
In the end support for something this simple and complete isn’t something I’d have big concerns about.
Since Postgres 13 installing an extension is no longer necessary as gen_random_uuid() is part of the core.
This very much misses the point of UUID/ULID pkeys. The whole idea is that you can generate them client-side without needing a connection to the database and assume that they will not conflict. This speeds up everything considerably allowing you to just write to the database once when generating new records without holding onto a sequence, without needing to worry about synchronizing ids between client & server, and without needing to worry about synhronizing sequences between multiple shards.
We've been using ULIDs with postgres in production for years without the database even being ULID-aware at all. The only thing you might want is support for a ULID type so you can manually draft queries with ULIDs directly (rather than having to use their binary equivalent as a UUID) and to display results of manual db queries with pkeys cast as Crockford base-32 ULIDs rather than UUID format.
Yeah, it's more simple.
> The whole idea is that you can generate them client-side ...
And this is not precluded in the article. One may, at INSERT, also provide the ID. But if you don't it's not an error - the database chooses a default for you.
Or in multiple write shards of the db without worrying about whether you've suffered a network partition.
Whether it makes sense for application code or the db itself to generate them . . . depends.
One use case of ULIDs is as event IDs in distributed systems. If the producers are horizontally scaled Microservices you might generate events with reversed ULIDs compared to their logical order.
Ordering in distributed systems is anyway very hard but be aware that ULIDs are also no silver bullet
Do note that postgres supports generating UUIDv4 without any extension, via the `gen_random_uuid ` function, since PG 13. (released sep 2020).
A lot of people still haven't realized this! (Apparently including OP author!)
There are some circumstances where even loading built-in extensions is inconveient. (Some people have told me their container-based setup makes this infeasible, I don't totally understand why, it's not not my use case, but just in case...) But no extension necessary for UUIDv4 in postgres 13 and following!
A lot of people still use pre-PG13 versions (maybe including OP author).
> The main differences between UUIDv7 and ULID are:
> * UUIDv7
> * UUIDv7 will work until 4,147AD whereas ULID will work until 10,889AD
Is the first bullet point a typo, missing something? So we're just left with the second one.... is that really a concern for anyone?
Maybe the first bullet point was supposed to say that UUIDv7 explicitly exposes the fact that it's a UUIDv7, as the OP does mention that as another significant difference. To me that, and the fact that it is a UUID, seems enough reason to choose UUID7, when they are mostly otherwise pretty similar?
If it's true UUIDv7 spec isn't finalized yet though, it would be nice if they'd finalize it.... ah. It appears to be a "Proposed Standard". I have trouble figuring out what's what in IETF standard-making terminology (starting with "everything's an RFC"!), but wikipedia suggests "Actual practice has been that full progression through the sequence of standards levels is typically quite rare, and most popular IETF protocols remain at Proposed Standard." So sounds like it can be considered a standard. I wonder why eg postgres hasn't implemented it yet in a built-in thing.
As one might expect, they have a best current practice for that: https://datatracker.ietf.org/doc/html/rfc2026
You have internet drafts and RFCs, where RFCs are assigned numbers and are immutable. Internet Drafts are time limited and typically used for work in progress.
You have two tracks - informational (for things which are intended to document things but not be standards, including vendors documenting extensions to protocols) and standards track, which has maturity levels like proposed standard.
You could say the goal of both tracks is interoperability, while standards track is motivated by having everyone's comments be heard and respected (to the degree possible).
Labels like "proposed standard" are basically meant to mark the maturity in the standards process. A future revision might clarify or change the RFC, or the proposed standard might be replaced by something different.
A more material example of this progression:
HTTP/1.1 was released as:
1. RFC 2068 (proposed standard, January 1997)
2. RFC 2616 (draft standard, June 1999)
3. Reworked through the BIS process into several RFCs (7230-7235, proposed standards, June 2014)
4. Replaced by RFC 9110 and 9112 (and friends) (Internet Standard, STD 97, June 2022)
But note these are all revisions of HTTP/1.1 - HTTP/2 and HTTP/3 have developed independently and have their own maturity levels.
This is one of the ways that standards differ from software. There have been several documents about HTTP/1.1, all of which aimed to provide maximal backward compatibility with previous deployments, and to clarify and prescribe behavior where there were implementation, interoperability and operational difficulties identified. None of them were HTTP/1.2.
- everything is on same machine / database - great ! Use UUID 7 or just grab a long integer ...
- things are on distributed machines but they talk back to a central machine. Now this gets harder (and is kind of actually the same problem as single machine but we ignore that). Network speed really screws with you if you ask for a new monotonic id each time, and if you do anything else you have just sharded and well that's a new design.
- if you want some monotonic id generated distributed-ly then good luck. Not sure I have understood paxos that well.
In the end I strongly want to avoid the need for stric distributed ordering - and suggest it is often the failure of business model not the technology
Like v1, it has a 60 or so bits of timestamp at the front. But unlike v1 which ends with the generating node's MAC address, v1mc uses a random MAC, essentially giving it 48 bits of randomness.