On Thu, Sep 03, 2009 at 07:57:25PM -0400, Andrew Dunstan wrote:
> daveg wrote:
> >On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote:
> >>I'm having a hard time believing that VACUUM FULL really has any
> >>interesting use-case anymore.
> >
> >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. Without that pg_class, pg_attribute etc quickly balloon
> >to thousands of pages.
>
> That's a rate of more than one create and drop per second. How does your
> client handle the fact that VACUUM FULL will exclusively lock those
> catalog tables? Without knowing more, it looks like a bit of a design issue.
I'd say it is several per second.
They wait for the catalog locks sometimes. This is not an interactive
application so that is somewhat acceptable. It also occasionally causes
deadlocks which is less agreeable.
There are various reasons for the heavy use of temps, mainly having to do
with loading external feeds or reusing intermediate query results in a series
of queries.
It would be great if there was a way to have temp tables that
did not get cataloged, eg local cache only.
-dg
--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.
