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

On 01/05/2011 11:57 AM, Bill Moran wrote:
> In response to Rob Sargent <robjsargent@gmail.com>:
>
>>
>>
>> On 01/05/2011 08:55 AM, Bill Moran wrote:
>>> In response to Scott Ribe <scott_ribe@elevated-dev.com>:
>>>
>>>> On Jan 5, 2011, at 8:05 AM, Bill Moran wrote:
>>>>
>>>>> Beyond that, the namespace size for a UUID is so incomprehensibly huge
>>>>> that the chance of two randomly generated UUIDs having the same value
>>>>> is incomprehensibly unlikely
>>>>
>>>> Yes, as in: it is *far* more likely that all of your team members and all of your client contacts will be
simultaneouslystruck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be
wipedout by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and
earthwill fly off into space... If you're worried about UUID collisions, then either your priorities are completely
wrong,or you live in a bomb shelter--that's not sarcasm by the way, it's simply true, the chance of a collision is so
vanishinglysmall that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including
thechance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that
firesstart in all the locations where your tapes are stored and all the sprinkler systems fail... (By "far" more
likely,I mean many many many orders of magnitude...) 

>>>
>>> That statement demonstrates a lack of investigation and/or consideration
>>> of the circumstances.
>>>
>>> I can't find my math or I'd reproduce it here, but consider this:
>>>
>>> If you have 50 devices, each generating 100 UUIDs per hour, and you'll
>>> keep records for 1 year, then your argument above is probably accurate.
>>>
>>> However, if there are 5000 devices generating 100 UUIDs per hour, and you'll
>>> be keeping those records for 10+ years, the chances of collisions near
>>> the end of that 10 year span get high enough to actually make developers
>>> nervous.
>>>
>>
>> But we're talking about a primary key.  Postgres guarantees the
>> uniqueness.  1 transaction  in 10^^100 rolls back due to a second
>> instance of an (otherwise/almost) uuid.  Big deal.
>
> That doesn't make any sense.  If you're using a single PostgreSQL instance,
> then why not just use the built in SERIAL mechanism that guarantees that
> you will NEVER have a conflict?
>
> In our case (and I expect it's the case with most people considering UUIDs)
> we're talking about independent devices that occasionally synchronize
> data between themselves.  These devices need to generate a unique ID
> of some sort without having to check with every other device.  This is
> one of the problems that UUIDs were intended to fix.
>
Indeed there is a finite space.  A very large, but finite space, just as
sequence has I suspect.  If your software cannot handle a rollback for
whatever reason, you have much bigger problem on your hand than the the
remote chance of a collision in uuid generation.

From wikipedia, "only after generating 1 billion UUIDs every second for
the next 100 years, the probability of creating just one duplicate would
be about 50%. The probability of one duplicate would be about 50% if
every person on earth owns 600 million UUIDs."