public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dimitrios Apostolou <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: having temp_tablespaces on less reliable storage
Date: Fri, 11 Jul 2025 16:46:19 +0200 (CEST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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.CH...
[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
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: having temp_tablespaces on less reliable storage
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox