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

"Jim Hurne" wrote on 06/24/2020 03:44:26 PM: > That brings us to transactions. I looked for any long-running idle > transaction (transactions that have been idle for more than 15 or 5 > minutes), but found none. It turns out I didn't see any long running transaction

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

s changing, but the xmins are always the same. Strange. Having to check the logs to see what the oldest xmin is painful, and requires that a vacuum or autovacuum has been executed. Is there another way to check what the oldest xmin is on the dead tuples of a table? Regards, Jim Hurne

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

tem table? We're finding it to be especially difficult to maintain the pg_largeobject system table (tweak autovacuum settings, manually cleanup with something like pg_repack, etc), particularly since our PosrgreSQL instances are hosted and controlled by another team. Regards, Jim Hurne

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

pack[1] could be used to do? Would pg_repack require setting allow_system_table_mods to on? [1] https://github.com/reorg/pg_repack Regards, Jim Hurne

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

Magnus Hagander wrote on 06/22/2020 04:44:33 PM: > Yes, that's how VACUUM FULL works (more or less). Thanks for the confirmation and further explanation Magnus! I'm definitely getting a better idea of how the vacuum and autovacuum facilities work. > And for autovacuum, with a cost_delay of 20

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

ted 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

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

relation pg_largeobject" error when we try to create the replacement index (using CREATE INDEX CONCURRENTLY). > 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. My mistake! Sorry about that. Regards, Jim Hurne

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

27;t use vacuumlo, our service does clean up orphaned large objects in a similar way. And indeed, inspecting the database reveals that there are no orphaned large objects. Regards, Jim Hurne

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

_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 L

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

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? Regards, Jim Hurne