Re: Vacuum Tuning Question

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