"Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> http://linux.inet.hr/optimize_postgresql_database_size.html
> Personally I wouldn't think that a size-difference of roughly
> 20% between vacuum/re-index and drop/restore warrants
> the procedure, but the times he mentions?
I think the short answer is that autovacuum was failing to keep up,
else his database wouldn't have got to that size in the first place.
There are a number of likely reasons for this:
* It sounds like he was just using the default autovacuum parameters,
which are very unaggressive and don't really result in enough vacuum
commands (especially in the contrib version --- 8.1's integrated
autovac uses more aggressive parameters by default, and I suspect
we'll kick it up another notch in 8.2).
* If you don't have enough FSM space for your database, then all the
vacuuming in the world won't stop bloat. He doesn't say anything about
having checked the FSM settings...
* As noted in the comment that's already there, the external contrib
form of autovacuum isn't as reliable as one could wish.
Basically, if you want to rely on autovac, you want to be using PG 8.1
or later, and you do need to pay some attention to the autovac and
FSM settings. It's not yet a completely "fire and forget" solution.
regards, tom lane