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

381–390 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#381

Post by cruffle_duffle »

chuckadams wrote: post #297
cruffle_duffle wrote: post #177

I never understood the arguments against using using globally unique ids. For example how it somehow messes up indexes. I’m not a CS major but those are typically b-trees are they not? If you have a primary key whose generation is truly random such that each number is equally likely, then that b-tree is always going to be balanced. Yes there are different flavors of generating them with their own pros and cons, but a…

> If you have a primary key whose generation is truly random such that each number is equally likely, then that b-tree is always going to be balanced. Balanced and uniformly scattered. A random index means fetching a random page for every item. Fine if your access patterns are truly random, but that's rarely the case. > Why are you clustering on a random opaque key? InnoDB clusters by the PK if there is one, and that…

I feel, honestly, like while you are indeed correct for most cases it’s absolutely fine to use some flavor of uuid. I feel like the benefits outweighs the cost in most cases.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#382

Post by cowboylowrez »

sedatk wrote: post #278
gwbas1c wrote: post #149

I work on an application where we encrypt the integer primary key and then use the bytes to generate something that looks like a UUID. In our case, we don't want database IDs in an API and in URLs. When IDs are sequential, it enables things like dictionary attacks and provides estimates about how many customers we have. Encrypting a database ID makes it very obvious when someone is trying to scan, because the UUID wo…

That sounds quite troublesome if the encryption key is lost, compromised, or rotated for any other reason.

Key management seems to be as important as backups but I understand that something so small (an encryption key) could seem unimportant because database backups are so big lol but they really do share important attributes (do not lose your keys, do not lose your data, do not expose your keys, do not expose your data, etc etc)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#383

Post by notpushkin »

hyperman1 wrote: post #318
OptionOfT wrote: post #274

Earlier quoted context omitted.

Belgium's national register number is similar: YY.MM.DD-AAA.BB In either the AAA or BB component there is something about the gender. But it does mean that there is a limit of people born per day of a certain gender. But for a given year, using a moniker will only delay the inevitable. Sure, there are more numbers, but still limited as there are SOME parts that need to reflect reality. Year, gender (if that's still t…

BB is a mod-97 checksum. The first A of AAA encodes your gender in an even/odd fashion, I forgot if its the first or last A doing that. MM or DD can be 00 if unknown. Also MM has +20 or +40 in some cases. If you know someones birth date and gender, the INSZ is almost certainly 1 in 500 numbers, with a heavy skew to the lower AAA. Luckily, you can't do much damage with someones number,unlike an USA SSN (but I'd still…

> I'd still treat it confidential

Estonian isikukood is GYYMMDDNNNC, and is relatively public. You can find mine pretty easily if you know where to look (no spoilers!). It’s relatively harmless.

Kazakh IIN is YYMMDDNNNNNN (where N might have some structure) and is similarly relatively public: e.g. if you’re a sole proprietor, chances are you have to hang your license on the wall, which will have it.

It’s a bit more serious: I’ve got my mail at the post office by just showing a barcode of my IIN to the worker. They usually scan it from an ID, which I don’t have, but I’ve figured out the format and created a .pkpass of my own. Zero questions – here’s your package, no we don’t need your passport either, have a nice day!

(Tangential, but Kazakhs also happen to have the most peculiar post office layout: it looks exactly like a supermarket, where you go in, find your packages (sorted by the tracking number, IIRC), and go to checkout. I’ve never seen it anywhere else.)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#384

Post by hexbin010 »

pezezin wrote: post #341
JetSetIlly wrote: post #102

Earlier quoted context omitted.

In a business I once worked for, one of the users of the online ordering system represented over 50% of the business' income, something you wouldn't necessarily want them to know. However, because the online ordering system assigned order numbers sequentially, it would have been trivial for that company to determine how important their business was. For example, over the course of a month, they could order something…

This is also something that depends heavily on regulations. In my home country, invoice numbers have to be sequential by law, although you can restart the numbering every year.

A global sequence, or sequence-per-account? I suspect the latter?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#385

Post by nonethewiser »

stickfigure wrote: post #180

This is incredibly database-specific. In Postgres random PKs are bad. But in distributed databases like Cockroach, Google Cloud Datastore, and Spanner it is the opposite - monotonic PKs are bad. You want to distribute load across the keyspace so you avoid hot shards.

>In Postgres random PKs are bad

Why?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#386

Post by deepsun »

I still don't understand why people don't remove the hyphens from UUIDs. Hyphens makes it harder to copy-paste IDs. The only reason to keep them is to make it explicit "hey this is an UUID", otherwise it's a completely internal affair.

Even worse, some tools generate random strings and then ADD hyphens in them to look like UUID (even thought it's not, as the UUID version byte is filled randomly as well), cannot wrap my head why, e.g:

https://github.com/VictoriaMetrics/VictoriaLogs/blob/v1.41.0...

Re: Avoid UUID Version 4 Primary Keys in Postgres

#387

Post by lovich »

brigandish wrote: post #377
lovich wrote: post #342

Earlier quoted context omitted.

The only real states of matter are solids, liquids, and gases. Everything else is just woke lunacy. I am confident in this fact because I learned it in elementary school decades ago and it is impossible for humanity to discover new information that updates our world model. Every English speaker knows that “plasmas” and “Bose-Eisenstein condensates” are made up.

We all await your Nobel for finding a third type of gamete.

The person I was responding to was talking about gender, but if you want to talk about biology then

https://en.wikipedia.org/wiki/Intersex#Prevalence

https://en.wikipedia.org/wiki/Klinefelter_syndrome

https://en.wikipedia.org/wiki/XXYY_syndrome

https://en.wikipedia.org/wiki/XXXY_syndrome

https://en.wikipedia.org/wiki/XXXYY_syndrome

https://en.wikipedia.org/wiki/XXXXY_syndrome

https://en.wikipedia.org/wiki/Trisomy_X

I assume you will be one of the advocates for my nobel prize

edit: I'm sorry you specifically mentioned gametes, we can talk about diploids and haploids if you wish and how our bodies are such complicated machines that any sort of error that can occur in our growth is guaranteed to at scale

Re: Avoid UUID Version 4 Primary Keys in Postgres

#388

Post by lovich »

Dylan16807 wrote: post #378
lovich wrote: post #369

Earlier quoted context omitted.

> It won't happen though. 0.00000000% chance it happens even once in a trillion attempts. It has the same odds as any other specific configuration of randomly assigned dots. The overly active human pattern matching behavior is the only reason it would be treated as special.

Entropy says it's special. If you have a million dots and 10,000 coordinates, you have 10,000 ways for all the dots to land in the same coordinate, and a zillion kavillion stupillion ways to have somewhere near 100 dots in each coordinate.

No, if its randomly distributed then every specific configuration has the same exact chance of happening.

I am laughing at all the people coming out of the woodwork to reply to my original post in this thread misunderstanding randomness and chance.

If you flip a coin a million times and it lands on head every single time, the millionth and 1 time still has a 50/50 chance of landing on heads

Re: Avoid UUID Version 4 Primary Keys in Postgres

#389

Post by FartinMowler »

lovich wrote: post #369
Dylan16807 wrote: post #351

Earlier quoted context omitted.

> A million dots scattered randomly over a graph can all land on the exact same coordinate if it’s truly random. It won't happen though. 0.00000000% chance it happens even once in a trillion attempts. > What most people intuit as random is some sort of noise function that is generally dispersed and doesn’t trigger the pattern matching part of their brain Yes, people intuit the texture of random wrong in a situation w…

> It won't happen though. 0.00000000% chance it happens even once in a trillion attempts. It has the same odds as any other specific configuration of randomly assigned dots. The overly active human pattern matching behavior is the only reason it would be treated as special.

Lol, reminds me of a story: at his workplace my brother was invited to join a lottery ticket pool where each got to pick the numbers for a ticket. The numbers he picked were 1-2-3-4-5-6. Although the others, mostly fellow engineers, reluctantly agreed his numbers were as likely as the others, after a couple of weeks they neglected to invite him again.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#390

Post by thayne »

nbadg wrote: post #39
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…

I'm also not a huge fan of leaking server-side information; I suspect UUIDv7 could still be used in statistical analysis of the keyspace (in a similar fashion to the german tank problem for integer IDs). Also, leaking data about user activity times (from your other comment) is a *really* good point that I hadn't considered. I've read people suggest using a UUIDv7 as the primary key and a UUIDv4 as a user-visible one…

I think a bigger benefit from doing that would be that inserts would be cheaper. Instead of an expensive insert into the middle of an index for every table that needs an index on that key, you can do a cheaper insert at the end of the index for all of them except for the one that uses uuid4.

But if you are doing that, why not just use an incrementing integer instead of a uuidv7?

Post reply on HN