Autovacuum------Doubts

[jaya kumar <kumardba27@xxxxxxxxx>]

Hi Team,

The below updated parameter value has been set currently in postgresql.conf file.

autovacuum_analyze_threshold and autovacuum_vacuum_threshold both parameter values set from 50 to 20.

Created one table with 100 records. First, we deleted 21 records. after an autovacuum does not happen automatically. After again I deleted another 20. Out of 100 we delectated 40 records then the autovacuum happened automatically. As per the configuration Autocacuum will automatically delete 21 records in the table but my case does not happen.

Can someone check and explain this.




DatabaseName=# show autovacuum;
 autovacuum
------------
 on
(1 row)

DatabaseName=# show autovacuum_max_workers;
 autovacuum_max_workers
------------------------
 3
(1 row)

DatabaseName=# show autovacuum_naptime;
 autovacuum_naptime
--------------------
 1min
(1 row)

DatabaseName=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 20
(1 row)

DatabaseName=# show autovacuum_vacuum_insert_threshold;
 autovacuum_vacuum_insert_threshold
------------------------------------
 1000
(1 row)

DatabaseName=# show autovacuum_analyze_threshold;
 autovacuum_analyze_threshold
------------------------------
 20
(1 row)

DatabaseName=# show autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.1
(1 row)

DatabaseName=# show autovacuum_vacuum_insert_scale_factor;
 autovacuum_vacuum_insert_scale_factor
---------------------------------------
 0.2
(1 row)

DatabaseName=# show autovacuum_analyze_scale_factor;
 autovacuum_analyze_scale_factor
---------------------------------
 0.1
(1 row)

DatabaseName=# show autovacuum_freeze_max_age;
 autovacuum_freeze_max_age
---------------------------
 200000000
(1 row)

DatabaseName=#


DatabaseName=# select count(*) from emp10;
 count
-------
   100
(1 row)
DatabaseName=# select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup
DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        20 |             0 |         80 |         20
(1 row)

DatabaseName=# delete from emp10 where eno between 25 and 40;
DELETE 15
DatabaseName=# select n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup
DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        40 |             0 |         60 |         40
(1 row)

DatabaseName-# from pg_stat_all_tables where schemaname='public' and relname='emp10';
 n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
       100 |         0 |        40 |             0 |         60 |          0
(1 row)

DatabaseName=# SELECT schemaname||'.'||relname AS table_name
DatabaseName-#        ,last_vacuum
DatabaseName-#        ,last_autovacuum
DatabaseName-#        ,last_analyze
DatabaseName-#        , last_autoanalyze
DatabaseName-# FROM pg_stat_user_tables
DatabaseName-# where relname in ('emp10');
  table_name  | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze
--------------+-------------+-------------------------------+--------------+------------------------------
 public.emp10 |             | 2024-04-08 09:03:25.072008+00 |              | 2024-04-08 09:03:25.07849+00
(1 row)

DatabaseName=#

Thanks & Regards,

Jayakumar.S
+91-9840864439.