Re: temp tables, sessions, pgpool and disk

On Mon, Jun 22, 2009 at 09:53:59AM +0200, Ivan Sergio Borgonovo wrote:
> > The OS knows much less about what anonymous memory (memory not
> > backed by a file) "means" to a program and can't be as clever with
> > it. Swapping tends to be _much_ more CPU expensive than writing
>
> But issuing a write to disk Postgresql doesn't actually say anything
> more about what it is placing on the disk and how it is going to
> access it... and it is actually adding overhead to move it back and
> forward, no matter if this overhead happens on RAM or disk.
> Actually since temp table are private to the connection they should
> (?) be private to a postgresql process, so the OS should be able to
> do a good job.
> I don't see any atomicity constraint, so... if something fail while
> writing to RAM, as you said you shouldn't need a WAL.

For the record, temp tables are in fact handled differently, in
particular they are not stored in the shared_buffers, but instead are
in backend local (private) buffers, whose size is controlled by
temp_buffers. They are indeed not WAL archived, nor written to disk
unless needed.

So yes, small temp tables will likely stay in memory, but large temp
tables may spill to disk. There's no flushing or syncing so quite
likely they'll end up in the OS disk cache for a while. Once the temp
table is deleted, the file is deleted and the OS throws that data away.
So temp tables most likely won't use any disk I/O, but they *can* if
the need arises.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.