Re: UUID as primary key

From: decibel <decibel(at)decibel(dot)org>
To: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
Cc: tsuraan <tsuraan(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: UUID as primary key
Date: 2009-10-17 03:51:50
Message-ID: 2E10A410-894F-4C53-AAEA-C8EC7B4E294E@decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Oct 10, 2009, at 10:40 AM, Mark Mielke wrote:
> On 10/10/2009 01:14 AM, tsuraan wrote:
>>> 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.
>>>
>> Ok, that's what I was hoping. Out of curiosity, is there a preferred
>> way to store 256-bit ints in postgres? At that point, is a bytea the
>> most reasonable choice, or is there a better way to do it?
>>
>
> Do you need to be able to do queries on it? Numeric should be able
> to store 256-bit integers.
>
> If you don't need to do queries on it, an option I've considered in
> the past is to break it up into 4 x int64. Before UUID was
> supported, I had seriously considered storing UUID as 2 x int64.
> Now that UUID is supported, you might also abuse UUID where 1 x 256-
> bit = 2 x UUID.
>
> If you want it to be seemless and fully optimal, you would
> introduce a new int256 type (or whatever the name of the type you
> are trying to represent). Adding new types to PostgreSQL is not
> that hard. This would allow queries (=, <>, <, >) as well.

If you want an example of that, we had Command Prompt create a full
set of hash datatypes (SHA*, and I think md5). That stuff should be
on pgFoundry; if it's not drop me a note at jnasby(at)cashnetusa(dot)com and
I'll get it added.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Haas 2009-10-17 10:56:56 Re: [PERFORM] Regarding facing lot of time Consumed by Socket.Poll()
Previous Message Greg Stark 2009-10-17 01:27:15 Re: Indexes on low cardinality columns