MIME-Version: 1.0
References: <86f76aa5-1ab5-4e2e-9b15-405051852a2a@postgrespro.ru>
 <1e81a0a1-a63b-48fb-905a-d6495f89ab73@postgrespro.ru>
 <0b4eefc7-4c38-4caa-b2ca-a4c75dd7dd12@postgrespro.ru>
 <CAPpHfduoJEuoixPTTg2tjhnXqrdobuMaQGxriqxJ9TjN1uxOuA@mail.gmail.com>
 <fb8f05ad-096a-4865-9beb-08d4460222bb@postgrespro.ru>
 <333c2306-c401-4959-9f0c-a44c670a11a9@postgrespro.ru>
 <513f0188-b093-4cc8-98cf-4c324570d525@postgrespro.ru>
 <47a7b784-5218-43f2-96e3-65f9a729c5a5@tantorlabs.com>
 <d78a21c5-68ae-45b8-82a8-60f391b89d43@postgrespro.ru>
 <CAMFBP2oXkhX_k9FTqtW-LdTBepVq0PDuBEGO8-LpNGbyHTBrNw@mail.gmail.com>
 <Z9J+cRHwpBt4cQv3@ip-10-97-1-34.eu-west-3.compute.internal>
 <e66086a1-641a-4758-b3fc-5e92c74c9239@postgrespro.ru>
 <5583261b-eede-4341-b3b1-91650fefc1cf@postgrespro.ru>
 <2a04ad18-5572-4633-848b-eb57209e7ac0@postgrespro.ru>
 <CAA4eK1JOUn+EqWSfRgKfgBZOXT7Q2dw2enmSZZgOhoMOFwopPA@mail.gmail.com>
 <2f48399f-2959-4483-938f-64edb863ca76@postgrespro.ru>
In-Reply-To: <2f48399f-2959-4483-938f-64edb863ca76@postgrespro.ru>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Wed, 14 May 2025 14:25:05 +0530
Message-ID: 
 <CAA4eK1JatSKBuVZwsuHbV9==1WQ_6a2UswkmR2jEFK7kofshKQ@mail.gmail.com>
Subject: Re: Vacuum statistics
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
	Jim Nasby <jnasby@upgrade.com>,
 Bertrand Drouvot <bertranddrouvot.pg@gmail.com>,
	Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Kirill Reshke <reshkekirill@gmail.com>,
	Andrei Zubkov <zubkov@moonset.ru>, Masahiko Sawada <sawada.mshk@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
 jian he <jian.universality@gmail.com>,
	a.lepikhov@postgrespro.ru, Sami Imseih <samimseih@gmail.com>,
	vignesh C <vignesh21@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAA4eK1JatSKBuVZwsuHbV9%3D%3D1WQ_6a2UswkmR2jEFK7kofshKQ%40mail.gmail.com>
Precedence: bulk

On Tue, May 13, 2025 at 3:19=E2=80=AFPM Alena Rybakina
<a.rybakina@postgrespro.ru> wrote:
>
> On 12.05.2025 08:30, Amit Kapila wrote:
> > On Fri, May 9, 2025 at 5:34=E2=80=AFPM Alena Rybakina <a.rybakina@postg=
respro.ru> wrote:
> >> I did a rebase and finished the part with storing statistics separatel=
y from the relation statistics - now it is possible to disable the collecti=
on of statistics for relationsh using gucs and
> >> this allows us to solve the problem with the memory consumed.
> >>
> > I think this patch is trying to collect data similar to what we do for
> > pg_stat_statements for SQL statements. So, can't we follow a similar
> > idea such that these additional statistics will be collected once some
> > external module like pg_stat_statements is enabled? That module should
> > be responsible for accumulating and resetting the data, so we won't
> > have this memory consumption issue.
> The idea is good, it will require one hook for the pgstat_report_vacuum
> function, the extvac_stats_start and extvac_stats_end functions can be
> run if the extension is loaded, so as not to add more hooks.
> But I see a problem here with tracking deleted objects for which
> statistics are no longer needed. There are two solutions to this and I
> don't like both of them, to be honest.
> The first way is to add a background process that will go through the
> table with saved statistics and check whether the relation or the
> database are relevant now or not and if not, then
> delete the vacuum statistics information for it. This may be
> resource-intensive. The second way is to add hooks for deleting the
> database and relationships (functions dropdb, index_drop,
> heap_drop_with_catalog).
>

How does pg_stat_io manages this? I mean how it removes objects that
are dropped? Does some background task removes it?

> > BTW, how will these new statistics be used to autotune a vacuum?
> yes, but they are collected on demand - by guc.
> > And
> > do we need all the statistics proposed by this patch?
> >
> Regarding this issue, it was discussed here and so far we have come to
> the conclusion that statistics are needed for a deep understanding of
> the work of vacuum statistics [0] [1] [2].
>

I haven't gone through the emails, but my opinion is to break the
number of stats into some important subset of stats first and then
keep enhancing it. Right now, the patch struggles with two concerns:
one is what the design should be to capture the required stats, and
the second is convincing ourselves whether we need all the stats it is
trying to expose. Breaking into a smaller subset of stats could
alleviate the second concern.

--=20
With Regards,
Amit Kapila.