Re: temp tables, sessions, pgpool and disk

On Mon, 22 Jun 2009 11:40:08 +0200
Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:

> 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.

Just to make it extra-clear to people unaware of pg internals...
since the second paragraph may seems to contradict the first one...

could be "nor written to disk unless needed" rephrased as:
even repeated UPDATE/INSERT won't issue writes (no matter if they end
up on disk or not, it won't issue writes to the OS) if the table fit
the buffer?

I see the default is somehow "large" (8M) and it is not pre
allocated. Looks nice.

> Have a nice day,

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it