Re: UUID as primary key

On 10/09/2009 12:56 PM, tsuraan wrote:
> I have a system where it would be very useful for the primary keys for
> a few tables to be UUIDs (actually MD5s of files, but UUID seems to be
> the best 128-bit type available).  What is the expected performance of
> using a UUID as a primary key which will have numerous foreign
> references to it, versus using a 64-bit int (32-bit isn't big enough)?
>
> > From the uuid.c in adt, it looks like a UUID is just stored as 8
> consecutive bytes, and are compared using memcmp, whereas an int uses
> primitive CPU instructions for comparison.  Is that a significant
> issue with foreign key performance, or is it mostly just the size that
> the key would take in all related tables?
>    

The most significant impact is that it takes up twice as much space, 
including the primary key index. This means fewer entries per block, 
which means slower scans and/or more blocks to navigate through. Still, 
compared to the rest of the overhead of an index row or a table row, it 
is low - I think it's more important to understand whether you can get 
away with using a sequential integer, in which case UUID is unnecessary 
overhead - or whether you are going to need UUID anyways. If you need 
UUID anyways - having two primary keys is probably not worth it.

Cheers,
mark


-- 
Mark Mielke