public inbox for [email protected]help / color / mirror / Atom feed
Qualifying use of separate TABLESPACES (performance/functionality) 3+ messages / 2 participants [nested] [flat]
* Qualifying use of separate TABLESPACES (performance/functionality) @ 2024-07-17 04:45 [email protected] 0 siblings, 1 reply; 3+ messages in thread From: [email protected] @ 2024-07-17 04:45 UTC (permalink / raw) To: [email protected] Hi, I'm designing an appliance (think: 24/7/365/decades) in which Postgres will act as a "skin" on the sole persistent store. I.e., there is no "filesystem" visible to clients; *all* persistent (and temporary/shared) data is presented through Postgres. There are three different types of data maintained in the RDBMS: - static (firmware images, "reference" data, appliance configuration, etc) - evolving (largely static but slowly evolving) - transient (temp tables/joins, etc. built by cooperating groups of clients) As this is an appliance, cost is important -- along with availability. It's not acceptable to plan on a hardware upgrade in anything less than a decade or more) I would, ideally, like to tailor the media used for each type of (above) data. E.g., QLC SSDs for static data, SLC SSDs (or HDDs) for evolving and RAMdisks for transient. Can I do this? And, would my "expectations" for the types of accesses in each tablespace be intuitive? E.g., could I expect no/few WRITES to the tablespace with the static data if I never call for it to be explicitly updated?? (IIRC, Oracle allows a tablespace to be qualified as "READ-ONLY" and implemented on true R/O media) 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? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Qualifying use of separate TABLESPACES (performance/functionality) @ 2024-07-17 04:57 Christophe Pettus <[email protected]> parent: [email protected] 0 siblings, 1 reply; 3+ messages in thread From: Christophe Pettus @ 2024-07-17 04:57 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] > On Jul 16, 2024, at 21:45, [email protected] 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Qualifying use of separate TABLESPACES (performance/functionality) @ 2024-07-17 09:45 [email protected] parent: Christophe Pettus <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: [email protected] @ 2024-07-17 09:45 UTC (permalink / raw) To: Christophe Pettus <[email protected]>; +Cc: [email protected] On 7/16/2024 9:57 PM, Christophe Pettus wrote: >> On Jul 16, 2024, at 21:45, [email protected] 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.). ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-17 09:45 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-17 04:45 Qualifying use of separate TABLESPACES (performance/functionality) [email protected] 2024-07-17 04:57 ` Christophe Pettus <[email protected]> 2024-07-17 09:45 ` [email protected]
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox