On 01/15/2014 04:24 PM, Tom Lane wrote:
> Lists <lists@benjamindsmith.com> writes:
>> Our app makes extensive use of temp tables, and this causes a
>> significant amount of bloat that can often only be cleared with a manual
>> vacuum process. We're looking for a better way that doesn't involve
>> locking, we found pg_repack and pg_reorg and were wondering if anybody
>> here could weigh in on using this instead of using vacuum?
> A temp table is only accessible to the owning process, so if you're hoping
> for vacuuming of it to happen silently in background, you'll be sadly
> disappointed. The speed advantage of a temp table come exactly from not
> having to worry about concurrent access, so this isn't a tradeoff that can
> easily be adjusted.
>
> regards, tom lane
Tom,
The process(es) creating the temp tables are not persistent, so the
issue isn't trying to clean up bloat from a long running process, it's
clearing out the cruft that results from creating temp tables, loading a
bunch of data, then dropping the table, either explicitly or when the
connection is terminated. This causes PG disk usage to climb without
causing any change in pg_dump output.
I was wondering if anybody else had used either of these projects
(pg_repack or pg_reorg, though reorg seems to be unsupported) and if so,
how successful they had been.
-Ben