Re: Adding a concept of TEMPORARY TABLESPACE for the use in temp_tablespaces

On Sun, Jan 6, 2019 at 11:01:52AM -0800, Mitar wrote:
> 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.

I wrote a blog entry about this:

https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017

This is certainly an area we can improve, but it would require changes
in several parts of the system to handle cases where the tablespace
disappears.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +