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

On 7/16/2024 9:57 PM, Christophe Pettus wrote:
>> 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.

Picking a nit, here: if the write() never returns an error, then Postgres
never (?) knows that the write "didn't happen" (as expected).

If Postgres is writing the same data that ALREADY EXISTS, then the
physical medium could be immutable and Postgres would never know (see
next comment)

> At some point, it will
> need to (for example) vacuum the tables, and that will means writes.

But, if nothing has ever been *written* (UPDATE) to that tablespace, what
value would VACUUM have?

> 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.

Are there ever any "anonymous"/temporary objects that might be created
alongside these?

> You may want to do a VACUUM (ANALYZE, FREEZE) on the tables
> in those tablespaces once the data is loaded.

Ideally, I would like to find a way to "import" a preexisting table, index,
etc. without literally having to have two copies (the "original" being
read and the copy being *created*). Having the original fill that role
*immutably* assures the customer that the original data hasn't been
altered in any way, even during its "initialization" within the RDBMS.

[It also avoids the issue of ever needing two *physical* copies of the data]

> PostgreSQL will be generating WAL as you do data-modifying operations, so
> that should be aimed at storage that very low write fatigue.

Yes, but there is also the need to safeguard against read disturb induced
errors. Using MLC/TLC/QLC/PLC devices on the "static" portions of the
data puts them at higher risk. The FLASH controller needs to rewrite
errored pages, even if the application isn't explicitly altering the data.

I.e., "read only" can be interpreted on several different layers:
- the application never calls for a write (but the DBMS silently does)
- the DBMS never calls for a write (but the memory controller silently does)
- the physical memory is truly immutable

The last being the ideal.

> 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.

The server is battery backed. As long as the server can "fix things" before
the battery is exhausted, then all is well. (Imagine the case of a physical
disk dying; what recourse, there?)

My recovery strategy is to checkpoint the database periodically so the most
recent snapshot can be reloaded.

As I said, it's an appliance. You don't have a DBA attending to the
database in your vehicle's navigation system (the system would be deemed
too costly if it had to support such an "accessory" :> ). The DBMS is
expected to be as reliable as the underlying data store. It abstracts
references to the data in the store and ensures applications can't create
"bad" data (strict typing, constraints, triggers, etc.).