Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Avoid UUID Version 4 Primary Keys in Postgres

andyatkinson.com

Post reply on HN
Print view View original

231–240 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#231

Post by benterix »

mckirk wrote: post #88
tacone wrote: post #85

Earlier quoted context omitted.

Fantastic real life example. Italian PNs carry also the gender, which something you can change surgically, and you'll eventually run into the issue when operating at scale. I don't agree with the absolute statement, though. Permanent identifiers should not generally carry data. There are situations where you want to have a way to reconciliate, you have space or speed constraints, so you may accept the trade off, md5…

I'm not sure whether that was intended, but 'operating at scale' actually made me laugh out loud :D

I have to admit an unintended chuckle, too.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#232

Post by dpark »

groundzeros2015 wrote: post #182
vrighter wrote: post #138

Earlier quoted context omitted.

You can't take into account the fact that things change when you don't know what those changes might be. You might end up needing to either rebuild a new database, have some painful migration, or support two codepaths to work with both types of keys.

You can’t design something by trying to anticipate all future changes. things will change and break. In my personal design sense, I have found keeping away generality actually helps my code last longer (based on more concrete ideas) and easier to change when those days come.

In my experience, virtually every time I bake concrete data into identifiers I end up regretting it. This isn’t a case of trying to predict all possible future changes. It’s a case of trying to not repeat the exact same mistake again.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#233

Post by stickfigure »

benterix wrote: post #4

The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.

In Postgres I often like to use a single sequence for everything. It leaks some information yes but in a busy system it tends to be "obscure enough".

Re: Avoid UUID Version 4 Primary Keys in Postgres

#234

Post by bigmadshoe »

tmountain wrote: post #42
sbuttgereit wrote: post #27

Earlier quoted context omitted.

You might have missed the big H2 section in the article: "Recommendation: Stick with sequences, integers, and big integers" After that then, yes, UUIDv7 over UUIDv4. This article is a little older. PostgreSQL didn't have native support so, yeah, you needed an extension. Today, PostgreSQL 18 is released with UUIDv7 support... so the extension isn't necessary, though the extension does make the claim: "[!NOTE] As of Po…

Sticking with sequences and other integer types will cause problems if you need to shard later.

I’m really no expert on sharding but if you’re using increasing ints why can’t you just shard on (id % n) or something?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#235

Post by delichon »

cozyman wrote: post #194
tacone wrote: post #85

Earlier quoted context omitted.

Fantastic real life example. Italian PNs carry also the gender, which something you can change surgically, and you'll eventually run into the issue when operating at scale. I don't agree with the absolute statement, though. Permanent identifiers should not generally carry data. There are situations where you want to have a way to reconciliate, you have space or speed constraints, so you may accept the trade off, md5…

how does one change their gender surgically?

You can't, but since gender isn't defined by anything physical, there's no need.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#236

Post by sethhochberg »

mkleczek wrote: post #216
Ukv wrote: post #171

Earlier quoted context omitted.

> Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. The identifier is still connected to the user's data, just through the appropriate other fields in the table as opposed to embedded into the identifier itself. > So, what happens next is that the real world t…

> Using a random UUID as primary key does not mean users have to memorize that UUID. In fact in most cases I don't think there's much reason for it to even be exposed to the user at all. So what is such an identifier for? Is it only for some technical purposes (like replication etc.)? Why bother with UUID at all then for internal identifiers? Sequence number should be enough.

"Internal" is a blurry boundary, though - you pick integer sequence numbers and then years on an API gets bolted on to your purely internal database and now your system is vulnerable to enumeration attacks. Does a vendor system where you reference some of your internal data count as "internal"? Is UID 1 the system user that was originally used to provision the system? Better try and attack that one specifically... the list goes on.

UUIDs or other similarly randomized IDs are useful because they don't include any ordering information or imply anything about significance, which is a very safe default despite the performance hits.

There certainly are reasons to avoid them and the article we're commenting on names some good ones, at scale. But I'd argue that if you have those problems you likely have the resources and experience to mitigate the risks, and that true randomly-derived IDs are a safer default for most new systems if you don't have one of the very specific reasons to avoid them.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#237

Post by FancyFane »

Even MySQL benefits from these changes as well. What we're really discussing is random primary key inserts (UUIDv4) vs incrementing primary key inserts (UUIDv6 or v7).

PlanetScale wrote up a really good article on why incrementing primary keys are better for performance when compared to randomly inserted primary keys; when it comes to b-tree performance. https://planetscale.com/blog/btrees-and-database-indexes

Re: Avoid UUID Version 4 Primary Keys in Postgres

#238

Post by ralferoo »

tyre wrote: post #229
ralferoo wrote: post #221

Earlier quoted context omitted.

> 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…

And then have to enter/handle a non-date through all systems? How do you know if this non-dated person is over the age of minority? Eligible for a pension? Maybe the answer is to evenly spread the defaults over 365 days.

If you don't know their birthday, you can presumably never answer that question in any case.

If you only know the birth year and keyed 99 as the month for unknown, then your algorithm would determine they were of a correct age on the start of the year after that was true, which I guess would be what you want for legal compliance.

If you don't even know if the birth year is correct, then the correct process depends on policy. Maybe they choose any year, maybe they choose the oldest/youngest year they might be, maybe they just encode that as 0000/9999.

Again, if you don't know the birth year of someone, you would have no way of knowing their age. I'm not sure that means that the general policy of putting a birthday into their ID number is flawed.

Many governments re-issue national IDs to the same person with different numbers, which is far less problematic that the many governments who choose to issue the same national ID (looking at you USA with your SSN) to multiple individuals. It doesn't seem like a massive imposition on a person who was originally issued an ID based on not knowing when their birthday to be re-issued a new ID when their birthday was ascertained. Perhaps even give them a choice of keeping the old one knowing it will cause problems, or take the new one instead and having the responsibility to tell people their number had changed.

Presumably the governments that choose to embed the date into a national ID number do so because it's more useful for their purposes to do so than just assigning everyone a random number.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#240

Post by Ukv »

mkleczek wrote: post #216
Ukv wrote: post #171

Earlier quoted context omitted.

> Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place. The identifier is still connected to the user's data, just through the appropriate other fields in the table as opposed to embedded into the identifier itself. > So, what happens next is that the real world t…

> Using a random UUID as primary key does not mean users have to memorize that UUID. In fact in most cases I don't think there's much reason for it to even be exposed to the user at all. So what is such an identifier for? Is it only for some technical purposes (like replication etc.)? Why bother with UUID at all then for internal identifiers? Sequence number should be enough.

UUIDs are good for creating entries concurrently where coordinating between distributed systems may be difficult.

May also be that you don't want to leak information like how many orders are being made, as could be inferred from a `/fetch_order?id=123` API with sequential IDs.

Sequential primary keys are still commonly used though - it's a scenario-dependant trade-off.

Post reply on HN