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

311–320 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#311

Post by halffullbrain »

mkleczek wrote: post #279
everforward wrote: post #249

Earlier quoted context omitted.

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

In my country, citizens have an "ID" (a UUID, which most people don't know the value of!) and a social security number which they know - which has all the problems described above). While the social security number may indeed change (doubly assigned numbers, gender reassignment, etc.), the ID needn't change, since it's the same physical person.

Public sector it-systems may use the ID and rely on it not changing.

Private sector it-systems can't look up people by their ID, but only use the social security number for comparisons and lookups, e.g. for wiping records in GDPR "right to be forgotten"-situations. Social security numbers are sortof-useful for that purpose because they are printed on passports, driver's licenses and the like. And they are a problem w.r.t. identity theft, and shouldn't ever be used as an authenticator (we have better methods for that). The person ID isn't useful for identity theft, since it's only used between authorized contexts (disregarding Byzantine scenarios with rogue public-sector actors!). You can't social engineer your way to personal data using that ID unless (safe a few movie-plot scenarios).

So what is internal in this case? The person id is indeed internal to the public sector's it-systems, and useful for tracking information between agencies. They're not useful for Bob or Alice. (They ARE useful for Eve, or other malicious inside actors, but that's a different story, which realistically does require a much higher level of digital maturity across the entire society)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#312

Post by hedora »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

It's also application specific. If you have workload that's write heavy, has temporal skew and is highly concurrent, but rarely creates new records, you're probably better off with a random PK, even in PG.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#314

Post by BobaFloutist »

cozyman wrote: post #239
WalterSlovotsky wrote: post #222

Earlier quoted context omitted.

The preferred method would be gender affirming surgery.

affirming would mean the change has already taken place

Right, because it has. The change in gender identity (or in choosing to make said identity more public )has already taken place, and the surgery seems to affirm that.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#315

Post by HendrikHensen »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

In Google Cloud Bigtable we had the issue that our domain's primary key was a sequential integer autogenerated by another app. So we just reversed it, and it distributed automatically quite nicely.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#316

Post by sgarland »

lukeschlather wrote: post #241
alerighi wrote: post #218

Earlier quoted context omitted.

Why they are a security feature? They are not, the article even says it. Even if UUID4 are random, nobody guarantees that they are generated with a cryptographically secure random number generator, and in fact most implementations don't! The reason why in a lot of context you use UUID is when you have a distributed system where you want your client to decide the ID that is then stored in multiple systems that not com…

If a sequential ID is exposed to the client, the client can trivially use it to determine the number of records and the relative age of any records. UUID solves this, and the use of a cryptographically secure number generator isn't really necessary for it to solve this. The author's scheme might be similarly effective, but I trust UUIDs to work well. There are obviously varying ways to hide this information other tha…

I have never seen anyone post an actual example of the German Tank problem creating an issue for them, only that it’s possible.

> I don’t have to think about it

And here we have the main problem of most DB issues I deal with on a daily basis - someone didn’t want to think about the implications of what they were doing, and it’s suddenly then my emergency because they have no idea how to address it.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#317

Post by bruckie »

HendrikHensen wrote: post #315
stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

In Google Cloud Bigtable we had the issue that our domain's primary key was a sequential integer autogenerated by another app. So we just reversed it, and it distributed automatically quite nicely.

Doing this (reversing the bits of sequential ids) is common guidance inside Google for its distributed datastores like Spanner and Bigtable.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#318

Post by hyperman1 »

OptionOfT wrote: post #274
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…

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

BB is a mod-97 checksum. The first A of AAA encodes your gender in an even/odd fashion, I forgot if its the first or last A doing that. MM or DD can be 00 if unknown. Also MM has +20 or +40 in some cases.

If you know someones birth date and gender, the INSZ is almost certainly 1 in 500 numbers, with a heavy skew to the lower AAA. Luckily, you can't do much damage with someones number,unlike an USA SSN (but I'd still treat it confidential).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#319

Post by kgeist »

AtNightWeCode wrote: post #310

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

Some additional cases we encounter quite often where UUIDs help:

- A client used to run our app on-premises and now wants to migrate to the cloud.

- Support engineers want to clone a client’s account into the dev environment to debug issues without corrupting client data.

- A client wants to migrate their account to a different region (from US to EU).

Merging data using UUIDs is very easy because ID collisions are practically impossible. With integer IDs, we'd need complex and error-prone ID-rewriting scripts. UUIDs are extremely useful even when the tables are small, contrary to what the article suggests.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#320

Post by andatki »

dotancohen wrote: post #6

From the fine article: > Random values don’t have natural sorting like integers or lexicographic (dictionary) sorting like character strings. UUID v4s do have "byte ordering," but this has no useful meaning for how they’re accessed. Might the author mean that random values are not sequential, so ordering them is inefficient? Of course random values can be ordered - and ordering by what he calls "byte ordering" is exa…

Hi there. Thanks for the feedback. I updated that section to hopefully convey the intent more. The type of ordering we care about for this topic is really B-Tree index traversal when inserting new entries and finding existing entries (single and multiple values i.e. an IN clause, updates, deletes etc). There's a compelling example I re-created from Cybertec showing the pages needed and accessed for equivalent user-facing results, comparing storing PKs as big integers vs. UUID v4s, and how many more pages were needed for v4 UUIDs. I found that to be helpful to support my real world experience as a consultant on various "medium sized" Postgres databases (e.g. single to 10s of millions of records) where clients were experiencing excessive latency for queries, and the UUID v4 PK/FKs selection made for reasons earlier was one of the main culprits. The indexes wouldn’t fit into memory resulting in a lot of sequential scans. I’d confirm this by showing an alternative schema design and set of queries where everything was the same except integer PKs/FKs were used. Smaller indexes (fit in memory), reliable index scans, less latency, faster execution time.
Post reply on HN