Ah okay, that makes perfect sense and connects the dots in my head with regards to temp tables and much more. Thanks so much for the explanation and the reasoning behind why we reserve the space on the disk (i'm guessing its the same reasoning for other entities as well, for instance, WAL files).
Again, thank you so much, it has been such a help! I hope you're safe, do have a great day!
Hritik Gupta <hritik122@gmail.com> writes:
> 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?
No. We *reserve space* on disk, typically by writing zeroes. But actual
data would only get spilled there if we run out of temp buffer space.
It's entirely possible for a temp table's data to stay in memory for
its whole existence.
For regular tables, there are effects such as checkpoints that would
cause data to get written out from shared buffers periodically, even
if there's plenty of buffer space. That doesn't apply to temp tables.
The reason for reserving space is to try to avoid having to deal with
ENOSPC failures during buffer spills. On newer filesystems that
can happen anyway, of course, but it's still useful on non-COW
filesystems.
regards, tom lane
--