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

271–280 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#271

Post by cycomanic »

vintermann wrote: post #65

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

Like the other poster said, this is a problem with default values not encoding the birthday into the personnummer.

I think it also is important to remember the purpose of specific numbers. For instance I would argue a PN without the birthday would be strictly worse. With the current system (I only know the Swedish one, but assume it's the same) I only have to remember a 4 digit (because the number is bdate + unique 4 digits). If we would instead use completely random numbers I would have to remember at least an 8 digit number (and likely to be future proof you'd want at least 9 digits). Sure that's fine for myself (although I suspect some people already struggle with it), but then I also have to remember the numbers for my 2 kids and my partner and things become quickly annoying. Especially, because one doesn't use the numbers often enough that it becomes easy, but still often enough that it becomes annoying to look up, especially when one doesn't always cary their phone with them.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#273

Post by phendrenad2 »

You probably don't want integer primary keys, and you probably don't want UUID primary keys. You probably want something in-between, depending on your use case. UUID is one extreme on this spectrum, which tries to solve all of the problems, including ones you might not have.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#274

Post by OptionOfT »

ralferoo wrote: post #221
vintermann wrote: post #65

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

Belgium's national register number is similar:

YY.MM.DD-AAA.BB

In either the AAA or BB component there is something about the gender.

But it does mean that there is a limit of people born per day of a certain gender.

But for a given year, using a moniker will only delay the inevitable. Sure, there are more numbers, but still limited as there are SOME parts that need to reflect reality. Year, gender (if that's still the case?) etc.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#275

Post by Terr_ »

bigstrat2003 wrote: post #262
delichon wrote: post #235

Earlier quoted context omitted.

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

That is only true if you're using an extremely idiosyncratic definition of gender. As far as 95% of English speakers are concerned, gender is defined by the body you possess.

Does that mean hundreds of years of English-speakers referring to sailing ship as "she" were all part of a conspiracy to hide that ships have jiggly bits? :p

Re: Avoid UUID Version 4 Primary Keys in Postgres

#276

Post by dpark »

mixmastamyk wrote: post #267
dpark wrote: post #266

Earlier quoted context omitted.

> the prefix is an index to its disk block location What? This is definitely not the case and can’t be because B-tree nodes change while UUIDs do not.

I didn’t mean that literally, but no longer editable. Was supposed to have “like” etc in there.

But UUIDv7 doesn’t change that at all. It doesn’t matter what flavor of UUID you choose. The ID is always “like” an index to a block in that you traverse the tree to find the node. What UUIDv7 does is improve some performance characteristics when creating new entries and potentially for caching.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#278

Post by sedatk »

gwbas1c wrote: post #149

I work on an application where we encrypt the integer primary key and then use the bytes to generate something that looks like a UUID. In our case, we don't want database IDs in an API and in URLs. When IDs are sequential, it enables things like dictionary attacks and provides estimates about how many customers we have. Encrypting a database ID makes it very obvious when someone is trying to scan, because the UUID wo…

That sounds quite troublesome if the encryption key is lost, compromised, or rotated for any other reason.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#279

Post by mkleczek »

everforward wrote: post #249
mkleczek wrote: post #228

Earlier quoted context omitted.

> I think artificial and data-less identifiers are the better means of identification that takes into account that things change. They don't have to be the identifier you present to the world, but having them is very useful. If the only reason you need a surrogate key is to introduce indirection in your internal database design then sequence numbers are enough. There is no need to use UUIDs. The whole discussion is a…

> If the only reason you need a surrogate key is to introduce indirection in your internal database design then sequence numbers are enough. There is no need to use UUIDs. The UUID would be an example of an external key (for e.g. preventing crawling keys being easy). This article mentions a few reasons why you may later decide there are better external keys. > When I come to you and say "My name is X, this is my phon…

> The UUID would be an example of an external key (for e.g. preventing crawling keys being easy). This article mentions a few reasons why you may later decide there are better external keys.

So we are talking about "external" keys (ie. visible outside the database). We are back to square one: externally visible surrogate keys are problematic because they are detached from real world information they are supposed to identify and hence don't really identify anything (see my example about GDPR).

It does not matter if they are random or not.

> How are you going to trace all those records if the requester has changed their name, phone number and email since they signed up if you don't have a surrogate key?

And how does surrogate key help? I don't know the surrogate key that identifies my records in your database. Even if you use them internally it is an implementation detail.

If you keep information about the time information was captured, you can at least ask me "what was your phone number last time we've interacted and when was it?"

> I think that spirals into way more complexity than you're thinking.

This complexity is there whether you want it or not and you're not going to eliminate it with surrogate keys. It has to be explicitly taken care of.

DBMSes provide means to tackle this essential complexity: bi-temporal extensions, views, materialized views etc.

Event sourcing is a somewhat convoluted way to attack this problem as well.

> Those queries are incredibly simple with surrogate keys: "SELECT * FROM phone_number_changes WHERE user_id = blah".

Sure, but those queries are useless if you just don't know user_id.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#280

Post by konart »

dpark wrote: post #259
konart wrote: post #256

Earlier quoted context omitted.

>Before you know it, someone's relying Do not expose your internal IDs. As simple as that.

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.

Post reply on HN