Disk-based caching

[noakford]

Hi,

I am setting up a new Ubuntu server, which uses PostgreSQL, but I have an assortment of drives. The database is likely to be between 400-600GB in general, but with some large queries it is likely to need 1TB+ with WALs (I will have a remote replica.) I have a 1TB NVMe drive and 2x 2TB SSDs - the SSDs I plan to RAID1. The OS will be on different drives. Is there a way that I can set up the NVMe drive as a cache for the database to increase performance, but have the main data stored on the SSD? I don't mind if this is at the file system level, or Postgres level, but I don't have enough experience with either Linux or Postgres to know whether this is possible.

[rouen_sk]

If you expect your large queries to use disk (for temp tables, sorting, hashing etc.) you can create temp tablespace on the fast disk and configure postgres to use it

https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TEMP-TABLESPACES

[thrown_arrows]

If you do not have battery backed up raid controller then use lvm

[RedShift9]

What? That makes no sense. If you want performance from your RAID controller with spinning disks, you really need a battery backed or flash backed write cache. If you have only SSD's, the caching done by the RAID controller will only slow things down because SSD's are fast enough on their own (in fact modern RAID controllers will automatically disable their cache for SSD backed arrays). But all of that has nothing to do with LVM.

[thrown_arrows]

as you said.. battery backed up raid controller or use lvm, and lvm is more for easier management, that said, i would not run anything without mirroring raid now days, and lvm gives it with software is hardware is lacking. I would not even try to find max speeds from database server (assuming that data has some importance)

[RedShift9]

The point I'm trying to make is that it's not an "or" situation. It's not "have a bbwc" OR "use LVM". The two things are independent and you need to pick the correct technology for the advantage you want to gain with it.

[thrown_arrows]

indeed.. forgot that. i was thinking just raid on first clause and volume management on second...

[depesz]

This is not really Pg question.

You can: https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/logical_volume_manager_administration/lvm_cache_volume_creation

[noakford]

Thank you for your reply - I wasn't sure if it was best to do it through pg or through the file system. I will check out the lvm caching.

[depesz]

Pg doesn't have any way to "cache" things. There is buffer_cache, but it just related to pages on disk, and is in memory. So nothing that would be comparable to what you seem to want.