[HN Gopher] ULIDs and Primary Keys
___________________________________________________________________
ULIDs and Primary Keys
Author : s4i
Score : 54 points
Date : 2022-12-29 18:00 UTC (5 hours ago)
(HTM) web link (blog.daveallie.com)
(TXT) w3m dump (blog.daveallie.com)
| dang wrote:
| Related:
|
| _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)
| onnnon wrote:
| I prefer UUIDv7 over ULID because of the standardization process.
|
| https://en.wikipedia.org/wiki/Universally_unique_identifier#...
| drzaiusx11 wrote:
| v6 and v7 aren't standardized yet, although proposals are
| ongoing.
| cratermoon wrote:
| https://segment.com/blog/a-brief-history-of-the-uuid/
| dang wrote:
| Thanks! Here are a couple past threads on that:
|
| _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)
| ComputerGuru wrote:
| > 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.
| jagged-chisel wrote:
| >> ... or the purpose of simplicity
|
| 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.
| sroussey wrote:
| 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.
| [deleted]
| pfooti wrote:
| 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?
| mdaniel wrote:
| 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
| nwah1 wrote:
| 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.
| SirensOfTitan wrote:
| 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.
|
| * 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
| sedatk wrote:
| UUIDv7 (and v6, v8 for that matter) hasn't been approved by
| IETF yet though, has it?
| codetrotter wrote:
| > 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.
| RobertRoberts wrote:
| Why does the spec need updating? As far as I can tell it's
| finished. Maybe your concern is a little irrational?
|
| (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.
| castorp wrote:
| > PostgreSQL has built in support for UUIDv4 through the pgcrypto
| or the uuid-ossp extensions.
|
| Since Postgres 13 installing an extension is no longer necessary
| as gen_random_uuid() is part of the core.
___________________________________________________________________
(page generated 2022-12-29 23:00 UTC)