Re: autovacuum not freeing up unused space on 8.3.0

Stuart Brooks wrote:

>> Are you measuring index as well as table size?  VACUUM FULL is no good
>> at compacting indexes.
>>
> I am measuring pg_total_relation_size which I believe includes indexes.
> How does one go about compacting indexes if a VACUUM doesn't do the
> trick? I see that a recommendation is to drop and recreate the indexes.
> If one has a system running 24-7, then this might not be feasible.

The simplest way is to use REINDEX INDEX, but it needs a strong lock.

The more complex way is to do

CREATE INDEX CONCURRENTLY index_2 ...    -- duplicating the original index
DROP INDEX index;

which does not need to grab a lock for a long period.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.