Re: On-the-fly index tuple deletion vs. hot_standby

On Mon, Jun 13, 2011 at 3:11 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Jun 12, 2011 at 3:01 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>> I fully agree.  That said, if this works on the standby, we may as well also use
>> it opportunistically on the master, to throttle bloat.
>
> As long as the performance cost is de minimis, I agree.
>
>>> At any rate, if taking a cleanup lock on the right-linked page on the
>>> standby is sufficient to fix the problem, that seems like a far
>>> superior solution in any case.  Presumably the frequency of someone
>>> having a pin on that particular page will be far lower than any
>>> matching based on XID or heavyweight locks.  And the vast majority of
>>> such pins should disappear before the startup process feels obliged to
>>> get out its big hammer.
>>
>> Yep; looks promising.
>>
>> Does such a thing have a chance of being backpatchable?  I think the chances
>> start slim and fall almost to zero on account of the difficulty of avoiding a
>> WAL format change.
>
> I can't see back-patching it.  Maybe people would feel it was worth
> considering if we were getting legions of complaints about this
> problem, but so far you're the only one.  In any case, back-patching a
> WAL format change is a complete non-starter -- we can't go making
> minor versions non-interoperable.
>
>> Assuming that conclusion, I do think it's worth starting
>> with something simple, even if it means additional bloat on the master in the
>> wal_level=hot_standby + vacuum_defer_cleanup_age / hot_standby_feedback case.
>> In choosing those settings, the administrator has taken constructive steps to
>> accept master-side bloat in exchange for delaying recovery conflict.  What's
>> your opinion?
>
> I'm pretty disinclined to go tinkering with 9.1 at this point, too.

Not least because a feature already exists in 9.1 to cope with this
problem: hot standby feedback.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services