Re: 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
thathave significant amounts of updates performed on them, as a result the primary key indexes are becoming
significantlybloated.  There are other indexes on the tables that also become bloated as a result of this, but these
areautomatically rebuild periodically by the application (using the concurrently flag) when read usage is expected to
bevery low.  
>
> We don’t want to remove the unique constraint of that the primary key is providing, but the space on disk will
continueto grow unbounded so we must do something.  Can we replace the primary key with a unique index that could be
rebuiltconcurrently, or would this be considered bad design?  The reasoning behind this would be that the unique index
couldbe rebuilt concurrently without taking the application down or exclusively locking the table for an extending
periodof 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/frameworkscan automatically make use of the information as well. I like having them for clarity, but you
reallycan do away with them if your deployment needs to do so. 

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