Re: autovacuum

In response to Robert Fitzpatrick <lists(at)webtent(dot)net>:

> I have a large database used with our mail filter. The pg_dumpall
> results in about 3GB with this being the only database in the system
> besides templates and postgres.
>
> I do a vacuum every night after backup and it takes about an hour, is
> this normal for this size db?

"normal" is relative. If it's taking an hour to vacuum 3G, I would say
that either your hardware is undersized/badly configured, or you're
not vacuuming often enough.

> I also have autovacuum enabled and when it
> is running during the day, our mail queues will tend to fill up with
> slow response to the server. Should I have autovacuum on even if I am
> vacuuming the db every night?

I'm not aware of any problems with autovaccum and scheduled vacuum
working together.

That doesn't mean you're vacuuming often enough, however. Switch your
nightly vacuum to vacuum verbose and capture the output to see how much
work it has to do. Are your fsm settings high enough?

> Let me know if you need more specifics. Just trying to get some feedback
> on if my vacuum is taking too long or if both are necessary...thanks for
> the help!

How much RAM does the system have? What's your shared_buffer settings?
What's your maintenance_work_mem set to?

--
Bill Moran
http://www.potentialtech.com