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

151–160 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#151

Post by sgarland »

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.

IME, when performance issues become obvious, the devs are in growth mode and have no desire / time to revisit PK choice.

Integer PKs were seen as fine for years - decades, even - before the rise of UUIDs.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#152

Post by sgarland »

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.

For InnoDB-based DBs that are not Aurora, and if the secondary index isn’t UNIQUE, it solves the problem, because secondary non-unique index changes are buffered and written in batches to amortize the random cost. If you’re hashing a guaranteed unique entity, I’d argue you can skip the unique constraint on this index.

For Aurora MySQL, it just makes it worse either way, since there’s no change buffer.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#153

Post by parpfish »

BartjeD wrote: post #75

Personally my approach has been to start with big-ints and add a GUID code field if it becomes necessary. And then provide imports where you can match objects based on their code, if you ever need to import/export between tenants, with complex object relationships. But that also adds complexity.

Two things I don’t like about big-int indexes:

- If you use uuids as foreign keys to another table, it’s obvious when you screw up a join condition by specifying the wrong indices. With int indices you can easily get plausible looking results because your join will still return a bunch of data

- if you’re debugging and need to search logs, having a simple uuid string is nice for searching

Re: Avoid UUID Version 4 Primary Keys in Postgres

#154

Post by sgarland »

mexicocitinluez wrote: post #109

You'll have to rip the ability to generate unique numbers from quite literally anywhere in my app and save them without conflict from my cold, dead hands. The ability to know ahead of time what a primary key will be (in lieu of persisting it first, then returning) opened up a whole new world of architecting work in my app. It made a lot of previous awkward things feel natural.

Sounds like a lot of referential integrity violations.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#155

Post by infogulch »

mcny wrote: post #142
vintermann wrote: post #135

Earlier quoted context omitted.

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…

> What could you suspect if a person born in 1987 got a new PN/SSN around 2022? Thank you for spelling it for me. For the readers, It leaks information that the person is likely not a natural born citizen. The assumption doesn't have to be a hundred percent accurate, There is a way to make that assumption And possibly hold it against you. And there are probably a million ways that a record created date could be held…

To quote the great Mr Sparrow:

> The only rules that really matter are these: what a man can do and what a man can't do.

When evaluating security matters, it's better to strip off the moral valence entirely ("rightly") and only consider what is possible given the data available.

Another potential concerning implication besides citizenship status: a person changed their id when put in a witness protection program.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#156

Post by majorchord »

vintermann wrote: post #135
mcny wrote: post #112

Earlier quoted context omitted.

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…

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

Pretty sure sorting and filtering them by date/time range in a database is the purpose.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#157

Post by mgoetzke »

That depends a lot on many factors and thus I dont like generic statements like that which tend to be more focused on a specific database pattern. That said everyone should indeed be aware of the potential tradeoffs.

And of course we could come up with many ways to generate our own ids and make them unique, but we have the following requirements.

- It needs to be a string (because we allow composing them to 'derive' keys) - A client must be able to create them (not just a server) without risk for collisions - The time order of keys must not be guessable easily (as the id is often leaked via references which could 'betray' not just the existence of a document, but also its relative creation time wrt others). - It should be easy to document how any client can safely generate document ids.

The lookup performance is not really such a big deal for us. Where it is we can do a projection into a more simple format where applicable.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#158

Post by aynyc »

I've seen this type of advice a few times now. Now I'm not a database expert by any stretch of imagination, but I have yet to see UUID as primary key in any of the systems I've touched.

Are there valid reasons to use UUID (assuming correctly) for primary key? I know systems have incorrectly expose primary key to the public, but assuming that's not the concern. Why use UUID over big-int?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#160

Post by reffaelwallen »

aynyc wrote: post #158

I've seen this type of advice a few times now. Now I'm not a database expert by any stretch of imagination, but I have yet to see UUID as primary key in any of the systems I've touched. Are there valid reasons to use UUID (assuming correctly) for primary key? I know systems have incorrectly expose primary key to the public, but assuming that's not the concern. Why use UUID over big-int?

At my company we only use UUIDs as PKs.

Main reason I use it is the German Tank problem: https://en.wikipedia.org/wiki/German_tank_problem

(tl;dr; prevent someone from counting how many records you have in that table)

Post reply on HN