stats_fetch_consistency: caching PostgreSQL monitoring statistics?

I have worked with PostgreSQL since 2006. Still, I keep learning about new aspects of PostgreSQL regularly. Sometimes it feels like the more I know, the better I see that I have yet a lot to learn. Often, beginners' questions in a training session make me learn something new! Today, my co-worker Pavlo told be about the PostgreSQL parameter stats_fetch_consistency. Probably not a parameter that you want to tune every day, but you never know.

Surprise: stale statistics with the default stats_fetch_consistency

Let's imagine you are trying to investigate checkpoint statistics, which you can find in the view pg_stat_checkpointer since PostgreSQL v17:

Huh? Why did num_requested not jump to 5? If we commit the transaction, it does:

It looks like the statistics only get updated in a new transaction. That is somewhat surprising, as I didn't use the REPEATABLE READ isolation level. Until today, I didn't know that I can change this behavior with stats_fetch_consistency!

Possible settings for stats_fetch_consistency

The parameter stats_fetch_consistency governs how PostgreSQL caches data for the following statistics views:

• pg_stat_archiver (WAL archiver statistics)
• pg_stat_bgwriter (background writer statistics)
• pg_stat_checkpointer (checkpointer statistics)
• pg_stat_io (I/O statistics per backend type)
• pg_stat_slru (statistics about SLRU caches)
• pg_stat_wal (WAL statistics)

The possible values are:

• none: PostgreSQL doesn't cache statistics data at all, but reads them whenever you access one of the above statistics views
• cache: when you access the statistics for an object (table etc.) for the first time in a database transaction, PostgreSQL reads the data for that object and caches then for the duration of the transaction
• snapshot: when you access the statistics for an object for the first time in a database transaction, PostgreSQL reads the data for all objects in the database and keeps them cached for the duration of the transaction

The default value is cache. So to avoid the surprise in the previous section, all I would have had to do was to set stats_fetch_consistency to “none”!

The history of stats_fetch_consistency

PostgreSQL v15 introduced stats_fetch_consistency in commit 5891c7a8ed as part of the effort to track database statistics in shared memory. So if you, like me, have never heard about this parameter before, you have at least the excuse that it is a fairly recent one.

Use cases for stats_fetch_consistency

I'd argue that the default value cache is rarely what you want. For ad-hoc queries, you probably want the setting “none” to avoid surprises like the one above. If you have a monitoring system that regularly queries all kinds of database statistics in a single transaction, “snapshot” may make sense to get consistent values. Otherwise, I'd also use “none” for my monitoring system, hoping that it won't query statistics for the same object multiple times.

Conclusion

stats_fetch_consistency is one of the less important PostgreSQL parameters. I don't think it matters much, but a setting of “none” is probably good in most cases.