From: Josh Berkus <josh@agliodbs.com>
To: Markus Bertheau <twanger@bluetwanger.de>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Wed, 21 Jul 2004 00:30:17 -0700
Message-ID: <200407210030.17534.josh@agliodbs.com> (raw)
In-Reply-To: <1090325769.3241.27.camel@dicaprio.akademie1.de>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, twanger@bluetwanger.de
Subject: Re: surrogate key or not?
In-Reply-To: <200407210030.17534.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox