Re: pg_buffercache: Add per-relation summary stats

Hi,

On Sat, Feb 28, 2026 at 03:58:34PM -0800, Lukas Fittl 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:

Thanks for the patch!

A few comments:

=== 1

+typedef struct
+{
+ RelFileNumber relfilenumber;
+ Oid reltablespace;
+ Oid reldatabase;
+ ForkNumber forknum;
+} BufferRelStatsKey;

What about making use of RelFileLocator (instead of 3 members relfilenumber,
reltablespace and reldatabase)?

=== 2

+ <para>
+ The <function>pg_buffercache_relation_stats()</function> function returns a
+ set of rows summarizing the state of all shared buffers, aggregated by
+ relation and fork number. Similar and more detailed information is
+ provided by the <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_relation_stats()</function> is significantly
+ cheaper.
+ </para>

I'm not 100% sure about the name of the function since the stats are "reset"
after a rewrite. What about pg_buffercache_relfilenode or
pg_buffercache_aggregated?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com