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

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

> Is this a destructive queue of sorts with no rows permanently stored?

Essentially, yes. The system can create and delete objects at a high rate.
Here are some recent stats on the pg_largeobject table:

# select * from pg_stat_all_tables where relname = 'pg_largeobject';
-[ RECORD 1 ]-------+---------------
relid | 2613
schemaname | pg_catalog
relname | pg_largeobject
seq_scan | 0
seq_tup_read | 0
idx_scan | 66619475
idx_tup_fetch | 126816721
n_tup_ins | 57580140
n_tup_upd | 188474
n_tup_del | 57640395
n_tup_hot_upd | 108845
n_live_tup | 47771
n_dead_tup | 57787135
n_mod_since_analyze | 115409009
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0

# SELECT pg_size_pretty( pg_total_relation_size('pg_largeobject') );
-[ RECORD 1 ]--+-------
pg_size_pretty | 350 GB

The total size of the actual undeleted large objects is about 60 MB.

Regards,

Jim Hurne

From: Michael Lewis <mlewis(at)entrata(dot)com>
To: Jim Hurne <jhurne(at)us(dot)ibm(dot)com>
Cc: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Date: 06/16/2020 01:06 PM
Subject: [EXTERNAL] Re: autovacuum failing on pg_largeobject and
disk usage of the pg_largeobject growing unchecked

On Tue, Jun 16, 2020 at 10:01 AM Jim Hurne <jhurne(at)us(dot)ibm(dot)com> wrote:
Other than the increasing elapsed times for the autovacuum, we don't see
any other indication in the logs of a problem (no error messages, etc).

We're currently using PostgreSQL version 10.10. Our service is JVM-based
and we're using the PostgreSQL JDBC driver version 42.2.5.

Have we stumbled upon a potential bug here, or do we need to tweak some
autovacuum settings? What should we look at next or what should we try
next to further troubleshoot this?

What are your current autovacuum settings? Do you have long
running transactions with any frequency? Decreasing
autovacuum_vacuum_cost_delay to 1 or 2ms may be prudent (default changes
from 20ms down to 2ms with PG 12). Is this a destructive queue of sorts
with no rows permanently stored? If so, I would expect that a daily
scheduled vacuum analyze may be the best course of action.