Re: Temporary tablespaces on a RAM disk

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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(dot)consulting(at)comcast(dot)net