Re: is autovacuum recommended?

Willy-Bas Loos escribió:
> Hi,
>
> 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.

1. the server should never crash. If it does you have another problem
that perhaps is triggered by the high load. Investigate that.

2. what PG version are you running? What operating system?

3. You can turn autovacuum off for particular tables. This would allow
you to have it process most tables, and manually vacuum only the
specific tables that are causing you grief.

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

4. VACUUM FULL is (almost) never needed unless your server is not
properly tuned. If you find a need for it, it's a symptom that you need
to tweak something somewhere. Need more details about the problem to
give you more advice.

2a. Upgrading to 8.4 may give you several benefits in this area.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.