MIME-Version: 1.0
References: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
 <CAKZiRmw1prTzB4DrU32AzPEN5tqE=XSpMNkZ86i-kkKq57F9kg@mail.gmail.com>
In-Reply-To: 
 <CAKZiRmw1prTzB4DrU32AzPEN5tqE=XSpMNkZ86i-kkKq57F9kg@mail.gmail.com>
From: Lukas Fittl <lukas@fittl.com>
Date: Mon, 2 Mar 2026 18:39:30 -0800
Message-ID: 
 <CAP53PkyFgPeBW1pMD_X0maEOvEJgMe=_cHG4kp4z=B_z4CZN5w@mail.gmail.com>
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAP53PkyFgPeBW1pMD_X0maEOvEJgMe%3D_cHG4kp4z%3DB_z4CZN5w%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 2, 2026 at 2:16=E2=80=AFAM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
> Hi Lukas, I have glanced at the patch briefly and couldn't find any
> issues - patch looks solid, however I'm not sure if e.g. launching whole
> NBuffers scan let's say every 5mins doesn't cause latency spikes on the
> system? I mean introducing such function seems to invite users to use
> pg_buffercache and I'm wondering if such regular pattern doesn't cause
> issues? (this is not FUD :), just more like a question based on Your's
> obervation)

Thanks for taking a look!

I think its the definitely the kind of thing you'd want to have people
opt-in to (we currently only do it when someone enables pg_buffercache
on their own, and allow turning it off completely), but on systems
that experience performance issues due to what's in cache (and have
some CPU capacity to spare), it seems to be helpful more than harmful,
I think. To be clear on the sample size, this is a subset of our user base
where we have experience with this, probably across 50-100 installs,
roughly speaking, of small to medium sized production systems (size as
in shared_buffers).

Also, FWIW, this isn't going not helpful if your cache contents change
completely once a minute, but in practice I think its more the
unexpected effects of e.g. large data loads or background processes
that mess with the cache, where tracking this over time can help you
find the root cause of slowness - we currently run this on a 10 minute
schedule when enabled, and that seems to work in terms of
understanding large swings in cache contents. I think even if you ran
this once an hour it could be helpful, and with the patch would give
you the data you're interested in ("whats in the cache") without
causing a large temporary file to be created.

>
> Also have you quantified what was the breaking point of previous query?
> (You wrote "larger database servers", but was that like 128GB+ shared_buf=
fers?
> and if so what would be the difference in terms of runtime there -- also
> like ~7x?)

We've currently set the default limit of where we measure this with
our tool at 200GB, but that's mainly because the temporary file that
gets written out with pg_buffercache today to do the grouping just
becomes noticeably large.

I'll work on sharing more numbers in the following days for larger
servers, to show the benefit of the patch.

Thanks,
Lukas


--
Lukas Fittl