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

Michael Lewis <mlewis(at)entrata(dot)com> wrote on 06/23/2020 04:44:51 PM:
> Long running transactions are the common one that I see. You might
> be dealing with replication slots or prepared transactions.
> Basically, if some process might see that "old truth", then it can't
> be vacuumed away yet.

Thanks, those links you provided were informative.

Our application doesn't use prepared transactions, so that one is easy to
eliminate.

Our database does use replication, but there's only one replication slot
and the xmin and catalog_xmin columns are blank. I presume the only
replication slot that exists is the one that the other replica is in fact
using. I *think* this means replication isn't the issue, but it's hard to
say since the xmin column is blank (and all the reading I've done doesn't
even mention that the xmin on replication slots can be null).

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.

I tried:

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

But the backend_xmin for all of the rows returned is exactly the same, and
that xmin is greater than the oldest xmin reported in the autovacuum logs.
It does seem odd that the backend_xmin value isn't changing though. Is
that normal?

So, for replication slots, I'm seeing a null xmin value, which the
articles do not comment on how that should be interpreted. And for
transactions, all of the transaction xmins are the same, which also seems
odd and not what the articles suggested. I know the transactions
themselves are coming and going because I can see the pids 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