Skip site navigation (1) Skip section navigation (2)

Re: VACUUM FULL versus system catalog cache invalidation

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: VACUUM FULL versus system catalog cache invalidation
Date: 2011-08-12 20:26:02
Message-ID: CA+U5nMK6t-2XKCii24SxM1pL+5x5=oRHFDuOV0Q-T2MEYLoy2g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, Aug 12, 2011 at 7:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Jeff DavisDate: 2011-08-12 20:26:58
Subject: Re: Extra check in 9.0 exclusion constraint unintended consequences
Previous:From: PostgreSQL - Hans-Jürgen SchönigDate: 2011-08-12 20:21:25
Subject: Re: index-only scans

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group