Subject: Re: having temp_tablespaces on less reliable storage

From: Dimitrios Apostolou <[email-masked]>
Date: Fri, 11 Jul 2025 16:46:19 +0200 (CEST)

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?

Thanks,
Dimitris

---

From: Ron Johnson <[email-masked]>
Date: Fri, 11 Jul 2025 11:08:34 -0400

On Fri, Jul 11, 2025 at 10:46 AM Dimitrios Apostolou <[email-masked]> wrote:

> [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/
>

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!