Message-ID: <6a2ac9b7-6535-4bb1-8274-0647f7c31c82@dalibo.com>
Date: Mon, 12 Aug 2024 15:41:26 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: New GUC autovacuum_max_threshold ?
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 "Imseih (AWS), Sami" <simseih@amazon.com>,
 David Rowley <dgrowleyml@gmail.com>, Joe Conway <mail@joeconway.com>,
 Michael Banck <mbanck@gmx.net>, Laurenz Albe <laurenz.albe@cybertec.at>,
 Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <CA+Tgmoa9FzNLNtD-PYptJP1ApV=a57RwqOVypb0gL5uLzLtCFQ@mail.gmail.com>
 <CAApHDvo8DWyt4CWhF=NPeRstz_78SteEuuNDfYO7cjp=7YTK4g@mail.gmail.com>
 <CA+TgmoaCQ_GCwn3V8uXc+60oSJivY6ouiAqe81Z3h1kT6zMMYQ@mail.gmail.com>
 <20240507211702.GA2720371@nathanxps13>
 <0A799687-DE0A-4BFC-8900-E1D57EB6C211@amazon.com>
 <CA+TgmoYF3HqEUArbsqtCtP7YMJ62G3U8W1mSmZFZoCK8uBSi9g@mail.gmail.com>
 <cc23c226-e0be-47a7-bf6f-bcedd097a239@dalibo.com>
 <CA+TgmoaER+O40PA_CB8eSKm7KT37wDT+-y_mnsWXb9-Keivu1Q@mail.gmail.com>
 <ZnD509Q1jROYKhz_@nathan> <97f04e09-a08a-417c-b65d-5d5cbdd0b845@dalibo.com>
 <ZrPprRLBzi1YUBLn@nathan>
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Content-Language: fr, en-US
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAmZDVJ8CGwMFCQtfi90FCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNV65gEA6o+UrebFjn2CIAOYwP3CiiJugiM5TMH9yJyehQua5ZoA/2ChxiVn
 8OUaAw/ErxJmQi1mSN0tNZBL/KhkUPBDUDQNzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAmZDVJ8FCQtfi90ACgkQ1XGWhN8zGNWOOAEArSqePBqfd4Kx4ulACaWO
 6fM+XSDfUlBAQXx9rU6DR4MBALwozl9g91tRysim6lKxggvBfp/PvbpTZNrxYLWB2fYP
In-Reply-To: <ZrPprRLBzi1YUBLn@nathan>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6a2ac9b7-6535-4bb1-8274-0647f7c31c82%40dalibo.com>
Precedence: bulk


On 8/7/24 23:39, Nathan Bossart wrote:
> I've attached a new patch to show roughly what I think this new GUC should
> look like.  I'm hoping this sparks more discussion, if nothing else.
>

Thank you. FWIW, I would prefer a sub-linear growth, so maybe something 
like this:

vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples, 
vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);

This would give :

* 386M (instead of 5.1 billion currently) for a 25.6 billion tuples table ;
* 77M for a 2.56 billion tuples table (Robert's example) ;
* 15M (instead of 51M currently) for a 256M tuples table ;
* 3M (instead of 5M currently) for a 25.6M tuples table.

The other advantage is that you don't need another GUC.

> On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
>> By the way, I wonder if there were any off-list discussions after Robert's
>> conference at PGConf.dev (and I'm waiting for the video of the conf).
> 
> I don't recall any discussions about this idea, but Robert did briefly
> mention it in his talk [0].
> 
> [0] https://www.youtube.com/watch?v=RfTD-Twpvac
> 

Very interesting, thanks!