Offtopic, database design; Was: Re: Get PostgreSQL installed as part of the distros

[Patrick Spinler]

> For example filling in a sequential primary
> key properly is a typical case, each database provides its own facility for,
> Oracle sequences, MSSQL identity columns, etc.
>
 
Just a note as a gainfully employed DBA:

_DO_NOT_USE_SEQUENTIALLY_INCREASING_COLUMNS_AS_PRIMARY_KEYS_ !!!!

Please.  They do extraordinarily bad things to the index structure on
disk.

A hint as to why: you can't balance an on-disk tree the same as an in
memory tree.  Ergo, they don't get balanced often (typically it's a
manual operation a DBA has to do).  Ergo, with a sequentially increasing
key you quickly have a badly balanced index, shit poor performance, and
the need for frequent database service interruptions to rebalance that
table.  It's similar for hash structured indexes (think hash bucket
overflows and overflow chains, here) and worst of all for ISAM's.

This is one of the most common things that dba's have to educate
developers about.  (And _way_ too many developers don't understand this
and want this feature, this explains why the market provides it, even
though it's a bad idea.)

My advice: if you think you need a sequence in a table, examine your
assumptions about the data carefully.  Why do you need the data
retrieved in sequence ?  The only case I have ever encountered have been
transaction timestamps.

If you still really need a sequence in a table, I suggest taking a
sequence and deriving a psuedo-randomized integer from it as a bitswap
calculation, and using that psuedo-randomized number as the primary key.

Sorry, but this is one of my hot buttons. :-(

-- Pat
 
p.s. Yes - timestamps do suffer from the same problem, but at least you
can be aware of it in your design, and make the timestamps just be a
secondary index instead of the primary, and thus be smaller and easier
to do periodic maintainance on it.

-- 
      This message does not represent the policies or positions
	     of the Mayo Foundation or its subsidiaries.
  Patrick Spinler			email:	Spinler.Patrick@Mayo.EDU
  Mayo Foundation			phone:	507/284-9485