Re: Temporary tables and disk activity

Martijn van Oosterhout <kleptog(at)svana(dot)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 :-(

regards, tom lane