"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> So it's a good idea to allocate 20 to 50% more than what vacuum
> verbose says you'll need for overhead. also keep in mind that vacuum
> verbose only tells you what the one db in the server needs.
No, that's not true --- the numbers it prints are cluster-wide.
However, they are only guaranteed to be up-to-date with respect
to the DB you just finished vacuuming. So the right strategy
is to vacuum all your active DBs, using VERBOSE on the last one,
and then believe what it tells you (plus a suitable fudge factor
for future growth, as Scott says).
> Note that the preferred state for pgsql is to
> have 10-25% free space in frequently updated tables, rather than
> removing it all with reindex / vacuum full.
Right. If you are doing frequent vacuum fulls, you are really trying to
keep the DB smaller than its ideal size --- and that means you might be
getting an unrealistically small estimate from this process. Try to do
without the VAC FULLs for awhile and see if more frequent plain vacuums
aren't enough.
It seems likely that Karl needs to increase the aggressiveness of
autovac's threshold and scale parameters, so that it runs more often.
regards, tom lane