Re: autovacuum not freeing up unused space on 8.3.0

[Stuart Brooks <stuartb(at)cat(dot)co(dot)za>]

> > Process 2:
> >  - running ANALYZE VERBOSE and pg_total_relation_size every second
> >     
>
>   
> > The result is that autovacuum appears to be canceled.
> >     
>
> Yes, that will pretty much guarantee that an autovacuum is never able to
> complete...
>
>   
That's what I figured. Since I won't be running ANALYZE under normal 
circumstances this should all work fine...
> > What concerns me is that once the size has grown, even a VACUUM FULL
> > doesn't recover the space. Regular external VACUUMs keep the table at
> > around 10MB but if I use autovacuum and it grows to 40MB, a VACUUM FULL
> > will only get it down to 35MB. Is it possible that a canceled autovacuum
> > could result in permanently lost space?
> >     
>
> 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.

Thanks
Stuart