Re: perfromance impact of vacuum

On Tue, Jul 15, 2003 at 11:53:50AM -0500, Bruno Wolff III wrote:
> On Tue, Jul 15, 2003 at 10:37:28 -0700,
> Jay O'Connor <joconnor(at)cybermesa(dot)com> wrote:
> >
> > Just curious but is the length of time to vacuum influenced more by the
> > size of the table or the number of dead tuples?
>
> I think the fraction of the table that is dead tuples is probably the best
> thing to look at for deciding when to vacuum (unless the tables are very
> small in which case checking for the number of blocks used may be better).

Actually, for a full vacuum, where the tuples are also has a significant
effect. If you have a large table and you do a large update so that the
table is as follows:

<------------------ data ------------------><deleted-tuples><updated-tuples>

The vacuum full will then move the updated over the deleted. But it does
this by scanning backwards through the table copying tuples forward over the
deleted ones. I have a sneaking suspicion Linux's caching algorithm in some
versions doesn't handle this very well. I've going to see if inserting into
another table, deleteing, vacuuming and then inserting back is faster.

Or don't do large updates on large tables.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato