Date: Fri, 11 Jul 2025 16:46:19 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
cc: david.g.johnston@gmail.com, daniel.westermann@dbi-services.com, 
    tgl@sss.pgh.pa.us
Subject: Re: having temp_tablespaces on less reliable storage
In-Reply-To: <1de78bf7-940a-9be5-e98d-a11f02d9e898@gmx.net>
Message-ID: <a97a5c42-5396-e30f-715d-5051b0a90e93@gmx.net>
References: <1de78bf7-940a-9be5-e98d-a11f02d9e898@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:1eGp31FmTZw=;cY+DO5GMe6xkDqOpZHUmnOg77xc
 jVf+4SzRYTAE6rgpYUCj284SacYp42oAreKHPZILW7kuu58QHQ4BcX1FXwqGdDLCfFzcBj/+F
 Z76cwxflhTF7C+geMr1P6lmydAJOyMPPKxNTwAeqQp1ADx1VVZoNfl1+2iXo/5GSIwjjQ3ELY
 Eucs8+2vFzp5ehVbBmvIMMqWgFzNVt3fG4CQ0jDLWPFjTM3TYAb+84//7s35+7LVR8vqptX9v
 bGmugk9rA92ilmv7IfwhS/FBiF0MpCyKqWD1I0xMCjcCOVWFJ9uSwXFAP1EBJdA5M5sPURIPL
 zrbrchUK3mKlgI/L4+3QOio4EhCLa6193TM3FDE2m3cN2srtExD9tP0KfRhmUFMCfQKTcCRSz
 iKdFoZOm/7gHMf6xs4T18G6O9IGcGSMC/JrjSTxmqTCD5eNMyLuL0f4+5XKsegNnMyUqmjjA8
 YVShdTWKFrzQrLaKU7DNUJyVYITNhvspQzF1GLEyI5apzTOQoqsdV71229BtRZXD0JJs7ABeI
 D3pL0JPPU4GC8kjoLmOEyZf78skss2/fiYzQaIMDOhtC6L9X+gtUQRnTazMknvc3n6QKmFmes
 bljk9AtNT7QR1km40rJGaAPrIGn8+gxgOa0F/X3OlIo107g9WZLW4bsxlystk6lvIJHpN5/c4
 NCABRCWJ67/MLqzHTljljbgnf/hm1kF8T9s5F4MDZnh3ZxT9z2+orwHuF4omTefYzrJut7bTk
 hj8guP+87ZP9a8eLSPyuiOXDp9o/e/qQnWhMXIDgYhx2UB9t9kzDe3xbkrIi3ek++2bAG8jJQ
 HL/Y2r/nDzIrER0YLBCAbQ/HTwAuGZsrv7DLkGobDwpXSeq1585aWOhY6TXrpyxMp48pvZA8j
 b+UoghZI6dHxgXdEeUUqfpKLLFLb9ZeFFMJVTFQ0CMgfo75BzIM4zNRwihnpMVLbogAgKAwrF
 DI9WvQJCq7065u0bi7nIAyMt98L3V/H3B8nF6slEGpeTgJ3eIj5Rpskp8C10Tok68vKMAojOc
 z7t2ao7TvwL2ccXNcpkgQg+JWZRZKMGWd0uD6dqEQj1qehcM2tAngUOIvMLUkJNec2bCYNJ2g
 FOrNV+k+MV9Py/auPuaJlgvQaidvtoZcLANl+ZF0dU+pvyvLGPaKhWHLG8aGr1VlgZ3Ej7RlB
 Y7Gdbm4xU3+03IwDz5m7W1X1hL2F3jIovbeIJqS4sKLZLdxRiRgSqXw+FZOzGJL3B5GyWVHhO
 oRMmF09uoUEOQ/yYYAM3T7oCNZbeSOLLysI1S2S12ifC1EdLxf34kKPuIbn1sK9127vTNNKeQ
 VM+goW61SlK/ZQwgMHEXIRcr470+jhoVAPLHqc9Mu0t6Y8mPNV7sHPsX2U9TgqBKBpEf5k/MF
 H7pVyUbVIsFoqS6qqDpM0VbeP5PxU0zmhSzhPsnaR7Zy/nqYYsOYDGuGqNI+txld+m0Cbt1c0
 2YdUn4IRH/+xhf8C79OG7JJXIA+Y+0CrFfRUUziYAsvo8WBTraCxIaCsf7MFVgiig4MOoFOt3
 jM60IwG+oquYVKiV7b064DwWwAeMVle5Ei0bAw6itwchGq1D7+CvszP/xAmUjzuqEf+llhRt6
 OmuUvjCMFOp1aZgnOtA81e0EfakK5ace69wXu++4R+sraE9QwThea+UzlAJEaAf0l/kIA/Os9
 riMVTUgHKSCJSgL73z6SvuLxNhqx762pHYT4gc0kGy565PlTxwKLRH8x4Y6yLusI6oikAzcEU
 PFRjMYiGjv0oKIjrlJaaDpiro4sN/8P9qw6aw9bfKy74GfF5MXGDdAnHO8Ne+ygJiDN/H02CS
 APMPCFPtB/Znau8AdwNwLQA7aUXTObKi2+g+WUUiEO1EKJt7+qx8nxtp9okJOGwmXJaWjxSSf
 dy4mNM8w5HeojWVW8rLoLd9zO8sEvPRwOEOFIRdZDU9loTW9YetEjyCWXJ8+WcCadUElQMeVz
 SvUZnUijDTo1WrVELUXuZp9mf71J/7BqVYSb4XEs22S5wO6jTMITFzsImNMOGSSUEdfQCc64x
 7O1ANUmYsmR4CsDJgWSbajWKbQm2dPFPm31mKDqnmLfS02W7X9yTDrRZyoZtLT3fnxtjylBDO
 YQ9eUS2kDHV+sLARSDiXRLp/9u91YUnJJqk5LryPDdbyc3gS2yGEzUMC79oF321MPg+BSSbqL
 JfCTnF7/gzKLHHH1ZVEk/b0ZJKKYsx0ZlgiiRbDfHHBCHzOAxw9yRUMNJUjl2BIjTMpK2M714
 d7Y9T0HmJfcn3Leux+Y6cmQqnnNYqzXDIF804/m/FewA29a6IMElXrJLYm2xNhuxuqtC0Pzr3
 oBWTYWxDjGFEklFnuhf64oSppwxf4W2geHBlFqOpKW1X5hRPbSUrR85ddh0cOZi2Mo4mKJCkg
 p1euts05HuPOTqZS1C8GiTIgaON6RZ6vZmY57u7U1nxLBoELqx8cpz2I2mQKNClqvESD42QVA
 rEfnwIOjf7abN0qdD9FdJvWajvRxP15U4iCgv8D831WzARKJzj5mMGMrORT/2rp9+7TofS9bn
 RR1pukVEfejTzMNuja8TjE/uM7tUDESjYyfFVrdLjmVo4S0ssDfs4Noo/Xc7RFOdULUHe3fIN
 JdSrXjtMvO2ckpiKQl9LtlEPvJkBBtS5ZfN4uhVCFRPgv+sFrfQLcrS7Fq7RRH+GVkTOM1IqZ
 Xgni/fbc2P+Scy2kXgPxwCt8l16O0G8RH4iTc0Z/ebcG5BHj1MELx3YueSoPto+OSMeuKwbP/
 b67xgxVzsvT5+fGuYLhbURvmQ2i7E2K6VD0BUd3xasWa657LgtP2VRy9kSXtWclDsAjD7+Hkh
 HRGaZcWGWomGl10KookgT6E65V/v0J+10wnNUzSb9XC7Pu3KliXcJ3sH5CsANemxRTbybAXd8
 4j3BHU9uPE3IfumKbKLduo5DX8pzPkH9uIuOSWMHBvViweO0zpO0abzDaX0cllnWk3eKIcREk
 4PQ74lWz4uUAcuX08QDcYNOTC4y7+WXj6cdBC45y3B3K0OdBgxnSjOVA8Dnkzq7uu5YNMWmrT
 tNV83rz8PsqWlINiHrlc+Rw2pTWLTaj3fi2Xbcw6klardx9iOtrvOVYziTz6T/tMAnN5sqFse
 pmj1RX5MkorwAlFr9Pve9RlFarxfaVfLNiRHwBaGCActtXa5QOfF8z5Dwa1R4nD7xWu8wBItH
 qzb5UjEYEpkzYa8r5gbzAdxVTto6nUXxWqKUogAyTfT32t5Pjh/ZcWDTjYYQ4cV0Ex0wC7BX8
 apNO8t7k3E3seMyAXzg1YJeYbvBY7MvrqdiG0Nxal5rqLDEggBIXWOhp+QWkBPawoQW+bEqEB
 dBZg5C2WDM+RKIXI4ByhLoqKeOks8N+Zx0glyELvnGSBdmJXJ+B1d5/MTO2dARpxhOl75tCz2
 KhC/x/J2X1I+ZZzr2sTyPlmuKQSJOwI/TC7uJvKCL3fiVe39w/TScve+90+boWExPQk3XTr5L
 ZgjQ6Ae+FdOnfgsLhuMPKQIg3xVQ2xjavjUMDQpdcSUZvkxlwH9C5hOWLKdDJYo+CGYozBlv0
 AQ==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/a97a5c42-5396-e30f-715d-5051b0a90e93%40gmx.net>
Precedence: bulk


On Thu, 10 Jul 2025, Dimitrios Apostolou wrote:

> Hello list,
>
> I have a database split across many tablespaces, with temp_tablespaces=
=20
> pointing to a separate, less reliable device (single local NVMe drive). =
How=20
> 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_tablesp=
aces,=20
> what will happen?
>
> If I then configure temp_tablespaces to point to a working location, wou=
ld=20
> 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=20
> minor importance.


David G. Johnston wrote:
>
> You might want to try finding some old discussions about why putting tem=
p
> 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/ZR0P278MB0028A89FAA3E31E7F1=
514EF6D2F60%40ZR0P278MB0028.CHEP278.PROD.OUTLOOK.COM
[3] https://www.dbi-services.com/blog/can-i-put-my-temporary-tablespaces-o=
n-a-ram-disk-with-postgresql/

At [1] is the standard documentation warning about tablespaces in general:=
=20
"if you lose a tablespace (file deletion, disk failure, etc.), the=20
database cluster might become unreadable or unable to start".

I believe this could be improved, especially with regards to=20
temp_tablespaces.

At [2] is a thread started by Daniel Westermann (CC'd) with lots of=20
uncertainty in the air. Tom Lane (CC'd) mentions that as long as files are=
=20
temporary (not supposed to be there after restart), it should be fine, but=
=20
there might be additional issues with the directory disappearing after a=
=20
restart.

At [3] is a blog from Daniel who started the previous thread. He removes=
=20
directories and restarts the cluster and things go OK.


I'm leaning towards doing it, i.e. creating a tablespace on the super-fast=
=20
local SSD and using it exclusively for temp_tablespaces. The queries my=20
database is facing are crunching TBs of data for many hours and write tons=
=20
of temporary data, and the local NVMe storage is a huge improvement over=
=20
the enterprise-storage volumes the VM is provided with (I believe they are=
=20
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=
=20
and change temp_tablespaces to point there, and everything should be fine.=
=20
Possibly without even a cluster restart.

The very bad scenario is that the cluster will crash and will need=20
restart, but that will go sideways and will eventually need restore from=
=20
backup or other hacks.

How possible would that be?

Thanks,
Dimitris