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

431–440 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#431

Post by mkleczek »

everforward wrote: post #422
mkleczek wrote: post #279

Earlier quoted context omitted.

> The UUID would be an example of an external key (for e.g. preventing crawling keys being easy). This article mentions a few reasons why you may later decide there are better external keys. So we are talking about "external" keys (ie. visible outside the database). We are back to square one: externally visible surrogate keys are problematic because they are detached from real world information they are supposed to i…

> It does not matter if they are random or not. Again, sometimes it does, the article lists a few of them. Making it harder to scrape, unifying across databases that share a keyspace, etc. > And how does surrogate key help? I don't know the surrogate key that identifies my records in your database. Even if you use them internally it is an implementation detail. That surrogate key is linked to literally every other re…

I am not sure you are arguing against my claims or not :)

I am not arguing against surrogate keys in general. They are obviously very useful _internally_ to introduce a level of indirection. But if they are used _internally_ then it doesn't really matter if they are UUIDs or sequence numbers or whatever - it is just an implementation detail.

What I claim is that surrogate keys are problematic as _externally visible_ identifiers.

> Okay, then lets do an exercise here. A user gives you a transaction ID, and you have to tell them the date they signed up and the date you first billed them. I think yours is going to be way more complicated.

> Mine is just something like:

> SELECT user_id FROM transactions WHERE transaction_id=X; SELECT transaction_date FROM transactions WHERE user_id=Y ORDER BY transaction_date ASC LIMIT 1; SELECT signup_date FROM users WHERE user_id=Y;

I think you are missing the actual problem I am talking about: where does the user take the transaction ID from? Do you expect the users to remember all transaction IDs your system ever generated for them? How would they know which transaction ID to ask about? Are they expected to keep some metadata that would allow them to identify transaction IDs? But if there is metadata that enables identification of transaction IDs then why not use it instead of transaction ID in the first place?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#432

Post by coldtea »

lovich wrote: post #429
coldtea wrote: post #380

Earlier quoted context omitted.

> It has the same odds as any other specific configuration of randomly assigned dots Which doesn't change anything in practice, since it having "the same odds as any other specific configuration" ignores the fact that more scattered configurations are still far more numerous than it (or even from ones with more visual order in general) taken all together. > The overly active human pattern matching behavior is the onl…

> >It has the same odds as any other specific configuration of randomly assigned dots > Nope, it's also the fact that it is ONE configuration, whereas all the rest are much much larger number. That is the human pattern overactive pattern matching at play. I compared the single configuration of all dots on one location to any other specific configuration. You are not comparing to to _every other configuration_ because…

>That is the human pattern overactive pattern matching at play. I compared the single configuration of all dots on one location to any other specific configuration. You are not comparing to to _every other configuration_ because they are not the same. You are assigning specific importance to a single valid set of randomly selected data, because it seems significant to our brains.

That's just how importance works.

It sets some things aside as "significant to our brains". The universe doesn't care, even total heat death is not "important" if one excluses us making a prioritization of things.

Given our classification of orderly configurations as a distinct set, the comparison is between "any from all random-looking/noise-like configuration" vs "any from all orderly-like configurations". And the former are much more.

>if you answer anything other than it being the same chance, then you either don't think the selection mechanism is random, or you are falling to the standard fallacies around randomness

You're confusing the selection mechanism (random) with the classification mechanism that segments the set of possible outcomes into orderly vs not (not random).

As a simpler example, imagine a bag with N loterry numbers on individual cards. If they pick one at random, the chance any number has is 1/N. But the chance that a number OTHER than ours has is N-1/N. Our chances are as good as any other single number's, sure. But they're NOT as good as all other numbers put together.

You're argue that "but all are just sets of coordinates" or "all are just lottery numbers".

Sure, but some of those coordinate sets have importance to us, and others don't. And one of these lottery numbers is important t us, all the others aren't. And since the latter is a much larger group, the posibility of a member of it coming up is too.

That we consider one subset of results more special than the other is not negotiable. It's a thing we actually do in the real world, and it's the premise of the whole discussion.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#433

Post by dpark »

mkleczek wrote: post #431
everforward wrote: post #422

Earlier quoted context omitted.

> It does not matter if they are random or not. Again, sometimes it does, the article lists a few of them. Making it harder to scrape, unifying across databases that share a keyspace, etc. > And how does surrogate key help? I don't know the surrogate key that identifies my records in your database. Even if you use them internally it is an implementation detail. That surrogate key is linked to literally every other re…

I am not sure you are arguing against my claims or not :) I am not arguing against surrogate keys in general . They are obviously very useful _internally_ to introduce a level of indirection. But if they are used _internally_ then it doesn't really matter if they are UUIDs or sequence numbers or whatever - it is just an implementation detail. What I claim is that surrogate keys are problematic as _externally visible_…

> I think you are missing the actual problem I am talking about: where does the user take the transaction ID from? Do you expect the users to remember all transaction IDs your system ever generated for them? How would they know which transaction ID to ask about? Are they expected to keep some metadata that would allow them to identify transaction IDs? But if there is metadata that enables identification of transaction IDs then why not use it instead of transaction ID in the first place?

Your notion that you can avoid sharing internal ids is technically true, but that didn’t mean it’s a good idea. You’re trying force a philosophical viewpoint and disregarding practical concerns, many of which people have already pointed out.

But to answer your question, yes, your customer will probably have some notion of a transaction id. This is why everyone gives you invoice numbers or order numbers. These are indexes back into some system. Because the alternative is that your customer calls you up and says “so I bought this thing last week, maybe on Tuesday?” And it’s most likely possible to eventually find the transaction this way, but it’s a pain and usually requires human investigation to find the right transaction. It’s wasteful for you and the customer to do business this way if you don’t have to.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#434

Post by pelheit »

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?

I think he means attempting to mimic some features of the opposite sex, with procedures like penile inversion and so on.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#435

Post by pelheit »

defrost wrote: post #416
EnergyAmy wrote: post #405

Earlier quoted context omitted.

Technically correct, but to be specific sex is binary, not merely bimodal. Sex is entirely defined by gametes, and is binary in anisogamous species such as humans. Isogamous species don't have sexes, they have mating types (and often many thousands of them). There's actually an ideological movement to try to redefine sex based on sex traits instead of gametes, but this ends up being incoherent and useless for the fie…

Reproductive sex is determined by gametes .. sure. Not all humans are born with the attribute of reproductive sex via gametes. Hence "biological sex is real and strongly bimodal with outliers" (in humans, it gets odder elsewhere in animal life on earth) it's just not all reproductive sex, nor is all just strictly M or strictly F despite it mostly being one or the other. > To bring it back around to the general topic…

If you decide to redefine sex as a collection of traits, the problem with that is it's dependent on the specific developmental mechanisms of each species. Then the question is, how do you decide which traits are female and which are male? Especially in as yet undiscovered species. And how to classify species where each individual is both male and female?

The answer to all of this is to remember that sex is about reproduction, so it must fundamentally be based on gametes.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#436

Post by pelheit »

lovich wrote: post #427
brigandish wrote: post #426

Earlier quoted context omitted.

I keep addressing your points and you keep moaning about other people. Since sex and gender are not different until you are able to provide some reason that they are beyond bare assertion then gametes are relevant. > you could tell us all what the scientific discovery was that made gender separate from sex, who found it and when, and what the defining difference is. Did they win a Nobel for that? Take your time, but…

> I keep addressing your points… No you did not. > Since sex and gender are not different until you are able to provide some reason that they are beyond bare assertion then gametes are relevant. Sex is a parameter of biology, gender is a parameter of social constructs. You are also having bare assertions that they are the same. Gametes are not relevant. You are unable to discern between different values. Also stop br…

I've yet to see a definition of gender that isn't based on restrictive and harmful sex stereotypes, or is circular and empty. It's not a helpful concept.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#437

Post by defrost »

pelheit wrote: post #435
defrost wrote: post #416

Earlier quoted context omitted.

Reproductive sex is determined by gametes .. sure. Not all humans are born with the attribute of reproductive sex via gametes. Hence "biological sex is real and strongly bimodal with outliers" (in humans, it gets odder elsewhere in animal life on earth) it's just not all reproductive sex, nor is all just strictly M or strictly F despite it mostly being one or the other. > To bring it back around to the general topic…

If you decide to redefine sex as a collection of traits, the problem with that is it's dependent on the specific developmental mechanisms of each species. Then the question is, how do you decide which traits are female and which are male? Especially in as yet undiscovered species. And how to classify species where each individual is both male and female? The answer to all of this is to remember that sex is about repr…

That's reproductive sex.

The question of classifying human births is larger - not all humans born have gametes. Some have two sets.

For people interested in actual observed birth cases there's a lot more going on than a moronically over simplified two buckets cover all cases when it comes to attributing sex [] .. clearly M or clearly F with everything aligned (physical form + chromosones + gamates) covers most cases .. and then there's the rest.

It gets even broader when including mammals such as rabbits and pigs as they express cases that are potentially possible in humans but not (as yet) observed or on record.

> so it must fundamentally be based on gametes.

Wishful thinking stemming from a strong held preconceived idea of how the workd must be rather than field based observation of that which occurs.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#438

Post by defrost »

EnergyAmy wrote: post #419
defrost wrote: post #416

Earlier quoted context omitted.

Reproductive sex is determined by gametes .. sure. Not all humans are born with the attribute of reproductive sex via gametes. Hence "biological sex is real and strongly bimodal with outliers" (in humans, it gets odder elsewhere in animal life on earth) it's just not all reproductive sex, nor is all just strictly M or strictly F despite it mostly being one or the other. > To bring it back around to the general topic…

You can't legislate reality away. If you're tracking biological sex, then it doesn't matter what a court decides. If you're tracking legal fictions then you might. I look forward to your citation disputing the truth of what he lays out in that paper. In the meantime, feel free to peruse the list here of people affirming the same stance: https://projectnettie.wordpress.com/ Or someone else: https://www.nas.org/academi…

> You can't legislate reality away

So why are you trying to?

> I look forward to your citation disputing the truth of what he lays out in that paper.

Just look to his reputation in the field .. it's up there with Jo Nova on climate physics .. laughable.

> You should ask the people you run with why no human is born with a body not organized around the production of gametes.

So you're implicitly admitting that humans are born without gamates then? You've certainly dodged that question multiple times in your comment history.

You're also not admitting to yourself the existence of those humans born with conflicting organisation re: sexual reproduction - when the physical form, the chromosones, the gamates, et al don't align.

From an empirical PoV for people in field work here it's simply silly to claim that only two cases cover all variations - it's a mystery why any one would work so hard to force it.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#439

Post by EnergyAmy »

defrost wrote: post #438
EnergyAmy wrote: post #419

Earlier quoted context omitted.

You can't legislate reality away. If you're tracking biological sex, then it doesn't matter what a court decides. If you're tracking legal fictions then you might. I look forward to your citation disputing the truth of what he lays out in that paper. In the meantime, feel free to peruse the list here of people affirming the same stance: https://projectnettie.wordpress.com/ Or someone else: https://www.nas.org/academi…

> You can't legislate reality away So why are you trying to? > I look forward to your citation disputing the truth of what he lays out in that paper. Just look to his reputation in the field .. it's up there with Jo Nova on climate physics .. laughable. > You should ask the people you run with why no human is born with a body not organized around the production of gametes. So you're implicitly admitting that humans a…

The gamete-based definition of sex is merely a description of reality.

I continue to look forward to your citation disputing the truth of what he lays out in that paper, or the other links I provided that affirm the same stance. Ad hominems are boring, don't you have anything?

You unfortunately don't really understand the point here, but to reiterate, just because someone is born with nonfunctional/missing gonads doesn't mean their body isn't sexed. As an analogy, if someone is born without a hand, we don't just shrug and say that it could've been a fin, or antlers, or a firetruck. That's the point of saying that their body is organized around the production of one of exactly two gamete types.

There's no conflict, physical form and chromosomes are variations within a sex, which is entirely defined by gametes. Chromosomes are part of how sex is determined, but gametes are how sex is defined.

I look forward to your citations of these people doing field work that support your points.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#440

Post by defrost »

EnergyAmy wrote: post #439
defrost wrote: post #438

Earlier quoted context omitted.

> You can't legislate reality away So why are you trying to? > I look forward to your citation disputing the truth of what he lays out in that paper. Just look to his reputation in the field .. it's up there with Jo Nova on climate physics .. laughable. > You should ask the people you run with why no human is born with a body not organized around the production of gametes. So you're implicitly admitting that humans a…

The gamete-based definition of sex is merely a description of reality. I continue to look forward to your citation disputing the truth of what he lays out in that paper, or the other links I provided that affirm the same stance. Ad hominems are boring, don't you have anything? You unfortunately don't really understand the point here, but to reiterate, just because someone is born with nonfunctional/missing gonads doe…

> The gamete-based definition of sex is merely a description of reality.

An incomplete one that fails to cover all cases.

You unfortunately don't really understand the point here

Post reply on HN