Date: Mon, 9 Mar 2026 09:15:03 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Lukas Fittl <lukas@fittl.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_buffercache: Add per-relation summary stats
Message-ID: <aa6Pl/+Oxl6ZipWw@ip-10-97-1-34.eu-west-3.compute.internal>
References: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aa6Pl/%2BOxl6ZipWw%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk

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