Skip site navigation (1) Skip section navigation (2)

Re: autovacuum not freeing up unused space on 8.3.0

From: Stuart Brooks <stuartb(at)cat(dot)co(dot)za>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: autovacuum not freeing up unused space on 8.3.0
Date: 2008-02-27 13:51:06
Message-ID: 47C56ACA.1020006@cat.co.za (view raw or flat)
Thread:
Lists: pgsql-general
>>> 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.
>
>   

That does the trick and gets the table size down to what I'd expect from 
a 'clean' run. Now I just need to run a few tests to work out what a 
stable size is for a table with this many rows. Thanks for all the help 
tracking this down. It's really appreciated :)

Kind regards
 Stuart


In response to

pgsql-general by date

Next:From: Richard HuxtonDate: 2008-02-27 14:41:09
Subject: Re: PostgreSQL-installation-problem on Windows XP Home edition
Previous:From: Jan ChristensenDate: 2008-02-27 13:41:42
Subject: PostgreSQL-installation-problem on Windows XP Home edition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group