On Fri, 2023-05-19 at 15:35 +0000, Murthy Nunna wrote: > Following are the settings I have. I am wondering if there is a way autovacuum can keep the above > “datfrozenxid” low and not keep increasing. Thank you ahead time for reading my post. > > postgres=# select name, setting, unit from pg_settings where name like '%autovacuum%'; > name | setting | unit > ---------------------------------------+------------+------ > autovacuum | on | > autovacuum_analyze_scale_factor | 0.1 | > autovacuum_analyze_threshold | 5000 | > autovacuum_freeze_max_age | 1500000000 | > autovacuum_max_workers | 5 | > autovacuum_multixact_freeze_max_age | 400000000 | > autovacuum_naptime | 60 | s > autovacuum_vacuum_cost_delay | 2 | ms > autovacuum_vacuum_cost_limit | -1 | > autovacuum_vacuum_insert_scale_factor | 0 | > autovacuum_vacuum_insert_threshold | 5000 | > autovacuum_vacuum_scale_factor | 0 | > autovacuum_vacuum_threshold | 5000 | > autovacuum_work_mem | -1 | kB > log_autovacuum_min_duration | 0 | ms > (15 rows) There is no need to keep "age(datfrozenxid)" low, but if you want to do that, the correct way is to reduce "autovacuum_multixact_freeze_max_age". Your settings are pretty crazy. You won't succeed in anything but having autovacuum run all the time. You should reset the thresholds and scale_factors to the default values. To make autovacuum more aggressive (if that is indeed necessary), reduce "autovacuum_vacuum_cost_delay". I wouldn't do that globally, but only on the tables that receive lots of updates and deletes. Yours, Laurenz Albe
