Autovacuum not running properly

-- Find current setting (this is at database level)

select * from pg_settings  where name in ('autovacuum','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold','autovacuum_vacuum_scale_factor');

select current_setting('autovacuum_vacuum_scale_factor') as "analyze_scale_factor",current_setting('autovacuum_vacuum_threshold') as "vacuum_threshold";

select current_setting('autovacuum_analyze_scale_factor') as "analyze_scale_factor", current_setting('autovacuum_analyze_threshold') as "analyze_threshold";

-- Note: The smaller number = more aggressive = vacuum more frequence

-- Current:

-- autovacuum_analyze_scale_factor = 0.05    

-- autovacuum_vacuum_scale_factor = 0.1      

-- Fine Tune at table level = ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02);

ALTER TABLE your_schema.your_table SET (autovacuum_enabled = true,autovacuum_analyze_scale_factor = 0.002,autovacuum_vacuum_scale_factor = 0.001);

-- Put it back to use global setting

ALTER TABLE your_schema.your_table RESET (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);

From: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
Sent: Sunday, August 27, 2023 4:09 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Autovacuum not running properly