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

81–90 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#81

Post by socketcluster »

kunley wrote: post #57
christophilus wrote: post #47

Earlier quoted context omitted.

Not really, no. They’re very convenient for certain problems and work really well in general. I’ve never had a performance issue where the problem boiled down to my use of UUID.

What are these certain problems, if I may ask?

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 happen purely on the basis that they did not receive a success response. It could very well be that the insertion succeeded but the connection failed shortly after so response was not received. If the IDs are auto-incremented on the server and the client posts the same object again without any ID on it, the server will create a duplicate record in the database table (same object with a different ID).

On the other hand, if the client generates a UUID for the object it wants to create on the front-end, then it can safely resend that exact object any number of times and there is no risk of double-insertion; the object will be rejected the second time and you can show the user a meaningful error "Record was already created" instead of creating two of the same resource; leading to potential bugs and confusion.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#82

Post by pdimitar »

formerly_proven wrote: post #60
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.

If all you want is to obfuscate the fact that your social media site only has 200 users and 80 posts, simply use a permutation over the autoincrement primary key. E.g. IDEA or CAST-128, then encode in base64. If someone steps on your toes because somewhere in your codebase you're using a forbidden legacy cipher, just use AES-128. (This is sort of the degenerate/tautological base case of format-preserving encryption)…

> What do you think Youtube video IDs are?

I actually haven no idea. What are they?

(Also what is the format of their `si=...` thing?)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#83

Post by kaladin_1 »

I really hoped the author would discuss alternatives for distributed databases that writes in parallel. Sequential key would be atrocious in such circumstance this could kill the whole gain of distributed database as hotspots would inevitably appear.

I would like to hear from others using, for example, Google Spanner, do you have issues with UUID. I don't for now, most optimizations happen at the Controller level, data transformation can be slow due to validations. Try to keep service logic as straightforward as possible.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#84

Post by mort96 »

nish__ wrote: post #80
mort96 wrote: post #36

Earlier quoted context omitted.

Well you're leaking user data. I'm sure you can imagine situations where "the defendant created an account on this site on this date" could come up. And the user could have created that account not knowing that the creation date is public, because it's not listed anywhere in the publicly viewable part of the profile other than the UUID in the URL.

Pretty much every social media app has a "Member since X" visible on public profiles. I don't think it's an issue.

Who said I was talking about social media?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#85

Post by tacone »

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…

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 your data and store it in a primary index as a UUID. Your index will fragment and thus you will vacuum, but life will still be good overall.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#86

Post by nish__ »

mort96 wrote: post #84
nish__ wrote: post #80

Earlier quoted context omitted.

Pretty much every social media app has a "Member since X" visible on public profiles. I don't think it's an issue.

Who said I was talking about social media?

Well where else do users have public profiles?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#87

Post by Denvercoder9 »

grugdev42 wrote: post #77

A much simpler solution is to keep your tables as they are (with an integer primary key), but add a non sequential public identifier too. id => 123, public_id => 202cb962ac59075b964b07152d234b70 There are many ways to generate the public_id. A simple MD5 with a salt works quite well for extremely low effort. Add a unique constraint on that column (which also indexes it), and you'll be safe and performant for hundreds…

This misses the point. The reason not to use UUIDv4 is that having an index on random values is slow(er), because sequential inserts into the underlying B-tree are faster than random inserts. You're hitting the same problem with your `public_id` column, that it's not the primary key doesn't change that.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#88

Post by mckirk »

tacone wrote: post #85
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…

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

Re: Avoid UUID Version 4 Primary Keys in Postgres

#89

Post by mkleczek »

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…

This is actually a very deep and interesting topic. 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. So, what happens next is that the real world tries to adjust and the "data-less" identifier becomes a real world artifact. The situation becomes the same but worse (eg. you don't exist if you don't remember your social security id). In extreme cases people are tattooed with their numbers.

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.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#90

Post by dsego »

dimitrisnl wrote: post #14

Noob question, but why no use ints for PK, and UUIDs for a public_id field?

I also think we can use a combination of a PID - persistent ID (I always thought it was public) and an auto-increment integer ID. Having a unique key helps when migrating data between systems or referencing a piece of data in a different system. Also, using serial IDs in URLs and APIs can reveal sensitive information, e.g. how many items there are in the database.
Post reply on HN