Re: UUID as primary key

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.

Cheers,
mark

--
Mark Mielke<mark@mielke.cc>