Thread: Temporary tablespaces on a RAM disk

On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> wrote:

is someone using temporary tablespaces on a RAM disk ? Any experiences with that?

I did some quick tests and checked the archives but could not find any information that either confirmed it is a bad idea nor the opposite.

There is a nice big bold warning callout in the documentation that covers this explicitly.

https://www.postgresql.org/docs/12/manage-ag-tablespaces.html

Ron <ronljohnsonjr@gmail.com> writes:
> On 3/17/20 12:19 PM, David G. Johnston wrote:
>> There is a nice big bold warning callout in the documentation that covers
>> this explicitly.
>> https://www.postgresql.org/docs/12/manage-ag-tablespaces.html

>>> Warning
>>> Placing a tablespace on a temporary file system like a RAM disk risks the
>>> reliability of the entire cluster.

> But aren't temporary files removed when you restart Postgres?  (I'm assuming
> that temp_tablespaces is treated "the same" as data/pgsql_tmp.  Is that a
> false assumption?)

I think the main issue is that there's no mechanism preventing you from
putting regular (non-temp) tables into the "temporary" tablespace.
If you do, crash recovery will get very unhappy when it tries to replay
updates for those tables and they're not there.

There are probably additional issues having to do with the tablespace
directory(s) disappearing.  That, you could maybe finesse by having the
postmaster start script re-create any missing directories.

All in all, I wouldn't try it without careful pre-testing of what happens
after the RAM disk gets wiped.

People have asked about this before, so maybe it'd be an idea to make
an explicit concept of a temp tablespace that only accepts temp tables,
and do whatever is needful to make that robust.  But I've not heard of
any work towards that.

            regards, tom lane

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
>> People have asked about this before, so maybe it'd be an idea to make
>> an explicit concept of a temp tablespace that only accepts temp tables,
>> and do whatever is needful to make that robust.  But I've not heard of
>> any work towards that.

> That's what I thought temp_tablespaces are for ( plus sorts, temporary files getting created by materialized views
...) 

No ... temp_tablespaces says it's okay to use any of the listed
tablespaces to keep temporary working files in, but it doesn't
say that those tablespaces can *only* be used for that.

The whole business of temp tables (as opposed to those invisible-to-SQL
working files) in such a tablespace is a separate issue, too.  I think
that the server would mostly survive having temp-table files disappear
during reboot, but it's not an officially supported or tested scenario.

            regards, tom lane

On Tue, 2020-03-17 at 09:49 +0000, Daniel Westermann (DWE) wrote:
> is someone using temporary tablespaces on a RAM disk ? Any experiences with that?
> I did some quick tests and checked the archives but could not find any information that either confirmed it is a bad
ideanor the opposite.
 

Taking a step back, wouldn't it be better to increase "work_mem"
and "temp_buffers" and set "temp_file_limit", so that temporary
files are avoided whenever possible and temporary tables are cached?

Then essentially you are using the same memory for the same purposes,
but in a supported fashion.

Yours,
Laurenz Albe

On Tue, 2020-03-17 at 19:57 +0000, Daniel Westermann (DWE) wrote:
> >> is someone using temporary tablespaces on a RAM disk ? Any experiences with that?
> >> I did some quick tests and checked the archives but could not find any
> >> information that either confirmed it is a bad idea nor the opposite.
> 
> >Taking a step back, wouldn't it be better to increase "work_mem"
> >and "temp_buffers" and set "temp_file_limit", so that temporary
> >files are avoided whenever possible and temporary tables are cached?
> 
> I don't think you can avoid that for all cases, especially when working
> with large data sets. That was one of the reasons for my initial question.

But with your proposed setup, any query that needs more temp space
than your RAM disk has will fail.  I don't think that is good for large
data sets.

Setting "work_mem" and "temp_buffers" high, you can use all the RAM
you have, and if it still is not enough, it spills to disk rather than die.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

> On Tue, 2020-03-17 at 19:57 +0000, Daniel Westermann (DWE) wrote:
>
>> >> is someone using temporary tablespaces on a RAM disk ? Any experiences with that?
>> >> I did some quick tests and checked the archives but could not find any
>> >> information that either confirmed it is a bad idea nor the opposite.
>> 
>> >Taking a step back, wouldn't it be better to increase "work_mem"
>> >and "temp_buffers" and set "temp_file_limit", so that temporary
>> >files are avoided whenever possible and temporary tables are cached?
>> 
>> I don't think you can avoid that for all cases, especially when working
>> with large data sets. That was one of the reasons for my initial question.
>
> But with your proposed setup, any query that needs more temp space
> than your RAM disk has will fail.  I don't think that is good for large
> data sets.

Perhaps not, but disk filling on the same volume as WAL is also a
serious problem in case the process that eventually took the storage to
100% and got an ENoSpace was the WAL write :-)

Er, but any standard 1-tablespace configuration is at risk of that,
generally.

FWIW

>
> Setting "work_mem" and "temp_buffers" high, you can use all the RAM
> you have, and if it still is not enough, it spills to disk rather than die.
>
> Yours,
> Laurenz Albe
>
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net