Re: UNDO and in-place update

On Thu, Nov 24, 2016 at 2:32 AM, Tsunakawa, Takayuki
<tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> IMHO, overall, there should be pros and cons of the current approach and the new UNDo one (like Oracle?), depending on the workload. Under update-heavy workload, the UNDO method may be better. OTOH, under the mostly-INSERT workload (like data warehouse?), the current method will be better because it writes no log for UNDO.

The foreground operation will complete more quickly, because it won't
have to write UNDO. On the other hand, you'll have to set hint bits
later, as well as freeze, which may be more expensive than writing
UNDO by the time all is said and done. Whether it's better to do pay
a foreground tax immediately or to do deferred work at a later time
depends on things like whether you have quiet times during which you
can catch up on the deferred work ... but the number of users who have
gotten unpleasant surprises due to autovacuum kicking in during a busy
period is not small.

> Furthermore, it maybe the best to be able to switch the method for each table and/or tablespace. For example, in pgbench, history table uses the current method,
> and other tables use the UNDO method. Is it time to introduce a pluggable storage system?

IMHO, it's past time for that.

> Because PostgreSQL is a follower in the UNDO approach, I think it will be better to study other DBMSs well (Oracle and MySQL?). That includes not only their manuals, but also whitepapers and books. Especially, I expect good books to give deep knowledge on performance tuning and troubleshooting, from which we will be able to know the cons that Oracle's materials don't state.

I agree up to a point. I think we need to design our own system as
well as we can, not just copy what others have done. For example, the
design I sketched will work with all of PostgreSQL's existing index
types. You need to modify each AM in order to support in-place
updates when a column indexed by that AM has been modified, and that's
probably highly desirable, but it's not a hard requirement. I believe
that's a better approach for us than insisting that we have to do it
in exactly the same way as some other system. Now, that doesn't mean
we shouldn't learn from what works well and poorly in other systems,
but I think our goal here should be to chart the best way forward
given PostgreSQL's existing architecture and its existing strengths
and weaknesses, rather than to make it exactly like Oracle or MySQL or
anything else. Few people on this mailing list would say that either
of those systems are categorically better than PostgreSQL; most, I
suspect, would disagree somewhat vigorously.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company