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

On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne <jhurne(at)us(dot)ibm(dot)com> 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. Still, if you run a manual vacuum
analyze verbose, what sort of output do you get? What
is maintenance_work_mem set to? Are there indexes on this table that
perhaps are very large and needing to be rescanned many times because
maintenance_work_mem isn't high enough to handle in a single pass? You
might try "create index concurrently, drop index concurrently, & rename
index" (reindex concurrently if you were on PG 12) as a sort of online
'vacuum full' on the index(es).

By the way, the best practices for these mailing list suggest partial
quoting and responding in-line or below, not "top posting" with the entire
conversation below.