Hacker News Reader

Comment by kogus | Hacker News Reader

“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.

View on HN

Crucially, "5" might also be the key to a thousand different records in dozens of other development & QA databases for applications with integrations configured incorrectly. Integers can match coincidentally. UUIDs won't.

While wrong joins is a weak argument IMHO, our customers somewhat frequently ask to merge data, either from test to prod or from one company to another (after acquisition f.ex.).

In a lot of cases that can be a pain due to overlapping integer primary keys and large parent-child table sets.

You can incorrectly join on any columns you want. That's not really the fault of the column types IMO, that's a problem in the layers above.

True, but you can't incorrectly join on UUID due to its anti-collision nature.

Yes you can, you’ll not get the correct result set.

As sibling says, you can make exactly the same mistake and it not colliding just means no join - but if it's an outer join and one of many joins or in a nested query it might not be immediately obvious it's happened, or what the issue is even if it is.

We have some app generated internal IDs which have a 1 character prefix by type and then base64 sequential stuff after that. I'll admit it's nice knowing the type of an identifier by looking at it. I don't really like that they are strings, however. The original architect made some weird choices as far as deciding where and where not to optimize for performance.

You can tell all your tables to use the same sequence.

Reply on news.ycombinator.com