Hacker News Reader

Comment by dokimorning | Hacker News Reader

It seems if he's planning to make 100 million records with a probability of 1 in 1 million of a collision, he's going to end up with ~100 collisions. I think I would plan to make the collision probability of N records at least < 1/N. Plus, one million records is really not so big.

I do like the point that UUIDs are generally stored as strings whereas they represent a 122 bit value. Seems encoding the UUIDs as binary would offer much greater efficiency in storage space as well as indexes.

View on HN

It's not a 1 in a million chance for each record, it's a one in a million chance of their being a collision in the entire set of 100 million records. So for every million sets of 100 million records you generate you'll get on average 1 set with a collision.

I would never store a UUID as a string for exactly that reason. Either a binary or a native UUID datatype. Any developer IMHO who stores a UUID as a string is suspect.

Postgres has a UUID type, for example.

I've always seen UUIDs stored as strings. What's the suspect part? Favoring human readability over optimal machine storage utilization?

Human readability is a concern of a client and is independent of the storage mechanism. Every modern database stores integers in binary format, for instance, but clients display them as a decimal string of characters as opposed to a binary or hexadecimal representation. Timestamps are similarly stored in binary fashion, but often formatted for human readability in the client.

UUID is not a universally available primitive type like integer or date.

Someone hasn't used Postgres. http://www.postgresql.org/docs/9.1/static/datatype-uuid.html

And anyway, all a UUID is, ultimately, is a big number. It's a simple transcoding to get it into base-10 integer format and back.

If you're using an SQL database you're presumably doing so so that humans can run ad-hoc reports (otherwise there are better datastores). So the UX they get for that is important. And in mysql (yes, not the best choice these days, but a reasonable one when the decision was made), if you store UUIDs as binary (there's no native UUID type) then you do not provide a good UX.

MySQL itself should not be the main interface; there should be some kind of model layer on top of that which does the translation of things like that, such as (for example) ActiveRecord if you were on a Rails stack. That gets you the best of both worlds.

OR you could store the uuid twice, once "natively" and once as a computed column. Searches on the native field would be faster vs. an index on a string column.

> you're presumably doing so so that humans can run ad-hoc reports (otherwise there are better datastores)

oh dear, someone has drank the NoSQL punch... Storing data relationally is NOT something only suitable for ad-hoc queries by end-users! :O

It's 1 in a million chance of any collision across the entire dataset, not 1 in a million records that will have a collision. This is why the article mentions the birthday paradox -- it's taking the chance of a collision between two particular values (and knowing the bits of randomness is enough to give you this) and calculating the chance that there's any collision at all.

Ah. Thank you for clarifying that point. Seems 1 per 100 trillion records would be much more acceptable.

Also, to whoever downvoted my very first post here: Way to build a community @sshole. I'm never commenting here again thanks to you, jack@ss.

Reply on news.ycombinator.com