Re: Qualifying use of separate TABLESPACES (performance/functionality)

> On Jul 16, 2024, at 21:45, Imndlf8r(at)gmx(dot)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.