Date: Wed, 16 Oct 2024 09:01:46 -0700 (PDT)
From: TIM CHILD <tim.child@comcast.net>
To: Onni Hakala <onni@fyff.ee>, pgsql-novice@lists.postgresql.org
Message-ID: <492978770.733068.1729094506490@connect.xfinity.com>
In-Reply-To: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
References: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
Subject: Re: Recommendations on how to combine SSD and HDD drives in bare
 metal PostgreSQL server
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/492978770.733068.1729094506490%40connect.xfinity.com>
Precedence: bulk

Omni,

Firstly, it looks like to have a solution, ( a fixed number and type of dis=
ks) looking for a problem. Its better to consider what is right mix of disk=
s for your application the server.=20

To understand the best physical layout, you need to know the logical  acces=
s patterns to your data. When you access your data are you doing random que=
ries, or index scans or table scans or  computing aggregates? What queries =
are most important and what response time are you targeting?=20
Once you have an idea of your logical access patterns you map that to you p=
hysical layout. =20

Postgres Tablespaces are useful as they allow you locate tables  and indexe=
s of different physical devices. For example it will be better to put small=
 frequently used tables of SSD's. Or just put frequently used indexes on th=
e SSD. =20

There are many factors to consider when planning the physical storage layou=
t. There could me a need for AI apps to do just that!

-Tim


> On 10/16/2024 7:06 AM PDT Onni Hakala <onni@fyff.ee> wrote:
>=20
> =20
> Hey,
>=20
> I have a large dataset of > 100TB which would be very expensive to store =
solely into SSD drives.
>=20
> I have access to a server which has 2x 3.84TB NVME SSD disks and large ar=
ray of HDD drives 8 x 22TB.
>=20
> Most of the data that I have in my dataset is very rarely accessed and is=
 stored only for archival purposes.
>=20
> What would be the de-facto way to use both SSD and HDD together in a way =
use where commonly used data would be fast to access and old data would eve=
ntually only be stored in compressed format in the HDDs?
>=20
> I was initially looking into building zpool using zfs with raidz3 and zst=
d compression for my HDDs but I=E2=80=99m unsure how to add the SSDs into t=
his equation and I thought that this is probably a common scenario and want=
ed to ask opinions from here.
>=20
> Thanks in advance,
> Onni Hakala