Hacker News Reader

Comment by dewey | Hacker News Reader

> sacrifice the beauty and elegance of UUIDs

Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

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

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.

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 tell all your tables to use the same sequence.

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.

Their uniqueness of course.

> To be able to generate keys independently of the database

> To move sets of related records between different databases without having to deal with renumbering everything

And adding greppability of logs in debugging situations. You can just search logs in any systems of yours with the UUID and find exact hits you are dealing with. Whereas with integers you will get all kinds of hits

Because UUIDs are universally Unique IDentifiers.

Unfortunately, UUIDs aren't as universally unique in practice as people think. The standard flavors are banned in quite a few organizations that care a lot about data integrity for valid reasons. Standard UUIDs work for some applications but definitely not all.

For most applications you are better off using a sensible structured key, like incrementing an integer or similar, and encrypting it if you want to obscure it. Encrypting a 128-bit value is approximately free on modern CPUs.

> To be able to generate keys independently of the database

This is a must if you don't want to couple your domain logic and DB. I want to generate a record with an ID inside my domain layer without depending on the DB and doing awkward DB save - DB read.

Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.

How are integers simpler? Even generation of unique integers is more complicated, let alone migrating them or moving them around.

Nothing? Really? Have you ever thought about that?

1. You have to handle race conditions if there is ever more than one process writing to the DB at one time 2. It’s ambiguous whether a number is an ID. E.g. when looking at logs 3. Numbers leak information about table size, row age, and crucially future row IDs.

Reply on news.ycombinator.com