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

221–230 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#221

Post by ralferoo »

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 series!"

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.

But in case, the intention for encoding a timestamp into a UUID isn't for any implied meaning. It's both to guarantee uniqueness with a side effect that IDs are more or less monotonically increasing. Whether this is actually desirable depends on your application, but generally if the application is as a indexed key for insertion into a database, it's usually more useful for performance than a fully random ID as it avoids rewriting lots of leaf-nodes of B-trees. If you insert a load of these such keys, it forms a cluster on one side of the tree that can the rebalance with only the top levels needing to be rewritten.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#222

Post by WalterSlovotsky »

cozyman wrote: post #194
tacone wrote: post #85

Earlier quoted context omitted.

Fantastic real life example. Italian PNs carry also the gender, which something you can change surgically, and you'll eventually run into the issue when operating at scale. I don't agree with the absolute statement, though. Permanent identifiers should not generally carry data. There are situations where you want to have a way to reconciliate, you have space or speed constraints, so you may accept the trade off, md5…

how does one change their gender surgically?

The preferred method would be gender affirming surgery.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#223

Post by bri3d »

nine_k wrote: post #198
bri3d wrote: post #129

Earlier quoted context omitted.

> You're not going to try and extract a timestamp from a uuid. What? The first 48 bits of an UUID7 are a UNIX timestamp. Whether or not this is a meaningful problem or a benefit to any particular use of UUIDs requires thinking about it; in some cases it’s not to be taken lightly and in others it doesn’t matter at all. I see what you’re getting at, that ignoring the timestamp aspect makes them “just better UUIDs,” but…

Nobody forces you to use a real Unix timestamp. BTW the original Unix timestamp is 32 bits (expiring in 2038), and now everyone is switching to 64-bit time_t. What 48 bits? All you need is a guaranteed non-decreasing 48-bit number. A clock is one way to generate it, but I don't see why a UUIDv7 would become invalid if your clock is biased, runs too fast, too slow, or whatever. I would not count on the first 48 bits b…

> Nobody forces you to use a real Unix timestamp.

Besides the UUIDv7 specification, that is? Otherwise you have some arbitrary kind of UUID.

> I would not count on the first 48 bits being a "real" timestamp.

I agree; this is the existential hazard under discussion which comes from encoding something that might or might not be data into an opaque identifier.

I personally don't agree as dogmatically with the grandparent post that extraneous data should _not_ be incorporated into primary key identifiers, but I also disagree that "just use UUIDv7 and treat UUIDs as opaque" is a completely plausible solution either.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#224

Post by dap »

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.

It is, although you can have sharded PostgreSQL, in which case I agree with your assessment that you want random PKs to distribute them.

It's workload-specific, too. If you want to list ranges of them by PK, then of course random isn't going to work. But then you've got competing tensions: listing a range wants the things you list to be on the same shard, but focusing a workload on one shard undermines horizontal scale. So you've got to decide what you care about (or do something more elaborate).

Re: Avoid UUID Version 4 Primary Keys in Postgres

#225

Post by boruto »

jakeydus wrote: post #208
reffaelwallen wrote: post #160

Earlier quoted context omitted.

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)

I'm new to the security side of things; I can understand that leaking any information about the backend is no bueno, but why specifically is table size an issue?

In my old company new joiners are assigned an monotonic number as id in tech. GitHub profile url reflected that.

Someone may or may not have used the pattern to get to know the attrition rate through running a simple script every month))

Re: Avoid UUID Version 4 Primary Keys in Postgres

#226

Post by pdimitar »

intalentive wrote: post #214
pdimitar wrote: post #82

Earlier quoted context omitted.

> What do you think Youtube video IDs are? I actually haven no idea. What are they? (Also what is the format of their `si=...` thing?)

Can’t recall where I heard this, but I’m pretty sure the si=… is tracking information that associates the link with the user who shared it.

Oh absolutely, I am just wondering _what_ does it contain.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#227

Post by duckerude »

barrkel wrote: post #113
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…

Uuid v7 just has a bias in its generation; it isn't carrying information. You're not going to try and extract a timestamp from a uuid. Random vs time biased uuids are not a decision to shave off ms that you will regret. Most likely they will be a decision that shaves off seconds (yes, really - especially when you consider locality effects) and you'll regret nothing.

I've worked on a system where ULIDs (not UUIDv7, but similar) were used with a cursor to fetch data in chronological order and then—surprise!—one day records had to be backdated, meaning that either the IDs for those records had to be counterfeited (potentially violating invariants elsewhere) or the fetching had to be made smarter.

You can choose to never make use of that property. But it's tempting.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#228

Post by mkleczek »

everforward wrote: post #178
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…

> The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change. I think artificial and data-less identifiers are the better means of identification that takes into account that things change. They don't have to be the identifier you present to the world, but having them is very useful. E.g. phone numbers are se…

> I think artificial and data-less identifiers are the better means of identification that takes into account that things change. They don't have to be the identifier you present to the world, but having them is very useful.

If the only reason you need a surrogate key is to introduce indirection in your internal database design then sequence numbers are enough. There is no need to use UUIDs.

The whole discussion is about externally visible identifiers (ie. identifiers visible to external software, potentially used as a persistent long-term reference to your data).

> E.g. phone numbers are semi-common identifiers now, but phone numbers change owners for reasons outside of your control. If you use them as an internal identifier, changing them between accounts gets very messy because now you don't have an identifier for the person who used to have that phone number.

Introducing surrogate keys (regardless of whether UUIDs or anything else) does not solve any problem in reality. When I come to you and say "My name is X, this is my phone number, this is my e-mail, I want my GDPR records deleted", you still need to be able to find all data that is related to me. Surrogate keys don't help here at all. You either have to be able to solve this issue in the database or you need to have an oracle (ie. a person) that must decide ad-hoc what piece of data is identified by the information I provided.

The key issue here is that you try to model identifiable "entities" in your data model, while it is much better to model "captured information".

So in your example there is no "person" identified by "phone number" but rather "at timestamp X we captured information about a person at the time named Y and using phone number Z". Once you start thinking about your database as structured storage of facts that you can use to infer conclusions, there is much less need for surrogate keys.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#229

Post by tyre »

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…

And then have to enter/handle a non-date through all systems? How do you know if this non-dated person is over the age of minority? Eligible for a pension?

Maybe the answer is to evenly spread the defaults over 365 days.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#230

Post by benterix »

vintermann wrote: post #213
spoiler wrote: post #209

Earlier quoted context omitted.

More broadly, this is the ages old surrogate vs natural key discussion, but yes the comment completely misses the point of the article. I can only assume they didn't read it in full!

The article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it. 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 article explicitly argues against the use of GUIDs as primary keys, and I'm arguing for it.

Let's clarify things.

The author argues against UUIDv4 as primary keys when compared to integers or bigints in large databases for performance reasons.

The examples you give refer to the common mistake of using a non-unique attribute that can be changed for a given entity as a primary key.

Post reply on HN