Re: VACUUM FULL on 24/7 server

Christopher Browne wrote:
> aserba(at)gmail(dot)com (Aleksey Serba) wrote:
>
>> Hello!
>>
>> I have 24/7 production server under high load.
>> I need to perform vacuum full on several tables to recover disk
>> space / memory usage frequently ( the server must be online during
>> vacuum time )
>
>
> The main thought is: "Don't do that."
>
> It is almost certainly the wrong idea to do a VACUUM FULL.
>
> Assuming that the tables in question aren't so large that they cause
> mass eviction of buffers, it should suffice to do a plain VACUUM (and
> NOT a "VACUUM FULL") on the tables in question quite frequently.

This is easy to say and almost impraticable. I run a 7.4.5 with the autovacuum:

pg_autovacuum -d 3 -v 300 -V 0.5 -S 0.8 -a 200 -A 0.8

I have also a "vacuumdb -z -v -a" running each six hours and if i don't execute
a vacuum FULL for one weeks I collect almost 400 MB of dead rows :-(
For this reason even with a 7.4.5 I'm obliged to run a vacuum full at least once
a week and a reindex once a month.

And my FSM parameters are large enough:

INFO: free space map: 141 relations, 26787 pages stored; 26032 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 2000000 pages = 11780 kB shared memory.

Regards
Gaetano Mendola

PS: I do not have any "idle in transaction" connections around.