| From: | Lukas Fittl <lukas(at)fittl(dot)com> |
|---|---|
| To: | Sami Imseih <samimseih(dot)pg(at)gmail(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 05:02:09 |
| Message-ID: | CAP53PkzL8K9W_KWtxCg8dozuPRruveaa6D81JKwLRNjxZN=d6Q@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Mon, Nov 16, 2026 at 8:39 PM Sami Imseih <samimseih(dot)pg(at)gmail(dot)com> wrote:
> > It appears that the recent design for mid-transaction flushing has
> > taken the route of an on-demand API to trigger flushes, in part
> > following Michael's comment in March 2026 [0], and in v6 that's only
> > used by a backend reading the view, for its own statistics. It was
> > suggested in July 2026 that we can build on top of that infrastructure
> > [1] to automatically flush when idle in a transaction, but I feel that
> > discussion has not really come back to that point (yet).
>
> Right, thanks for the reminder. We also discussed this off-list. I
> think the mid-transaction flush patch is in good shape, and my plan
> was to follow up with the automatic flush discussed in [1] once the
> mid-transaction flush APIs are committed.
Great!
> > I think what we need is an in-core mechanism that can flush stats
> > kinds opting in at statement end, at most once a second, or while idle
> > (including idle in transaction) within about 10 seconds.
>
> Something like this could work, but I think a single throttle for
> all kinds, every 10 seconds or some configurable value, is the better
> way forward. We understand it's not going to be 1-1 with the current
> pg_stat_statements behavior, but I think that is an acceptable
> trade-off. If we flush at a longer interval, such as 10 seconds, I am
> not sure we need kinds to opt in, since the cost of flushing all
> kinds at that rate should be small. Bertrand did some benchmarking in
> the first iteration of this work [2] and did not see a noticeable
> regression, even with a 1 second interval, but we will need to
> benchmark again with this design. What do you think?
>
> > 1) Tools that take snapshots of the counters, diff them, and assume
> > that activity between snapshots means "these statements ended in that
> > time frame" will misattribute when activity happened, since it's
> > pinned to transaction end instead of (roughly) statement end -- with
> > no bound on when the statement actually ended in case of long-running
> > transactions
>
> I think for this, tools should learn the new semantics, but we also
> need to make sure we bound the flushes. Existing tooling expects
> stats to be visible shortly after a statement ends, so the question
> is how tight the bound needs to be (1s, 10s?), balanced against the
> cost of flushing more often. What are your thoughts?
Makes sense, I don't have a strong opinion on what interval makes
sense here, but I suspect 1s would be nice to make work, unless we can
argue it has too much overhead.
FWIW, in pganalyze we currently pull every 1 minute (stats only) and
every 10 minutes (query text), so I think either 10 seconds or 1
second is fine from that perspective, but I know that other vendor's
tools, specifically Datadog DBM, queries pg_stat_statements every 10
seconds (with a mix of text and stats-only), so these other tools
might be skewed more if we delay flushing up to 10 seconds vs 1
second.
>
> > 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)
> > Attached is a sketch of what I think makes sense as an alternate
> > design for an in-core mechanism (on top of v6), building on
> > pgstat_report_stat() and the existing idle stats timeout, to help the
> > discussion. I can start a new thread for that (or add it to the other
> > thread), but wanted to make sure I post it here first in context of
> > where it's needed and what I think gets us to the right semantics.
>
> Maybe I am misunderstanding, but the attachment looks to be the
> example patch I posted in [1]. Did you mean for it to be applied on
> top of v6-0001, or did you intend to attach something else?
Whoops - that was me not looking carefully enough when attaching it.
See the right patch attached now.
Thanks,
Lukas
--
Lukas Fittl
| Attachment | Content-Type | Size |
|---|---|---|
| nocfbot-0001-pgstat-Allow-variable-stats-kinds-to-be-flu.patch | application/octet-stream | 21.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Henson Choi | 2026-11-17 04:57:13 | [DEPARSE] A JSON DEFAULT cast to a length-limited RETURNING type truncates after restore |