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

Re: UUID column as pimrary key?

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: Dennis Gearon <gearond(at)sbcglobal(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: UUID column as pimrary key?
Date: 2011-01-05 08:31:51
Message-ID: 05af4d43490ca257564b66dd9a3de971@softperience.pl (view raw or flat)
Thread:
Lists: pgsql-general
 On Tue, 4 Jan 2011 11:07:00 -0800 (PST), Dennis Gearon 
 <gearond(at)sbcglobal(dot)net> wrote:
> I haven't been able to find anywhere, easily, in the documentation
> using google
> where a list of allowed data types for primary keys is.
>
> So, UUIDs can be primary keys?
> Any issues wtih them on sorting or paging of index tables, etc.?

 Disadvantage
 * aren't ordered in way as records are added to DB.
 * 128bit length (PSQL stores them as 128bit value)
 * slower to generate you need to use random number generator
 * ... if you do select * on table with two uuids you will need to 
 scroll GUI to see data :)
 * ... really unhandy if you want to make manual updates :)

 Advantage:
 * simple to generate, and 128bit random is almost globally unique,
 * you have your id, before executing query, (in contrast to all this 
 autoincrement) so you may put it in dependant rows
 * almost every platform has UUID generator

 Advantage / disadvantage
 * depending on UUID generator, UUID can store some "privacy" 
 information e.g. MAC address of your card, such UUID.

 Personally I prefer pooled incremental id's. Fast, unique, you have Id 
 before query - but you need to write "code" by self.

> Also, the documentation says that UUIDs are 128 bit value, but never
> explicitly
> says that's how it's stored. Nor does it use one of the nice, blue 
> headered
> tables for UUID (or ENUM) showing storage and other attributes as it
> does for
> numeric, character,boolean, date/time, binary, monetary, geometric,
> or network
> types.
>
>
>
>  Dennis Gearon
>
>
> Signature Warning
> ----------------
> It is always a good idea to learn from your own mistakes. It is
> usually a better
> idea to learn from others’ mistakes, so you do not have to make them
> yourself.
> from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036'
>
>
> EARTH has a Right To Life,
> otherwise we all die.


In response to

Responses

pgsql-general by date

Next:From: tuanhoanganhDate: 2011-01-05 10:36:48
Subject: PlPerl ODBC connect error question?
Previous:From: John R PierceDate: 2011-01-05 03:46:40
Subject: Re: Disable Postgresql startup on boot (Windows XP)

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