Just learned that PostgresSQL can auto-generate UUID primary key now. Does a UUID primary key serve any purpose other than avoiding conflict in case you scale to multiple databases?
derek-zhou
webuhu
In purpose the ID is somewhere public it avoids that the sequence is predictable.
If you want to hide how many are already existing.
0
sbuttgereit
Yep, PostgreSQL can default a column value either using a literal value or using the output of any function that returns the correct type; of course in the case of a primary key or other unique column the return value of the function must be unique or you’ll get some flavor of unique violation. The commonly used, though now old fashioned, serial/bigserial column types are really pseudo-types/syntactical sugar for generating both the column type of integer or bigint, creating a sequence, and setting the default value to the return value of the nextval(<sequence regclass>) function call.
Assuming a surrogate key as primary key, I have a number of reasons for preferring UUIDs over incrementing integers. One is the case you cite, but consider a case where you’re writing SQL by hand. I can’t tell you how many bugs I’ve seen of the type:
CREATE TABLE pohead
(pohead_id bigserial PRIMARY KEY <columns>);
CREATE TABLE podetail
(podetail_id bigserial PRIMARY KEY,
podetail_pohead_id bigint REFERENCES pohead (pohead_id),
<columns>);
SELECT *
FROM pohead JOIN podetail ON pohead_id = podetail_id;
When using integers for primary key, the bug above may actually produce a result… not the right result, but a result. When you do get a result from the bug above, it may be just close enough to the correct answer that you can go through quite a few operations disconnected in time making it all that much harder to track down the bug. With UUIDs that class of bug becomes pretty obvious pretty early because the process where the bug sits simply won’t itself produce any result where a result is expected
0
derek-zhou
It is fair, however in practice:
- for commonly used joins, I just create a view ahead of time and use the view in my code
- in the context of Elixir, Ecto can effectively prevent this kind of bug.
I think UUID is an eye sore but it could be just me.
0
dimitarvp
FWIW I agree but f.ex. @Exadra37 argued at length in favour of UUIDs, mostly citing enumeration attacks – like if the attacker knows you have /profiles/1/edit URLs, they will just make a script looping over all integers and attempt to find an authorization bug in your app that would allow them to edit an user profile.
And even if that fails every time for them they can DoS your app that way.
I’m semi-apathetic to the former problem and sympathetic to the latter. But I’d still put flood protection in front of my app and some ban rules.
50/50 though. I can clearly see the argument in favor of opaque and unpredictable IDs but I’m still skeptical how prevalent the prevented problem is in practice out there. Can’t hurt to protect yourself just in case, I suppose.
1
sbuttgereit
I’ve seen this in practice. Our company was sourcing human resources management systems and one of the new/hot/disruptive/etc. SaaS companies was on the table. We noticed URLs of exactly that sort and also noticed the IDs sequentially incrementing. We trivially demonstrated that you get data you weren’t suppose to get just by manually manipulating the IDs in the URLs. Luckily, human resources data isn’t so sensitive… we didn’t buy that system :-).
1
derek-zhou
hashids can make integer id opaque, but it is a very weak kind of encryption. A determined hacker with access to enough hashids can recover the salt in use and do the enumeration attack.
I agree with you that flood protection shall be the first line of defense. UUID key can prevent the enumeration but not the DDoS.
1
sbuttgereit
It’s not just you. They are an eyesore. But it’s about trade-offs.
Strong/static typing can be a pain in the ass to work with compared to weak/dynamic typing. Working with something like Rust’s borrow checking is more annoying than, say, working in a more permissive environment such as C++ or C.
However certain classes of errors are largely eliminated by working in the more constraining environment, but at the cost of some ergonomic loss. Sometimes those trade-offs are worth it: JavaScript, Python, PHP, … Elixir & Erlang… etc. do have their place and time compared to less forgiving languages like Rust.
I find that UUIDs can strongly prevent certain classes of data management & maintenance problems at the cost of being harder to deal with on an intuitive basis. In applications with less demanding requirements of data persistence… sure, use the integer keys. Some classes of applications aren’t as conducive to simpler remediations, like canned views, in enough cases to avoid the issues of integer based keys and the trade off swings to a UUID class of identifier. I am usually working in environments where data retention is complex and the required certainty around data management is sufficiently justified to warrant the cost of UUIDs (or similar).
I’ll get off the soapbox here, we’ve derailed the conversation a bit, but I do think it’s useful to think about these things.
2
dimitarvp
(OFF-TOPIC, sorry)
Tell me about it! I’ve been prototyping no less than 7 separate Rust programs in the last months – relatively complex too, with async / parallelism mechanics and a lot of zero-copy code blocks that involved way too much fighting with the borrow checker (at one point I gave up and started using .clone() in all non-hot coding paths because the return-of-investment ratio of developer time was abysmal).
I can confidently say that:
-
Rust is absolutely fantastic, it can ingest thousands of events per second from network and the memory usage in release/optimized mode was like 25MB and the CPU barely registered 3% load. And I had several Rust jobs in the last years, intermingled (and sometimes mixed; maybe I should apply to Fly.io!) with Elixir ones, and I’ve seen Rust programs not breaking a sweat working with 150k req/s in some meager dual-core CPU containers with 4GB RAM. But it’s not just the performance, that’s only a nice bonus. The correctness guarantees and the fearless concurrency do work and they do work amazing. The compiler has stopped me from introducing weird race conditions, many times. I learned and re-learned a lot.
-
…but if you want to prototype quickly and make a proof-of-concept THEN STAY THE HELL AWAY from Rust!
(Unless you’re already a master which I am working hard on becoming but not quite there yet.) Rust is not good for prototypes. I dearly regret not writing a library for the network protocols I needed in Golang or Elixir. I could have iterated no less than 5x to 10x faster after that initial time investment. I am not joking. Later on I did a more detailed GitHub search and found a Golang library, and honestly, it took me 2 hours both to relearn Golang and write a prototype that took me 2 weeks with Rust. The difference is that huge. (Although the Rust libraries I had to work with were train wrecks and I had to assemble a working solution out of the ruins of 4 of them – so that was a factor as well).
2