MIME-Version: 1.0
References: <1de78bf7-940a-9be5-e98d-a11f02d9e898@gmx.net> <a97a5c42-5396-e30f-715d-5051b0a90e93@gmx.net>
In-Reply-To: <a97a5c42-5396-e30f-715d-5051b0a90e93@gmx.net>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 11 Jul 2025 11:08:34 -0400
Message-ID: <CANzqJaBDJc2CtTY5UZWBMO-c7r462nrqV-iio_g+a=mDv4yPgA@mail.gmail.com>
Subject: Re: having temp_tablespaces on less reliable storage
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c4487b0639a8af4a"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBDJc2CtTY5UZWBMO-c7r462nrqV-iio_g%2Ba%3DmDv4yPgA%40mail.gmail.com>
Precedence: bulk

--000000000000c4487b0639a8af4a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jul 11, 2025 at 10:46=E2=80=AFAM Dimitrios Apostolou <jimis@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 te=
mp
> > 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/ZR0P278MB0028A89FAA3E31E7F1514=
EF6D2F60%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 ar=
e
> temporary (not supposed to be there after restart), it should be fine, bu=
t
> 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-fas=
t
> 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 ton=
s
> 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 ar=
e
> 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 locatio=
n
> 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 =F0=9F=98=80, and (4) regul=
arly 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...

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

--000000000000c4487b0639a8af4a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 11, 2025 at 10:46=E2=80=AFAM =
Dimitrios Apostolou &lt;<a href=3D"mailto:jimis@gmx.net">jimis@gmx.net</a>&=
gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><br>
On Thu, 10 Jul 2025, Dimitrios Apostolou wrote:<br>
<br>
&gt; Hello list,<br>
&gt;<br>
&gt; I have a database split across many tablespaces, with temp_tablespaces=
 <br>
&gt; pointing to a separate, less reliable device (single local NVMe drive)=
. How <br>
&gt; dangerous is it for the cluster to be unrecoverable after a crash?<br>
&gt;<br>
&gt; If the drive goes down and the database can&#39;t read/write to temp_t=
ablespaces, <br>
&gt; what will happen?<br>
&gt;<br>
&gt; If I then configure temp_tablespaces to point to a working location, w=
ould <br>
&gt; that be enough to start the cluster? Or other bad things can happen?<b=
r>
&gt;<br>
&gt; Can&#39;t find any related documentation, but I expect loss of &quot;t=
emp&quot; space is of <br>
&gt; minor importance.<br>
<br>
<br>
David G. Johnston wrote:<br>
&gt;<br>
&gt; You might want to try finding some old discussions about why putting t=
emp<br>
&gt; tablespace on a RAM-drive is not a supported configuration.<br>
<br>
Thank you, I found the following:<br>
<br>
[1] <a href=3D"https://www.postgresql.org/docs/current/manage-ag-tablespace=
s.html" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/doc=
s/current/manage-ag-tablespaces.html</a><br>
[2] <a href=3D"https://www.postgresql.org/message-id/flat/ZR0P278MB0028A89F=
AA3E31E7F1514EF6D2F60%40ZR0P278MB0028.CHEP278.PROD.OUTLOOK.COM" rel=3D"nore=
ferrer" target=3D"_blank">https://www.postgresql.org/message-id/flat/ZR0P27=
8MB0028A89FAA3E31E7F1514EF6D2F60%40ZR0P278MB0028.CHEP278.PROD.OUTLOOK.COM</=
a><br>
[3] <a href=3D"https://www.dbi-services.com/blog/can-i-put-my-temporary-tab=
lespaces-on-a-ram-disk-with-postgresql/" rel=3D"noreferrer" target=3D"_blan=
k">https://www.dbi-services.com/blog/can-i-put-my-temporary-tablespaces-on-=
a-ram-disk-with-postgresql/</a><br>
<br>
At [1] is the standard documentation warning about tablespaces in general: =
<br>
&quot;if you lose a tablespace (file deletion, disk failure, etc.), the <br=
>
database cluster might become unreadable or unable to start&quot;.<br>
<br>
I believe this could be improved, especially with regards to <br>
temp_tablespaces.<br>
<br>
At [2] is a thread started by Daniel Westermann (CC&#39;d) with lots of <br=
>
uncertainty in the air. Tom Lane (CC&#39;d) mentions that as long as files =
are <br>
temporary (not supposed to be there after restart), it should be fine, but =
<br>
there might be additional issues with the directory disappearing after a <b=
r>
restart.<br>
<br>
At [3] is a blog from Daniel who started the previous thread. He removes <b=
r>
directories and restarts the cluster and things go OK.<br>
<br>
<br>
I&#39;m leaning towards doing it, i.e. creating a tablespace on the super-f=
ast <br>
local SSD and using it exclusively for temp_tablespaces. The queries my <br=
>
database is facing are crunching TBs of data for many hours and write tons =
<br>
of temporary data, and the local NVMe storage is a huge improvement over <b=
r>
the enterprise-storage volumes the VM is provided with (I believe they are =
<br>
iSCSI based underneath, bound to network latency).<br>
<br>
What if the NVMe drive fails?<br>
<br>
The good scenario is that I will create a new tablespace at a new location =
<br>
and change temp_tablespaces to point there, and everything should be fine. =
<br>
Possibly without even a cluster restart.<br>
<br>
The very bad scenario is that the cluster will crash and will need <br>
restart, but that will go sideways and will eventually need restore from <b=
r>
backup or other hacks.<br>
<br>
How possible would that be?<br></blockquote><div>=C2=A0</div></div><div>How=
 regularly do you backup your databases?</div><div>How regularly do you tes=
t those backups?</div><div><br></div><div>If you (1) can tolerate the sligh=
t risk of a crash, (2) take regular backups, (3) check that the backup jobs=
=C2=A0succeed=C2=A0=F0=9F=98=80, and (4) regularly test that the backups ar=
e valid, then by all means put temp_tablespaces on=C2=A0 local NVMe storage=
.</div><div><br></div><div>Of course, you should be doing steps 2, 3 and 4 =
anyway...</div><div><br></div><span class=3D"gmail_signature_prefix">-- </s=
pan><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death t=
o &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still =
alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div>=
</div>

--000000000000c4487b0639a8af4a--