We're still struggling to figure out why autovacuum seems to be failing or
is ineffective sometimes against the pg_largeobject table.
We decided to try a VACUUM FULL VERBOSE on one of our problematic
databases. It did complete, but it took a surprisingly long time:
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: "pg_largeobject": found 97326130 removable, 22230 nonremovable
row versions in 207508868 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 1085.87 s, system: 3803.66 s, elapsed: 10883.94 s.
VACUUM
So it took about 3 hours. It's surprising because there was only 58 MB of
undeleted data in about 65 large objects. When we ran this particular
vacuum, we made sure there were no other processes connected to the DB and
that no other statements were running that would have locked the table.
It's my understanding that a VACUUM FULL works by creating a brand new
table and copying the "non-deleted" data into the new table. If that's the
case, I would have expected it to complete very quickly, given how little
"non-deleted" data existed at the time.
We are of course going to continue to try different things, but does
anyone have any other suggestions on what we should be looking at or what
settings we might want to adjust?
For reference, here was my original problem description:
> We have a cloud service that uses PostgreSQL to temporarily store binary
> content. We're using PostgreSQL's Large Objects to store the binary
> content. Each large object lives anywhere from a few hundred
milliseconds
> to 5-10 minutes, after which it is deleted.
>
> Normally, this works just fine and we have no issues. However, roughly
> every 3 months or so, disk usage on our PostgreSQL database starts to
> increase at an alarming rate. More specifically, the pg_largeobject
> table's disk usage steadily increases even though we've deleted most of
> the large objects. This will continue until the database server runs out
> of disk space (and then all sorts of bad things happen of course).
>
> It looks to us like autovacuum against the pg_largeobject table stops
> working because the pg_stat_all_tables.last_autovacuum column stops
> updating, or is updated infrequently, while it is still updated on other
> tables.
>
> 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.
Regards,
Jim Hurne
