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