Re: Global temporary tables

 

3. It is not possible to use temporary tables at replica.

For physical replicas, yes.

Yes, definitely logical replicas (for example our PgPro-EE multimaster based on logical replication) do not suffer from this problem.
But in case of multimaster we have another problem related with temporary tables: we have to use 2PC for each transaction and using temporary tables in prepared transaction is now prohibited.
This was the motivation of the patch proposed by Stas Kelvich which allows to use temporary tables in prepared transactions under some conditions.

 5. Inefficient memory usage and possible memory overflow: each backend

maintains its own local buffers for work with temporary tables.

Is there any reason that would change with global temp tables? We'd still be creating a backend-local relfilenode for each backend that actually writes to the temp table, and I don't see how it'd be useful or practical to keep those in shared_buffers.

Yes, my implementation of global temp tables is using shared buffers.
It was not strictly needed as far as data is local. It is possible to have shared metadata and private data accessed through local buffers.
But I have done it for three reasons:
1, Make it possible to use parallel plans for temp tables.
2. Eliminate memory overflow problem.
3. Make in possible to reschedule session to other backens (connection pooler).

Using local buffers has big advantages too. It saves shared_buffers space for data where there's actually some possibility of getting cache hits, or for where we can benefit from lazy/async writeback and write combining. I wouldn't want to keep temp data there if I had the option.

Definitely local buffers have some advantages:
- do not require synchronization
- avoid flushing data from shared buffers

But global temp tables are not excluding use of original (local) temp tables.
So you will have a choice: either to use local temp tables which can be easily created on demand and accessed through local buffers,
either create global temp tables, which eliminate catalog bloating, allow parallel queries and which data is  controlled by the same cache replacement discipline as for normal tables...

 

Default size of temporary buffers is 8Mb. It seems to be too small for
modern servers having hundreds of gigabytes of RAM, causing extra
copying of data between OS cache and local buffers. But if there are

thousands of backends, each executing queries with temporary tables,

then  total amount of memory used for temporary buffers can exceed

several tens of gigabytes.

Right. But what solution do you propose for this? Putting that in shared_buffers will do nothing except deprive shared_buffers of space that can be used for other more useful things. A server-wide temp buffer would add IPC and locking overheads and AFAICS little benefit. One of the big appeals of temp tables is that we don't need any of that.

I do not think that parallel execution and efficient connection pooling are "little benefit".

If you want to improve server-wide temp buffer memory accounting and management that makes sense. I can see it being useful to have things like a server-wide DSM/DSA pool of temp buffers that backends borrow from and return to based on memory pressure on a LRU-ish basis, maybe. But I can also see how that'd be complex and hard to get right. It'd also be prone to priority inversion problems where an idle/inactive backend must be woken up to release memory or release locks, depriving an actively executing backend of runtime. And it'd be as likely to create inefficiencies with copying and eviction as solve them since backends could easily land up taking turns kicking each other out of memory and re-reading their own data.

I don't think this is something that should be tackled as part of work on global temp tables personally.

My assumptions are the following: temporary tables are mostly used in OLAP queries. And OLAP workload  means that there are few concurrent queries which are working with large datasets.
So size of produced temporary tables can be quite big. For OLAP it seems to be very important to be able to use parallel query execution and use the same cache eviction rule both for persistent and temp tables
(otherwise you either cause swapping, either extra copying of data between OS and Postgres caches).

 

6. Connection pooler can not reschedule session which has created temporary tables to some other backend because it's data is stored in local buffers.

Yeah, if you're using transaction-associative pooling. That's just part of a more general problem though, there are piles of related issues with temp tables, session GUCs, session advisory locks and more.

I don't see how global temp tables will do you the slightest bit of good here as the data in them will still be backend-local. If it isn't then you should just be using unlogged tables.

You can not use the same unlogged table to save intermediate query results in two parallel sessions.

 

Definition of this table (metadata) is shared by all backends but data
is private to the backend. After session termination data is obviously lost.

+1 that's what a global temp table should be, and it's IIRC pretty much how the SQL standard specifies temp tables.

I suspect I'm overlooking some complexities here, because to me it seems like we could implement these fairly simply. A new relkind would identify it as a global temp table and the relfilenode would be 0. Same for indexes on temp tables. We'd extend the relfilenode mapper to support a backend-local non-persistent relfilenode map that's used to track temp table and index relfilenodes. If no relfilenode is defined for the table, the mapper would allocate one. We already happily create missing relfilenodes on write so we don't even have to pre-create the actual file. We'd register the relfilenode as a tempfile and use existing tempfile cleanup mechanisms, and we'd use the temp tablespace to store it.

I must be missing something important because it doesn't seem hard.

As I already wrote, I tried to kill two bird with one stone: eliminate catalog bloating and allow access to temp tables from multiple backends (to be able to perform parallel queries and connection pooling).
This is why I have to use shared buffers for global temp tables.
May be it was not so good idea. But it was one of my primary intention of publishing this patch to know opinion of other people.
In PG-Pro some of my colleagues think  that the most critical problem is inability to use temporary tables at replica.
Other think that it is not a problem at all if you are using logical replication.
From my point of view the most critical problem is inability to use parallel plans for temporary tables.
But looks like you don't think so.

I see three different activities related with temporary tables:
1. Shared metadata
2. Shared buffers
3. Alternative concurrency control & reducing tuple header size (specialized table access method for temporary tables)

In my proposal I combined 1 and 2, leaving 3 for next step.
I will be interested to know other suggestions.

One more thing - 1 and 2 are really independent: you can share metadata without sharing buffers.
But introducing yet another kind of temporary tables seems to be really overkill:
- local temp tables (private namespace and lcoal buffers)
- tables with shared metadata but local bufferes
- tables with shared metadata and bufferes

 

The drawback of such approach is that it will be necessary to
reimplement large bulk of heapam code.
But this approach allows to eliminate visibility check for temporary
table tuples and decrease size of tuple header.

That sounds potentially cool, but perhaps a "next step" thing? Allow the creation of global temp tables to specify reloptions, and you can add it as a reloption later. You can't actually eliminate visibility checks anyway because they're still MVCC heaps.

Sorry?
I mean elimination of MVCC overhead (visibility checks) for temp tables only.
I am not sure that we can really fully eliminate it if we support use of temp tables in prepared transactions and autonomous transactions (yet another awful feature we have in PgPro-EE).
Also looks like we need to have some analogue of CID to be able to correctly executed queries like "insert into T (select from T ...)" where T is global temp table.
I didn't think much about it, but I really considering new table access method API for reducing per-tuple storage overhead for temporary and append-only tables.

Savepoints can create invisible tuples even if you're using temp tables that are cleared on commit, and of course so can DELETEs or UPDATEs. So I'm not sure how much use it'd really be in practice.

Yehh, subtransactions can be also a problem for eliminating xmin/xmax for temp tables. Thanks for noticing it.


I noticed that I have not patched some extension - fixed and rebased version of the patch is attached.
Also you can find this version in our github repository: https://github.com/postgrespro/postgresql.builtin_pool.git
branch global_temp.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company