Hi Lukas,
I have read the patch, and I have a few questions/comments while going through it: Could this use RelFileLocator plus ForkNumber instead of open-coding BufferRelStatsKey? That seems closer to existing PostgreSQL abstractions for physical relation identity. I wonder whether pg_buffercache_relation_stats() is the best name here. The function is really aggregating by relation file identity plus fork, and it is producing a summary of the current buffer contents rather than what many readers might assume from “relation stats”. Would something with summary be clearer than stats? Why are OUT relforknumber and OUT relfilenode exposed as int2 and oid respectively? Internally these are represented as ForkNumber and RelFileNumber, so I wonder whether the SQL interface should reflect that more clearly, or at least whether the current choice should be explained. The comment says, “Hash key for pg_buffercache_relation_stats — groups by relation identity”, but that seems imprecise. It is really grouping by relfilenode plus fork, i.e. physical relation-file identity rather than relation identity in a more logical sense. Is PARALLEL SAFE actually desirable here, as opposed to merely technically safe? A parallel query could cause multiple workers to perform full shared-buffer scans independently, which does not seem obviously desirable for this kind of diagnostic function. Best regards, Haibo Yan > On Feb 28, 2026, at 3:58 PM, Lukas Fittl <[email protected]> wrote: > > Hi, > > See attached a patch that implements a new function, > pg_buffercache_relation_stats(), which returns per-relfilenode > statistics on the number of buffers, how many are dirtied/pinned, and > their avg usage count. > > This can be used in monitoring scripts to know which relations are > kept in shared buffers, to understand performance issues better that > occur due to relations getting evicted from the cache. In our own > monitoring tool (pganalyze) we've offered a functionality like this > based on the existing pg_buffercache() function for a bit over a year > now [0], and people have found this very valuable - but it doesn't > work for larger database servers. > > Specifically, performing a query that gets this information can be > prohibitively expensive when using large shared_buffers, and even on > the default 128MB shared buffers there is a measurable difference: > > postgres=# WITH pg_buffercache_relation_stats AS ( > SELECT relfilenode, reltablespace, reldatabase, relforknumber, > COUNT(*) AS buffers, > COUNT(*) FILTER (WHERE isdirty) AS buffers_dirty, > COUNT(*) FILTER (WHERE pinning_backends > 0) AS buffers_pinned, > AVG(usagecount) AS usagecount_avg > FROM pg_buffercache > WHERE reldatabase IS NOT NULL > GROUP BY 1, 2, 3, 4 > > ) > SELECT * FROM pg_buffercache_relation_stats WHERE relfilenode = 2659; > > relfilenode | reltablespace | reldatabase | relforknumber | buffers | > buffers_dirty | buffers_pinned | usagecount_avg > -------------+---------------+-------------+---------------+---------+---------------+----------------+-------------------- > 2659 | 1663 | 5 | 0 | 8 | > 0 | 0 | 5.0000000000000000 > 2659 | 1663 | 1 | 0 | 7 | > 0 | 0 | 5.0000000000000000 > 2659 | 1663 | 229553 | 0 | 7 | > 0 | 0 | 5.0000000000000000 > (3 rows) > > Time: 20.991 ms > > postgres=# SELECT * FROM pg_buffercache_relation_stats() WHERE > relfilenode = 2659; > relfilenode | reltablespace | reldatabase | relforknumber | buffers | > buffers_dirty | buffers_pinned | usagecount_avg > -------------+---------------+-------------+---------------+---------+---------------+----------------+---------------- > 2659 | 1663 | 1 | 0 | 7 | > 0 | 0 | 5 > 2659 | 1663 | 229553 | 0 | 7 | > 0 | 0 | 5 > 2659 | 1663 | 5 | 0 | 8 | > 0 | 0 | 5 > (3 rows) > > Time: 2.912 ms > > With the new function this gets done before putting the data in the > tuplestore used for the set-returning function. > > Thanks, > Lukas > > [0]: https://pganalyze.com/blog/tracking-postgres-buffer-cache-statistics > > -- > Lukas Fittl > <v1-0001-pg_buffercache-Add-pg_buffercache_relation_stats-.patch>
