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

341–350 of 463 posts

Re: Avoid UUID Version 4 Primary Keys in Postgres

#341

Post by pezezin »

JetSetIlly wrote: post #102
saaspirant wrote: post #91

Earlier quoted context omitted.

There was a HN comment about competitors tracking how many new signups are happening and increasing the discounts/sales push based on that. Something like this.

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.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#342

Post by lovich »

bigstrat2003 wrote: post #262
delichon wrote: post #235

Earlier quoted context omitted.

You can't, but since gender isn't defined by anything physical, there's no need.

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.

The only real states of matter are solids, liquids, and gases. Everything else is just woke lunacy.

I am confident in this fact because I learned it in elementary school decades ago and it is impossible for humanity to discover new information that updates our world model. Every English speaker knows that “plasmas” and “Bose-Eisenstein condensates” are made up.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#344

Post by defrost »

bigstrat2003 wrote: post #262
delichon wrote: post #235

Earlier quoted context omitted.

You can't, but since gender isn't defined by anything physical, there's no need.

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.

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.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#345

Post by mrkeen »

sagarm wrote: post #137
ivan_gammel wrote: post #72

The is article is about a solution in search of a problem, a classic premature optimization issue. UUIDv4 is perfectly fine for many use cases, including small databases. Performance argument must be considered when there’s a problem with performance on the horizon. Other considerations may be and very often superior to that.

It's not really feasible to rekey your UUIDv4 keyed database to int64s after the fact, imo. Sure your new tables could be integer-keyed, but the bulk of your storage will be UUID (and UUIDv4, if that's what you started with) for a very long time

If you have too many UUIDs, throw more DBs at the problem. Hopefully you haven't tied yourself to any architectural decisions that would limit you to only using a single database.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#346

Post by mrinterweb »

iainctduncan wrote: post #243

Counterargument... I do technical diligence so I talk to a lot of companies at points of inflection, and I also talk to lots who are stuck. The ability to rapidly shard everything can be extremely valuable. The difference between "we can shard on a dime" and "sharding will take a bunch of careful work" can be expensive If the company has poor margins, this can be the difference between "can scale easily" and "we're n…

I can see how sharding could be difficult with a bigint FK, but UUIDv7 would still play nice, if I understand your point correctly. Monotonically increasing foreign keys have performance benefits over random UUIDv4 FKs in postgresql is the point of the article.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#347

Post by mrkeen »

m000 wrote: post #306

Why not just use UUIDs as a unique column next to a bigint PK? The power and main purpose of UUIDs is to act as easy to produce, non-conflicting references in distributed settings. Since the scope of TFA is explicitly set to be "monolithic web apps", nothing stops you from having everything work with bigint PKs internally, and just add the UUIDs where you need to provide external references to rows/objects.

Yes, if you're in the group of developers who are passionate about db performance, but have ruled out the idea of spreading work out to multiple DBs, then continuing to use sequential IDs is fine.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#348

Post by PunchyHamster »

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…

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

well, till you run out of numbers for the immigrants that don't have exact birth date

Re: Avoid UUID Version 4 Primary Keys in Postgres

#349

Post by Dylan16807 »

jonny_eh wrote: post #333
nonethewiser wrote: post #295

Earlier quoted context omitted.

>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. You still have that problem from organic birthdays and also the problem of needing to change ids to correct birth dates.

To add to that, birthdays can clump, just like any seemingly "random" data.

Not significantly. For actual births, a couple holidays have very low rates but clumping into much higher rates happens on no dates.

Re: Avoid UUID Version 4 Primary Keys in Postgres

#350

Post by PunchyHamster »

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…

The cause is more just "not having enough bits". UUID is 128 bit. You're not running out even if you use part for timestamp, the random part will be big enough.

Like, it's a valid complaint.. just not for discussion at hand.

Also, we do live in reality and while having entirely random one might be perfect from theory of data, in reality having it be prefixed by date have many advantages performance wise.

> Permanent identifiers should not carry data. This is like the cardinal sin of data management

As long as you don't use the data and have actual fields for what's also encoded in UUID, there is absolutely nothing wrong with it, provided there is enough of the random part to get around artifacts in real life data.

Post reply on HN