Skip site navigation (1) Skip section navigation (2)

Re: UUID column as pimrary key?

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
Cc: Radosław Smogura <rsmogura(at)softperience(dot)eu>, PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Re: UUID column as pimrary key?
Date: 2011-01-05 15:55:22
Message-ID: 20110105105522.4b3314a2.wmoran@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-general
In response to Scott Ribe <scott_ribe(at)elevated-dev(dot)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 simultaneously struck by lightning and killed in their sleep, and it is *far* more likely that all life on earth will be wiped out by an asteroid impact, and it is *far* more likely that the solar system orbits are not actually stable and earth will 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 vanishingly small that it is dwarfed by all sorts of risks that we all ignore because the chances are so low, including the chance that all drives in all your RAIDs across all your replicas will simultaneously fail on the same day that fires start 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.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2011-01-05 16:01:52
Subject: Re: UUID column as pimrary key?
Previous:From: pasman pasmańskiDate: 2011-01-05 15:55:20
Subject: Archive command and used size of wal

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group