Thread: 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

Aleksander Alekseev <a.alekseev@postgrespro.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

On Tue, Mar 1, 2016 at 10:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Aleksander Alekseev <a.alekseev@postgrespro.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 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

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
wouldnever 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
permanentobjects are stored in a permanent parent. New backends would create UNLOGGED children off of that parent.
Therewould 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
anOID. 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