Re: auto vacuum

Herouth Maoz wrote:
> First, I'd like to thank Bill and Alvaro as well as you for your replies.
>
> Quoting Tom Lane:
> >Hmm. Given the churn rate on the table, I'm having a very hard time
> >believing that you don't need to vacuum it pretty dang often. Maybe the
> >direction you need to be moving is to persuade autovac to vacuum it
> >*more* often, not less often, so that the time needed to finish each
> >vacuum is small enough.
> Other than reclaiming disk space, is there any advantage to vacuum?
> Is a vacuumed table more efficient?

Yeah, it frees up space for new tuples. Whether it does anything or not
depends on you having long running transactions; if you do, maybe more
frequent vacuuming would just scan the table to no useful purpose.

> So far, every time it vacuums -
> which is around every 15-20 minutes under load conditions - it slows
> down processing. I think perhaps Bill's suggestion of just
> scheduling the vacuums myself (e.g. 1-2am, off peak) coupled with
> cost-based vacuuming might be a good answer? Unless I'm missing an
> important point about vacuuming.

I think you should continue with your current vacuuming strategy, or
perhaps make it more frequent, not less. To prevent it being a load hog
you can set vacuum_cost_delay to some nonzero value. Keep in mind that
vacuum_cost_delay affects all vacuuming tasks, including autovacuum,
whereas autovacuum_vacuum_cost_delay affects autovacuum but not manually
invoked vacuum (including the ones you invoke via cron of course)

> Alvaro and Bill both suggested scheduling analyzes on a
> minute-by-minute cron. Would this be no different than automatic
> analyze? No extra overhead for connection, perhaps?

No difference. Autovacuum also uses a connection; the only difference
is that it does it internally.

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