Re: About Autovacuum Query

Hi Ron Johnson,

Thanks so much for your update. As per my understanding

If table size increases, then autovacuum_vacuum_threshold can decide
whether to trigger a VACUUM or not.

If I will set autovacuum_analyze_scale_factor==>0.1 means (table level 10%
n_dead_tup activity happens). It will perform VACUUM automatically.

Same time set autovacuum_vacuum_scale_factor===>0.1 parameter value.

I will set the postgresql.conf file. Hole tables VACUUM will happen
automatically. If I will set the above, if any performance issues will
occur?

On Wed, Apr 3, 2024 at 8:34 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> https://www.postgresql.org/docs/14/runtime-config-autovacuum.html
>
> autovacuum_vacuum_scale_factor (floating point)
>
> Specifies a fraction of the table size to add to
> autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.
> The default is 0.2 (20% of table size). This parameter can only be set in
> the postgresql.conf file or on the server command line; but the setting
> can be overridden for individual tables by changing table storage
> parameters.
>
> On Wed, Apr 3, 2024 at 10:33 AM jaya kumar <kumardba27(at)gmail(dot)com> wrote:
>
>> Hi Ron Johnson,
>>
>> Thanks for your update. Here, I have one doubt.
>>
>> If we will down autovacuum_vacuum_threshold value from 50 to 30 or 20
>> means. Autovacuum will run automatically then delete n_dead_tup.
>>
>> As you suggested to down autovacuum_vacuum_scale_factor down to something
>> like 0.05.
>>
>> Can you explain more based on your update. I will implement the task to
>> our database.
>>
>> On Wed, Apr 3, 2024 at 6:50 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
>> wrote:
>>
>>> On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27(at)gmail(dot)com> wrote:
>>>
>>>> Hi Team,
>>>>
>>>> In my database Autovacuum is enabled and sets the below updated value
>>>> in the Autovacuum related parameter. Here my question is after performing
>>>> Autovacuum on a daily basis the below value is coming in n_dead_tup. If I
>>>> will manually Autovacuum then it's fine.
>>>>
>>>> Can someone help me to solve my request? The below current parameter
>>>> setting updated
>>>>
>>>> databaseName=# show autovacuum;
>>>> autovacuum
>>>> ------------
>>>> on
>>>> (1 row)
>>>>
>>>> databaseName=# show autovacuum_vacuum_threshold;
>>>> autovacuum_vacuum_threshold
>>>> -----------------------------
>>>> 50
>>>> (1 row)
>>>>
>>>> databaseName=# show autovacuum_vacuum_scale_factor;
>>>> autovacuum_vacuum_scale_factor
>>>> --------------------------------
>>>> 0.2
>>>> (1 row)
>>>>
>>>> databaseName=# show autovacuum_vacuum_cost_delay;
>>>> autovacuum_vacuum_cost_delay
>>>> ------------------------------
>>>> 2ms
>>>> (1 row)
>>>>
>>>> databaseName=#
>>>>
>>>>
>>>>
>>>> databaseName=# /
>>>> tblnam | n_dead_tup | pfrag
>>>> --------------------------+------------+---------
>>>> applicationusername.tablename1 | 52625 | 5262500
>>>> applicationusername.tablename2 | 23538 | 2353800
>>>> applicationusername.tablename3 | 3290 | 329000
>>>> applicationusername.tablename4 | 3092 | 309200
>>>> applicationusername.tablename5 | 2262 | 226200
>>>> applicationusername.tablename6 | 2110 | 211000
>>>> applicationusername.tablename7 | 678 | 67800
>>>> applicationusername.tablename8 | 491 | 49100
>>>> applicationusername.tablename9 | 431 | 43100
>>>> applicationusername.tablenam10 | 351 | 35100
>>>> applicationusername.tablenam11 | 188 | 18800
>>>> applicationusername.tablenam12 | 150 | 15000
>>>>
>>>
>>> Drop autovacuum_vacuum_scale_factor down to something like 0.05.
>>>
>>> autovacuum_analyze_scale_factor, too.
>>>
>>
>>
>> --
>> Thanks & Regards,
>> Jayakumar.S
>> +91-9840864439.
>>
>

--
Thanks & Regards,
Jayakumar.S
+91-9840864439.