Re: Temporary tablespaces on a RAM disk

Ron <ronljohnsonjr(at)gmail(dot)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.

Yes, that works and this is why I asked for temp_tablespaces which are supposed to hold temporary objects only, I believe.

>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.

Yes, but the server will start even if the tmpfs is gone, you just need to recreate it, which also can be handled with /etc/fstab for booting.

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

Sure.

>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 ... )

Regards
Daniel