Date: Thu, 15 Jan 2026 12:18:13 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Flush some statistics within running transactions
Message-ID: <aWjbBd5z+LJNWX1l@ip-10-97-1-34.eu-west-3.compute.internal>
References: <aWTVEycKj7Qh/SXH@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAA5RZ0vgN8nf7yPrk_uNt-ABFtRnRuz7-9CM=qSzUSwj=EEUKw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAA5RZ0vgN8nf7yPrk_uNt-ABFtRnRuz7-9CM=qSzUSwj=EEUKw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aWjbBd5z%2BLJNWX1l%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk

Hi,

On Wed, Jan 14, 2026 at 09:54:17PM -0600, Sami Imseih wrote:
> I took a quick look at the patches and I have some general comments.

Thanks!

> 
> > Long running transactions can accumulate significant statistics (WAL, IO, ...)
> > that remain unflushed until the transaction ends. This delays visibility of
> > resource usage in monitoring views like pg_stat_io and pg_stat_wal.
> 
> +1. I do think this is a good idea. Long-running transactions cause accumulated
> stats to appear as spikes in monitoring tools rather than as gradual activity.
> This would help level out, though not eliminate, those artificial spikes.

Yeah.

> > The 1 second flush interval is currently hardcoded but we could imagine increase
> > it or make it configurable.
> 
> Someone may want to turn this off as well. I think a GUC will be needed.

I gave this more thoughts and I wonder if this should be configurable at all.
I mean, we don't do it for PGSTAT_MIN_INTERVAL, PGSTAT_MAX_INTERVAL and
PGSTAT_IDLE_INTERVAL. We could imagine make it configurable if it produces
noticeable performance impact but that's not what I observed. 

> > RELATION stats are making use of FLUSH_MIXED:
> 
> > stats: numscans, tuples_returned, tuples_fetched, blocks_fetched,
> > blocks_hit
> 
> I’m concerned that fields being temporarily out of sync might impact monitoring
> calculations, if the formula is dealing with fields that have
> different flush strategies.

That's a good point. Maybe we should document the fields flush strategy?

> That said, minor discrepancies are usually tolerable for monitoring
> data analysis.
> 
> For the numscans, should we not also update the scan timestamp?

The problem is that we could not call GetCurrentTransactionStopTimestamp(), so 
we would need to call GetCurrentTimestamp() instead. I'm not sure that calling
GetCurrentTimestamp() every second would be a real issue though, and if it is
maybe we could increase this 1s value.

That said I agree that having seq_scan being updated and not last_seq_scan is not
that great. 

Maybe we should keep this in mind and see what to do depending where this thread
is going (I mean if the current proposed design has to be changed).

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com