public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bertrand Drouvot <[email protected]>
To: Lukas Fittl <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_buffercache: Add per-relation summary stats
Date: Mon, 9 Mar 2026 09:15:03 +0000
Message-ID: <aa6Pl/[email protected]> (raw)
In-Reply-To: <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
References: <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>

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

view thread (27+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: pg_buffercache: Add per-relation summary stats
  In-Reply-To: <aa6Pl/[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox