Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Qualifying use of separate TABLESPACES
 (performance/functionality)
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <d8ae1bb4-3220-66e4-b873-5d8e19edc9e2@gmx.com>
Date: Tue, 16 Jul 2024 21:57:01 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <70BDA5E1-F800-44CB-A1FC-C7597D53ABCD@thebuild.com>
References: <d8ae1bb4-3220-66e4-b873-5d8e19edc9e2@gmx.com>
To: Imndlf8r@gmx.com
Archived-At: <https://www.postgresql.org/message-id/70BDA5E1-F800-44CB-A1FC-C7597D53ABCD%40thebuild.com>
Precedence: bulk


> On Jul 16, 2024, at 21:45, Imndlf8r@gmx.com wrote:
> Or, does Postgres expect to be able to access any media however it =
wants
> (i.e., R/w), regardless of the expected access patterns of the data =
stored
> there?

Well, yes and no.

PostgreSQL will not respond well to having media that is literally read =
only in the sense that a write operation to it will fail.  At some =
point, it will need to (for example) vacuum the tables, and that will =
means writes.  That being said, if the only thing in a tablespace are =
tables (and their indexes) that are written once then never again, you =
won't be constantly getting writes to them.  You may want to do a VACUUM =
(ANALYZE, FREEZE) on the tables in those tablespaces once the data is =
loaded.

PostgreSQL will be generating WAL as you do data-modifying operations, =
so that should be aimed at storage that very low write fatigue.  Be very =
cautious about using a RAM disk for anything, though, unless you are =
*very* confident the battery backup on it is 100% reliable.  PostgreSQL =
isn't designed to handle recovery from having the WAL just disappear out =
from under it on a crash.=