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

91–100 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#91

Post by saaspirant »

cdmckay wrote: post #19
mort96 wrote: post #13

Earlier quoted context omitted.

I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…

Out of curiosity, why is it an issue if you leak creation time?

There was a HN comment about competitors tracking how many new signups are happening and increasing the discounts/sales push based on that. Something like this.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#92

Post by 0x3f »

nish__ wrote: post #86
mort96 wrote: post #84

Earlier quoted context omitted.

Who said I was talking about social media?

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.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#93

Post by dev_l1x_be »

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…

Why would you need to order by UUID? I am missing something here. Most of the time we use UUID keys for being able to create a new key without coordination and most of the time we do not want to order by primary key.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#94

Post by hyperpape »

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…

Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#95

Post by danparsonson »

christophilus wrote: post #47
kunley wrote: post #22

Earlier quoted context omitted.

Wasn't choosing uuids as ids falling for the deceptive argument in the first place?

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.

You never having seen the problem doesn't mean it never happens; I have dealt with a serious performance problem in the past that was due to excessive page fragmentation due to a GUID PK.

To your original point, these are heuristics; there isn't always time to dig into every little architectural decision, so having a set of rules of thumb on hand helps to preempt problems at minimal cognitive cost. "Avoid using a GUID as a primary key if you can" is one of mine.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#96

Post by vintermann »

oncallthrow wrote: post #79
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…

It sounds to me like you’re just arguing for premature optimization of another kind (specifically, prematurely changing your entire architecture for edge cases that probably won’t ever happen to you).

If you have an architecture already, obviously it's hard to change and you may want to postpone it until those edge cases which probably won't ever happen to you, happen. But for new architectures, value your own grey hairs over small performance improvements.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#97

Post by hnfong »

Denvercoder9 wrote: post #87
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.

Ints as pk would be quicker for joins etc though.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#98

Post by benterix »

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

I always thought they are used and stored as they are because the kind of transformation you mention seems terribly expensive given the YT's scale, and I don't see a clear benefit of adding any kind of obfuscation here.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#99

Post by vintermann »

hyperpape wrote: post #94
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…

Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.

I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to.

I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID.

Isn't that clear enough?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#100

Post by hans_castorp »

dev_l1x_be wrote: post #93
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…

Why would you need to order by UUID? I am missing something here. Most of the time we use UUID keys for being able to create a new key without coordination and most of the time we do not want to order by primary key.

I have seen a lot of people sort by (generated) integer values to return the rows "in creation order" assuming that sorting by an integer is somehow magically faster than sorting by a proper timestamp value (which give a more robust "creation order" sorting than a generated integer value).
Post reply on HN