Re: Primary key vs unique index

Thanks for the reply.  I should have mentioned in the first post that we do delete significant amounts of the table
whichI thought was the cause of the bloat.  We are already performing automatic vacuums nightly. 

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Thursday, March 17, 2011 2:52 PM
To: Voils, Steven M
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Primary key vs unique index

On Thu, Mar 17, 2011 at 12:51 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Mar 17, 2011 at 6:19 AM, Voils, Steven M <steve@sensorswitch.com> 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.
>
> If you're experiencing bloat, but not deleting huge chunks of your
> table at a time, then you're not vacuuming aggressively enough

Or you're on 8.3 or before and blowing out your free space map.