MIME-Version: 1.0
References: <bee34abe-fe87-49b3-8523-8ae19e641ccf@yandex.ru>
 <9b10c6d3-52c4-4eef-b67c-c33442667729@postgrespro.ru> <CAD21AoAVK7DwTZLfhwuRhTGgR=_ASu5YshEg_Cmpojk5ZdZ3tA@mail.gmail.com>
 <CAAKRu_auu=xt4w3Mm_jW-voJunZgno6XDDohH6hhRc4Z9dGdYQ@mail.gmail.com>
 <CAD21AoD66b3u28n=73kudgMp5wiGiyYUN9LuC9z2ka6YTru5Gw@mail.gmail.com>
 <9485d892-fd04-4e3a-ac24-7dd767cb7333@postgrespro.ru> <CAPpHfduwY8-fp34CuO9O57ouCs1K=Gn1rTnuG4AaWYhEo6nXyw@mail.gmail.com>
 <0B6CBF4C-CC2A-4200-9126-CE3A390D938B@upgrade.com> <6732acf8ce0f31025b535ae1a64568750924a887.camel@moonset.ru>
 <5AA8FFD5-6DE2-4A31-8E00-AE98F738F5D1@upgrade.com> <85b963fe-5977-43aa-9241-75b862abcc69@postgrespro.ru>
 <E0B6D242-C16D-4848-930E-B693D4E5C678@upgrade.com> <e2003f94-2075-4183-8298-9d25d8431fb1@postgrespro.ru>
 <9C7A167C-DCDE-4A17-9ABE-6276723FEC50@upgrade.com> <2d493cf9-9ba7-4cc1-a3f2-67afd7c163ee@postgrespro.ru>
 <CAPpHfds=woPcB9nPtMmu=g=U9q6-FHFh7fF_x=uhU3k2Oi03sA@mail.gmail.com>
 <f972c2c9-edcc-49b8-9f23-6208eedc3d23@tantorlabs.com> <77e6e723-0d3e-4235-8386-03d143916125@postgrespro.ru>
In-Reply-To: <77e6e723-0d3e-4235-8386-03d143916125@postgrespro.ru>
From: Sami Imseih <samimseih@gmail.com>
Date: Thu, 2 Jan 2025 14:12:08 -0600
Message-ID: <CAA5RZ0sjDx6QFRJJ+e=orwvLboLWO42f2qF0_HbDq-sMq=tW4g@mail.gmail.com>
Subject: Re: Vacuum statistics
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>, Jim Nasby <jnasby@upgrade.com>, 
	Andrei Zubkov <zubkov@moonset.ru>, Masahiko Sawada <sawada.mshk@gmail.com>, 
	Melanie Plageman <melanieplageman@gmail.com>, jian he <jian.universality@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>, a.lepikhov@postgrespro.ru, 
	Alexander Korotkov <aekorotkov@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAA5RZ0sjDx6QFRJJ%2Be%3DorwvLboLWO42f2qF0_HbDq-sMq%3DtW4g%40mail.gmail.com>
Precedence: bulk

Hi,

Thanks for the work you have done here. Exposing cumulative
metrics at this level of detail for vacuum is surely useful to find
vacuum bottlenecks and to determine the effectiveness of
vacuum tuning.

I am yet to look very closely, but I think some additional columns that
will be useful is the number of failsafe autovacuums occurred. Also
the counter for number of index_cleanup skipped, truncate phase
skipped and toast vacuuming skipped ( the latter will only be relevant
for the main relation ).

I also wonder if if makes sense to break down timing by phase. I surely
would like to know how much of my vacuum time was spent in index
cleanup vs heap scan, etc.

A nit: I noticed in v14, the column is "schema". It should be "schemaname"
for consistency.

Also, instead of pg_stat_vacuum_tables, what about pg_stat_vacuum?

Now, I became aware of this discussion after starting a new thread
to track total time spent in vacuum/analyze in pg_stat_all_tables [1].
But this begs the question of what should be done with the current
counters in pg_stat_all_tables? I see it mentioned above that (auto)vacuum_count
should be added to this new view, but it's also already in pg_stat_all_tables.
I don't think we should be duplicating the same columns across views.

I think total_time should be removed from your current patch and added
as is being suggested in [1]. This way high level metrics such as counts
and total time spent remain in pg_stat_all_tables, while the new view
you are proposing will contain more details. I don't think we will have
consistency issues between the views because a reset using pg_stat_reset()
will act on all the stats and pg_stat_reset_single_table_counters() will act on
all the stats related to that table. There should be no way to reset the vacuum
stats independently, AFAICT.

Alternatively, we can remove the vacuum related stats from pg_stat_all_tables,
but that will break monitoring tools and will leave us with the (auto)analyze
metrics alone in pg_stat_all_tables. This sounds very ugly.

What do you think?

Regards,

Sami Imseih
Amazon Web Services (AWS)

[1] https://commitfest.postgresql.org/52/5485/