MIME-Version: 1.0
References: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
In-Reply-To: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Tue, 24 Mar 2026 12:09:13 -0700
Message-ID: 
 <CAD21AoCKz0t06-qOOm_7kgMn=Niu0yYB3R5jhOY0oVb=5AWytA@mail.gmail.com>
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Lukas Fittl <lukas@fittl.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Paul A Jungwirth <pj@illuminatedcomputing.com>,
 Khoa Nguyen <khoaduynguyen@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoCKz0t06-qOOm_7kgMn%3DNiu0yYB3R5jhOY0oVb%3D5AWytA%40mail.gmail.com>
Precedence: bulk

Hi Lukas,

On Sat, Feb 28, 2026 at 3:59=E2=80=AFPM Lukas Fittl <lukas@fittl.com> 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.

Thank you for the proposal!

Paul A Jungwirth, Khoa Nguyen, and I reviewed this patch through the
Patch Review Workshop, and I'd like to share our comments.

>
> 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=3D# 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 =3D 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=3D# SELECT * FROM pg_buffercache_relation_stats() WHERE
> relfilenode =3D 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.

Overall, we find that the proposed feature is useful. The proposed way
is much cheaper, especially when the number of per-relation stats is
not large.

Here are review comments on the v1 patch:

---
-   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql
+   pg_buffercache--1.5--1.6.sql pg_buffercache--1.6--1.7.sql \
+   pg_buffercache--1.7--1.8.sql

Since commit 4b203d499c6 bumped the version from 1.6 to 1.7 last
November, we think we don't need to bump the version again for this new
feature.

---
+/*
+ * Hash key for pg_buffercache_relation_stats =E2=80=94 groups by relation=
 identity.
+ */
+typedef struct
+{
+   RelFileNumber relfilenumber;
+   Oid         reltablespace;
+   Oid         reldatabase;
+   ForkNumber  forknum;
+} BufferRelStatsKey;
+
+/*
+ * Hash entry for pg_buffercache_relation_stats =E2=80=94 accumulates per-=
relation
+ * buffer statistics.
+ */
+typedef struct
+{
+   BufferRelStatsKey key;      /* must be first */
+   int32       buffers;
+   int32       buffers_dirty;
+   int32       buffers_pinned;
+   int64       usagecount_total;
+} BufferRelStatsEntry;

Can we move these typedefs above function prototypes as other typedefs
are defined there?

---
+   relstats_hash =3D hash_create("pg_buffercache relation stats",
+                               128,
+                               &hash_ctl,
+                               HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);

It might be worth considering simplehash.h for even better performance.

---
+   while ((entry =3D (BufferRelStatsEntry *) hash_seq_search(&hash_seq)) !=
=3D NULL)
+   {
+       if (entry->buffers =3D=3D 0)
+           continue;
+

We might want to put CHECK_FOR_INTERRUPTS() here too as the number of
entries can be as many as NBuffers in principle.

---
We've discussed there might be room for improvement in the function
name. For example, pg_buffercache_relations instead of
pg_buffercache_relation_stats might be a good name, since everything
in this module
is stats.  if we drop "_stats" then "relation" should be plural, to
match other functions in the module ("pages", "os_pages",
"numa_pages", "usage_counts").

Regards,

--=20
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com