On Mon, 22 Jun 2009 11:40:08 +0200 Martijn van Oosterhout <kleptog@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
