Re: estimating the need for VACUUM FULL and REINDEX

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.

AFAIR what it did was precisely to remember the numbers from the last
vacuum, which was cumbersome and not very effective (because they were
lost on restart for example). Also, the new autovac has some features
that the old one didn't have. Ability to set per-table configuration
for example.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.