PostgreSQL: Is there any way to differentiate an actual Disk I/O from system cache hit?

Question

When using EXPLAIN with buffers, the blocks read sometimes are actually from system cache, this is understandable sometimes based on the read time. But is there any proper way to understand a system cache-miss as of version 13?

Answer 1

Since the system cache belongs to the system, you mostly need to use system tools to assess it. vmstat being the preeminent one for me. Unless you have a lot of non-PostgreSQL related things also going on (in which case, maybe you should avoid that) it should be pretty easy to interpret that output as pertaining directly to what you see in EXPLAIN (BUFFERS).

If you need more focused ways, I often resort to strace -y -ttt -T -epread64 -p pid_goes_here and then you can see the timing of every read. Another option is set client_min_messages TO log; set log_statement_stats TO on;

You also might want to try the 3rd party extension pg_stat_kcache, but I've never familiar enough with it to know if I can trust it.

I've also considered hacking a feature into track_io_timing to declare reads to be cache misses if they took more than a certain number of microseconds to complete, but never got very far on that.