Re: partial VACUUM FULL

Christopher Petrilli wrote:
>
> On Mar 23, 2004, at 3:57 PM, Bill Moran wrote:
>
>> Joseph Shraibman wrote:
>>
>>> If I cancel a VACUUM FULL, is the work that was done up until that
>>> point thrown away? I have a table that needs vacuuming but I can't
>>> accept the downtime involved in vacuuming.
>>
>> Not sure about the "cancel vacuum full" question, but I had some other
>> thoughts
>> for you.
>>
>> Keep in mind that a plain vacuum can do a lot of good if done
>> regularly, and
>> it doesn't lock tables, thus the database can be in regular use while
>> it's
>> run. As a result, there is no downtime involved with regularly scheduled
>> vacuums.
>
> 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.
>
> Is there a better way to deal with this? This is on 7.3, and I wonder
> if 7.4 fixed that, but it's been hard to schedule time to upgrade.

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. Another
is that the setting of vacuum_mem (in postgresql.conf) limits the amount of
cleanup that vacuum can do.

I suspect that the vacuum_mem setting is the problem in your case. Possible
solutions are:
1) Run vacuuum full instead
2) Run vacuum more frequently
3) Increase the value of vacuum_mem

Each of these has it's disadvantages. You should do a little research into
how vacuum works with vacuum_mem to determine which is the best approach for
your circumstance.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com