Re: autovacuum: change priority of the vacuumed tables

On 02/15/2018 09:28 AM, Masahiko Sawada wrote:

> Hi,
>
> On Thu, Feb 8, 2018 at 11:01 PM, Ildus Kurbangaliev
> <i.kurbangaliev@postgrespro.ru> wrote:
>> Hi,
>>
>> Attached patch adds 'autovacuum_table_priority' to the current list of
>> automatic vacuuming settings. It's used in sorting of vacuumed tables in
>> autovacuum worker before actual vacuum.
>>
>> The idea is to give possibility to the users to prioritize their tables
>> in autovacuum process.
>>
> Hmm, I couldn't understand the benefit of this patch. Would you
> elaborate it a little more?
>
> Multiple autovacuum worker can work on one database. So even if a
> table that you want to vacuum first is the back of the list and there
> other worker would pick up it. If the vacuuming the table gets delayed
> due to some big tables are in front of that table I think you can deal
> with it by increasing the number of autovacuum workers.
>
> Regards,
>
> --
> Masahiko Sawada
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>

Database can contain thousands of tables and often updates/deletes 
concentrate mostly in only a handful of tables.
Going through thousands of less bloated tables can take ages.
Currently autovacuum know nothing about prioritizing it`s work with 
respect to user`s understanding of his data and application.
Also It`s would be great to sort tables according to dead/live tuple 
ratio and relfrozenxid.

-- 
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company