ULIDs and Primary Keys

I wouldn&#x27;t use ULIDs on a new project. The spec doesn&#x27;t seem like it has much stewardship, the last commit was in 2019 with a bunch of open PRs and issues: <a href="https:&#x2F;&#x2F;github.com&#x2F;ulid&#x2F;spec&#x2F;issues">https:&#x2F;&#x2F;github.com&#x2F;ulid&#x2F;spec&#x2F;issues</a><p>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.<p>Either UUIDv7 or XIDs seem like better choices than ULIDs for new projects.<p>* Supabase on different primary key considerations: <a href="https:&#x2F;&#x2F;supabase.com&#x2F;blog&#x2F;choosing-a-postgres-primary-key">https:&#x2F;&#x2F;supabase.com&#x2F;blog&#x2F;choosing-a-postgres-primary-key</a><p>* Postgres extension for generating various kinds of IDs: <a href="https:&#x2F;&#x2F;github.com&#x2F;VADOSWARE&#x2F;pg_idkit">https:&#x2F;&#x2F;github.com&#x2F;VADOSWARE&#x2F;pg_idkit</a>

&gt; 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.<p>This very much misses the point of UUID&#x2F;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 &amp; server, and without needing to worry about synhronizing sequences between multiple shards.<p>We&#x27;ve been using ULIDs with postgres in production for years without the database even being ULID-aware at all. The only thing you <i>might</i> 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.

I don&#x27;t understand the OP&#x27;s case for ULID over UUIDv7.<p>&gt; The main differences between UUIDv7 and ULID are:<p>&gt; * UUIDv7<p>&gt; * UUIDv7 will work until 4,147AD whereas ULID will work until 10,889AD<p>Is the first bullet point a typo, missing something? So we&#x27;re just left with the second one.... is that really a concern for anyone?<p>Maybe the first bullet point was supposed to say that UUIDv7 explicitly exposes the fact that it&#x27;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?<p>If it&#x27;s true UUIDv7 spec isn&#x27;t finalized yet though, it would be nice if they&#x27;d finalize it.... ah. It appears to be a &quot;Proposed Standard&quot;. I have trouble figuring out what&#x27;s what in IETF standard-making terminology (starting with &quot;everything&#x27;s an RFC&quot;!), but wikipedia suggests &quot;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.&quot; So sounds like it can be considered a standard. I wonder why eg postgres hasn&#x27;t implemented it yet in a built-in thing.

&gt; PostgreSQL has built in support for UUIDv4 through the pgcrypto or the uuid-ossp extensions.<p>Do note that postgres supports generating UUIDv4 without any extension, via the `gen_random_uuid ` function, since PG 13. (released sep 2020).<p>A lot of people still haven&#x27;t realized this! (Apparently including OP author!)<p>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&#x27;t totally understand why, it&#x27;s not not my use case, but just in case...) But no extension necessary for UUIDv4 in postgres 13 and following!

Related:<p><i>Understanding UUIDs, ULIDs and string representations</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=29794186" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=29794186</a> - Jan 2022 (100 comments)<p><i>Going Deep on UUIDs and ULIDs</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=28948815" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=28948815</a> - Oct 2021 (2 comments)

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.

&gt; PostgreSQL has built in support for UUIDv4 through the pgcrypto or the uuid-ossp extensions.<p>Since Postgres 13 installing an extension is no longer necessary as gen_random_uuid() is part of the core.

I am struggling understanding the underlying point here - UniqueIDs of some sort are generally great but if You&#x27;re desperately need some kind of precise monotonic increase I need to have my thinking cap on<p>- everything is on same machine &#x2F; database - great ! Use UUID 7 or just grab a long integer ...<p>- 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&#x27;s a new design.<p>- if you want some monotonic id generated distributed-ly then good luck. Not sure I have understood paxos that well.<p>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

<a href="https:&#x2F;&#x2F;segment.com&#x2F;blog&#x2F;a-brief-history-of-the-uuid&#x2F;" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;blog&#x2F;a-brief-history-of-the-uuid&#x2F;</a>

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?

Surprised to see no mention of UUID v1mc, which Postgres supports today via ossp.<p>Like v1, it has a 60 or so bits of timestamp at the front. But unlike v1 which ends with the generating node&#x27;s MAC address, v1mc uses a random MAC, essentially giving it 48 bits of randomness.

I prefer UUIDv7 over ULID because of the standardization process.<p><a href="https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;Universally_unique_identifier#Standards" rel="nofollow">https:&#x2F;&#x2F;en.wikipedia.org&#x2F;wiki&#x2F;Universally_unique_identifier#...</a>

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&#x27;t. Something about the differences in the blob representation of the Microsoft GUID format, which is a variant of UUID.<p>You can add some secret sauce on top of the ULID spec to force monotonic behavior if needed.

I don&#x27;t know if it&#x27;s coincidence, or driven by the front page post on Nano IDs but those interested in this may be interested in that: <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34172989" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34172989</a>

I have been using ULID(github.com&#x2F;oklog&#x2F;ulid) for years. Cannot imagine going back to AI uint64 ever again.