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. 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.
