Re: VACUUM FULL versus system catalog cache invalidation

On Fri, Aug 12, 2011 at 7:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Right at the moment I'm leaning to approach #2.  I wonder if anyone
> sees it differently, or has an idea for a third approach?

You are trying to solve the problem directly, which seems overkill.

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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services