Hi, On Wed, Nov 11, 2026 at 2:58 PM Sami Imseih <[email protected]> wrote: > My apologies in advance for the long message, but there is quite a bit of > progress to cover. > > Since presenting the design and benchmarks at the PGConf.EU 2026 > Community Events Day Observability Summit, I have been getting the > patches in a reviewable state and benchmarking them a lot more, across > workloads and machine sizes.
Thanks for the productive conversations at PGConf.EU! > A good part of the groundwork has already been committed. > > - 4ba012a8ed9 Allow cumulative statistics to read/write auxiliary data > from/to disk > - 44539786b82 Allow a stats kind to use its own dshash in pgstats > - 6296e78c3d2 Add "own_hash" to pg_stat_kind_info > - 31f89191ff7 Add stats kind filter to pgstat_drop_matching_entries() > - e65c331b8fb Add new pgstats routine to split pending data setup > > 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). I've been poking at this locally with a few different use cases I have, and keep coming back to what I think is the biggest open issue in v5 and v6: When we flush, and how we can bridge the gap between what the cumulative statistics system does today and what pg_stat_statements users expect. 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). 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. 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. This matters most for long running transactions with short queries, where we otherwise face two problems with monitoring tools: 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 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 Having an explicit bound after statement end makes both problems manageable (and explainable to users), and is, I think, the best we can do. We can't fully guarantee it because of long-running queries that kick off before we were able to flush the prior query, and a current lack of facility that could trigger a flush mid-query. 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. 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. [0]: https://www.postgresql.org/message-id/acNTfL1xO_UUXkZQ%40paquier.xyz [1]: https://www.postgresql.org/message-id/CAA5RZ0u84eMFeFWMaEo0D84ed3jF_RY0%3DRWD8tDwQWsNsU1qvA%40mail.gmail.com Thanks, Lukas -- Lukas Fittl
nocfbot-0001-pgstat-Flush-non-transactional-stats-at-statement-bo.patch
Description: Binary data
