Hi Tom, thanks a lot for the reply and clearing this out for me!
I did some digging and figured that the backend file is created as soon as the create temp table is executed like you mentioned (with relpersistence as RELPERSISTENCE_TEMP, further calling smgrcreate() to create the backend file).
https://doxygen.postgresql.org/storage_8c_source.html#l00118
https://doxygen.postgresql.org/smgr_8c_source.html#l00333
Q=> So does that mean postgres does not have *only* “in memory” temporary tables in any case?
At best, what it is going to do is to cache the temp. table pages in the temp buffer, but the blocks are still there on the storage, its just cached in memory for the time being, no matter how small the temporary table is. is it correct?
There are conflicting information around this and postgres docs are also not very clear with regards to temp tables and how its handled.
In the below forum discussion as well, it is mentioned that
“For a small table the data will be in the memory, For a large table if data is not fit in memory then data will be flushed to disk periodically as the database engine needs more working space for other requests.
..
The memory is controlled by temp_buffers parameter”
https://www.postgresql.org/message-id/CALnrrJThsS3ZshTvsUOpBNbSQKkRWSnRaPS9CtVHwYUJh4%2Bgww%40mail.gmail.comQ=> I might seem to be misunderstanding the working here, but flushing from the temp buffers to the disk by checkpointer will be done every time the checkpointer kicks in, and not only if the temp_buffer is full/exceeded, right?
Thanks again for all the help!