Re: Qualifying use of separate TABLESPACES (performance/functionality) - Mailing list pgsql-general
From | Imndlf8r@gmx.com |
---|---|
Subject | Re: Qualifying use of separate TABLESPACES (performance/functionality) |
Date | |
Msg-id | 38903d1a-7481-9f38-4552-248eafa3106f@gmx.com Whole thread Raw |
In response to | Re: Qualifying use of separate TABLESPACES (performance/functionality) (Christophe Pettus <xof@thebuild.com>) |
List | pgsql-general |
On 7/16/2024 9:57 PM, Christophe Pettus wrote: >> 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. 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.).
pgsql-general by date: