Re: About Autovacuum Query - Mailing list pgsql-admin

Lot of autovacuum parameters are dependent to each other, there are hierarchy of parameters.

On Wed, 3 Apr 2024, 20:03 jaya kumar, <kumardba27@gmail.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@gmail.com> wrote:

On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@gmail.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.