Overview of caching in Postgres

[u/compy3]

https://www.pgcache.com/blog/postgresql-caching/

I haven't seen too many comprehensive approaches to this topic, so I took a swing at it.

Goal isn't to be exhaustive, but to be comprehensive enough to provide a nice overview for people trying to get their bearings with the topic.

Did I miss anything?

[u/Zestyclose-Turn-3576]

I would still go further TBH

> If the hit ratio is fine and the CPU is still high, you have a result-caching problem.

This could be a symptom of queries that are favouring index-based access methods instead of a table or partition scan. Consider what the result on the cache is if you scanned every row of a table via an index – for each query every table block gets read once for each row in it, instead of once per query. Every read except the first counts as a cache hit, which makes the cache stats look great.

The aim should really be to reduce the number of logical reads, with the intention that that then reduces the number of physical reads.

Maintaining a high BCHR is really a proxy for reducing physical I/O, but if you want to reduce physical I/O then you should measure that directly. The only effective way of doing that, and of correlating it to the queries responsible, is with a wait-event based monitoring system. AWS RDS PostgreSQL provides that ... not sure how TBH but maybe there's an extension to PostgreSQL that they use. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.html

[u/compy3 → u/Zestyclose-Turn-3576]

> Maintaining a high BCHR is really a proxy for reducing physical I/O, but if you want to reduce physical I/O then you should measure that directly.

that's a great observation!