Re: Primary key vs unique index

Thanks for the reply, that's what I was looking for. I just wasn't sure if there was another compelling advantage to use primary keys instead of a unique index.

-----Original Message-----
From: Scott Ribe [mailto:scott_ribe(at)elevated-dev(dot)com]
Sent: Thursday, March 17, 2011 12:13 PM
To: Voils, Steven M
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Primary key vs unique index

On Mar 17, 2011, at 6:19 AM, Voils, Steven M wrote:

> Is there a fundamental difference between a primary key and a unique index? Currently we have primary keys on tables that have significant amounts of updates performed on them, as a result the primary key indexes are becoming significantly bloated. There are other indexes on the tables that also become bloated as a result of this, but these are automatically rebuild periodically by the application (using the concurrently flag) when read usage is expected to be very low.
>
> We don't want to remove the unique constraint of that the primary key is providing, but the space on disk will continue to grow unbounded so we must do something. Can we replace the primary key with a unique index that could be rebuilt concurrently, or would this be considered bad design? The reasoning behind this would be that the unique index could be rebuilt concurrently without taking the application down or exclusively locking the table for an extending period of time. Are there other advantages to a primary key outside of a uniqueness constraint and an index?

So, you can't use REINDEX because it lacks the CONCURRENTLY option, and that would lock too much for too long?

The only thing the primary key designation provides beyond not null & unique is the metadata about what is the primary key. Which for example in the db allows foreign key constraints to be created without specifying that column. And some ORM/apps/frameworks can automatically make use of the information as well. I like having them for clarity, but you really can do away with them if your deployment needs to do so.

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice