public inbox for [email protected]  

help / color / mirror / Atom feed

From: Lukas Fittl <[email protected]>
To: Jakub Wartak <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_buffercache: Add per-relation summary stats
Date: Mon, 2 Mar 2026 18:39:30 -0800
Message-ID: <CAP53PkyFgPeBW1pMD_X0maEOvEJgMe=_cHG4kp4z=B_z4CZN5w@mail.gmail.com> (raw)
In-Reply-To: <CAKZiRmw1prTzB4DrU32AzPEN5tqE=XSpMNkZ86i-kkKq57F9kg@mail.gmail.com>
References: <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
	<CAKZiRmw1prTzB4DrU32AzPEN5tqE=XSpMNkZ86i-kkKq57F9kg@mail.gmail.com>

On Mon, Mar 2, 2026 at 2:16 AM Jakub Wartak
<[email protected]> 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_buffers?
> 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

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]
  Subject: Re: pg_buffercache: Add per-relation summary stats
  In-Reply-To: <CAP53PkyFgPeBW1pMD_X0maEOvEJgMe=_cHG4kp4z=B_z4CZN5w@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