Add an emptyDir ephemeral volume for stats_temp_directory to be in RAM

[gricey432]

trafficstars

(I'm fairly confident I can't already do this, apologies if I missed something).

Overview

The Postgres statistics collector can use a high rate of IO to the disk for its tempfile-based communication between processes. When using IOPS-constrained cloud drives this can be a waste of potential performance.

Postgres suggests mounting this temp directory into RAM to improve performance and reduce disk load.

Desired Behavior

AWS RDS offers this through a custom parameter rds.pg_stat_ramdisk_size which creates a ramdisk if a non-0 value is provided.

PGO could provide spec.instances[n].statsTempDirectoryVolumeSpec which is just an ephemeral volume spec. This'd be powerful and would allow for using a local SSD instead of precious memory, but it's more complex.

Or could add a simpler setting like RDS which is a size in MB (maybe spec.instances[n].statsRamdiskSize). This is then used by PGO to make an emptyDir for us like

  volumes:
  - name: stats-temp-directory
    emptyDir:
     medium: Memory
     sizeLimit: {{size}}

Though this relies on SizeMemoryBackedVolumes feature gate (on by default in 1.22).

Then, if the user configures as above:

• PGO mounts the volume somewhere
• PGO modifies the postgres config to set stats_temp_directory to this volume's mount point

Environment

Tell us about your environment:

Please provide the following details:

• Platform: EKS
• Platform Version: eks.2 k8s 1.22
• PGO Image Tag: ubi8-14.2-1
• Postgres Version: 14
• Storage: gp3
• Number of Postgres clusters: 2