Re: About Autovacuum Query

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.