On Thu, Nov 24, 2016 at 2:32 AM, Tsunakawa, Takayuki <tsunakawa.ta...@jp.fujitsu.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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers