MIME-Version: 1.0
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>
 <aXFpAisDKy6g_cKx@paquier.xyz> <CAA5RZ0sQxAu6bU8wTgvs+aTSvhBcziH9jCJ27aS1hzKsm2kmTQ@mail.gmail.com>
In-Reply-To: <CAA5RZ0sQxAu6bU8wTgvs+aTSvhBcziH9jCJ27aS1hzKsm2kmTQ@mail.gmail.com>
From: Fujii Masao <masao.fujii@gmail.com>
Date: Thu, 22 Jan 2026 10:56:48 +0900
Message-ID: <CAHGQGwGBkPEK=NpLuk1HSmccu6OA2FYqkkGcA-Hb3WLs6dX=cg@mail.gmail.com>
Subject: Re: Flush some statistics within running transactions
To: Sami Imseih <samimseih@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Bertrand Drouvot <bertranddrouvot.pg@gmail.com>, 
	pgsql-hackers@lists.postgresql.org, Zsolt Parragi <zsolt.parragi@percona.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAHGQGwGBkPEK%3DNpLuk1HSmccu6OA2FYqkkGcA-Hb3WLs6dX%3Dcg%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 22, 2026 at 10:41=E2=80=AFAM Sami Imseih <samimseih@gmail.com> =
wrote:
>
> > > No, 0003 also changes the flush mode for the database KIND. All the f=
ields that
> > > I mentioned are inherited from relations stats and are flushed only a=
t transaction
> > > boundaries (so they don't appear in pg_stat_database until the transa=
ction
> > > finishes). Does that make sense? (if the database kind is not switche=
d to
> > > flush any time then none would appear while the transaction is in pro=
gress, even
> > > the ones inherited from relations stats).
> > >
> > > 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()?
>
> There are some important cases in which we would want to
> distinguish between a "transaction boundary" flush vs an
> "anytime" flush.
>
> For example, xact_commit/rollback. I would want those
> fields to be in sync with tuples_inserted/updated/deleted
> to allow for accurate calculations like number of inserts
> per commit, etc.
>
> Another one would be n_mod_since_analyze, That should
> only be updated after commit (or not after rollback). Otherwise,
> it may throw autovanalyze threshold calculations way off. Same
> for n_dead_tup and autovacuum.
>
> > 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),
>
> PGSTAT_IDLE_INTERVAL is flushing an idle backend every 10 seconds
> IIUC. So this value only applies when outside of a transaction.
>
> > A 1s vs 10s report interval does not really matter for long analytical =
queries.
>
> Sure, Bertrand mentioned early in the thread that the anytime flushes
> could be made configurable. Perhaps that is a good idea where we can
> default with something large like 10s intervals for anytime flushes, but =
allow
> the user to configure a more frequent flushes ( although I would think
> that 1 sec is the minimum we should allow ).

+1 on adding an option to control the interval. With a fixed interval
(for example, 1s), log_lock_waits messages could be emitted that frequently=
,
which may be annoying for some users.

Of course, it would be even better if these periodic wakeups did not trigge=
r
log_lock_waits messages at all, though.

Regards,


--=20
Fujii Masao