Hi Laurenz,
I am not clear. Could you explain again. If I delete 20 records out of 100. It means Autocacumm should run but not run automatically. As you provide a solution, the method should be deleted but does not happen.
Total my table count=100
Delete record=30
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows ?
o/p: 20+0.1*100=2010?
DatabaseName=# show autovacuum_vacuum_threshold;
autovacuum_vacuum_threshold
-----------------------------
20
(1 row)
DatabaseName=# show autovacuum_vacuum_scale_factor;
autovacuum_vacuum_scale_factor
--------------------------------
0.1
(1 row)
DatabaseName=# select count(*) from emp9;
count
-------
100
(1 row)
DatabaseName=# delete from emp9 where eno between 1 and 30;
DatabaseName=# SELECT schemaname || '.' || relname as tblnam, n_dead_tup, (n_dead_tup::float) * 100 as pfrag from pg_stat_user_tables where n_dead_tup > 0 and n_live_tup > 0 order by pfrag desc;
tblnam | n_dead_tup | pfrag
-------------+------------+-------
public.emp9 | 30 | 3000
(1 row)
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows
20+
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 ('emp9');
table_name | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
-------------+-------------+-----------------+--------------+-------------------------------
public.emp9 | | | | 2024-04-08 10:15:25.949394+00
(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='emp9';
n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+-----------+---------------+------------+------------
100 | 0 | 30 | 0 | 70 | 30
(1 row)
DatabaseName=#
On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> 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.
The threshold is
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of table rows
So since you have set autovacuum_vacuum_scale_factor to 0.1 and there are 100 rows,
you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.
Yours,
Laurenz Albe
+91-9840864439.
