Re: Keeping temporary tables in shared buffers

Asim Praveen <apraveen(at)pivotal(dot)io> writes:
> We are evaluating the use of shared buffers for temporary tables. The
> advantage being queries involving temporary tables can make use of parallel
> workers.

Hm...

> Challenges:

> 1. We lose the performance benefit of local buffers.

Yeah. This would be an absolute dead loss for any situation where you
couldn't get major parallelism wins, which I'm afraid would be often.
So then you have to think about how to transition smoothly between "rel
is in local buffers" and "rel is in shared buffers", bearing in mind that
ever having the same page in two different buffers would be disastrous.

> 2. Additional complexity in shared buffer manager - BufferTag needs to
> include backend ID to distinguish 'my' temp buffers from non-temp or
> 'others' temp buffers.

I think that would be a deal breaker right there, because of the
distributed overhead of making the tags bigger. However, I don't
actually understand why you would need to do that. Temp tables
have unique OIDs/relfilenodes anyway, don't they? Or if I'm
misremembering and they don't, couldn't we make them so?

> 3. Checkpointer needs to skip them for fsync but bgwriter needs to write
> them out.

Not really sure why that would be a "must", either. If the checkpointer
performs some useless writes, that's a bit of a performance drag, but
taking a performance hit to avoid it could be a net loss. The only reason
why you'd care about writing at all is to try to make the buffers clean
in case they have to be reclaimed for some other use --- and if the
checkpointer does such a write instead of the bgwriter, why's that bad?

regards, tom lane