This is such a mediocre article. It provides plenty of valid reasons to consider avoiding UUID in databases, however, it doesn’t say what should be used should one want primary keys that are not easy to predict. The XOR alternative is too primitive and, well, whereas I get why should I consider avoiding UUID, then what should I use instead?
Avoid UUID Version 4 Primary Keys in Postgres
301–310 of 463 posts
Re: Avoid UUID Version 4 Primary Keys in Postgres
#302I've been using ULIDs [0] in prod for many years now, and I love them. I just use string encoding, though if I really wanted to squeeze out every last MB, I could do some conversion so it is stored as 16 bytes instead of 26 chars. In practice it's never mattered, and the simplicity of just string IDs everywhere is nice.
Sometimes I have to talk to legacy systems, all my APIs have str IDs, and I encode int IDs as just decimal left padded with leading zeros up to 26 chars. Technically not a compliant ULID but practically speaking, if I see leading `00` I know it's not an actual ULID, since that would be before Nov-2004, and ULID was invented in 2017. The ORM automatically strips the zeros and the query just works.
I'm just kind of over using sequential int IDs for anything bigger than hobby level stuff. Testing/fixturing/QA are just so much easier when you do not have to care about whether an ID happens to already exist.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#303I've been using ULIDs [0] in prod for many years now, and I love them. I just use string encoding, though if I really wanted to squeeze out every last MB, I could do some conversion so it is stored as 16 bytes instead of 26 chars. In practice it's never mattered, and the simplicity of just string IDs everywhere is nice. Sometimes I have to talk to legacy systems, all my APIs have str IDs, and I encode int IDs as just…
Re: Avoid UUID Version 4 Primary Keys in Postgres
#304Earlier quoted context omitted.
This came up in the last two threads I read about uuidv7. This is simply not a meaningful statement. Any ID you expose externally is also an internal ID. Any ID you do not expose is internal-only. If you expose data in a repeatable way, you still have to choose what IDs to expose, whether that’s the primary key or a secondary key. (In some cases you can avoid exposing keys at all, but those are narrow cases.)
You have one ID as a primary key. It is used for building relations in your database. The second ID has nothing to do with internal structure of your data. It is just another field. You can change your structure however you want (or type of your "internal" IDs) and you don't have to worry about an external consumer. They still get their artificial ID.
[deleted]
Re: Avoid UUID Version 4 Primary Keys in Postgres
#305A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…
> Norwegian PNs have your birth date (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that ser…
> If they'd chosen a non-date for unknown values, maybe 00 or 99 for day or month components, then the issue you described would disappear
> Norwegian PNs have your birth date (in DDMMYY format) as the first six digits.
You can already feel the disaster rising because sone program expects always the latter.
And it doesn’t fix the problem, it just makes it less likely.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#306Why not just use UUIDs as a unique column next to a bigint PK?
The power and main purpose of UUIDs is to act as easy to produce, non-conflicting references in distributed settings. Since the scope of TFA is explicitly set to be "monolithic web apps", nothing stops you from having everything work with bigint PKs internally, and just add the UUIDs where you need to provide external references to rows/objects.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#307A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…
> Permanent identifiers should not carry data. Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. > A prime example of premature optimization. Disagree. Data is sticky, and PKs especially so. Moreover, if you’re going to spend time optimizing anything early on, it should be your data model. > Don't make decisions you will regret just to shave off a couple of milliseco…
I read it (and regret it is a waste of my time). Their arguments are:
* integer keys are faster;
* uuidv7 keys are faster;
* if you want obfuscated keys, using integer and do some your own obfuscation (!!!).
I can get on-board of uuidv7 (with the trade-off, of course, on stronger guessability). The integer keys argument is strange. At that point, you need to come up with a custom-built system to avoid id collision in a distribution system and tries to achieve only 2x saving (the absolute minimal you should do is 64-bit keys). Very puzzling suggestion and to me very wrong.
Note that in this entire article, the recommendation is not about using natural keys (email address, some composite of user identification etc.), so I am skipping that whole discussion.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#308An additional thing I learned when I worked on a ulid alternative over the weekend[0] is: Postgres's internal Datum type is at most 64 bits which means every uuid requires heap allocation[1] (at least until we get 128 bit machines). 0: https://bsky.app/profile/hugotunius.se/post/3m7wvfokrus2g 1: https://github.com/postgres/postgres/blob/master/src/backend...
you may be interested in this postgres extension as well
Re: Avoid UUID Version 4 Primary Keys in Postgres
#309Earlier quoted context omitted.
A major one for me is preventing duplicate records. If the client POSTs a new object to insert it into the database; if there is a connection failure and the client does not receive a success response from the server, the client cannot know whether the record was inserted or not without making an expensive and cumbersome additional read call to check... The client cannot simply assume that the insertion did not happe…
Preferably, you would design you APIs and services to be idempotent (ie. use PUT not POST etc.) Using idempotency identifier is the last resort in my book.
How is URI not an idempotency identifier for PUT?
Re: Avoid UUID Version 4 Primary Keys in Postgres
#310The counter argument I would say is that having all these integer ids comes with many problems. You can't make em public cause they leak info. They are not unique across environments. Meaning you have to spin up a lot of bs envs to just run it. But retros are for complaining about test envs, right?
Uuid4 are only 224bits is a bs argument. Such a made up problem.
But a fair point is that one should use a sequential uuid to avoid fragmentation. One that has a time part.