Re: Toast issues with OldestXmin going backwards

On Mon, Apr 23, 2018 at 1:55 AM, Andrew Gierth
<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>>>>>> "Amit" == Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
>
> Amit> by computing it before scanning the main heap (lazy_vacuum_rel)
> Amit> and then pass it down. I have tried it and came up with the
> Amit> attached patch.
>
> Your patch would actually be needed if (and only if) autovacuum was
> changed back to its old behavior of never vacuuming toast tables
> independently, and if manual VACUUM pg_toast.*; was disabled. But in the
> presence of either of those two possibilities, it does nothing useful.
>

Yeah, right, I have missed the point that they can be vacuumed
separately, however, I think that decision is somewhat questionable.
Basically, it doesn't appear logical to leave the sort-of dangling
toast pointer in heap. I think this is somewhat akin to our current
index and heap binding where we never let heap itemid to go off till
all index entries pointing to it are taken care of. I have gone
through the commit (3ccde312ec8ee47f5f797b070d34a675799448ae) which
appears to have decoupled this mechanism, but it doesn't provide any
reason for doing so. I have also gone through the related thread [1]
which is not much help either. I think there must be some patterns
where toast table bloat causes much more harm than heap as toast rows
are much larger in size. Are you aware of what were the concrete
reasons behind this change? I think it would have been better if
along with decoupling of vacuum for main heap and toast tables, we
would have come up with a way to selectively remove the corresponding
rows from the main heap, say by just vacuuming heap pages/rows which
have toast pointers but maybe that is not viable or involves much more
work without equivalent benefit.

Also, we can think along the lines of another idea suggested by Andres
[2] on the thread mentioned by you.

[1] - https://www.postgresql.org/message-id/20080808165809.GB3800%40alvh.no-ip.org
[2] - https://www.postgresql.org/message-id/20130204164341.GB22226%40awork2.anarazel.de

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com