Re: Autovacuum of independent tables

On Tue, Sep 8, 2020 at 5:38 PM Tom Lane wrote:

> Magnus Haganderwrites:
> > Oh sure, but there is clearly *something* going on, so we should try to
> > figure that out. Because a transaction running multiple independent
> selects
> > with the defaults settings will not actually block autovacuum.
>
> I don't think the OP is claiming that autovacuum is blocked, only that
> it's failing to remove recently-dead rows that he thinks could be removed.
>
Yes, this is exactly what happens.

The reason that's not so is that whether or not transaction A *has*
> touched table B is irrelevant. It *could* read table B at any moment,
> for all autovacuum knows. Therefore we cannot remove rows that should
> still be visible to A's snapshot.
>
> There are some approximations involved in figuring out which rows are
> potentially still visible to someone. So perhaps this is a situation
> where an approximation is being used and tighter analysis would have
> shown that indeed a row could be removed. But we haven't seen any
> evidence of that so far. The basic fact that A's snapshot is limiting
> removal of rows from a table it has not touched is not a bug.
>
It's obviously not a bug. I was just surprised when I figured that out.
It's also quite complex to explain to my colleagues. Actually, this is the
main reason I started this thread: I tried to explain to someone and felt
that I miss something.

--
Regards,
Michael Holzman