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 04:39:26
Message-ID: CAN12+Y+D4W_+G-sqLjgvLCKmxfB18F7SspXtUerKQw4QTZarvA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Lukas,

On Tue, Nov 17, 2026 at 12:51:55AM, Lukas Fittl wrote:
>> The rest is in the attached patches, 0001 and 0002 being the parts
>> still needed in core.
>
> This is excellent progress, but I think we're missing something here
> in terms of required in-core work (unfortunately).

Yes, the remaining core patches are necessary for the rest of the
pg_stat_statements work.

> 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.

> After thinking more about it, I think mid-transaction flushes (shortly
> after top level statement end) are a requirement for us to
> successfully move pg_stat_statements to the cumulative stats system,
> because fundamentally pg_stat_statements is about statements, not
> transactions. Also, FWIW, I don't think the on-demand API helps us
> much there, unless I misunderstood how we want pg_stat_statements to
> use it.

You did not misunderstand. Right now the mid-transaction patch is there
so the pg_stat_statements regression tests pass, since they need to
flush mid-transaction. We still need the automatic flush to match the
current pg_stat_statements behavior.

> 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?

> 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.

> But in my experience the more common pattern (that we can fix) is
> transactions with a bunch of short-lived queries, e.g. very common
> with ORMs that do a transaction with simple queries, plus a bunch of
> non-database work that makes the transaction run long.

Agree, this is a common pattern, but that is also unfortunate,
because the best practice is to make transactions as short as
possible :)

> 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?

[1] https://www.postgresql.org/message-id/CAA5RZ0u84eMFeFWMaEo0D84ed3jF_RY0%3DRWD8tDwQWsNsU1qvA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/aWTVEycKj7Qh/SXH(at)ip-10-97-1-34(dot)eu-west-3(dot)compute(dot)internal

--
Sami Imseih
Amazon Web Services (AWS)

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-11-17 04:41:36 Re: Assertion failure with temporary relations in single-user mode
Previous Message Chee Wooson 2026-11-17 03:20:52 Re: [PATCH v2] Avoid carrying self lock-only xmax to updated tuple