RE: Recommendations on how to combine SSD and HDD drives in bare metal PostgreSQL server [SEC=UNOFFICIAL] - Mailing list pgsql-novice

UNOFFICIAL
Further to this, the advantages of using the SSD as a cache in the hardware/file system/operating system are:
* You don't have to understand the deep system performance issues of PostgreSQL or your application
* The result will be optimal or near optimal
* The system will self tune even as your usage of your PostgreSQL database changes overtime

The main thing you will need to monitor is the health of the SSD (and of course also the HDDs) but hopefully you will
havethe HDDs in a RAID configuration anyway.
 

Do not forget to do backups.

Here are some links that go into further detail which will hopefully help.

https://robots.net/tech/how-to-use-a-solid-state-drive-as-a-cache/
https://superuser.com/questions/390071/how-can-i-use-my-small-ssd-as-a-cache-for-a-larger-hard-disk
https://bytebitebit.com/tips-tricks/how-to-use-ssd-as-cache-for-hdd/

Happy configuring
Cheers,
Michael

Dr Michael Pilling (him / they)
Defence Analyst / Software Specialist 
_____________________________________________________________________________
Defence acknowledges the Traditional Custodians of the Country throughout Australia. We recognise their continuing
connectionto land, waters and community. We pay our respects to them, their culture and to their Elders past and
present. 


-----Original Message-----
From: Pilling, Michael DR <michael.pilling@defence.gov.au> 
Sent: Thursday, 17 October 2024 1:57 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>; Onni Hakala <onni@fyff.ee>; pgsql-novice@lists.postgresql.org
Subject: RE: Recommendations on how to combine SSD and HDD drives in bare metal PostgreSQL server [SEC=UNOFFICIAL]

EXTERNAL EMAIL: Do not click any links or open any attachments unless you trust the sender and know the content is
safe.

UNOFFICIAL
I would say the best way to handle this would be to do it in the hardware drivers or file system components of the OS
sothat the SSD drives were used as a cache for the HDDs. This would generally result in all the active indices of the
tablesresiding on the SSDs. It all depends whether your OS and its files systems are to the job. Certainly, Reisier4
filesystem aimed for all of its indices to be held in memory with only writes going to the HDDs - by using the SSDs for
swapspace this would have the desired effect under Reiser4.
 

Dr Michael Pilling (him / they)
Defence Analyst / Software Specialist
Human and Decision Sciences Division | Analytical Wargaming | Capability Analysis and Design Defence Science and
TechnologyGroup _____________________________________________________________________________
 
Department of Defence | Edinburgh  | 23L Bay 35 PO Box 1500 | EDINBURGH SA 5111
M: 0414 405 711 | www.defence.gov.au
E: michael.pilling@defence.gov.au

Defence acknowledges the Traditional Custodians of the Country throughout Australia. We recognise their continuing
connectionto land, waters and community. We pay our respects to them, their culture and to their Elders past and
present. 

-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, 17 October 2024 2:53 AM
To: Onni Hakala <onni@fyff.ee>; pgsql-novice@lists.postgresql.org
Subject: Re: Recommendations on how to combine SSD and HDD drives in bare metal PostgreSQL server

EXTERNAL EMAIL: Do not click any links or open any attachments unless you trust the sender and know the content is
safe.

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.
> 
> I have access to a server which has 2x 3.84TB NVME SSD disks and large array of HDD drives 8 x 22TB.
> 
> Most of the data that I have in my dataset is very rarely accessed and is stored only for archival purposes.
> 
> 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
formatin the HDDs?
 
> 
> I was initially looking into building zpool using zfs with raidz3 and 
> zstd compression for my HDDs but I’m unsure how to add the SSDs into 
> this equation and I thought 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'dmove tables that are no longer hot to that slow tablespace.
 
If you cannot move the whole table, use partitioning and move old partitions to the slow tablespace.

Yours,
Laurenz Albe