From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Markus Bertheau <twanger@bluetwanger.de>,
	pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Wed, 21 Jul 2004 00:30:17 -0700
User-Agent: KMail/1.5.4
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
In-Reply-To: <1090325769.3241.27.camel@dicaprio.akademie1.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200407210030.17534.josh@agliodbs.com>

Markus,

Oh, so you want USEFUL answers.  OK.

> Now when I want to search for a type in types or another table that
> references types(type_id), under what circumstances is it advisable to
> have a surrogate integer key and not use the unique type name?

When using the actual name will be a performance problem.

> Is
> searching for an integer as fast as is searching for a string when both
> have an index? 

Not usually, no.   The index on the text values will simply be larger than the 
one on 4-byte INTs, which means it's "slower", assuming you run out of memory 
some of the time.   If your whole DB fits in RAM, it's not worth worrying 
about.

> How many records in the type table do I need to make a
> surrogate key a not unsignificantly faster way to retrieve a row? 

It needs to be large enougth that the difference in data types makes a 
difference in whether or not it will fit into sort_mem, and how likely it is 
to be already cached in memory.

> What
> about joins? 

Double jeopardy; you're using the column twice so double the storage 
difference.   Otherwise, it's just the same issue; does it still fit in RAM 
or not?

> Are these the right questions?

Also you'll want to consider the speed of CASCADE operations whenever a 
type_name changes.   If these changes occur extremely infrequently, then you 
can ignore this as well.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco