Re: uuid type for postgres - Mailing list pgsql-hackers

From Greg Stark
Subject Re: uuid type for postgres
Date
Msg-id 873bohwz21.fsf@stark.xeocode.com
Whole thread Raw
In response to Re: uuid type for postgres  (mark@mark.mielke.cc)
List pgsql-hackers
mark@mark.mielke.cc writes:

> Eventually, SERIAL wraps around. So you switch to SERIAL8. At the point that
> you have SERIAL8, you aren't worried terribly about disk space, and you
> realize there is usually no benefit at all to the numbers being ordered so
> closely.

a) Except for trivially small applications you are _always_ worried terribly  about disk space. The more money you
spendon high end raid arrays the  *more* you're worried about an incremental drain on performance.
 

b) You only have to go to SERIAL8 for the few tables that actually have that  many records. You may well still have
SERIALdata types in 99% of your  tables.
 

c) You underestimate the cost of the added space. Don't forget it's not just  an extra few bytes in the primary key.
It'salso quadrupling the size of  your primary key index (doubling over bigint).
 
  Most importantly it's also adding a few extra bytes to every foreign key  column in every table that references that
primarykey. For very relational  databases with tables doing things like many-to-many joins or having 4+  foreign key
referencingcolumns increasing all those integers to be 16  bytes increases the size of your database *immensely*.
 

> Why not pick a scheme that is based on time? Perhaps create a revision code
> field to deal with objects created simultaneously from the same source. And
> why not identify the source to prevent collisions from multiple sources? If
> we go from 8 bytes, to 16 bytes, we can encode all of this information
> neatly. Welcome, UUID. :-)

Welcome to exactly the abuse that people are fearing if it were included as a
built-in type.

Personally I don't think the argument that some people might abuse it is a
good reason not to provide it. There are uses for which it's very effective --
even necessary. And I think it's important enough for the people that need it
that it should be considered a fundamental database feature these days. 

The people who will abuse it (like yourself, imho) will always exist and the
more powerful the tool the bigger the holes in their feet. Shouldn't stop us
from having powerful tools when we need them.

-- 
greg



pgsql-hackers by date:

Previous
From: "Jonah H. Harris"
Date:
Subject: Re: uuid type for postgres
Next
From: Tom Lane
Date:
Subject: Re: [ADMIN] How to determine date / time of last postmaster restart