Re: is autovacuum recommended?

Hi,

On Thursday 09 July 2009 19:25:15 Willy-Bas Loos wrote:
> Whenever i start a big action, like inserting millions of recs or doing a
> large update, the autovacuum fires on top of that.
You can configure autovacuum to use less resources.
http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

> 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.
That sounds like misconfiguration. PG got OOM killed?

It is also possible that your max_fsm_pages were to small, so autovacuum hat
to start all over.

> My database is growing, so really freeing up space isn't such an issue.
> What I want is optimal performance.
Well, not doing vacuum regulary can lead to significant bloat which makes
lookups (and thus updates/deletes) way much slower.

> Of course autovacuum is more generic, but for me i think the daily scheme
> will be sufficient.
That may be. Hard to say without knowing more though. How fast are your tables
changing? Inserts only or also updates/deletes?

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

> vacuumdb -azf (once a week)
Using -f is normally not a good idea. It takes an exclusive lock on the tables
- so you cannot do anything in those tables.

Which version of PG are you using?

Another possibility would be to upgrade to 8.4 - keeping track of free space
works quite different there.

Andres