MIME-Version: 1.0
References: 
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAApHDvpOq09uVq7aXcuSBPAhZBTfAL-m2c4FOF2PphFe-YcnRg@mail.gmail.com>
 <aQEvm40W3aVizp5Q@nathan>
 <CAA5RZ0sdhUjVVKYbBGs1qFsYC3-Mn+as=K5v8ydVGR5iziabFQ@mail.gmail.com>
 <aQI39ln_jZ8qorLE@nathan>
 <CA+Tgmoa_5aC0w1fG7pLhev+F-GtRhQ2OzePy7t059c9JTnvjow@mail.gmail.com>
 <aQPSYD11NDoREZsg@nathan>
 <CAA5RZ0uo-nU9KqgXV5Tcf8aWWQkxsb5BDpkb-2Qfwbw-UVVaUA@mail.gmail.com>
 <CAA5RZ0tpV3PRHejTGG5-LSsqNKKV0qP=SDvurs8wj7pTk7jYJw@mail.gmail.com>
 <aQUYP1WjrEP3buQz@nathan>
 <CAApHDvqe7ee=vobWe4GVAt2gm_H6eiGNZeo_dEMptvHYAkibBA@mail.gmail.com>
 <CAApHDvoCFQxaQjUncTAtCRFAeANe2tpc-WCkJue=FaXRYOkV=Q@mail.gmail.com>
 <CAA5RZ0sw+9rEaW9taNpRZWvuLYMjRa9iibneGfB2ftNSUHT0Ww@mail.gmail.com>
 <CAApHDvq_j+GVqX_ZAmvn236Mgg5OYQ6_s9kVsyoo1tJa2RJ=2w@mail.gmail.com>
 <CAA5RZ0udjEYJupob5tv3286e28bMpajgsy+4nAbxg73YyigZFw@mail.gmail.com>
 <CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A@mail.gmail.com>
 <CAA5RZ0sJGg209gSpEzLpO3DPiF8r8n_xVbQDnd_92BV+-5kvDA@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0sJGg209gSpEzLpO3DPiF8r8n_xVbQDnd_92BV+-5kvDA@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 12 Nov 2025 09:43:46 +1300
Message-ID: 
 <CAApHDvoBZu-oEsRV+QzVACkQCdGxm5Q7RSs_q+J+G27EObS+zA@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Sami Imseih <samimseih@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvoBZu-oEsRV%2BQzVACkQCdGxm5Q7RSs_q%2BJ%2BG27EObS%2BzA%40mail.gmail.com>
Precedence: bulk

On Wed, 12 Nov 2025 at 09:25, Sami Imseih <samimseih@gmail.com> wrote:
> The thing I=E2=80=99m hoping to address is something I=E2=80=99ve seen ma=
ny times in practice.
> Autovacuum workers can get stuck on specific large or slow tables, and wh=
en
> that happens, users often end up running manual vacuums on those tables
> just to keep things moving for the smaller/faster vacuumed tables.
>
> Now, I am not so sure any type of autovacuum prioritization could actuall=
y
> help in these cases. What does help is adding more autovacuum workers.

Thanks for explaining. I think the scoring system in Nathan's patch
helps with this as any smaller table which are neglected continue to
bloat, and because they're smaller, the score will increase more
quickly, and eventually they'll have a higher score than the larger
tables.  I think the situation you're talking about is when *all*
autovacuum workers are busy with large tables and no workers remaining
to deal with the now-higher-scoring smaller tables and they bloat
severely or statistics become wildly outdated as a result.

I'm aware of that problem. It seems to happen mostly when large tables
are busy receiving an anti-wraparound vacuum. I'm not sure what to do
about it, but I don't think changing the scoring system is the right
thing. Maybe we can have it configurable so that 1 worker can be
configured to not work on tables above a given size, so there's at
least 1 worker that is less likely to be tied up for extended periods
of time. I don't know if that's a good idea, and also don't know what
realistic values for "given size" are.

David