Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces

Hi!

I have read around the Internet a lot about the idea of using /dev/shm
for a tablespace to put tables in and issues with that. But I still
have not managed to get a good grasp why would that be a bad idea for
using it for temporary objects. I understand that for regular tables
this might prevent database startup and recovery because tables and
all files associated with tables would be missing. While operations
for those tables could reside in the oplog. (Not sure if this means
that unlogged tables can be stored on such tablesspace.)

I have experimented a bit and performance really improves if /dev/shm
is used. I have experimented with creating temporary tables inside a
regular (SSD backed) tablespace /dev/shm and I have seen at least 2x
improvement in time it takes for a set of modification+select queries
to complete.

I have also tested what happens if I kill all processes with KILL and
restart it. There is noise in logs about missing files, but it does
start up. Dropping and recreating the tablespace works.

So I wonder, should we add a TEMPORARY flag to a TABLESPACE which
would mark a tablespace such that if at startup its location is empty,
it is automatically recreated, without warnings/errors? (Maybe some
other term could be used for this.)

Ideally, such tablespace could be set as temp_tablespaces and things
should work out: PostgreSQL should recreate the tablespace before
trying to use temp_tablespaces for the first time.

We could even make it so that only temporary objects are allowed to be
created in a TEMPORARY TABLESPACE, to make sure user does not make a
mistake.

Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m