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@xxxxxxxxx> 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@xxxxxxxxx> wrote:On Wed, Apr 3, 2024 at 5:55 AM jaya kumar <kumardba27@xxxxxxxxx> 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 | 15000Drop autovacuum_vacuum_scale_factor down to something like 0.05.autovacuum_analyze_scale_factor, too.--Thanks & Regards,Jayakumar.S
+91-9840864439.
