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>
 <CAD21AoCzv5q89VDZ=Ot1wWguBO0MGdCc0-NXN83-Jg57uiJN1Q@mail.gmail.com>
 <CAExHW5tynt3Vmmhvvk929Ljfz6jTaJWWoRDthhV=e13GvYRSqQ@mail.gmail.com>
 <670b867e-6518-48b0-b7c2-bd5f10ab58c6@vondra.me>
 <CAD21AoAHGyenvu7rh_f6d=LTi5=sgmJO=8jZgb3zeRjEjdckdQ@mail.gmail.com>
In-Reply-To: 
 <CAD21AoAHGyenvu7rh_f6d=LTi5=sgmJO=8jZgb3zeRjEjdckdQ@mail.gmail.com>
From: Lukas Fittl <lukas@fittl.com>
Date: Sat, 28 Mar 2026 11:51:36 -0700
Message-ID: 
 <CAP53PkzB00SBhtfRs4V8pUDtdxYYwm7KPizizhzs4gTbVxCrCw@mail.gmail.com>
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>,
 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/CAP53PkzB00SBhtfRs4V8pUDtdxYYwm7KPizizhzs4gTbVxCrCw%40mail.gmail.com>
Precedence: bulk

On Fri, Mar 27, 2026 at 10:37=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
>
> On Fri, Mar 27, 2026 at 3:58=E2=80=AFPM Tomas Vondra <tomas@vondra.me> wr=
ote:
> > One thing we lose by doing ad hoc aggregation (instead of just relying
> > on the regular SQL aggregation operators) is lack of memory limit.
> > There's a simple in-memory hash table, no spilling to disk etc. The
> > simple pg_buffercache view does not have this issue, because the
> > tuplestore will spill to disk after hitting work_mem. Simplehash won't.
> >
> > The entries are ~48B, so there would need to be buffers for ~100k
> > (relfilenode,forknum) combinations to overflow 4MB. It's not very
> > common, but I've seen systems with more relations that this. Would be
> > good to show some numbers showing it's not an issue.
>
> Good point. I agree that we should not introduce the function in a way
> that there is a risk of using excessive memory while not respecting
> work_mem or other GUC parameters.

Yeah, I agree that is problematic regarding work_mem.

FWIW, I could see two methods to address that specifically, if we
wanted the special purpose function:

1) Error out if our hash table grows too large and require the user to
increase work_mem to get the data - seems inconvenient, but might be
okay if we are typically below work_mem limit anyway (I haven't run
the numbers on that yet)

2) Implement disk spill logic using a LogicalTapeSet or similar - I
think that'd be substantially more code, doesn't seem worth it just
for this (but if a situation like this recurs, we could consider a
more generalized facility)

Thanks,
Lukas

--=20
Lukas Fittl