Re: UUID column as pimrary key? - Mailing list pgsql-general

On 6 Jan 2011, at 24:27, Chris Browne wrote:

>> Next to that, UUID's are generated by computers. I have no doubts that
>> the numeric space that makes up a UUID allows for collision chances as
>> low as described, but are computers capable of generating those
>> numbers sufficiently random that they actually achieve that low a
>> chance? I think that's pushing it.
>
> RFC 4122 does NOT point to randomness as the only criterion to
> discourage collisions, and treating UUIDs as if they were merely about
> being "sufficiently random to achieve low chance of collision" is
> insulting to the drafters of the standard, because they were certainly
> NOT so naive as to think that was sufficient.

I'm sure the designers knew what they were getting into. This comment was aimed at people claiming things like "with
thisand that huge number of events a collision won't occur in 100 billion years", which - to me at least - looks like
they'reonly looking at the big number of bits involved without understanding statistical analysis. 
Let's just say, if the developers of "Microsoft Visual Nuclear Power Plant Designer Professional" were claiming things
likethat, would you trust their product? 

The main point with the randomness of UUID's remains that you _can_ have a collision at any given moment. It's unlikely
toever happen, but you can't predict when it will happen if it does. The possible consequences of a collision matter a
lotin designing whether and how to handle these collisions. Maybe it doesn't matter at all, maybe you should get a
reallyhefty insurance, or maybe you need to evacuate the country. 

Opposed to that, a sequence isn't random and therefore you can predict when you will run into collisions - namely once
thesequence wraps. Considering that even a 32-bit sequence allows for several billions of rows before collisions _can_
occur,you can be certain that your problem is pretty far into the future. 
It _will_ be a big problem without an obvious solution if it occurs though, as from that point on you will run into a
lotof collisions and the resolution to the problem is rather dependent on what you're working on. 

Now that is not an argument against protecting your application against collisions, if there is a chance that you will
runinto collisions (you won't in a 10-record lookup table, for example) then you need to take that into consideration
inyour designs, but there are many (usually obvious) cases in which it's safe to omit it. With UUID's that's a little
morecomplicated. 

I don't think anyone in this discussion is saying "Don't use UUID's!". Just be aware of their limitations and the
problemdomains where they are sensible to use. The same goes for sequences. 
It would, for example, be (obviously) pretty insane to use UUID's for a 10-record lookup table. There's plenty of
examplesin this thread where they shine, I don't need to repeat that. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d25777f11541886517442!