Re: UUID column as pimrary key?

On Jan 5, 2011, at 12:03 PM, Bill Moran wrote:

> For crying out loud. If you're going to pick me apart with numbers, then
> actually do it with some intelligence.

If you're going to get nasty, at least try to be accurate.

> I could easily counter your argument by upping the numbers to 500,000
> mobile devices generating 10000 UUIDs per hour over 20 years ... or raise
> it even higher if you come back with that same argument ...

Yeah, then you get into the realm of 1 in 10s of millions of a chance of collision. But you would need to explain to me how you would get that many records into the database committed to disk, when the UUIDs alone without any other data represent a stream of 22MB/s ;-) Or, looked at another way, inserting 1,388,889 rows/second would indeed be difficult to sustain.

> But the point (that you are trying to sidestep) is that the UUID namespace
> is finite, so therefore you WILL hit a problem with conflicts at some point.
> Just because that point is larger than most people have to concern themselves
> with isn't an invalidation.

I'm not sidestepping the point at all. The point is that the finiteness of the space is a red herring. The space is large enough that there's no chance of collision in any realistic scenario. In order to get to a point where the probability of collision is high enough to worry about, you have to generate (and collect) UUIDs at a rate that is simply not realistic--as in your second example quoted above. If you just keep raising your numbers, you could go for 100,000,000,000,000 devices generating 100,000,000,000,000 UUIDs an hour for 10,000 years. Collisions would be guaranteed, but that does not make it a useful scenario to consider.

2^256 is a finite space as well. Would you argue that because it "is finite, so therefore you WILL hit a problem with conflicts at some point"? How about 2^512? (Bearing in mind that even though finite that space would be large enough to assign approximately 10^74 UUIDs to every atom in the observable universe, or 10^51 UUIDs to every atom in the total universe using high-end estimates of the size of the non-observable universe)?

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice