Re: partial VACUUM FULL

> Christopher Petrilli wrote:
>> Unfortunately, with some things, and I'm not sure why, as I don't
>> understand the VACUUM stuff that well, I had assumed that running VACUUM
>> ANALYZE nightly would be enough.  After I noticed that a specific
>> database (very transient data) had bloated to nearly 7Gb, I ran VACUUM
>> FULL on it, which took an hour or so, and it was reduced down to under 1GB.

Evidently, nightly is not enough.  How often are you turning over the
content of the database's largest tables?  If you want to keep the
wasted space to, say, 50%, then you need to vacuum about as often as
the application will update every row of the table once.  Then you have
at most one dead tuple for every live tuple.

Bill Moran <wmoran@potentialtech.com> writes:
> vacuum's ability to clean things up has certain limitations.  One is that it
> can't vacuum dead tuples that are still locked up in a transaction.

Correct.  If you have clients that are sitting around holding open
transactions for very long periods (comparable to your inter-vacuum
interval) then you need to fix those clients.

> Another
> is that the setting of vacuum_mem (in postgresql.conf) limits the amount of
> cleanup that vacuum can do.

This is completely untrue.  Increasing vacuum_mem will likely make
things faster on large tables (by avoiding the need for multiple passes
over the indexes).  It will not change the end result though.

            regards, tom lane