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

131–140 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#131

Post by anamexis »

vintermann wrote: post #99
hyperpape wrote: post #94

Earlier quoted context omitted.

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?

For what it’s worth, it was also completely unclear to me how you were responding to the article itself. It does not discuss natural keys at all.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#132

Post by socketcluster »

mkleczek wrote: post #116
socketcluster wrote: post #81

Earlier quoted context omitted.

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

Preferably, you would design you APIs and services to be idempotent (ie. use PUT not POST etc.) Using idempotency identifier is the last resort in my book.

Still, UUID is probably the simplest and most reliable way to generate such idempotency identifiers.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#134

Post by sagarm »

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.

Most common database indexes are ordered, so if you are using UUIDv4 you will not only bloat the index you will also have poor locality. If you try to use composite keys to fix locality, you'll end up with an even more bloated index.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#135

Post by vintermann »

mcny wrote: post #112
vintermann wrote: post #99

Earlier quoted context omitted.

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?

That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.

You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing?

I bet people will extract that date and use it, and it's hard to imagine use which wouldn't be abuse. To take the example of a PN/SSN and the usual gender bit: do you really want anyone to be able to tell that you got a new ID at that time? What could you suspect if a person born in 1987 got a new PN/SSN around 2022?

Leaks like that, bypassing whatever access control you have in your database, is just one reason to use real random IDs. But it's even a pretty good one in itself.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#136

Post by socketcluster »

kunley wrote: post #123
socketcluster wrote: post #81

Earlier quoted context omitted.

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

Ehm.. so you're saying that INSERT ... RETURNING id is not atomic from the client's pov because something terrible could happen just when client is receiving the answer inside its SQL driver?

I'm actually more thinking about the client sitting on the front-end like a single page app. Network instability could cause the response to not reach the front-end after a successful insert. This wouldn't be extremely common but would definitely be a problem for you as the database admin if you have above a certain number of users. I've seen this issue on live production systems and the root cause of duplicate records can be baffling because of how infrequently it may happen. Tends to cause issues that are hard to debug.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#137

Post by sagarm »

ivan_gammel wrote: post #72

The is article is about a solution in search of a problem, a classic premature optimization issue. UUIDv4 is perfectly fine for many use cases, including small databases. Performance argument must be considered when there’s a problem with performance on the horizon. Other considerations may be and very often superior to that.

It's not really feasible to rekey your UUIDv4 keyed database to int64s after the fact, imo. Sure your new tables could be integer-keyed, but the bulk of your storage will be UUID (and UUIDv4, if that's what you started with) for a very long time

Re: Avoid UUID Version 4 Primary Keys in Postgres

#138

Post by vrighter »

mkleczek wrote: post #89
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…

You can't take into account the fact that things change when you don't know what those changes might be. You might end up needing to either rebuild a new database, have some painful migration, or support two codepaths to work with both types of keys.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#139

Post by ivan_gammel »

sagarm wrote: post #137
ivan_gammel wrote: post #72

The is article is about a solution in search of a problem, a classic premature optimization issue. UUIDv4 is perfectly fine for many use cases, including small databases. Performance argument must be considered when there’s a problem with performance on the horizon. Other considerations may be and very often superior to that.

It's not really feasible to rekey your UUIDv4 keyed database to int64s after the fact, imo. Sure your new tables could be integer-keyed, but the bulk of your storage will be UUID (and UUIDv4, if that's what you started with) for a very long time

Yes, sure. My point is, it may never be necessary.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#140

Post by hyperpape »

vintermann wrote: post #99
hyperpape wrote: post #94

Earlier quoted context omitted.

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?

Those are two unrelated points and the connection between them was unclear in the original post.
Post reply on HN