Re: Improve pg_stat_statements scalability

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 00:51:55
Message-ID: CAP53PkzizggqCeS2bsXYjfLyJi2MUNWnu_85-rhz=wfjZ3ypng@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On Wed, Nov 11, 2026 at 2:58 PM Sami Imseih <samimseih(dot)pg(at)gmail(dot)com> 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

Attachment Content-Type Size
nocfbot-0001-pgstat-Flush-non-transactional-stats-at-statement-bo.patch application/octet-stream 2.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shihao zhong 2026-11-17 01:01:39 Re: Fix two race conditions in processing interrupts
Previous Message Manu 2026-11-17 00:32:20 Assertion failure with temporary relations in single-user mode