Message-ID: <d8ae1bb4-3220-66e4-b873-5d8e19edc9e2@gmx.com>
Date: Tue, 16 Jul 2024 21:45:57 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
To: pgsql-general@lists.postgresql.org
Content-Language: en-US
From: Imndlf8r@gmx.com
Subject: Qualifying use of separate TABLESPACES (performance/functionality)
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
UI-OutboundReport: notjunk:1;M01:P0:zN+yicvBzhI=;uWi0VmqMEtv9wc8zdkhTBChmF/L
 HMw3Kuq0NrtetcD0oJaHh5FmkMXCYrmDBTR6NWt+KlagkvjG5I8O7VVhui6xdAd4FCF2WdPLM
 oB8Um1B7fawF5X1x1j+VWR/YBnSI/CR85lOb5eW/QQTxUbUjrjcwtgrv+inbHqKM67Kc6Sb2d
 fiS8C1xuxohz+783OwrtzZKfNgOEO6PfqW7AVj3yeBrZMZMA62J6uZ+pAYKtJeHw6IZRv6brf
 30hTiBV+/pt24osRG6NwhMDBsILVHcDek/Aw1M+pfXaEHjbVY1fMGFOmP8wSew+JuqKePZUtV
 Q60ipTOHsuMKvealQtDtgAZvz+3YJYyG9g4nBjp+q1X26FO8TjQEvTGCgtuYgWnhOyzwDXKJx
 E4tlnEdIvo1DJewZBquJWCDdf1fkhVkdZktLJQKLIda2zObRQJj2UQDcWPwMiX8PIh/HF8TPD
 xlPHbDhoDpwP7P8wXnIfkJjMFcpD7eV9bHOPUP6uXs5NDkAF69zwN4EXWuW6JxgbwBbJ4jzn1
 QMg/Ie3cBWFHp8z3pFBU3fs122d5jWDzRV8eerGQTB+ZIuESonaL6ijysgacUwrWzu9blURJM
 CUgOQO4a5R0NMQNCvlZ+yoL4OJTJ+D0yweUwLqOhx+578FAX2Pc1ip+BStj6NKQ8pXF+qlFsH
 YpY/B1QqRI5ywiy+pQiOCx/2twVy/pALzbutGPFI4sJqUpfVWiomzzzQA5DmeytJY0+RDcpTN
 F62Ll+bIQ3i6EVw7iKLSwRzi/QHuKPX1UZJsC8GI4uVsR2LrUfLVuHNbPpd9oL6WFhzPjiVom
 6di/iGO4kAZ56/jx7UOYrQ1g==
Archived-At: <https://www.postgresql.org/message-id/d8ae1bb4-3220-66e4-b873-5d8e19edc9e2%40gmx.com>
Precedence: bulk

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?