Re: Primary Key Index Bloat?

On 11/17/2013 11:48 PM, David Johnston wrote:
> I am guessing that it is the need for the index to point to new versions of
> the physical record that the index is churning so much and causing this kind
> of bloat?

Bingo.

> I am preparing to REINDEX the unique index and DROP the non-unique one over
> the same field - probably Tuesday evening. Does everything I am saying here
> sound kosher or would someone like me to provide additional information?
...
> I have mostly left VACUUMing to auto-vacuum though the occasional manual one
> has been performed. Never performed VACUUM FULL.

REINDEX will only rebuild the indexes (obviously) and VACUUM FULL would
clean up any bloat in the indexes and the table itself. Either would
require an exclusive lock on the table.

If the exclusive lock is a problem, you might look in to pg_repack as an
option as well. It essentially recreates the table in parallel then
swaps in the newly built one. It only requires an exclusive lock for a
moment.

- Chris