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.