Re: having temp_tablespaces on less reliable storage

On Fri, Jul 11, 2025 at 10:46 AM Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:

>
> On Thu, 10 Jul 2025, Dimitrios Apostolou wrote:
>
> > Hello list,
> >
> > I have a database split across many tablespaces, with temp_tablespaces
> > pointing to a separate, less reliable device (single local NVMe drive).
> How
> > dangerous is it for the cluster to be unrecoverable after a crash?
> >
> > If the drive goes down and the database can't read/write to
> temp_tablespaces,
> > what will happen?
> >
> > If I then configure temp_tablespaces to point to a working location,
> would
> > that be enough to start the cluster? Or other bad things can happen?
> >
> > Can't find any related documentation, but I expect loss of "temp" space
> is of
> > minor importance.
>
>
> David G. Johnston wrote:
> >
> > You might want to try finding some old discussions about why putting temp
> > tablespace on a RAM-drive is not a supported configuration.
>
> Thank you, I found the following:
>
> [1] https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
> [2]
> https://www.postgresql.org/message-id/flat/ZR0P278MB0028A89FAA3E31E7F1514EF6D2F60%40ZR0P278MB0028.CHEP278.PROD.OUTLOOK.COM
> [3]
> https://www.dbi-services.com/blog/can-i-put-my-temporary-tablespaces-on-a-ram-disk-with-postgresql/
>
> At [1] is the standard documentation warning about tablespaces in general:
> "if you lose a tablespace (file deletion, disk failure, etc.), the
> database cluster might become unreadable or unable to start".
>
> I believe this could be improved, especially with regards to
> temp_tablespaces.
>
> At [2] is a thread started by Daniel Westermann (CC'd) with lots of
> uncertainty in the air. Tom Lane (CC'd) mentions that as long as files are
> temporary (not supposed to be there after restart), it should be fine, but
> there might be additional issues with the directory disappearing after a
> restart.
>
> At [3] is a blog from Daniel who started the previous thread. He removes
> directories and restarts the cluster and things go OK.
>
>
> I'm leaning towards doing it, i.e. creating a tablespace on the super-fast
> local SSD and using it exclusively for temp_tablespaces. The queries my
> database is facing are crunching TBs of data for many hours and write tons
> of temporary data, and the local NVMe storage is a huge improvement over
> the enterprise-storage volumes the VM is provided with (I believe they are
> iSCSI based underneath, bound to network latency).
>
> What if the NVMe drive fails?
>
> The good scenario is that I will create a new tablespace at a new location
> and change temp_tablespaces to point there, and everything should be fine.
> Possibly without even a cluster restart.
>
> The very bad scenario is that the cluster will crash and will need
> restart, but that will go sideways and will eventually need restore from
> backup or other hacks.
>
> How possible would that be?
>

How regularly do you backup your databases?
How regularly do you test those backups?

If you (1) can tolerate the slight risk of a crash, (2) take regular
backups, (3) check that the backup jobs succeed 😀, and (4) regularly test
that the backups are valid, then by all means put temp_tablespaces on
local NVMe storage.

Of course, you should be doing steps 2, 3 and 4 anyway...

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!