From: Dave Dutcher <dave@tridecap.com>
To: pgsql-performance@postgresql.org
Subject: Re: Basic Q on superfluous primary keys
Date: Wed, 18 Apr 2007 13:09:57 -0500
Message-ID: <01db01c781e4$c700bf30$2e00a8c0@tridecap.com> (raw)
In-Reply-To: <b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.com>
I think a database with all natural keys is unrealistic. For example if you
have a table that refers to people, are you going to use their name as a
primary key? Names change all the time due to things like marriage,
divorce, or trouble with the law. We have tables with 20 million rows which
reference back to a table of people, and if I used the person's name as key,
it would be a major pain when somebody's name changes. Even if there is
referential integrity, one person might be referred to by 25% of the 20
million rows, so the update would take quite a long time. Also the table
will be filled with dead rows and the indexes will likely be bloated. If I
want to clean that up, it will take a vacuum full or a cluster which will
lock the whole table and run for hours. If I use a surrogate key, I can
change their name in one row and be done with it.
Just my 2 cents.
Dave
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-performance@postgresql.org
Cc: dave@tridecap.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <01db01c781e4$c700bf30$2e00a8c0@tridecap.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