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

291–300 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#291

Post by naasking »

scottlamb wrote: post #219
naasking wrote: post #196

Earlier quoted context omitted.

I don't think the objection is that it exposes semantic meaning, but that any meaningful information is contained within the key at all, eg. even a UUID that includes timestamp information about when it was generated is "bad" in a sense, as it leaks information. Unique identifiers should be opaque and inherently meaningless.

Your understanding is inconsistent with the examples in vintermann's comment. Using a sequence number as an internal-only surrogate key (deliberately opaqued when sent outside the bounds of the database) is not the same as sticking gender identity, birth date, or any natural properties of a book into a broadly shared identifier.

No it's not, they very explicitly clarify in follow-up comments that unique identifiers should not be embedded any kind of meaningful content. See:

https://news.ycombinator.com/item?id=46276995

https://news.ycombinator.com/item?id=46273798

Re: Avoid UUID Version 4 Primary Keys in Postgres

#292

Post by jessep »

iainctduncan wrote: post #243

Counterargument... I do technical diligence so I talk to a lot of companies at points of inflection, and I also talk to lots who are stuck. The ability to rapidly shard everything can be extremely valuable. The difference between "we can shard on a dime" and "sharding will take a bunch of careful work" can be expensive If the company has poor margins, this can be the difference between "can scale easily" and "we're n…

Sort of related, but we had to shard as usage grew and didn’t have uuids and it was annoying. Wasn’t the most annoying bit though. Whole thing is pretty complex regardless of uuid, if you have a highly interconnected data model that needs to stay online while migrating.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#293

Post by scottlamb »

naasking wrote: post #291
scottlamb wrote: post #219

Earlier quoted context omitted.

Your understanding is inconsistent with the examples in vintermann's comment. Using a sequence number as an internal-only surrogate key (deliberately opaqued when sent outside the bounds of the database) is not the same as sticking gender identity, birth date, or any natural properties of a book into a broadly shared identifier.

No it's not, they very explicitly clarify in follow-up comments that unique identifiers should not be embedded any kind of meaningful content. See: https://news.ycombinator.com/item?id=46276995 https://news.ycombinator.com/item?id=46273798

Okay, but they ignore the stuff I was talking about, consistent with my description of this as a straw man attack.

> A running number also carries data. Before you know it, someone's relying on the ordering or counting on there not being gaps - or counting the gaps to figure out something they shouldn't.

The opaquing prevents that.

They also describe this as a "premature optimization". That's half-right: it's an optimization. Having the data to support an optimization, and focusing on optimizing things that are hard to migrate later, is not premature.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#294

Post by p1necone »

Being able to create something and know the id of it before waiting for an http round trip simplifies enough code that I think UUIDs are worth it for me. I hadn't really considered the potential perf optimization from orderable ids before though - I will consider UUID v7 in future.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#295

Post by nonethewiser »

ralferoo wrote: post #221
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 (in DDMMYY format) as the first six digits. Surely that doesn't change, right? Well, wrong, since although the date doesn't change, your knowledge of it might. Immigrants who didn't know their exact date of birth got assigned 1. Jan by default... And then people with actual birthdays on 1 Jan got told, "sorry, you can't have that as birth date, we've run out of numbers in that ser…

>To me, what your example really shows is the problem with incorrect default values, not a problem with encoding data into a key per se. If they'd chosen a non-date for unknown values, maybe 00 or 99 for day or month components, then the issue you described would disappear.

You still have that problem from organic birthdays and also the problem of needing to change ids to correct birth dates.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#296

Post by iainctduncan »

jessep wrote: post #292
iainctduncan wrote: post #243

Counterargument... I do technical diligence so I talk to a lot of companies at points of inflection, and I also talk to lots who are stuck. The ability to rapidly shard everything can be extremely valuable. The difference between "we can shard on a dime" and "sharding will take a bunch of careful work" can be expensive If the company has poor margins, this can be the difference between "can scale easily" and "we're n…

Sort of related, but we had to shard as usage grew and didn’t have uuids and it was annoying. Wasn’t the most annoying bit though. Whole thing is pretty complex regardless of uuid, if you have a highly interconnected data model that needs to stay online while migrating.

Right, but if you start off with uuids and the expectation that you might use them to shard, you'll wind up factoring that into the data model. Retrofitting, as you rightly say, can be much harder.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#297

Post by chuckadams »

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 can't be changed (if you don't have a PK, you have some options, but let's assume you have one). MSSQL behaves similarly, but you can override it. If your PK is random, your clustering will be too. In Postgres, you'll just get fragmented indexes, which isn't quite as bad, but still slows down vacuum. Whether that actually becomes a problem is also going to depend on access patterns.

One shouldn't immediately freak out over having a random PK, but should definitely at least be aware of the potential degradation they might cause.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#298

Post by p1necone »

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…

I doubt many real world use cases would run out of incrementing 64 bit ids - collisions if they were random sure, but i64 max is 9,223,372,036,854,775,807 - if each row took only 1 bit of space, that would be slightly more than an exabyte of data.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#299

Post by mkleczek »

brettgriffin wrote: post #260
mkleczek wrote: post #89

Earlier quoted context omitted.

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…

> 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. The surrogate key's purpose isn't to directly store the natural key's information, rather, it's to provide an index to it. > The solution is not to come up with yet another artificial identifier but to come u…

Any piece of information that can be used to retrieve something using this index has to be available "outside" your database - ie. to issue a query "give me piece of information identified by X" you have to know X first. If X is only available in your index then you must have another index to retrieve X based on some externally available piece of information Y. And then X becomes useless as an identifier - it just adds a level of indirection that does not solve any information retrieval problem.

That's my whole point: either X becomes a "real world artifact" or it is useless as identifier.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#300

Post by [deleted] »

demurgos wrote: post #263
mkleczek wrote: post #251

Earlier quoted context omitted.

> "Internal" is a blurry boundary, though Not for me :) "Internal" means "not exposed outside the database" (that includes applications and any other external systems)

Internal means "not exposed outside some boundary". For most people, this boundary encompasses something larger than a single database, and this boundary can change.

[deleted]
Post reply on HN