Message-ID: <93e83d00f177281c434a9b648cbd92a2dd1ea141.camel@cybertec.at>
Subject: Re: Recommendations on how to combine SSD and HDD drives in bare
 metal PostgreSQL server
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Onni Hakala <onni@fyff.ee>, pgsql-novice@lists.postgresql.org
Date: Wed, 16 Oct 2024 18:23:08 +0200
In-Reply-To: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
References: <89E7D559-F734-4739-9730-7EDDF787910D@flaky.build>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/93e83d00f177281c434a9b648cbd92a2dd1ea141.camel%40cybertec.at>
Precedence: bulk

On Wed, 2024-10-16 at 17:06 +0300, Onni Hakala wrote:
> 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 eventually 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 this equation and I thoug=
ht that this is probably a common
> scenario and wanted to ask opinions from here.

The traditionalway to handle that would be to create an extra tablespace on=
 a file system built
on the slow disks.  You'd move tables that are no longer hot to that slow t=
ablespace.
If you cannot move the whole table, use partitioning and move old partition=
s to the slow
tablespace.

Yours,
Laurenz Albe