Subject: Re: POC: track vacuum/analyze cumulative time per relation

From: Alena Rybakina <[email-masked]>
Date: Fri, 10 Jan 2025 17:37:26 +0300

Hi!

On 04.01.2025 06:41, wenhui qiu wrote:
> Hi Sami
>  Thank you for your path，it seems some path monitor vacuum status，Can 
> we synthesize their good ideas together。
>
I've been working on a patch that collects vacuum statistics since May 
[0]. It includes heap and index relation vacuum statistics, and of 
course, database vacuum statistics. We welcome reviews and any feedback!

It collects a bit more information than total_time, such as delay_time, 
buffer statistics, wal statistics, number of processed, skipped pages 
and tuples during vacuum operations, so
this information gives a complete picture of vacuum efficiency, because 
analyzing only total_time statistics does not give a complete picture of 
what vacuum did: was it cleaning almost huge index, cleaning tables or 
just sleeping.

[0] https://commitfest.postgresql.org/51/5012/

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Sami Imseih <[email-masked]>
Date: Fri, 10 Jan 2025 10:07:43 -0600

> what vacuum did: was it cleaning almost huge index, cleaning tables or

The purpose of total_time is to be able to calculate the average
time of a vacuum per relation from pg_stat_all_tables (by
total_time/vacuum_count) This is useful on its own to be able to
trend slow downs of vacuum ( or improvements after vacuum tuning )
for specific relations.

The work going into [1] is more detailed and will be useful
for deep dive investigations.In short, I think both proposals
have merit.

[1] https://commitfest.postgresql.org/51/5012/

Regards,

Sami

---

From: Sami Imseih <[email-masked]>
Date: Fri, 10 Jan 2025 10:21:56 -0600

> for deep dive investigations.In short, I think both proposals

Missed this point in my last reply. This patch also tracks
(auto)analyze per relation cumulative timing which can
be useful along with (auto)analyze_count to derive
the average.

Regards,

Sami