Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Michael Lewis <mlewis@xxxxxxxxxxx>]

On Tue, Jun 16, 2020 at 2:41 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:

On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne <jhurne@xxxxxxxxxx> wrote:

Thanks Michael,

Here are our current autovacuum settings:

 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.1
 autovacuum_analyze_threshold        | 50
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 autovacuum_naptime                  | 1min
 autovacuum_vacuum_cost_delay        | 20ms
 autovacuum_vacuum_cost_limit        | -1
 autovacuum_vacuum_scale_factor      | 0.2
 autovacuum_vacuum_threshold         | 50
 autovacuum_work_mem                 | -1
 log_autovacuum_min_duration         | 1s

With no autovacuum_vacuum_cost_limit and autovacuum_work_mem set the same as maintenance_work_mem, I wouldn't expect any difference between the performance of manual vs auto vacuum. 

This was flawed. -1 for autovacuum_vacuum_cost_limit doesn't mean unlimited, it means inherit the manual vacuum cost limit which seems likely to be the default 200 units. Now that you have the manual vacuum done, it might be prudent to set the autovacuum_vacuum_cost_delay to 1 or 2ms globally, and watch to see if it stays caught up.