Re: Surrogate keys (Was: enums) - Mailing list pgsql-hackers

From Lukas Smith
Subject Re: Surrogate keys (Was: enums)
Date
Msg-id 43C91BDF.5010103@pooteeweet.org
Whole thread Raw
In response to Re: Surrogate keys (Was: enums)  (Rod Taylor <pg@rbt.ca>)
List pgsql-hackers
Rod Taylor wrote:

>> If you still declare the natural key(s) as UNIQUEs, you have just made 
>> performance worse.  Now there are two keys to be checked on UPDATEs and 
>> INSERTs, two indexes to be updated, and probably a SEQUENCE too.
> 
> Indeed. Using a surrogate key is not free and that is why it would be
> something the DBA would specify during table creation.
> 
> The main goal would be to give the option of using a surrogate key
> without being forced to expose it to the applications using the
> database. It is a feature akin to table spaces in that it can help
> performance but without the application or standard users knowing why.

Just this morning my father came to me (he is getting into SQL now that 
he is retired) with an issue where a surrogate key probably makes sense. 
He is storing a tree of plant families that can get fairly deep. The 
primary key is the scientific name. In order to improve performance and 
get rid of the recursive lookups he currently does he now wants to use 
materialized paths (<parent name>/<sub name>/<sub sub name>). He decided 
not to go with nested paths since that makes it very hard to hand fix 
things in the tree structure. Obviously using the scientific name in the 
materialized paths can quickly give you a really wide column if you have 
a fairly deep tree. In that case it could be beneficial to introduce a 
surrogate key.

The only annoying bit is that he frequently needs to sync with an 
external database where they use no surrogate key so the import slows 
down because he needs to check if a surrogate key has been introduced 
for every given scientific name before writing to the database.

regards,
Lukas


pgsql-hackers by date:

Previous
From: Rod Taylor
Date:
Subject: Re: Surrogate keys (Was: enums)
Next
From: mark@mark.mielke.cc
Date:
Subject: Re: Surrogate keys (Was: enums)