Re: estimating the need for VACUUM FULL and REINDEX

On Fri, May 11, 2007 at 01:25:04PM -0400, Alvaro Herrera wrote:
> Guillaume Cottenceau wrote:
> > Guillaume Cottenceau <gc 'at' mnc.ch> writes:
> >
> > > With that in mind, I've tried to estimate how much benefit would
> > > be brought by running VACUUM FULL, with the output of VACUUM
> > > VERBOSE. However, it seems that for example the "removable rows"
> > > reported by each VACUUM VERBOSE run is actually reused by VACUUM,
> > > so is not what I'm looking for.
> >
> > I've tried to better understand how autovacuum works (we use 7.4)
> > to see if a similar mechanism could be used in 7.4 (e.g. run
> > VACUUM ANALYZE often enough to not end up with a need to VACUUM
> > FULL).
> >
> > The autovacuum daemon uses statistics collected thanks to
> > stats_row_level. However, inside pg_stat_user_tables, the values
> > n_tup_upd and n_tup_del seem to be reported from pg startup and
> > never reset, whereas the information from previous VACUUM would
> > be needed here, if I understand correctly. Is there anything that
> > can be done from that point on with existing pg information, or
> > I'd need e.g. to remember the values of my last VACUUM myself?
>
> In 7.4 there was the pg_autovacuum daemon in contrib, wasn't there?  No
> need to write one yourself.

Correct. But one important note: the default parameters in the 7.4
contrib autovac are *horrible*. They will let your table grow to 3x
minimum size, instead of 1.4x in 8.0/8.1 and 1.2x in 8.2. You must
specify a different scale if you want anything resembling good results.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)