We are investigating I/O load in one of our VMs and possibilities to optimize it and it showed that most of the load is generated by the stats collector of PostgreSQL. It jumps between 3,5 and 6,5 MB/s. I've already found some interesting sources on this topic and they suggested using tmpfs to hold most of the stats in memory, which make sense to me and the concrete VM has enough RAM free to support such an scenario.
Source 1 says the following:
After restart, the PostgreSQL will copy the files to the new location (and back when it's stopped).
This in combination with the
temp in the configuration name of
stats_temp_directory sounds like the data is persisted elsewhere.
So what happens to the temp data in case of unclean shutdown of the Postgres process? Is it completely lost if the process ran for the last week without any problem? Or does Postgres regularly persist the data outside of the temp directory during runtime? May it simply use the available temp data on restart after unclean shutdown?
I'm asking because currently once written data is persisted in the local filesystem and operations for writing the data seem to be atomic, but if we would switch to using tmpfs, stats data for some weeks could be lost if the whole server goes down for some reason.
Is there maybe a way to get Postgres to regularly persist data outside of tmpfs, like once every hours or so?
Or would I need to use some overlay/stacked/whatever filesystem, mounting the persistent one as lower, the tmpfs as upper and find some way to sync manually once a while?