Re: About Autovacuum Query

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.