PROPOSAL: Fast temporary tables

Hello

There are applications that create and delete a lot of temporary
tables. Currently PostgreSQL doesn't handle such a use case well.
Consider the following benchmark/example.

postgresql.conf:

```
autovacuum = on
log_min_messages = debug2
```

temp-table.pgbench:

```
create temporary table tt1(x jsonb);
drop table tt1;
```

Benchmark:

# pgbench-server:

pgbench -h (ip) -f temp-table.pgbench -T 600 -P 1 -c 40 -j 12 test

# postgresql-server:

tail -f path/to/logfile | grep 'DEBUG: vacuuming'

At first everything is OK, PostgreSQL handles ~ 970 TPS. But after some
time this value starts to drop to 10-100 TPS, then return to normal. In
logfile we see:

```
DEBUG: vacuuming "pg_catalog.pg_class"
DEBUG: vacuuming "pg_catalog.pg_type"
DEBUG: vacuuming "pg_catalog.pg_index"
DEBUG: vacuuming "pg_catalog.pg_class"
DEBUG: vacuuming "pg_catalog.pg_type"
DEBUG: vacuuming "pg_catalog.pg_index"
DEBUG: vacuuming "pg_catalog.pg_depend"
DEBUG: vacuuming "pg_catalog.pg_type"
DEBUG: vacuuming "pg_catalog.pg_index"
DEBUG: vacuuming "pg_catalog.pg_class"
...

```

Catalog tables are bloating. But there was no real reason to write
anything to these tables in the first place since temporary tables
could be seen only from one session. Except to make possible for
autovacuum to find these tables.

I propose to solve described issue by introducing a new entity - fast
temporary tables (or maybe lightweight temporary tables? - name is
discussable):

create fast temporary table tt1(x jsonb);

Fast temporary tables work almost as usual temporary tables but they
are not present in the catalog. Information about tables is stored in
shared memory instead. This way we solve a bloating problem.

We should use *shared* memory so autovacuum could find these tables.
Note that state should be restored properly and acquired locks should
be released if one of backends terminates abnormally while accessing
shared memory.

Usually memory is considered an expensive resource. For these reason we
can't just change current behaviour of temporary tables. It would cause
a lot of problems for existing users. Also introducing a new type of
tables allows us to make some other changes. For instance, we could
drop trigger support for these tables if it would give us some sort of
benefit, e.g. better performance.

As I understand this feature is not too hard to implement. Basically
all usages (read and write) of catalog in context of temporary tables
should be found and carefully modified as described above. It reminds
me how interception of system API works. All procedures should receive
and return exact the same types of values as before, but implementation
should be changed a little bit.

Frankly so far I don't have a clear understanding which files exactly
would be modified, but I believe it would be at least:

* relcache.c
* genam.c --- systable_* procedures in particular
* heapam.c --- I would like to avoid this, but as I understand \d will
not see temporary tables otherwise

A few hints from people more experienced in this area would be
appreciated. Then we carefully check that everything works as expected
(indexes, autovacuum of temporary tables, etc), write regression tests
and we are done.

Here is what makes suggested approach in particular so interesting. I
think that using similar method in the future we could implement
writable temporary tables on replicas. This feature is very helpful in
OLAP tasks.

What do you think regarding described problem and proposed method of
solving it?

Best regards,
Aleksander

On Tue, Mar 1, 2016 at 8:55 PM, Aleksander Alekseev <
a(dot)alekseev(at)postgrespro(dot)ru> wrote:

> Hello
>
> There are applications that create and delete a lot of temporary
> tables. Currently PostgreSQL doesn't handle such a use case well.
> Consider the following benchmark/example.
>
>
FWIW, I and Pavel have been spending some time discussing global temporary
tables, and I have been taking a shot at it. This is pretty inline with
that.

The approach you suggest sounds fine. I am personally a tad concerned about
the extra overhead of the locks and sanity of concurrency for the in memory
cache as well. Something I am not too clear about (I need to read your
email again), is about cache invalidation. Do all pages stay in the memory
always?

Also, are you proposing to change the behaviour of syscaches to not write
out those pages to disk? Or do you create a new set of caches?

Regards,

Atri

Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> writes:
> There are applications that create and delete a lot of temporary
> tables. Currently PostgreSQL doesn't handle such a use case well.

True.

> Fast temporary tables work almost as usual temporary tables but they
> are not present in the catalog. Information about tables is stored in
> shared memory instead. This way we solve a bloating problem.

I think you have no concept how invasive that would be. Tables not
represented in the catalogs would be a disaster, because *every single
part of the backend* would have to be modified to deal with them as
a distinct code path --- parser, planner, executor, loads and loads
of utility commands, etc. I do not think we'd accept that. Worse yet,
you'd also break client-side code that expects to see temp tables in
the catalogs (consider psql \d, for example).

I think a workable solution to this will still involve catalog entries,
though maybe they could be "virtual" somehow.

> We should use *shared* memory so autovacuum could find these tables.

Autovacuum does not touch temp tables; never has and never will, at
least not with the current flavor of temp tables that don't keep their
data in shared buffers. Also, if you insist on keeping the data in
shared memory, there will be a fixed limit on how many temp tables
can exist at one time.

regards, tom lane

>
>
>
> I think you have no concept how invasive that would be. Tables not
> represented in the catalogs would be a disaster, because *every single
> part of the backend* would have to be modified to deal with them as
> a distinct code path --- parser, planner, executor, loads and loads
> of utility commands, etc. I do not think we'd accept that. Worse yet,
> you'd also break client-side code that expects to see temp tables in
> the catalogs (consider psql \d, for example).
>
>
I might be missing a point here, but I really do not see why we would need
an alternate code path for every part of the backend. I agree that all
utility commands, and client side code would break, but if we abstract out
the syscache API and/or modify only the syscache's underlying access paths,
then would the backend really care about whether the tuple comes from
physical catalogs or in memory catalogs?

--
Regards,

Atri
*l'apprenant*

On Tue, Mar 1, 2016 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> writes:
>> There are applications that create and delete a lot of temporary
>> tables. Currently PostgreSQL doesn't handle such a use case well.
>
> True.
>
>> Fast temporary tables work almost as usual temporary tables but they
>> are not present in the catalog. Information about tables is stored in
>> shared memory instead. This way we solve a bloating problem.
>
> I think you have no concept how invasive that would be. Tables not
> represented in the catalogs would be a disaster, because *every single
> part of the backend* would have to be modified to deal with them as
> a distinct code path --- parser, planner, executor, loads and loads
> of utility commands, etc. I do not think we'd accept that. Worse yet,
> you'd also break client-side code that expects to see temp tables in
> the catalogs (consider psql \d, for example).
>
> I think a workable solution to this will still involve catalog entries,
> though maybe they could be "virtual" somehow.

Yeah, I have a really hard time believing this can ever work. There
are MANY catalog tables potentially involved here - pg_class,
pg_attribute, pg_attrdef, pg_description, pg_trigger, ... and loads
more - and they all can have OID references to each other. If you
create a bunch of fake relcache and syscache entries, you're going to
need to give them OIDs, but where will those OIDs come from? What
guarantees that they aren't in use, or won't be used later while your
temporary object still exists? I think making this work would make
parallel query look like a minor feature.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Mar 1, 2016 at 9:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Mar 1, 2016 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> writes:
> >> There are applications that create and delete a lot of temporary
> >> tables. Currently PostgreSQL doesn't handle such a use case well.
>
>
> Yeah, I have a really hard time believing this can ever work. There
> are MANY catalog tables potentially involved here - pg_class,
> pg_attribute, pg_attrdef, pg_description, pg_trigger, ... and loads
> more - and they all can have OID references to each other. If you
> create a bunch of fake relcache and syscache entries, you're going to
> need to give them OIDs, but where will those OIDs come from? What
> guarantees that they aren't in use, or won't be used later while your
> temporary object still exists? I think making this work would make
> parallel query look like a minor feature.
>
>
Fair point, that means inventing a whole new OID generation structure..

--
Regards,

Atri
*l'apprenant*

2016-03-01 17:00 GMT+01:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> On Tue, Mar 1, 2016 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru> writes:
> >> There are applications that create and delete a lot of temporary
> >> tables. Currently PostgreSQL doesn't handle such a use case well.
> >
> > True.
> >
> >> Fast temporary tables work almost as usual temporary tables but they
> >> are not present in the catalog. Information about tables is stored in
> >> shared memory instead. This way we solve a bloating problem.
> >
> > I think you have no concept how invasive that would be. Tables not
> > represented in the catalogs would be a disaster, because *every single
> > part of the backend* would have to be modified to deal with them as
> > a distinct code path --- parser, planner, executor, loads and loads
> > of utility commands, etc. I do not think we'd accept that. Worse yet,
> > you'd also break client-side code that expects to see temp tables in
> > the catalogs (consider psql \d, for example).
> >
> > I think a workable solution to this will still involve catalog entries,
> > though maybe they could be "virtual" somehow.
>
> Yeah, I have a really hard time believing this can ever work. There
> are MANY catalog tables potentially involved here - pg_class,
> pg_attribute, pg_attrdef, pg_description, pg_trigger, ... and loads
> more - and they all can have OID references to each other. If you
> create a bunch of fake relcache and syscache entries, you're going to
> need to give them OIDs, but where will those OIDs come from? What
> guarantees that they aren't in use, or won't be used later while your
> temporary object still exists? I think making this work would make
> parallel query look like a minor feature.
>

The global temp tables can decrease these issues. Only few informations
should be private - and can be accessed via extra function call. Almost all
information can be shared in stable catalogue.

The private data are rownumbers, column statistics and the content
(filenode). Any other can be used from catalogue.

Regards

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 3/1/16 10:05 AM, Atri Sharma wrote:
> Fair point, that means inventing a whole new OID generation structure..

Generation is just the tip of the iceberg. You still need the equivalent
to foreign keys (ie: pg_depend). While you would never have a permanent
object depend on a temp object, the reverse certainly needs to be supported.

If I were attempting to solve this at a SQL level, I'd be thinking about
using table inheritance such that the permanent objects are stored in a
permanent parent. New backends would create UNLOGGED children off of
that parent. There would be a pid column that was always NULL in the
parent, but populated in children. That means children could use their
own local form of an OID. When a backend terminates you'd just truncate
all it's tables.

Actually translating that into relcache and everything else would be a
serious amount of work.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

2016-03-01 20:17 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 3/1/16 10:05 AM, Atri Sharma wrote:
>
>> Fair point, that means inventing a whole new OID generation structure..
>>
>
> Generation is just the tip of the iceberg. You still need the equivalent
> to foreign keys (ie: pg_depend). While you would never have a permanent
> object depend on a temp object, the reverse certainly needs to be supported.
>
> If I were attempting to solve this at a SQL level, I'd be thinking about
> using table inheritance such that the permanent objects are stored in a
> permanent parent. New backends would create UNLOGGED children off of that
> parent. There would be a pid column that was always NULL in the parent, but
> populated in children. That means children could use their own local form
> of an OID. When a backend terminates you'd just truncate all it's tables.
>

> Actually translating that into relcache and everything else would be a
> serious amount of work.
>

you have to store some metadata outside catalogue - in this moment is not
important the syntax or architecture (global temp tables or fast temp
children tables). You have not to use catalogue (when you use catalogue,
then you have bloating). But these special information are related mostly
to planner and should not be MVCC (number of pages, rows, statistics), and
because we are talking about temp tables, you can use session memory.

Regards

Pavel

> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

As far as I know we are trying to kill two birds with one stone:
1. Reduce overhead of accessing temporary tables
2. Make it possible to create temporary tables on replica.

Replicas with hot-standby are widely used for running read-only OLAP queries.
But such queries usually stores intermediate results in temporary tables.
Unfortunately creating temporary table at read-only replica is impossible now.
So some customers do the following tricks: them create pool of file FDWs at master and then use them at replicas.
But IMHO it is ugly and inefficient hack.

Ideally we should be able to create temporary tables at replica, not affecting system catalog.
But there are a lot of problems: where it should be stores, how to assign XIDs to the ruples inserted in temporary table,...

Unfortunately, looks like there is no simple solution of the problem.
The 100% solution is multimaster (which we are currently developing), but it is completely different story...

On 03/01/2016 10:17 PM, Jim Nasby wrote:
> On 3/1/16 10:05 AM, Atri Sharma wrote:
>> Fair point, that means inventing a whole new OID generation structure..
>
> Generation is just the tip of the iceberg. You still need the equivalent to foreign keys (ie: pg_depend). While you would never have a permanent object depend on a temp object, the reverse certainly needs to be supported.
>
> If I were attempting to solve this at a SQL level, I'd be thinking about using table inheritance such that the permanent objects are stored in a permanent parent. New backends would create UNLOGGED children off of that parent. There would be a pid column
> that was always NULL in the parent, but populated in children. That means children could use their own local form of an OID. When a backend terminates you'd just truncate all it's tables.
>
> Actually translating that into relcache and everything else would be a serious amount of work.

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