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

451–460 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#451

Post by mkleczek »

brettgriffin wrote: post #420
mkleczek wrote: post #299

Earlier quoted context omitted.

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 ad…

That's not really how data is requested. Most of these identifiers are foreign keys - they exist in a larger object graph. Most systems of records are too large for people to associate surrogate keys to anything meaningful - they can easily have hundreds of billions of records. Rather, users traverse that through that object graph, narrowing a range of keys of interest. This hacker news article was given a surrogate…

> This hacker news article was given a surrogate key, 46272487. From that, you can determine what it links to, the name/date/author of the submission, comments, etc.

> Do not encode identifying information in unique identifiers! The entire world of software is built on surrogate keys and they work wonderfully.

The amount of manual work required to manage duplicates is in no small part the result of not thinking enough about the identifiers and simply slapping surrogate keys on the data.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#452

Post by brigandish »

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…

> To make sure I am not speaking to a bot

Do grow up.

> Sex is a parameter of biology, gender is a parameter of social constructs.

So you assert, but until you can show the moment that gender was shown to be different from sex beyond bare assertion then I'm not willing to accept your assertion. Do you see how that works?

> Gametes are not relevant.

They are relevant to sex determination and hence gender, see previous paragraph for why.

That's all your points, if they can be called that, addressed.

> Also stop bringing up the Nobel prize like it matters for the conversation.

You'll need to provide something that can be competitive for it to have any impact . I won't hold my breath.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#453

Post by dpark »

mkleczek wrote: post #450
dpark wrote: post #433

Earlier quoted context omitted.

> 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 transactio…

> 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. What some call "philosophical viewpoint" I call "essential complexity" :) > But to answer your question, yes, your customer will probably have some notion of a transaction id.…

Much of this is not essential complexity, but accidental complexity.

* Based on the data they identify - This is a minefield of accidental complexity. Data changes and needs to be redacted for GDPR and other data laws. What do you do when someone demands you delete all personally identifiable data but you’ve burned it into invoice ids that you need to retain for other legal reasons? This is also begging for collisions and very much at odds with making IDs short.

* easy to remember - This is a nice to have. Short is convenient for sharing on the phone. Memorable didn’t matter much. I don’t remember any invoice number I’ve ever received.

* versioned - Versioning is only interesting because you’re trying to derive from real data. Again, accidental complexity.

* easy to index - Sure.

* sortable - Nice to have at best.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#454

Post by mkleczek »

dpark wrote: post #453
mkleczek wrote: post #450

Earlier quoted context omitted.

> 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. What some call "philosophical viewpoint" I call "essential complexity" :) > But to answer your question, yes, your customer will probably have some notion of a transaction id.…

Much of this is not essential complexity, but accidental complexity. * Based on the data they identify - This is a minefield of accidental complexity. Data changes and needs to be redacted for GDPR and other data laws. What do you do when someone demands you delete all personally identifiable data but you’ve burned it into invoice ids that you need to retain for other legal reasons? This is also begging for collision…

> * Based on the data they identify

> * easy to remember

(which means human readable and related to the actual information which makes them easier to remember)

These actually are the most important features.

Example: transaction references not related to the actual subject of the transaction (ie. what is being paid for) is enabler for MITM scam schemes.

> Short is convenient

Nah. Short is crucial for identifiers to be effective for computers to handle (memory and CPU efficiency). Otherwise we wouldn't need any identifiers and would just pass raw data around.

> * versioned - Versioning is only interesting because you’re trying to derive from real data.

Nah. Even UUID formats contain version information.

> * easy to index - Sure.

> * sortable - Nice to have at best.

These are directly related (and in the context of UUIDv4 vs UUIDv7 discussion sortable is not enough - we also want them to be "close" to each other when generating so that they can be indexed efficiently)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#455

Post by dpark »

mkleczek wrote: post #454
dpark wrote: post #453

Earlier quoted context omitted.

Much of this is not essential complexity, but accidental complexity. * Based on the data they identify - This is a minefield of accidental complexity. Data changes and needs to be redacted for GDPR and other data laws. What do you do when someone demands you delete all personally identifiable data but you’ve burned it into invoice ids that you need to retain for other legal reasons? This is also begging for collision…

> * Based on the data they identify > * easy to remember (which means human readable and related to the actual information which makes them easier to remember) These actually are the most important features. Example: transaction references not related to the actual subject of the transaction (ie. what is being paid for) is enabler for MITM scam schemes. > Short is convenient Nah. Short is crucial for identifiers to b…

> These actually are the most important features.

You keep saying that but you have provided virtually no evidence in support of this. This is why I called your claim philosophical. You are asserting this as fact and arguing from that standpoint rather than considering what is the best based on actual requirements and trade offs.

> Example: transaction references not related to the actual subject of the transaction (ie. what is being paid for) is enabler for MITM scam schemes.

I don’t see how this is true. If anything transaction references based on the actual subject would make scamming slightly easier because a scammer can glean information from the reference.

I’m going to stop here, though. I don’t see that this is going to converge on any shared agreement.

Take care. And if you celebrate the holidays, happy holidays, too.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#456

Post by wpollock »

pdimitar wrote: post #82
formerly_proven wrote: post #60

Earlier quoted context omitted.

If all you want is to obfuscate the fact that your social media site only has 200 users and 80 posts, simply use a permutation over the autoincrement primary key. E.g. IDEA or CAST-128, then encode in base64. If someone steps on your toes because somewhere in your codebase you're using a forbidden legacy cipher, just use AES-128. (This is sort of the degenerate/tautological base case of format-preserving encryption)…

> 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?)

YouTube video ids are just integers in a base-64 encoding, modified to be URL safe.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#457

Post by pdimitar »

wpollock wrote: post #456
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?)

YouTube video ids are just integers in a base-64 encoding, modified to be URL safe.

Interesting. Any examples? I mean, I can probably reverse-engineer something myself but just curious.

I am much more interested in the `si` parameter.. but I am fairly sure nobody outside of Google knows what it is exactly.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#458

Post by lovich »

brigandish wrote: post #452
lovich wrote: post #427

Earlier quoted context omitted.

> 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…

> To make sure I am not speaking to a bot Do grow up. > Sex is a parameter of biology, gender is a parameter of social constructs. So you assert, but until you can show the moment that gender was shown to be different from sex beyond bare assertion then I'm not willing to accept your assertion. Do you see how that works? > Gametes are not relevant. They are relevant to sex determination and hence gender, see previous…

> > To make sure I am not speaking to a bot

> Do grow up.

When I quote a fragment of someone else’s sentence I usually add an ellipses(…) to show that there’s more context and not imply a full statement, but you do you

Since you dropped the part about me asking you to state how the thread started, I am assuming this is at least a person dropping a prompt into ChatGPT and regurgitating it without editing.

Have a good day

Re: Avoid UUID Version 4 Primary Keys in Postgres

#459

Post by mkleczek »

dpark wrote: post #455
mkleczek wrote: post #454

Earlier quoted context omitted.

> * Based on the data they identify > * easy to remember (which means human readable and related to the actual information which makes them easier to remember) These actually are the most important features. Example: transaction references not related to the actual subject of the transaction (ie. what is being paid for) is enabler for MITM scam schemes. > Short is convenient Nah. Short is crucial for identifiers to b…

> These actually are the most important features. You keep saying that but you have provided virtually no evidence in support of this. This is why I called your claim philosophical. You are asserting this as fact and arguing from that standpoint rather than considering what is the best based on actual requirements and trade offs. > Example: transaction references not related to the actual subject of the transaction (…

> I don’t see how this is true.

There is a Bitcoin seller B, a thieve T and a victim V.

T proposes to buy Bitcoin from B. T offers a new iPhone for a very low price to unsuspecting V. V agrees to buy it. B gives T account details and transaction reference so that T can transfer money to B's account. T gives these details to V. V transfers the money. B transfers Bitcoin to T. T disappears.

If only transaction reference contained information that the transfer is about buying Bitcoin, V would have never paid the money.

The scheme was quite common in UK because banks did not like Bitcoin so Bitcoin sellers and buyers avoided referencing it in bank transfers.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#460

Post by dpark »

mkleczek wrote: post #459
dpark wrote: post #455

Earlier quoted context omitted.

> These actually are the most important features. You keep saying that but you have provided virtually no evidence in support of this. This is why I called your claim philosophical. You are asserting this as fact and arguing from that standpoint rather than considering what is the best based on actual requirements and trade offs. > Example: transaction references not related to the actual subject of the transaction (…

> I don’t see how this is true. There is a Bitcoin seller B, a thieve T and a victim V. T proposes to buy Bitcoin from B. T offers a new iPhone for a very low price to unsuspecting V. V agrees to buy it. B gives T account details and transaction reference so that T can transfer money to B's account. T gives these details to V. V transfers the money. B transfers Bitcoin to T. T disappears. If only transaction referenc…

You’re arguing that in this circumstance, the bitcoin seller should produce an ID for the payer that exposes the purchase contents.

Firstly, I am extremely doubtful that this would actually prevent the issue. A wary buyer would not agree to transfer money account to account like this to pay for a cell phone in the first place. Only gullible people would engage in this scam, and I am doubtful that they would question the transaction ID deeply. “Hey what is this bitcoin thing?” “Oh, don’t worry about it. That’s just internal for our tracking purposes. Do you want me to throw in a free phone case too?!”

Secondly, this seems like a massive privacy concern. Is someone purchasing sex toys supposed to use a transaction ID like purpledildo656 and expose what they are buying to the bank?

I’m sympathetic to people who get scammed, but I don’t think your transaction IDs solve this problem. People have been getting scammed like this forever. “Hey, just send the $600 via Western Union and I’ll totally put your phone in the mail for you tomorrow.”

This isn’t an ID anyway. What you are really asking for is to mandate that the contents of the purchase be burned into the transaction from the seller all the way to the buyer through the bank. I think that’s a terrible idea because of privacy concerns, but regardless, it’s not an ID. This would be much better expressed as a different form of metadata.

Post reply on HN