Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 22 Jan 2026 09:02:10 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, pgsql-hackers@lists.postgresql.org,
	Zsolt Parragi <zsolt.parragi@percona.com>
Subject: Re: Flush some statistics within running transactions
Message-ID: <aXFpAisDKy6g_cKx@paquier.xyz>
References: <aWTVEycKj7Qh/SXH@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0vgN8nf7yPrk_uNt-ABFtRnRuz7-9CM=qSzUSwj=EEUKw@mail.gmail.com>
 <aWjbBd5z+LJNWX1l@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0t6j0VYuUpxZ8JLq-ERoUriZ0rK=+8PCUtjRirmSmCx7A@mail.gmail.com>
 <aWojE18aF9qj0EPI@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0s9j3x5UPpgaQdDyGA=MNjVEkT73SL7LMoVEKUwiZrVqA@mail.gmail.com>
 <aW4gwqVybB4INCvu@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0s6FkEHFdgKf8J6vueZGwsH+08LvV0YPBXa4Dw_8QgtTw@mail.gmail.com>
 <aXCroXBLiUpxk0Ap@ip-10-97-1-34.eu-west-3.compute.internal>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="rtp21q2nnA9wQW1C"
Content-Disposition: inline
In-Reply-To: <aXCroXBLiUpxk0Ap@ip-10-97-1-34.eu-west-3.compute.internal>
Archived-At: <https://www.postgresql.org/message-id/aXFpAisDKy6g_cKx%40paquier.xyz>
Precedence: bulk


--rtp21q2nnA9wQW1C
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 21, 2026 at 10:34:09AM +0000, Bertrand Drouvot wrote:
> No, 0003 also changes the flush mode for the database KIND. All the field=
s that
> I mentioned are inherited from relations stats and are flushed only at tr=
ansaction
> boundaries (so they don't appear in pg_stat_database until the transaction
> finishes). Does that make sense? (if the database kind is not switched to=
=20
> flush any time then none would appear while the transaction is in progres=
s, even
> the ones inherited from relations stats).
>=20
> PFA v3, also taking care of Zsolt's comment (thanks!) done up-thread.

While reading through 0001, I got to question on which properties
and/or assumptions of a stats kind one has to rely on to decide to
what flush_mode should be set.  To put is simpler, why don't we just
do a periodic pgstat_report_stat(false) call that would flush all the
stats for all stats kinds based on the new timeout registered,
expanding a bit the flush we currently do when idle in
ProcessInterrupts()?  It seems that one point of contention should be=20
that we should be careful with entries in the shmem hash table that
have been created in a transactional way, but we may already flush
them while we are in a transaction state, no?  Are there any fields in
a stats kind that we do may not want to flush?  If yes, it sounds to
me that it would be better to document these in the structures to
explain the reason why a flush mode is chosen over the other.

I am also not convinced that we have to be that aggressive with these
extra flushes.  The target is long-running analytical queries, that
could take minutes or even hours.  Using the same value as
PGSTAT_IDLE_INTERVAL (10s), perhaps renaming the value while on it,
would be a more natural fit.  A 1s vs 10s report interval does not
really matter for long analytical queries, where I'd imagine data
being picked up on at least a 30s interval, at the shortest.  Of
course, one may want to get a more "live" representation of the data
with more aggressive flushes, but is that really helpful for
long-running queries to have more granularity, stressing more the
shmem state?
--
Michael

--rtp21q2nnA9wQW1C
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmlxaQIACgkQnvQgOdby
QH0BPg//TgRtxKmxmpegUo55m8MpgBhU9Rwq/eBfZcAS/wRaB4b/daYFS0riBUrg
K+Sj1CJ3k6HvaEB+hyQx2DG9lMWE8LrONFMofOZyL5PtNqa/wMOoEGgVqsVkk/B4
1dufGhpwpI29N9WjCeQOcnarmYv8aNuQc+1lkVnSAxG5w77ScFpEO2ifATWZahpv
LB1OrLeuvBqhY4hnC6GW1Ws1Kz8sqC0hdK9KLSkTtJ/qcn0jGeY1M6tasSRZpPqI
sCQbQUZEqZgUE9/8ZQWAahF1Qa7WCDDnfmK3khDnTQSB9D3FCElv7zsFc3TLqnAu
NcjshIFwgzLsYONkIAkj3yEUPfkcrruzsuqOPjE3A+DYpnypmZhyFnpLhdxg2SFv
yqAu9155szzZ0QDgPNgX/5n/2lxVnRfCxCnmd8Y6K6ETrCKgJn6mREXhJoJH3yHp
O2aM1rftn1BgymVg4hPeJiRBrkIUpLFhLp/rysNkAzXSy4j8J93at46F+KEgiGRM
D01niQI7bSth6inpv84TP+tVwzDcpIXvB9A2EE8b2OBBpCxOZxk9VuY8oHUpG20Q
CqzpRviHQbHRlk1j1PU5V8QQpSGGaRi2TQvGhQJIEcz60esCbHDq5JUMgN4mnQWW
tPLrSdw5821AzeruHb5BEh07OR8lZaAy+qP1f3+J+ntKUpG72Bg=
=JxQo
-----END PGP SIGNATURE-----

--rtp21q2nnA9wQW1C--