Hacker News Reader

Comment by cube00 | Hacker News Reader

I really wish I could get over my irrational fear that using an UUID will one day result in a collision and the associated data integrity issue when an update silently overwrites an unrelated record, where as the old sequential sequence never has this risk.

View on HN

Do you worry about the irrational fear that one day all the oxygen molecules in the room will gather in an upper corner, and you'll suffocate?

lol

Having witnessed a uuid collision in only millions records, I can assure you it's not a laughing matter.

That is likely due to a fault in the UUID generator, rather than the concept of using UUIDs.

http://www.h2database.com/html/advanced.html#uuid

Well than the java implementation of UUID is flawed!

You say 'only millions records'. Even if you had 2^36 records (that's over 68 billion) the chances of a collision should be 0.0000000000000004

That really is vanishingly small.

So, rationally, I'd guess it is more likely there is a problem with the implementation of UUID you are using, or just perhaps there is some other cause of the collision...like a bug?

Also possibly a problem with the version of UUID used. A v1 UUID is somewhat likely to collide if machines share MAC addresses and/or have problematic clocks.

The likelihood of a v4 collision is tied to the strength of the platform's (crypto) RNG.

If the UUIDs are actually being picked uniformly, the fear is entirely irrational, I agree. However, fear that the distribution isn't actually uniform in real implementations is much less crazy.

On the flipside, I have a very rational distrust of sequential sequences. Over the years I've seen far too many bad SQL database designs abuse sequential sequences for things that should not have a sequence, and too many bad SQL applications that create sequence collisions inadvertently through off-by-one bugs and forced inserts.

In several of those cases, because the sequence remained consistent/in order with no obvious holes, collisions went undetected for days/weeks because people just assumed that because they could count off 1, 2, 3, 4, 5 (not realizing they were missing that a second 2 had overwritten the first and a bad outer join was bringing in the wrong 5, for instance) that everything was working correctly. At least with UUIDs there's no accidental assumptions about data integrity from meaningless tangents like "looks like a proper number line" from the human brain's default pattern matching toolkit.

A company I used to work for had a nasty, hard to diagnose outage the other week - bad enough to publically announce and grovel ( I keep an eye on them). I believe, without access to any current technical detail is that they slavishly use sequence keys everywhere - so no real foreign keys. This in turn has caused them to massively over rely on elastic search, which with a bit of growth caused their queue infrastructure to get swamped and thus a big performance problem.

I realised the writing was on the wall there for me when I mentioned how useful proper (compound) foreign keys were and my comment was treated with derision. Of course now I work somewhere with a more properly designed database, and win having to cope with Oracle instead.

It is incredibly unlikely but you're right, especially when there might be poor UUID generators being used. We use 64-big ints for all ids which are resereved as batches from a global id service. Any datastore with atomic increments can serve this so I'm surprised it's not used more often.

I am inexperienced, so please correct me if I am wrong - but conceptually this way doesn't seem much more effective or different than UUIDs if the purpose is a non-consecutive unique identifier. Also, could you please explain what you mean by "...which are resereved as batches from a global id service"?

Does it matter if it's consecutive? If you want 0% chance of collisions and/or using numbers then they will be increasing in some way. ID generation scheme:

Key "counter" => value "123"

Server N starts, asks for 100 ids by incrementing counter, gets the new number 223, now it has numbers from 124->223 to use for ids. Server N+1 starts, does the same thing, and you can keep incrementing the counter to get the next batch of numbers.

Anything that supports atomic increments works. Design for whatever availability you need. Add in some random increments if you don't want perfectly sequential numbers. 64-bit numbers pretty much guarantee you'll never run out of space, while being much nicer to handle than UUIDs (which are 128-bit numbers but usually stored as bytes or strings).

If you want completely random IDs then UUID is the way to do it, but if it's just for load balancing or partitioning then you can always hash the numbers to get some random strings which work well enough.

There is also a pattern referred to as "high/low keys"[0] and works quite well so long as the architecture guarantees access to the master when a new high key is needed.

Of course, this can be mitigated by allocating small batches of high keys as well if connectivity to the master is unreliable. However, having a 32 bit low range implies the need for batch high key allocation is negligible for all but the most pathological situations.

0 - https://stackoverflow.com/questions/282099/whats-the-hi-lo-a...

EDIT: Re-read your original description and realized you described a related, but different, approach of directly reserving key ranges.

Yes, we reserve the actual number range which is simpler and avoids errors. I havent come across a standard name for this scheme though. We reserve millions of numbers at a time and it works perfectly, and the batch can easily be adjusted to account for usage factor and potential downtime buffering.

Reply on news.ycombinator.com