Re: Add last_vacuum_index_scans in pg_stat_all_tables

On 2022-Jul-04, Ken Kato wrote:

> I think having number of index scans of the last vacuum in
> pg_stat_all_tables can be helpful. This value shows how efficiently vacuums
> have performed and can be an indicator to increase maintenance_work_mem.

Yeah, this would be a good metric to expose, since it directly tells how
to set autovacuum_work_mem. I'm not sure that the shape you propose is
correct, though, because each vacuum run would clobber whatever value
was there before. No other stats counter works that way; they are all
additive. But I'm not sure that adding the current number each time is
sensible, either, because then the only thing you know is the average of
the last X runs, which doesn't tell you much.

Saving some sort of history would be much more useful, but of course a
lot more work.

> It was proposed previously[1], but it was not accepted due to the limitation
> of stats collector. Statistics are now stored in shared memory, so we got
> more rooms to store statistics. I think this statistics is still valuable
> for some people, so I am proposing this again.

> [1] https://www.postgresql.org/message-id/20171010.192616.108347483.horiguchi.kyotaro%40lab.ntt.co.jp

I read this thread, but what was proposed there is a bunch of metrics
that are not this one. The discussions there centered about how it
would be unacceptable to incur in the space cost that would be taken by
adding autovacuum-related metrics completely different from the one you
propose. That debate is now over, so we're clear to proceed. But we
need to agree on what to add.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/