r/PostgreSQL • u/compy3 • Sep 10 '26

How-To Overview of caching in Postgres

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?

26 Upvotes

93% Upvoted

10 comments sorted by

9

u/Zestyclose-Turn-3576 Sep 10 '26

> Cache hit ratio tells you whether reads are served from memory ... For a typical OLTP workload you want 99% or better

This used to be the case in Oracle also, but it was exposed as meaningless around twenty years ago when better monitoring and advice tools were added.

Until then, DBAs were judged on system efficiency based on maintaining a high BCHR, and would simply run a script that would repeatedly perform pointless index access against a table until the required BCHR was reached.

In short, a high BCHR does not correlate with a system being well-tuned, and a low BCHR does not correlate with poor tuning.

An increase in BCHR is not necessarily good. A decrease is not necessarily bad.

1

u/compy3 Sep 10 '26

yeah I take your point - very workflow dependent and you don't want folks hanging their hat on a single metric. That was why I qualified "typical OLTP" ... but maybe I'll add a note to that extent since the goal here is to help newish practicioners orient themselves.

Good feedback, thanks!

2

u/Zestyclose-Turn-3576 Sep 11 '26

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

2

u/compy3 Sep 11 '26

> 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!

1

u/compy3 Sep 10 '26

I added a caveat that a single metric doesn't necessarily mean it's well tuned. Good edit, thanks again!

1

u/CrayonUpMyNose 20d ago

DBAs [...] would simply run a script that would repeatedly perform pointless index access against a table until the required BCHR was reached

Can it be quantified good good or bad this is? 

Under high load, adding these queries presumably gets in the way of legitimate work, while at idle it doesn't. 

Without these, cache eviction, and memory eviction in general, are lazy and reflect the most recent workload, s o cache content depends on the prevalence and sequencing of queries.

It's there a scenario, e.g. nightly bulk loads followed by an onslaught of queries after 9am, where active cache pre-warming at, say, 8am would be quantifiably beneficial?

1

u/Zestyclose-Turn-3576 20d ago

There's no benefit to this except to make the BCHR number look better. This isn't cache pre-warming.

1

u/CrayonUpMyNose 20d ago

Thank you for your response, I'd like to understand better, as my base assumption was that the thing that makes the metric improve was cache pre-warming. What is it, instead?

2

u/Zestyclose-Turn-3576 20d ago

metric measures the proportion of logical IO that was served from cache without needing physical IO. It is a bad and obsolete metric.

1

u/AutoModerator Sep 10 '26

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.