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<mark(at)mielke(dot)cc>