Re: Global temporary tables

Fantastic.

I'll put that high on my queue.

I'd love to see something like this get in.

Doubly so if it brings us closer to being able to use temp tables on physical read replicas, though I know there are plenty of other barriers there (not least of which being temp tables using persistent txns not vtxids)

Does it have a CF entry?

 

Nice to see that split out. In addition to giving the first patch more hope of being committed this time around, it'll help with readability and testability too.

To be clear, I have long wanted to see PostgreSQL have the "session" state abstraction you have implemented. I think it's really important for high client count OLTP workloads, working with the endless collection of ORMs out there, etc. So I'm all in favour of it in principle so long as it can be made to work reliably with limited performance impact on existing workloads and without making life lots harder when adding new core functionality, for extension authors etc. The same goes for built-in pooling. I think PostgreSQL has needed some sort of separation of "connection", "backend", "session" and "executor" for a long time and I'm glad to see you working on it.

With that said: How do you intend to address the likelihood that this will cause performance regressions for existing workloads that use temp tables *without* relying on your session state and connection pooler? Consider workloads that use temp tables for mid-long txns where txn pooling is unimportant, where they also do plenty of read and write activity on persistent tables. Classic OLAP/DW stuff. e.g.:

* four clients, four backends, four connections, session-level connections that stay busy with minimal client sleeps

* All sessions run the same bench code

* transactions all read plenty of data from a medium to large persistent table (think fact tables, etc)

* transactions store a filtered, joined dataset with some pre-computed window results or something in temp tables

* benchmark workload makes big-ish temp tables to store intermediate data for its medium-length transactions

* transactions also write to some persistent relations, say to record their summarised results  

How does it perform with and without your patch? I'm concerned that:

* the extra buffer locking and various IPC may degrade performance of temp tables

* the temp table data in shared_buffers may put pressure on shared_buffers space, cached pages for persistent tables all sessions are sharing;

* the temp table data in shared_buffers may put pressure on shared_buffers space for dirty buffers, forcing writes of persistent tables out earlier therefore reducing write-combining opportunities;