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

21–30 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#21

Post by robertlagrant »

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?

Depends on the data. If you use a primary key in data about a person that shouldn't include their age (e.g. to remove age-based discrimination) then you are leaking an imperfect proxy to their age.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#22

Post by kunley »

socketcluster wrote: post #18

My advice is: Avoid Blanket Statements About Any Technology. I'm tired of midwit arguments like "Tech X is N% faster than tech Y at performing operation Z. Since your system (sometimes) performs operation Z, it implies that Tech X is the only logical choice in all situations!" It's an infuriatingly silly argument because operation Z may only represent about 10% of the total CPU usage of the whole system (averaged out…

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

Re: Avoid UUID Version 4 Primary Keys in Postgres

#23

Post by edding4500 »

dimitrisnl wrote: post #14

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

*edit: sorry, misread that. My answer is not valid to your question.

original answer: because if you dont come up with these ints randomly they are sequential which can cause many unwanted situations where people can guess valid IDs and deduce things from that data. See https://en.wikipedia.org/wiki/German_tank_problem

Re: Avoid UUID Version 4 Primary Keys in Postgres

#24

Post by javawizard »

edding4500 wrote: post #23
dimitrisnl wrote: post #14

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

*edit: sorry, misread that. My answer is not valid to your question. original answer: because if you dont come up with these ints randomly they are sequential which can cause many unwanted situations where people can guess valid IDs and deduce things from that data. See https://en.wikipedia.org/wiki/German_tank_problem

Hence the presumed implication behind the public_id field in GP's comment: anywhere identifiers are exposed, you use the public_id field, thereby preventing ID guessing while still retaining the benefits of ordered IDs where internal lookups are concerned.

Edit: just saw your edit, sounds like we're on the same page!

Re: Avoid UUID Version 4 Primary Keys in Postgres

#25

Post by bux93 »

Long article about why not to use UUIDv4 as Primary Keys, but.. Who is doing so? And why are they doing that? How would you solve their requirements? Just throwing out "you can use UUIDv7" doesn't help with, e.g., the size they take up.

Aren't people using (big)ints are primary keys, and using UUIDs as logical keys for import/export, solving portability across different machines?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#26

Post by javaunsafe2019 »

edding4500 wrote: post #23
dimitrisnl wrote: post #14

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

*edit: sorry, misread that. My answer is not valid to your question. original answer: because if you dont come up with these ints randomly they are sequential which can cause many unwanted situations where people can guess valid IDs and deduce things from that data. See https://en.wikipedia.org/wiki/German_tank_problem

So We make things hard in the backend because of leaky abstractions? Doesn't make sense imo.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#27

Post by sbuttgereit »

xandrius wrote: post #5

To summarise the article: in PG, prefer using UUIDv7 over UUIDv4 as they have slightly better performance. If you're using latest version of PG, there is a plugin for it. That's it.

You might have missed the big H2 section in the article:

"Recommendation: Stick with sequences, integers, and big integers"

After that then, yes, UUIDv7 over UUIDv4.

This article is a little older. PostgreSQL didn't have native support so, yeah, you needed an extension. Today, PostgreSQL 18 is released with UUIDv7 support... so the extension isn't necessary, though the extension does make the claim:

"[!NOTE] As of Postgres 18, there is a built in uuidv7() function, however it does not include all of the functionality below."

What those features are and if this extension adds more cruft in PostgreSQL 18 than value, I can't tell. But I expect that the vast majority of users just won't need it any more.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#28

Post by kreetx »

dotancohen wrote: post #17
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.

If one needs timestamp ordering, then UUIDv7 is a good alternative. But the author does not say timestamp ordering, he says ordering. I think he actually means and believes that there is some problem ordering UUIDv4.

Yup. There are alternatives depending on what the situation is: with non-distributed, you could just use a sufficiently sized int (which can be rather small when the table is for e.g humans). You could add a separate timestamp column if that is important.

But if you need UUID-based lookup, then you might as well have it as a primary key, as that will save you an extra index on the actual primary key. If you also need a date and the remaining bits in UUIDv7 suffice for randomness, then that is a good option too (though this does essentially amount to having a composite column made up of datetime and randomness).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#29

Post by grim_io »

dimitrisnl wrote: post #14

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

The article mentions microservices, which can increase the likelihood of collisions in sequential incremental keys.

One more reason to stay away from microservices, if possible.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#30

Post by Sayrus »

bux93 wrote: post #25

Long article about why not to use UUIDv4 as Primary Keys, but.. Who is doing so? And why are they doing that? How would you solve their requirements? Just throwing out "you can use UUIDv7" doesn't help with, e.g., the size they take up. Aren't people using (big)ints are primary keys, and using UUIDs as logical keys for import/export, solving portability across different machines?

UUIDs are usually the go-to solution to enumeration problems. The space is large enough that an attacker cannot guess how many X you have (invoices, users, accounts, organizations, ...). When people replace the ints by UUIDv4, they keep them as primary keys.
Post reply on HN