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

241–250 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#241

Post by lukeschlather »

alerighi wrote: post #218
lukeschlather wrote: post #206

Earlier quoted context omitted.

> Did you read the article? He doesn’t recommend natural keys, he recommends integer-based surrogates. I am not a cryptographer, but I would want his recommendation reviewed by a cryptographer. And then I would have to implement it. UUIDs have been extensively reviewed by cryptographers, I have a variety of excellent implementations I can use, I know they solve the problem well. I know they can cause performance issu…

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 than UUIDs, but UUIDs are simple and I don't have to think about it, I just get the security benefits. I don't have to worry about not exposing IDs to the clients, I can do it freely.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#242

Post by nutjob2 »

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.

> You want to distribute load across the keyspace so you avoid hot shards.

This is just another case of keys containing information and is not smart.

The obvious solution is to have a field that drives distribution, allowing rebalancing or whatever.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#243

Post by iainctduncan »

Counterargument... I do technical diligence so I talk to a lot of companies at points of inflection, and I also talk to lots who are stuck.

The ability to rapidly shard everything can be extremely valuable. The difference between "we can shard on a dime" and "sharding will take a bunch of careful work" can be expensive If the company has poor margins, this can be the difference between "can scale easily" and "we're not getting this investment".

I would argue that if your folks have the technical chops to be able to shard while avoiding surrogate guaranteed unique keys, great. But if they don't.... a UUID on every table can be a massive get-out-of-jail free card and for many companies this is much, much important than some minor space and time optimizations on the DB.

Worth thinking about.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#244

Post by SoftTalker »

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.

This is mentioned, and in many applications you can safely say you will never need to shard.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#245

Post by patmorgan23 »

scottlamb wrote: post #187
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…

> Permanent identifiers should not carry data. I think you're attacking a straw man. The article doesn't say "instead of UUIDv4 primary keys, use keys such as birthdays with exposed semantic meaning". On the contrary, they have a section about how to use sequence numbers internally but obfuscated keys externally. (Although I agree with dfox's and formerly_proven's comments [1, 2] that XOR method they proposed for thi…

Insert order or time is information. And if you depend on that information you are going to be really disappointed when back dated records have to be inserted.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#246

Post by nish__ »

0x3f wrote: post #92
nish__ wrote: post #86

Earlier quoted context omitted.

Well where else do users have public profiles?

The whole point though is that the ID itself leaks info, even if the profile is not public. There are many cases where you reference an object as a foreign key, even if you can't see the entire record of that foreign key.

I can't think of any.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#247

Post by conradludgate »

nutjob2 wrote: post #242
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.

> You want to distribute load across the keyspace so you avoid hot shards. This is just another case of keys containing information and is not smart. The obvious solution is to have a field that drives distribution, allowing rebalancing or whatever.

100%. You can use rendezvous hashing to determine the shard(s). The hash of a sequence should be randomly distributed as changing the LSB should propagate to 50% change in the output bits.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#248

Post by mkleczek »

Ukv wrote: post #240
mkleczek wrote: post #216

Earlier quoted context omitted.

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

If you expose the identifier outside the database, it is no longer "internal".

Re: Avoid UUID Version 4 Primary Keys in Postgres

#249

Post by everforward »

mkleczek wrote: post #228
everforward wrote: post #178

Earlier quoted context omitted.

> The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change. 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. E.g. phone numbers are se…

> 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 phone number, this is my e-mail, I want my GDPR records deleted", you still need to be able to find all data that is related to me.

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? All 3 of those are pretty routine to change. I've changed my email and phone number a few times, and if I got married my name might change as well.

> Once you start thinking about your database as structured storage of facts that you can use to infer conclusions, there is much less need for surrogate keys.

I think that spirals into way more complexity than you're thinking. You get those timestamped records about "we got info about person named Y with phone number Z", and then person Y changes their phone number. Now you're going to start getting records from person named Y with phone number A, but it's the same account. You can record "person named Y changed their phone number from Z to A", and now your queries have to be temporal (i.e. know when that person had what phone number). You could back-update all the records to change Z to A, but that breaks some things (e.g. SMS logs will show that you sent a text to a number that you didn't send it to).

Worse yet, neither names nor phone numbers uniquely identify a person, so it's entirely possible to have records saying "person named Y and phone number Z" that refer to different people if a phone number transfers from a John Doe to a different person named John Doe.

I don't doubt you could do it, but I can't imagine it being worth it. I can't imagine a way to do it that doesn't either a) break records by backdating information that wasn't true back then, or b) require repeated/recursive querying that will hammer the DB (e.g. if someone has had 5 phone numbers, how do you get all the numbers they've had without pulling the latest one to find the last change, and then the one before that, and etc). Those queries are incredibly simple with surrogate keys: "SELECT * FROM phone_number_changes WHERE user_id = blah".

Re: Avoid UUID Version 4 Primary Keys in Postgres

#250

Post by scottlamb »

patmorgan23 wrote: post #245
scottlamb wrote: post #187

Earlier quoted context omitted.

> Permanent identifiers should not carry data. I think you're attacking a straw man. The article doesn't say "instead of UUIDv4 primary keys, use keys such as birthdays with exposed semantic meaning". On the contrary, they have a section about how to use sequence numbers internally but obfuscated keys externally. (Although I agree with dfox's and formerly_proven's comments [1, 2] that XOR method they proposed for thi…

Insert order or time is information. And if you depend on that information you are going to be really disappointed when back dated records have to be inserted.

Right, to ensure your clients don't depend on that information, make the key opaque outside the database through methods such as the ones dfox and formerly_proven suggested, as I said.
Post reply on HN