public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lukas Fittl <[email protected]>
To: Masahiko Sawada <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Paul A Jungwirth <[email protected]>
Cc: Khoa Nguyen <[email protected]>
Subject: Re: pg_buffercache: Add per-relation summary stats
Date: Sat, 28 Mar 2026 11:51:36 -0700
Message-ID: <CAP53PkzB00SBhtfRs4V8pUDtdxYYwm7KPizizhzs4gTbVxCrCw@mail.gmail.com> (raw)
In-Reply-To: <CAD21AoAHGyenvu7rh_f6d=LTi5=sgmJO=8jZgb3zeRjEjdckdQ@mail.gmail.com>
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>
<[email protected]>
<CAD21AoAHGyenvu7rh_f6d=LTi5=sgmJO=8jZgb3zeRjEjdckdQ@mail.gmail.com>
On Fri, Mar 27, 2026 at 10:37 PM Masahiko Sawada <[email protected]> wrote:
>
> On Fri, Mar 27, 2026 at 3:58 PM Tomas Vondra <[email protected]> wrote:
> > 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
--
Lukas Fittl
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], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_buffercache: Add per-relation summary stats
In-Reply-To: <CAP53PkzB00SBhtfRs4V8pUDtdxYYwm7KPizizhzs4gTbVxCrCw@mail.gmail.com>
* 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