MIME-Version: 1.0
References: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
 <CAD21AoCKz0t06-qOOm_7kgMn=Niu0yYB3R5jhOY0oVb=5AWytA@mail.gmail.com>
 <CAExHW5uqQuPNEjj2ondemCYdtFHExm=jvw-4naPn7gdMmkhrgw@mail.gmail.com>
 <CAP53PkyK65PM-Ws7ctj5-eKqVxHo5FzoWKW7=XPq5Jc1qZCJNg@mail.gmail.com>
In-Reply-To: 
 <CAP53PkyK65PM-Ws7ctj5-eKqVxHo5FzoWKW7=XPq5Jc1qZCJNg@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 25 Mar 2026 09:48:52 -0700
Message-ID: 
 <CAD21AoCzv5q89VDZ=Ot1wWguBO0MGdCc0-NXN83-Jg57uiJN1Q@mail.gmail.com>
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Lukas Fittl <lukas@fittl.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
	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/CAD21AoCzv5q89VDZ%3DOt1wWguBO0MGdCc0-NXN83-Jg57uiJN1Q%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 24, 2026 at 11:47=E2=80=AFPM Lukas Fittl <lukas@fittl.com> wrot=
e:
>
> Hi Ashutosh,
>
> On Tue, Mar 24, 2026 at 11:24=E2=80=AFPM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > I know we already have a couple of hand-aggregation functions but I am
> > hesitant to add more of these. Question is where do we stop? For
> > example, the current function is useless if someone wants to find the
> > parts of a relation which are hot since it doesn't include page
> > numbers. Do we write another function for the same? Or we add page
> > numbers to this function and then there's hardly any aggregation
> > happening. What if somebody wanted to perform an aggregation more
> > complex than just count() like average number of buffers per relation
> > or distribution of relation buffers in the cache, do they write
> > separate functions?
>
> I think the problem this solves for, which is a very common question I
> hear from end users, is "how much of this table/index is in cache" and
> "was our query slow because the cache contents changed?".
>
> It can't provide a perfect answer to all questions regarding what's in
> the cache (i.e. it won't tell you which part of the table is cached),
> but its in line with other statistics we do already provide in
> pg_stat_user_tables etc., which are all aggregate counts, not further
> breakdowns.
>
> Its also a reasonable compromise on providing something usable that
> can be shown on dashboards, as I've seen in collecting this
> information using the existing methods from small production systems
> in practice over the last ~1.5 years.

Regarding the proposed statistics, I find them reasonably useful for
many users. I'm not sure we need to draw a strict line on what belongs
in the module. If a proposed function does exactly what most
pg_buffercache users want or are already writing themselves, that is
good enough motivation to include it.

I think pg_visibility is a good precedent here. In that module, we
have both pg_visibility_map() and pg_visibility_map_summary(), even
though we can retrieve the exact same results as the latter by simply
using the former:

select sum(all_visible::int), sum(all_frozen::int) from
pg_visibility_map('test') ;

Regards,

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