Re: About Autovacuum Query

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.
>