Re: Temporary tables and disk activity

Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > I don't think temporary tables have any special rules regarding disk
> > writes, so I'd expect them ot get written out like everything else.
>
> They'll be written out from PG's internal buffers, but IIRC they will
> never be fsync'd, and they definitely aren't WAL-logged.  (These
> statements hold true in 8.0, but not sure how far back.)
>
> In principle, therefore, the kernel could hold temp table data in its
> own disk buffers and never write it out to disk until the file is
> deleted.  In practice, of course, the kernel doesn't know the data is
> transient and will probably push it out whenever it has nothing else to
> do.
>
> One of the things on the TODO list is making the size of temp-table
> buffers user-configurable.  (Temp table buffers are per-backend, they
> are not part of the shared buffer arena.)  With a large temp-table arena
> we'd never need to write to the kernel in the first place.  Right now
> you could manually increase the #define that sets it, but it would not
> pay to make it very large because the management algorithms are very
> stupid (linear scans).  That has to be fixed first :-(

I assume you mean your TODO list because the official one has no mention
of this.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073