Re: Improve pg_stat_statements scalability

From: Sami Imseih <samimseih(dot)pg(at)gmail(dot)com>
To: Lukas Fittl <lukas(at)fittl(dot)com>
Cc: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>, michael(at)paquier(dot)xyz, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Improve pg_stat_statements scalability
Date: 2026-11-17 17:16:50
Message-ID: CAN12+YLV3t7PZwSjO9XG7hpGejodZggtiKgwF8sVbq=Pni3Bmw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> > > 2) Tools that reset min/max on a schedule (e.g. once a minute) will
> > > drop the min/max of executions that weren't flushed yet before the
> > > reset - which again is unbounded in terms of how many executions are
> > > affected in a long-running transaction
> >
> > For the min/max resets, 0003 already discards pending min/max values
> > from before a reset rather than merging them into the entry after the
> > reset, and with a throttled flush the number of executions that can
> > lose their min/max is bounded by the flush interval. Do you think
> > this will be a problem for tooling? If so, one option is to bypass
> > the pending entry for min/max and update the shared entry directly,
> > since updating min/max should not be a very common operation.
>
> Hmm. From a user/tooling perspective it would be appealing if we could
> always ensure min/max is updated in the shared stats, but are you sure
> that's not too expensive?
>
> Specifically, wouldn't you still need to lock the shared entry to
> compare, even if we don't update it? (I haven't played this through in
> detail, just a quick thought)

In v6, every execution already takes the entry lock exclusively, with
nowait, to update the usage. What we could do is update min/max while
holding that same lock, so no extra lock in the common case.

When the lock is busy, v6 leaves the usage to the pending entry, but
min/max can't go there. Instead, we could check min/max without the
lock, and only wait for it if the time is a new min or max, which is
rare except for the first few executions after a reset.

One thing I am not sure about though is how frequently tools reset
min/max during normal operations. You mentioned once a minute; which
is more frequent than I expected, but I will benchmark with this min/max
reset every minute as well.

> Whoops - that was me not looking carefully enough when attaching it.
> See the right patch attached now.

Thanks! I will take a look.

--
Sami

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2026-11-17 17:17:40 Re: pg_walinspect: add functions to locate and list WAL by time and LSN
Previous Message Tomas Vondra 2026-11-17 17:10:12 Re: [PATCH]Fix planner's strictness check for JsonExpr