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?



There is no tool built-in to PostgreSQL to persist the collector statistics periodically. They're considered replaceable. Do keep in mind that there is a difference between the table statistics gathered by the analyzer and the statistics collected by the statistics collector. The latter is what goes in the stats_temp_directory.

So your answer here is going to depend on why you want to persist them in the event of a crash. There are two reasons:

  1. You don't want Autovacuum to miss tables because it lost track of their update count;
  2. You're using the table update counts as part of your monitoring somewhere.

I'd argue that the first reason is likely inconsequential unless you have some reason why PostgreSQL is getting shut down unexpectedly every day, in which case you should fix that. Also, you can fix things by just running a database-wide VACUUM after you restart Postgres.

For the second reason, just accumulating counters isn't that useful in itself anyway. I mean, if a table has 100,000 deletes in its lifetime, does that mean it had 1000 a day for 100 days, or does it mean that someone just deleted half the table yesterday? You don't know. So if you care about those counts, you should be snapshotting your statistics every day or every hour so that you get a time as well as counts. You might look at this attempt to revive pgStatsPack, a tool which did exactly that.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.