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

401–410 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#401

Post by defrost »

EnergyAmy wrote: post #396
defrost wrote: post #344

Earlier quoted context omitted.

As far as nigh on 100% of Bugis speakers are concerned there has always been five genders and they'll tell you the words in their language they have for them. * https://en.wikipedia.org/wiki/Buginese_language It appears to be a cultural construct.

You and the other person are probably talking past each other. For most people, "gender" is merely the polite way of saying "sex", and that's probably what the other commenter was referring to. Gender in the sense of "the social roles and norms on top of biological sex" is indeed a construct, though heavily informed by the biology that they're based on. Biological sex is very much real and not a construct.

Of course biological sex is real and strongly bimodal with outliers, who ever said otherwise?

Re: Avoid UUID Version 4 Primary Keys in Postgres

#402

Post by brigandish »

lovich wrote: post #387
brigandish wrote: post #377

Earlier quoted context omitted.

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…

Not only have you undermined your claim to a Nobel award by showing a spurious understanding of biology, you wrote, quite sarcastically "it is impossible for humanity to discover new information that updates our world model". Well then, we will all await your discovery of that 3rd gamete, or some theory so innovative that it tips this well studied, well understood, uncontested (by any valid competitor) model to the wayside and humanity can revel in this new information, the better model of reality that you promise.

While you're at it, 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?

I request that in any reply, you refrain from spamming me with Wikipedia links to articles you don't understand and probably haven't read.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#403

Post by mrkeen »

sgarland wrote: post #316
lukeschlather wrote: post #241

Earlier quoted context omitted.

If a sequential ID is exposed to the client, the client can trivially use it to determine the number of records and the relative age of any records. UUID solves this, and the use of a cryptographically secure number generator isn't really necessary for it to solve this. The author's scheme might be similarly effective, but I trust UUIDs to work well. There are obviously varying ways to hide this information other tha…

I have never seen anyone post an actual example of the German Tank problem creating an issue for them, only that it’s possible. > I don’t have to think about it And here we have the main problem of most DB issues I deal with on a daily basis - someone didn’t want to think about the implications of what they were doing, and it’s suddenly then my emergency because they have no idea how to address it.

Here you go:

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

[2025-12-10]

> We generate every valid 7-digit North American phone number, then for every area code, send every number in batches of 40000

> Time to go do something else for a while. Just over 27 hours and one ill-fated attempt at early season ski touring later, the script has finished happily, the logfile is full of entries, and no request has failed or taken longer than 3 seconds. So much for rate limiting. We’ve leaked every Freedom Chat user’s phone number

Re: Avoid UUID Version 4 Primary Keys in Postgres

#405

Post by EnergyAmy »

defrost wrote: post #401
EnergyAmy wrote: post #396

Earlier quoted context omitted.

You and the other person are probably talking past each other. For most people, "gender" is merely the polite way of saying "sex", and that's probably what the other commenter was referring to. Gender in the sense of "the social roles and norms on top of biological sex" is indeed a construct, though heavily informed by the biology that they're based on. Biological sex is very much real and not a construct.

Of course biological sex is real and strongly bimodal with outliers, who ever said otherwise?

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 field of biology. Biologists have had to publish papers explaining the fundamentals of their field to counter the ideological narrative:

Why There Are Exactly Two Sexes

https://link.springer.com/article/10.1007/s10508-025-03348-3

That's why I thought it was worth mentioning. Many people are confused because of the culture wars. To bring it back around to the general topic of this thread, it's fine to store someone's sex as a boolean, because sex is binary and immutable. Storing cultural constructs like gender as anything other than an arbitrary string is asking for trouble, though.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#406

Post by vrighter »

mkleczek wrote: post #215
vrighter wrote: post #138

Earlier quoted context omitted.

You can't take into account the fact that things change when you don't know what those changes might be. You might end up needing to either rebuild a new database, have some painful migration, or support two codepaths to work with both types of keys.

Network protocol designers know better and by default embed protocol version number in message format spec. I guess you can assign 3-4 bits for identifier version number as well. And yes - for long living data dealing with compatibility issues is inevitable so you have to take that into account from the very beginning.

when I designed network protocols this is exactly what I did. I also did so in file formats had to create. But a database primary kea is not somewhere where that can be easily done.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#407

Post by kentm »

vintermann wrote: post #135
mcny wrote: post #112

Earlier quoted context omitted.

That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason. So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.

You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing? I bet people will extract that date and use it, and it's hard to imagine use which wouldn't be abuse. To take the example of a PN/SSN and the usual gender bit: do you really want anyone to be able to tell that you got a new ID at that time? What could you suspect if a person born…

> You can argue that, but then what is its purpose?

The purpose is to reduce randomness while still preserving probability of uniqueness. UUIDv4 come with performance issues when used to bucket data for updates, such as when there used as primary keys in a database.

A database like MySQL or PostgreSQL has sequential ids and you’d use those instead, but if you’re writing something like iceberg tables using Trino/Spark/etc then being able to generate unique ids (without using a data store) that tend to be clustered together is useful.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#408

Post by kentm »

dpark wrote: post #265
majorchord wrote: post #156

Earlier quoted context omitted.

> You can argue that, but then what is its purpose? Why should anyone care about the creation date of a by-design completely arbitrary thing? Pretty sure sorting and filtering them by date/time range in a database is the purpose.

That is absolutely not the purpose. The specific purpose of uuidv7 is to optimize for B-Tree characteristics, not so you can craft queries based on the IDs being sequential. This assumption that you can query across IDs is exactly what is being cautioned against. As soon as you do that, you are talking a dependency on an implementation detail. The contract is that you get a UUID, not that you get 48 bits of timestamp…

B-trees too but also bucketing for formats like delta lake or iceberg, where having ids that cluster will reduce the number of files you need to update.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#409

Post by JetSetIlly »

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.

Yes, even if it's not a legal requirement it's definitely best practice to have sequential invoice numbers. I thought about this at the time but these numbers aren't invoice numbers, only order numbers.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#410

Post by nbadg »

thayne wrote: post #390
nbadg wrote: post #39

Earlier quoted context omitted.

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?

Certainly for many applications, the autoint approach would be fine.

The benefit of uuid in this case is that it allows horizontally scalable app servers to construct PKs on their own without risk of collisions. In addition to just reducing database load by doing the ID generation on the app server (admittedly usually a minor benefit), this can be useful either to simplify insert queries that span multiple tables with FK relationships (potentially saving some round trips in the process) or in very niche situations where you have circular dependencies in non-nullable FKs (with the constraint deferred until the end of the transaction).

Post reply on HN