X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9748E53692 for ; Thu, 14 Apr 2005 13:34:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34969-04 for ; Thu, 14 Apr 2005 16:34:33 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id AA3C7533AC for ; Thu, 14 Apr 2005 13:34:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3EGYUv7001506; Thu, 14 Apr 2005 12:34:30 -0400 (EDT) To: Markus Bertheau Cc: pgsql-performance@postgresql.org Subject: Re: recovery after long delete In-reply-to: <1113493643.7249.35.camel@localhost.localdomain> References: <1113493643.7249.35.camel@localhost.localdomain> Comments: In-reply-to Markus Bertheau message dated "Thu, 14 Apr 2005 17:47:23 +0200" Date: Thu, 14 Apr 2005 12:34:30 -0400 Message-ID: <1505.1113496470@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/299 X-Sequence-Number: 11696 Markus Bertheau writes: > Now if you pull the plug after 2, at startup, pg will go through the > in-progress txns and mark them as aborted. That's all the recovery in > this case. All rows are still there. O(1). Right. (Actually it's O(checkpoint interval), because we have to make sure that everything we did since the last checkpoint actually got to disk --- but in principle, there's zero recovery effort.) > How does oracle do that? Has all this something to do with mvcc? Why > does it take oracle so long to recover? Oracle doesn't do MVCC the same way we do. They update rows in place and put the previous version of a row into an "undo log". If the transaction aborts, they have to go back through the undo log and put back the previous version of the row. I'm not real clear on how that applies to deletions, but I suppose it's the same deal: cost of undoing a transaction in Oracle is proportional to the number of rows it changed. There's also the little problem that the space available for UNDO logs is limited :-( As against which, they don't have to VACUUM. So it's a tradeoff. regards, tom lane