Message-ID: <79490c8c-2c35-464d-bb3e-3d2ea5aca9ab@postgrespro.ru>
Date: Thu, 4 Sep 2025 19:18:21 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Vacuum statistics
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Amit Kapila <amit.kapila16@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>
References: <86f76aa5-1ab5-4e2e-9b15-405051852a2a@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>
 <CAPpHfdtQd29O15Cmp1qeqTCerQF0Y+BGh63qtX3RkA7k=0TZ1Q@mail.gmail.com>
 <9a94f2a0-4698-40e3-9010-2567d3b8ba55@postgrespro.ru>
 <026722b5-6e61-44d8-816d-482ad24423a9@postgrespro.ru>
Content-Language: en-US
In-Reply-To: <026722b5-6e61-44d8-816d-482ad24423a9@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/79490c8c-2c35-464d-bb3e-3d2ea5aca9ab%40postgrespro.ru>
Precedence: bulk

To be honest, I haven=E2=80=99t provided extensions for the PostgreSQL [0=
] to=20
hackers yet, nor have I encountered this situation in general. Just in=20
case, I created an open repository on GitHub with the code and added a=20
description in the README.

[0] https://github.com/Alena0704/vacuum_statistics#

On 04.09.2025 18:49, Alena Rybakina wrote:
> Hi, all!
>
> On 02.06.2025 19:50, Alena Rybakina wrote:
>>
>> On 02.06.2025 19:25, Alexander Korotkov wrote:
>>> On Tue, May 13, 2025 at 12:49=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=20
>>>>> <a.rybakina@postgrespro.ru> wrote:
>>>>>> I did a rebase and finished the part with storing statistics=20
>>>>>> separately from the relation statistics - now it is possible to=20
>>>>>> disable the collection 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=20
>>>>> for
>>>>> pg_stat_statements for SQL statements. So, can't we follow a simila=
r
>>>>> idea such that these additional statistics will be collected once=20
>>>>> some
>>>>> external module like pg_stat_statements is enabled? That module=20
>>>>> 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=20
>>>> 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.
>>> +1
>>> Nice idea of a hook.=C2=A0 Given the volume of the patch, it might be=
 a
>>> good idea to keep this as an extension.
>> Okay, I'll realize it and apply the patch)
>>>
>>>> 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 th=
e
>>>> 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).
>>> Can we workaround this with object_access_hook?
>>
>> I think this could fix the problem. For the OAT-DROP access type, we=20
>> can call a function to reset the vacuum statistics for relations that=20
>> are about to be dropped.
>>
>> At the moment, I don=E2=80=99t see any limitations to using this appro=
ach.
>>
> I=E2=80=99ve prepared the first working version of the extension.
>
> I haven=E2=80=99t yet implemented writing the statistics to a file and=20
> reloading them into a hash table and shared memory at instance=20
> startup, and I also haven=E2=80=99t implemented a proper output for=20
> database-level statistics yet.
>
> I structured the extension as follows: statistics are stored in a hash=20
> table keyed by a composite key - database OID, relation OID, and=20
> object type (index, table, or database). When VACUUM or a worker=20
> processes a table or index, an exclusive lock is taken to update the=20
> corresponding record; a shared lock is taken when reading the=20
> statistics. For database-level output, I plan to compute the totals by=20
> summing table and index statistics on demand.
>
> To optimize that, I plan to keep entries in the hash table ordered by=20
> database OID. When accessing the first element by the partial key=20
> (database OID), I=E2=80=99ll scan forward and aggregate until the parti=
tial=20
> database key changes.
>
> Right now this requires adding the extension to=20
> `shared_preload_libraries`. I haven=E2=80=99t found a way to avoid that=
=20
> because of shared-memory setup, and I=E2=80=99m not sure it=E2=80=99s e=
ven possible.
>
> I=E2=80=99m also unsure whether it=E2=80=99s better to store the statis=
tics in the=20
> cumulative statistics system (as done here) or entirely inside the=20
> extension. Note that the code added to the core to support the=20
> extension executes regardless of whether the extension is enabled.