Re: auto vacuum

In response to Herouth Maoz <herouth(at)unicell(dot)co(dot)il>:

> 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? 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.
>
> 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?

Did I understand the original problem correctly? I thought you were saying
that _lack_ of analyzing was causing performance issues, and that running
vacuum analyze was taking too long and causing the interval between
analyze runs to be too long. If that is the case, then I still think
manually scheduling vacuum and analyze to run in separate threads is
the best approach.

If the problem is that overall performance slows too much when vacuum is
running, then you'll probably have to get more/faster hardware. Vacuum
has to run occasionally or your table will bloat. Bloated tables perform
lousy and waste a lot of space, and a table that is getting updates and
inserts without vacuuming will grow without bound, even if you delete
records. It's kind of like the trash bin on many desktop OSes ... when
you DELETE a record from the DB, it goes into the trash bin, when you
run VACUUM, the trash is emptied (Yes, I know that's not _exactly_ how
vacuum works, but I'm just drawing a parallel here)

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