Re: VACUUM FULL versus system catalog cache invalidation

On Fri, Aug 12, 2011 at 09:26:02PM +0100, Simon Riggs wrote:
> With HOT, there is very little need to perform a VACUUM FULL on any
> shared catalog table. Look at the indexes...
>
> I would a suggest that VACUUM FULL perform only a normal VACUUM on
> shared catalog tables, then perform an actual VACUUM FULL only in dire
> need (some simple heuristic in size and density). This avoids doing a
> VACUUM FULL unless it is actually necessary to do so. That has the
> added advantage of not locking out essential tables, which is always a
> concern.
>
> In the unlikely event we do actually have to VACUUM FULL a shared
> catalog table, nuke any cache entry for the whole shared catalog. That
> way we absolutely and positively will never get any more bugs in this
> area, ever again. Sounds harsh, but these events are only actually
> needed very, very rarely and hygiene is more important than a few
> minor points of performance.

This is a very optimistic view. My client makes heavy use of temp tables.
HOT and autovacuum are not sufficient to keep catalog bloat under control.
We run a daily script that calculates the density of the catalog and only
vaccum fulls those that are severely bloated. Here is a result from a
recent bloat check on one db. 'packed' is the number of pages needed for
the rows if they were packed, 'bloat' is the multiple of pages in use over
the number really needed.

relation | tuples | pages | packed | bloat
------------------+--------+-------+--------+-------
pg_class; -- | 4292 | 10619 | 114 | 93.2
pg_depend; -- | 25666 | 7665 | 217 | 35.4
pg_attrdef; -- | 6585 | 7595 | 236 | 32.2
pg_type; -- | 4570 | 8177 | 416 | 19.6
pg_shdepend; -- | 52040 | 7968 | 438 | 18.2

-dg
--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.