pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedhaving temp_tablespaces on less reliable storage
4+ messages / 3 participants
[nested] [flat]
* having temp_tablespaces on less reliable storage
@ 2025-07-10 17:58 Dimitrios Apostolou <jimis@gmx.net>
2025-07-10 18:04 ` Re: having temp_tablespaces on less reliable storage David G. Johnston <david.g.johnston@gmail.com>
2025-07-11 14:46 ` Re: having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: Dimitrios Apostolou @ 2025-07-10 17:58 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
Thanks in advance,
Dimitris
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: having temp_tablespaces on less reliable storage
2025-07-10 17:58 having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
@ 2025-07-10 18:04 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2025-07-10 18:04 UTC (permalink / raw)
To: Dimitrios Apostolou <jimis@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
On Thu, Jul 10, 2025 at 10:58 AM Dimitrios Apostolou <jimis@gmx.net> wrote:
> Can't find any related documentation, but I expect loss of "temp" space is
> of minor importance.
>
You might want to try finding some old discussions about why putting temp
tablespace on a RAM-drive is not a supported configuration.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: having temp_tablespaces on less reliable storage
2025-07-10 17:58 having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
@ 2025-07-11 14:46 ` Dimitrios Apostolou <jimis@gmx.net>
2025-07-11 15:08 ` Re: having temp_tablespaces on less reliable storage Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dimitrios Apostolou @ 2025-07-11 14:46 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: david.g.johnston@gmail.com; daniel.westermann@dbi-services.com; tgl@sss.pgh.pa.us
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: having temp_tablespaces on less reliable storage
2025-07-10 17:58 having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
2025-07-11 14:46 ` Re: having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
@ 2025-07-11 15:08 ` Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2025-07-11 15:08 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Fri, Jul 11, 2025 at 10:46 AM 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 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?
>
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-07-11 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-10 17:58 having temp_tablespaces on less reliable storage Dimitrios Apostolou <jimis@gmx.net>
2025-07-10 18:04 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-11 14:46 ` Dimitrios Apostolou <jimis@gmx.net>
2025-07-11 15:08 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox