Re: Unexpectedly high disk space usage

On Wed, Nov 7, 2012 at 3:15 PM, Lists <lists(at)benjamindsmith(dot)com> wrote:
> On 11/07/2012 12:42 PM, Tom Lane wrote:
>>
>> So you've turned off autovacuum, and are carefully not vacuuming the
>> system catalogs. That's your problem all right. Is there a
>> particularly good reason why this script isn't a one-liner "VACUUM"?
>
>
> Back in the 8.x days, we experienced "vacuum full analyze" occasionally
> causing other processes to hang/timeout.

That was your first mistake. By 8.0 the need for vacuum full was
almost zero. Except for instances where bloat got out of hand, vacuum
full should generally be avoided after 8.0. Regular vacuum should be
plenty, whether it's run by autovacuum daemon or a cron job. Until 8.3
autovacuum was single threaded so therefore often had trouble keeping
up with bloat. While vacuum full is a blocking operation plain
vacuums are not, so unless you REALLY need a vacuum full they should
be avoided.

> In an attempt to minimize the
> impact of the locking, we updated the script to vacuum one table at a time,
> which seemed to work well throughout the 8.x series. I'd happily accept that
> this conclusion may have simply have been wrong, but it worked well enough
> that nobody complained and life was good.

Yeah you still had blocking but it was probably less noticeable.

> After switching to 9.x, we read
> that the "full" vacuum was less useful and so the script was changed to
> "vacuum analyze $table" rather than "vacuum full analyze $table".

Yeah at that point you'd have been better off tuning autovacuum to be
more aggressive and let it do the job. Generally the time to call
vacuum by hand is right after you've done something like delete half
the rows in a large table.