Re: auto vacuum

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

> > 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)
> >
> I understand. Assuming that I have enough disk space, and I vacuum once
> a day instead of every 20 minutes. Does that cause deterioration in
> performance?

It can, but it depends on the actual circumstances. As with many things,
there's a threshold. Exactly where that threshold is depends on your
hardware, the actual amount data changing, the nature of your queries,
etc ... to the point where the only way to be sure is to try and see.

When a table goes a long time between vacuums, it accumulates "dead
tuples". This is space in the table that doesn't have any usable data
in it. As the percentage of dead tuples to live ones increases, the
system has to seek through more empty space to find real data, and
performance suffers. Vacuum cleans up what dead space it can, and
marks the rest as available for reuse so new rows will reuse it instead
of making the table larger.

If there's only a little dead space, the performance hit is minor,
frequently it's too small to worry about. But as the interval between
vacuums increases, the amount of dead space increases, and the system
can cross a threshold where that dead space presents a significant
performance problem. If vacuum runs frequently, the amount dead space
says small enough not to present a problem.

Of course, the question is "how often is often enough" and again, the
answer is dependent on a number of usage factors such that it's difficult
to offer any advice other than, "Try some different settings and see
how it turns out". However, given the information you've provided, I
would suspect that daily is not going to be often enough.

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