Re: perfromance impact of vacuum

On Mon, 14 Jul 2003, Jay O'Connor wrote:

> All,
>
> What impact in performance does vacuum have on an active database? I'm
> being asked about this...or rather...someone is questioning the use of
> postgresql because of this

Hey, if they can't win on actual performance, they can always create straw
men, huh?

Seriously, the impact of vacuum tends to be pretty small. On systems with
good I/O bandwidth the general performance hit of a regulat (non-full)
vacuum is about 1 to 5% performance loss.

My guess is you could buy a big enough box to offset that for a fraction
of the cost of a commercial license for most databases.

On my dual 750MHZ PIII with 1.5 gig it generally grabs about 10% of one
CPU.

the biggest problem with running vacuums while doing other things is that
sometimes vacuuming flushes out postgresql's caches by grabbing all the
shared memory when vacuuming large tables.

Just a quick check shows pgbench -c 5 -t 200 running during a lazy
vacuumdb -a (vacuum all databases) is about 75 to 80% as fast as it is
when there is no vacuumdb running on my box.

My box does NOT have a lot of memory allocated for vacuuming, as we do
most of our vacuuming of whole databases at night.

Note that you can vacuum individual tables / indexes, so if the problem is
with one frequently changing table, the cost can be kept down by vacuuming
just that one problem table.

Good Performance tuning is far more important to postgresql performance
than is whether or not you're vacuuming mid day.