Re: UNDO and in-place update

On Wed, Nov 23, 2016 at 10:07 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> On Tue, Nov 22, 2016 at 8:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Ultimately, I doubt that update-in-place buys much that we don't already
> > have with HOT updates (which postdate this old conversation, btw).
> > If you want MVCC semantics, you need to hold both versions of the tuple
> > *somewhere*. Oracle's solution is different from ours, but I'm not
> > exactly convinced that it's better. It makes some aspects better and
> > others worse.
>
> I agree that HOT is roughly as effective, at least when you get mostly
> HOT updates.
>
>
I agree. We'd tried update-in-place before writing HOT and it turned out to
be complex and error-prone [1]. I am not suggesting that we can't do a
better job this time, but as Tom said, it's going to be lot of work. If
WARM makes any progress, it will also help addressing some of the issues
around index bloats when HOT updates can not be done.

One key issue that I see is that unrelated long running transactions end up
holding up OldestXmin advances and that leads to significant bloat to
update-heavy tables. If we could somehow address that situation, that may
still go a long way in containing bloat. Of course, there may still be
cases where bloat will be unavoidable, but it seems far lesser work to me
that a complete overhaul of the MVCC working.

Thanks,
Pavan

[1]
https://www.postgresql.org/message-id/1163092396.3634.461.camel@silverbirch.site
--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services