On Fri, Jul 11, 2025 at 10:46 AM Dimitrios Apostolou <ji...@gmx.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!
