Josh Berkus <josh@agliodbs.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