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

411–420 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#411

Post by thraxil »

p2detar wrote: post #212
thraxil wrote: post #118

Earlier quoted context omitted.

Yep. We have tables that use UUIDv4 that have 60M+ rows and don't have any performance problems with them. Would some queries be faster using something else? Probably, but again, for us it's not close to being a bottleneck. If it becomes a problem at 600M or 6B rows, we'll deal with it then. We'll probably switch to UUIDv7 at some point, but it's not a priority and we'll do some tests on our data first. Does my exper…

Nice feedback. Out of curiosity, have you made any fine-tuning to psql that greatly improved performance?

Nope. Out of the box GCP Cloud SQL instance.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#412

Post by dotancohen »

sgarland wrote: post #148
K0nserv wrote: post #59

Earlier quoted context omitted.

Isn't part of this that inserting into a btree index is more performant when the keys are increasing rather than being random? A random id will cause more re-balancing operations than always inserting at the end. Increasing ids are also more cache friendly

Yes, and for Postgres, it also causes WAL bloat due to the high likelihood of full page writes.

Could you expand on this? I use postgres often and though I could have an LLM explain what you mean, I think I'd learn more to hear it from you. Thank you.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#413

Post by grugdev42 »

hnfong wrote: post #97
Denvercoder9 wrote: post #87

Earlier quoted context omitted.

This misses the point. The reason not to use UUIDv4 is that having an index on random values is slow(er), because sequential inserts into the underlying B-tree are faster than random inserts. You're hitting the same problem with your `public_id` column, that it's not the primary key doesn't change that.

Ints as pk would be quicker for joins etc though.

Exactly

Re: Avoid UUID Version 4 Primary Keys in Postgres

#414

Post by pezezin »

hexbin010 wrote: post #384
pezezin wrote: post #341

Earlier quoted context omitted.

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.

A global sequence, or sequence-per-account? I suspect the latter?

A sequence per "series", where a series can be a fiscal year, a department or category, etc. But I am not sure if you can have one series per customer, I only find conflicting information.

You can have more details here, in the section "Complete invoice":

https://sede.agenciatributaria.gob.es/Sede/en_gb/iva/factura...

https://www.boe.es/buscar/act.php?id=BOE-A-2012-14696#a6 (Spanish only)

Re: Avoid UUID Version 4 Primary Keys in Postgres

#415

Post by pezezin »

Terr_ wrote: post #275
bigstrat2003 wrote: post #262

Earlier quoted context omitted.

That is only true if you're using an extremely idiosyncratic definition of gender. As far as 95% of English speakers are concerned, gender is defined by the body you possess.

Does that mean hundreds of years of English-speakers referring to sailing ship as "she" were all part of a conspiracy to hide that ships have jiggly bits? :p

Wait until you find gendered languages (like most languages in Europe) and realize that grammatical gender usually doesn't have anything to do with biological sex :P

Re: Avoid UUID Version 4 Primary Keys in Postgres

#416

Post by defrost »

EnergyAmy wrote: post #405
defrost wrote: post #401

Earlier quoted context omitted.

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 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 of this thread, it's fine to store someone's sex as a boolean, because sex is binary and immutable.

Not in Australia, via a decision that ascended through all levels of the national court system, nor is sex, as you've chosen to define it ("entirely defined by gametes") binary.

Biology is truly messy. It's understandable not everbody truly grasps this.

Colin Wright is pretty much a prop up cardboard "scientist" for the Manhattan Institute (a political conservative think tank).

I tend to run with people with actual field credentials doing real biology and medicine; Michael Alpers, Fiona Stanley, Fiona Wood, et al were my influences.

If Colin Wright scratches your itch for bad biology then by all means run with the one hit wonder who reinforces a preconception untroubled by empiricism.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#417

Post by tmountain »

0x457 wrote: post #269
bigmadshoe wrote: post #234

Earlier quoted context omitted.

I’m really no expert on sharding but if you’re using increasing ints why can’t you just shard on (id % n) or something?

Because then you run into an issue when you 'n' changes. Plus, where are you increasing it on? This will require a single fault-tolerant ticker (some do that btw). Once you encode shard number into ID, you got: - instantly* know which shard to query - each shard has its own ticker * programatically, maybe visually as well depending on implementation I had IDs that encode: entity type (IIRC 4 bit?), timestamp, shard,…

^ This

Re: Avoid UUID Version 4 Primary Keys in Postgres

#418

Post by tmountain »

SoftTalker wrote: post #244
tmountain wrote: post #42

Earlier quoted context omitted.

Sticking with sequences and other integer types will cause problems if you need to shard later.

This is mentioned, and in many applications you can safely say you will never need to shard.

Yes, but if you do need to, it's much simpler if you were using UUID since the beginning. I'm personally not convinced that any of the tradeoffs that comes with a more traditional key are worth the headache that could come in a scenario where you do need to shard. I started a company last year, and the DB has grown wildly beyond our expectations. I did not expect this, and it continues to grow (good problem to have). It happens!

Re: Avoid UUID Version 4 Primary Keys in Postgres

#419

Post by EnergyAmy »

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…

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/academic-questions/33/2/in-humans-sex-is...

You should ask the people you run with why no human is born with a body not organized around the production of gametes. You'll notice that when you read about conditions like anorchia or ovarian agenesis, the sex of the person with that condition is not a mystery, it's literally in the name.

Biology is messy indeed, and that's why finding such a universal definition was so useful.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#420

Post by brettgriffin »

mkleczek wrote: post #299
brettgriffin wrote: post #260

Earlier quoted context omitted.

> 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 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 key, 46272487. From that, you can determine what it links to, the name/date/author of the submission, comments, etc.

46272487 means absolutely nothing to anybody involved. But if you wanted to see submissions from user pil0u, or submissions submissions on 2025-12-15, or submissions pertaining to UUID, 46272487 would in that in that result set. Once 46272487 joins out to all of its other tables, you can populate a list that includes their user name, title, domain, etc.

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

Post reply on HN