To summarise the article: in PG, prefer using UUIDv7 over UUIDv4 as they have slightly better performance. If you're using latest version of PG, there is a plugin for it. That's it.
With the latest Postgres version (>= 18) you do NOT need a plugin
Avoid UUID Version 4 Primary Keys in Postgres
101–110 of 463 posts
Re: Avoid UUID Version 4 Primary Keys in Postgres
#102Earlier quoted context omitted.
Out of curiosity, why is it an issue if you leak creation time?
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 at the start of the month and something at the end of the month. That would give them the total number of orders in that period. They already know how many orders they have placed during the month, so company_orders / total_orders = percentage_of_business
It doesn't even have to be accurate, just an approximation. I don't know if they figured out that they could do that but it wouldn't surprise me if they had.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#103Earlier quoted context omitted.
Out of curiosity, why is it an issue if you leak creation time?
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.
That's happening everywhere. You can order industrial parts from a Fortune 500 and check some of the numbers on it too, if they're not careful about it.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#104Very useful article, thank you! Many people suggest CUID2, but it is less efficient and is better used for frontend/url encoding. For backend/db, only UUID v7 should be used.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#105A 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 curious thing about the article is that, it's definitely premature optimization for smaller databases, but when the database gets to the scale where these optimizations start to matter, you actually don't want to do what they suggest.
Specifically, if your database is small, the performance impact is probably not very noticeable. And if your database is large (eg. to the extent primary keys can't fit within 32-bit int), then you're actually going to have to think about sharding and making the system more distributed... and that's where UUID works better than auto-incrementing ints.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#106A much simpler solution is to keep your tables as they are (with an integer primary key), but add a non sequential public identifier too. id => 123, public_id => 202cb962ac59075b964b07152d234b70 There are many ways to generate the public_id. A simple MD5 with a salt works quite well for extremely low effort. Add a unique constraint on that column (which also indexes it), and you'll be safe and performant for hundreds…
Per https://news.ycombinator.com/item?id=46273325, if you use a block cipher rather than a hash then you don't even need to store it anywhere.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#107A 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…
> Well, wrong, since although the date doesn't change.
Someone should have told Julius Caesar and Gregory XIII that :-p
Re: Avoid UUID Version 4 Primary Keys in Postgres
#108The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.
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)…
Why not use AES-128 by default? Your CPU has instructions to accelerate AES-128.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#109You'll have to rip the ability to generate unique numbers from quite literally anywhere in my app and save them without conflict from my cold, dead hands.
The ability to know ahead of time what a primary key will be (in lieu of persisting it first, then returning) opened up a whole new world of architecting work in my app. It made a lot of previous awkward things feel natural.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#110The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.
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)…
The problem with this approach is that you now have to manage a secret key/secret for a (maybe) a very long time.
I shared this article a few weeks ago, discussing the problems with this kind of approach: https://notnotp.com/notes/do-not-encrypt-ids/
I believe it can make sense in some situations, but do you really want to implement such crypto-related complexity?