Re: Vacuum Problems

On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew(at)zeut(dot)net> wrote:
> Rafael Domiciano wrote:
>>
>> I'm not using autovacuum. Regular vacuum goes ok.
>> To see the last 10 lines of verbose i will need to run vacuum tonight
>>
>> If a run a reindex before the vacuum full, increase the "speed" of doing
>> vacuum? I found something about it googling.
>
> It might help a bit, but by the end of VACUUM FULL you would need to run
> reindex again as VACUUM FULL tends to cause a lot of index bloat. It is
> normal for tables to have some slack space, so if you do a regular vacuum
> every day (or let autovacuum) it's normal for the table to be a bit bigger
> than after a VACUUM FULL, but they should ready steady state and stop
> growing.

But there are certain use cases that would be classified as
pathological in nature, that you can't handle with regular vacuum.
It's only when you've proven that that's your case, and you can't
program around it, that you should start using vacuum full though.
vacuum full is so expensive in terms of time the system is largely
unusable combined with the need to run reindex or replace the whole
thing with cluster, that if regular or autovacuum can handle the load,
then that's what you do.

I've only seen cases where things like large imports were using a
shared table where it would get bloated insanely if three or four
imports were running at the same time with the occasional update with
no where clause. Since you can't truncate the table, because it's
shared with other imports, you have to vacuum it, but if you bloat it
by 10x or 100x normal size in 30 seconds, no amount of regular
vacuuming will help.

So, users need to understand why they're always asked if they're
running autovacuum or not. It's like asking someone with a
nonfunctioning dryer if they've cleaned the lint trap. It's just
something we assume someone should try first unless there's a good
reason not to. Because it does work so well most of the time. I run
autovacuum. I also email myself the output from vacuum verbose every
week, to look through and see how the tables are looking. A quick
look near the end tells you if you're mostly ok, and quick
investigation can find bloated tables pretty fast.

So, for the OP, have you tried autovacuum, and why aren't you using
it. Most of the time people aren't running it it's for erroneous
reasons.

Also, look into updating to 8.3 or above. With its HOT update
mechanism, and autovacuum enabled by default it handles these
situations quite easily.