Re: Trimming transaction logs after extended WAL archive failures

On Mar 27, 2014, at 5:29 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

> On Thu, Mar 27, 2014 at 1:42 AM, Steven Schlansker <steven(at)likeness(dot)com> wrote:
>>
>> On Mar 25, 2014, at 7:58 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>>
>>> Yea, vacuum just marks space as available for reuse it does not actually free space.
>>>
>>
>> I even knew that. Funny what you'll forget when the system is down and you're in a panic.
>>
>> This is actually something that has bit me on more than one occasion -- if you accidentally temporarily use too much space, it is *very* hard to back out of the situation. It seems that the only way to actually release space to the system are VACUUM FULL, CLUSTER, or to DROP objects. None of these can be executed without severe disruption to a running database. A cluster operation on any of our tables that are large enough to matter can easily run through the night.
> Yep, depending on your application needs you could actually avoid any
> periodic VACUUM FULL-like operations that need an exclusive lock on
> the objects it is cleaning by making autovacuum more aggressive. This
> makes your house cleaner by dropping the garbage at a higher
> frequency.

Yes, although this will not save you in a couple of notable cases. We have run into this at least by:

* Accidentally leaving transactions open for days
* Runaway process inserting data until the disk fills

So yes we should autovacuum more, but it is not a total solution.