Re: is autovacuum recommended? - Mailing list pgsql-general

In response to Willy-Bas Loos <willybas@gmail.com>:
>
> Whenever i start a big action, like inserting millions of recs or doing a
> large update, the autovacuum fires on top of that.
> It has some adverse effects on performance when i need it most. More than
> once a postgres service crashed on me because of it.
> Sure, it had too little memory, but it wouldn't have happened if it wasn't
> for the autovacuum.

If autovacuum is interfering with performance, you have two basic choices:
disable autovac or add hardware (assuming your system is already optimally
tuned).

> Should I keep autovacuum on, or is it better to run a script like:
> vacuumdb -az (daily except one day)
> vacuumdb -azf (once a week)

VACUUM FULL is seldom a good choice for a scheduled activity.  That being
said, there are some corner use cases where a regular VACUUM FULL is a
good idea.

Also, it's likely that a daily vacuum won't be enough.  If that were the
case, then autovac wouldn't be kicking off as often as it does.  Once you
start vacuuming multiple times per day, you're back to interfering with
performance again.  However, if you're doing it on a controlled schedule,
it's possible that you can schedule it in such a way that it's less
intrusive.

Overall, however, you're probably going to have to solve the problem by
adding hardware, or better tuning your system.

> My database is growing, so really freeing up space isn't such an issue. What
> I want is optimal performance.

If you _only_ do inserts, then vacuum isn't required at all.  If you do _ANY_
UPDATE or DELETE operations, then you still need vacuum or your filesystem
will fill up with data that's no longer used.  If autovacuum is kicking off,
then you need to do vacuums, as autovac isn't random, it uses statistics on
the usage of tables to determine if it's needed.

You can also manually vacuum as part of your application.  For example,
vacuum a table manually after a bunch of UPDATEs.  If you do this
intelligently, you can leave autovacuum enabled, and it will only pick
vacuum tables that aren't done manually.

> Of course autovacuum is more generic, but for me i think the daily scheme
> will be sufficient.

Unlikely.  Unless your database sees very few updates, daily vacuum probably
isn't often enough.  However, your usage pattern will dictate that.

> A connection told me it would be better to enable the autovacuum, because it
> does more than the above script. Can anyone verify that?

Autovacuum is smarter -- it won't vacuum tables that don't need it, whereas
the above script vacuums everything, even if it doesn't need it.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/