Request a Demo
Home   >   mailing lists

Re: global temporary table (GTT) - are there some ideas how to implement it? - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: global temporary table (GTT) - are there some ideas how to implement it?
Date
Msg-id CAFj8pRD-SERziXaOj_f92CxTr5gaURJDaFr=_mh4HTy7W6F5tw@mail.gmail.com
Whole thread Raw
In response to Re: global temporary table (GTT) - are there some ideas how to implement it?  (Konstantin Knizhnik <knizhnik@garret.ru>)
List pgsql-hackers
Tree view


po 12. 1. 2026 v 10:16 odesílatel Konstantin Knizhnik <knizhnik@garret.ru> napsal:


On 12/01/2026 8:57 AM, Pavel Stehule wrote:
Hi
 

I do not completely understand how partitioning of system tables can
solve this problem.
Do you propose that each backend has its own (private) partition?
It seems to be impossible and can cause even worse catalog bloating
(instead of  one temp table we will have to create temp partitions for
multiple system tables).

yes - but what is important - each backend has its own global temp partitions. Then almost all metadata will be static and only statistic related will be temporary.

The backend related partitions have to be of global temp table, not local temp table (only then it makes sense).

The main target is removing bloat from the system catalog, and it is impossible without storing system data to GTT.

So do I correctly understand that at least pg_class and pg_statistics will have two partitions: "global" and "local" and last one will act as GTT: it has shared metadata but private data. It can work for pg_statistics, because it is actually only needed for the particular backend. 
But it will not work for pg_class, because all backends should observe information about this table, but each of them should see it's own relpages/reltuples.  So we need some kind of CoW here. Not sure that partitioning can solve this problem...


For this design all fields related to possibly unshared fields should be moved to a new table - and that table should be partitioned with a possible GTT partition. 


Also even if we manage to somehow solve this problem and provide private version of pg_statistics/pg_class, it is not clear who and when will fill this data.

It can be the same like now for temporary tables - they are empty until somebody runs a vacuum. 

The storage of GTT will be local, so autovacuum is not possible.

 Autovacuum is not processing temp tables now - and I didn't propose .

The proposed design doesn't try to solve some "automatic" vacuum or analyze temporary objects. 

The lifecycle of temporary tables is usually less than 1 minute so cron based design cannot work well, and I don't think using GTT can help. 

Missing ANALYZE for temporary tables is probably a common issue - if I can say (I found it as a customer issue more times). But it needs a different design than current autovacuum/autoanalyze.
Maybe the planner can raise a warning when trying to process tables without statistics (or planner can force ANALYZE on temp tables). I don't know. It is a different question - for this moment I want to expect so users are able to run ANALYZE manually.

Another question is if we need to be worried about choosing local or shared storage. Implementation of GTT is not a monthly game, and if Postgres moves from process to threads, then the question about choosing local or shared storage loses sense.

It will also solve other problems with temporary tables, i.e. connection pooling. 

This is another (but interesting) question - how the session and connections can be shared. I think there are the same issues like local temporary tables - so we don't need to open this question now.
At this moment I expect that the content of GTT doesn't survive DISCARD TEMP - and DISCARD TEMP can be implemented just by throwing local storage and local buffers.
 

pgsql-hackers by date:

Previous
From: Japin Li
Date:
Subject: Re: amcheck: support for GiST
Next
From: Michael Banck
Date:
Subject: Re: [PATCH] Expose checkpoint reason to completion log messages.