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

161–170 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#161

Post by ellisv »

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?

About 10 years ago I remember seeing a number of posts saying "don't use int for ids!". Typically the reasons were things like "the id exposes the number of things in the database" and "if you have bad security then users can increment/decrement the id to get more data!". What I then observed was a bunch of developers rushing to use UUIDs for everything.

UUIDv7 looks really promising but I'm not likely to redo all of our tables to use it.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#162

Post by assbuttbuttass »

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 least for the Spanner DB, it's good to have a randomly-distributed primary key since it allows better sharding of the data and avoids "hot shards" when doing a lot of inserts. UUIDv4 is the typical solution, although a bit-reversed incrementing integer would work too

https://cloud.google.com/blog/products/databases/announcing-...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#163

Post by mexicocitinluez »

sgarland wrote: post #154
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.

Why would generating a PK ahead of time cause referential integrity violations? Super curious to find out.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#164

Post by anarazel »

torginus wrote: post #61
kreetx wrote: post #11

Earlier quoted context omitted.

Using an UUIDv4 as primary key is a trade-off: you use it when you need to generate unique keys in a distributed manner. Yes, these are not datetime ordered and yes, they take 128 bits of space. If you can't live with this, then sure, you need to consider alternatives. I wonder if "Avoid UUIDv4 Primary Keys" is a rule of thumb though.

I do not understand why 128 bits is considered too big - you clearly can't have less, as on 64 bits the collision probability on real world workloads is just too high, for all but the smallest databases. Auto-incrementing keys can work, but what happens when you run out of integers? Also, distributed dbs probably make this hard, and they can't generate a key on client. There must be something in Postgres that wants t…

The issue is more fundamental - if you have purely random keys, there's basically no spatial locality for the index data. Which means that for decent performance your entire index needs to be in memory, rather than just recent data. And it means that you have much bigger write amplification, since it's rare that the same index page is modified multiple times close-enough in time to avoid a second write.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#165

Post by koakuma-chan »

mort96 wrote: post #36
cdmckay wrote: post #19

Earlier quoted context omitted.

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

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.

Discord is doing fine.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#166

Post by esafak »

splix wrote: post #50
kijin wrote: post #12

Earlier quoted context omitted.

The space requirement and index fragmentation issue is nearly the same no matter what kind of relational database you use. Math is math. Just the other day I delivered significant performance gains to a client by converting ~150 million UUIDv4 PKs to good old BIGINT. They were using a fairly recent version of MariaDB.

I think the author means all dbs that fit a single server. Because in distributed dbs you often want to spread the load evenly over multiple servers.

To spell it out: it improves performance by avoiding hot spots.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#167

Post by rcxdude »

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?

Uuids also allow the generation of the ID to seperate from the insertion into the database, which can be useful in distributed systems.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#168

Post by littlestymaar »

reffaelwallen wrote: post #160
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)

What stops you from having another uuid field as publicly visible identifier (which is only a concern for a minority of your tables).

This way you avoid most of the issues highlighted in this article, without compromising your confidential data.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#169

Post by Traubenfuchs »

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…

> Norwegian PNs have your birth date

Same with Austrian social security numbers, which, in somes cases, don't contain the persons birth date and in some cases don't contain any existing date at all.

Yet many websites enforce a valid date and pull the persons birthdate from it...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#170

Post by miroljub »

majorchord wrote: post #156
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…

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

If you need sorting and filtering by date, just add a timestamp to your table instead of misusing an Id column for that.
Post reply on HN