From: | "Dave Dutcher" <dave(at)tridecap(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Basic Q on superfluous primary keys |
Date: | 2007-04-18 18:09:57 |
Message-ID: | 01db01c781e4$c700bf30$2e00a8c0@tridecap.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2007-04-18 18:25:53 | Re: Long running transactions again ... |
Previous Message | Jim C. Nasby | 2007-04-18 18:07:59 | Re: a question about Direct I/O and double buffering |