Re: Eliminating VACUUM FULL WAS: remove flatfiles.c

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: daveg(at)sonic(dot)net, Andrew Dunstan <andrew(at)dunslane(dot)net>, Greg Stark <gsstark(at)mit(dot)edu>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Simon Riggs <simon(at)2ndQuadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Eliminating VACUUM FULL WAS: remove flatfiles.c
Date: 2009-09-04 18:48:42
Message-ID: 24477.1252090122@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> I have a client who uses temp tables heavily, hundreds of thousands of
>>> creates
>>> and drops per day. They also have long running queries. The only
>>> thing that
>>> keeps catalog bloat somewhat in check is vacuum full on bloated catalogs
>>> a few times a day. With

> Actually, this is a good point ... if we dropped VACUUM FULL, we'd need
> to also be able to call CLUSTER (or VACUUM REWRITE) on the system catalogs.

I don't think I believe the claim above that vacuum full is actually
necessary. Reasonably aggressive regular vacuuming ought to do it.

We used to have a bug that caused row deletions during backend shutdown
to not get reported to the stats collector; which had the effect that
dead catalog entries for temp tables didn't get counted, and so autovac
didn't hit the catalogs often enough, and so you'd get bloat in exactly
this scenario. I suspect the claim that manual vacuum full is necessary
is based on obsolete experience from before that bug got stomped.
It's hardly an ideal solution anyway given what an exclusive lock on
pg_class will do to the rest of the system --- and a cluster-like
cleanup won't be any better about that.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2009-09-04 18:53:04 Re: Eliminating VACUUM FULL WAS: remove flatfiles.c
Previous Message Josh Berkus 2009-09-04 18:37:24 Re: Eliminating VACUUM FULL WAS: remove flatfiles.c