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 A0FFA5296B for ; Thu, 14 Apr 2005 15:11:38 -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 40241-04 for ; Thu, 14 Apr 2005 18:11:28 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id 556E054059 for ; Thu, 14 Apr 2005 15:11:27 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DM8o7-0008LE-00; Thu, 14 Apr 2005 14:11:16 -0400 To: Markus Bertheau Cc: pgsql-performance@postgresql.org Subject: Re: recovery after long delete References: <1113493643.7249.35.camel@localhost.localdomain> In-Reply-To: <1113493643.7249.35.camel@localhost.localdomain> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 14 Apr 2005 14:11:15 -0400 Message-ID: <877jj5grek.fsf@stark.xeocode.com> Lines: 23 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/308 X-Sequence-Number: 11705 Markus Bertheau writes: > How does oracle do that? Has all this something to do with mvcc? Why > does it take oracle so long to recover? Postgres does "pessimistic MVCC" where it keeps the old versions where they are in the table. Only after it's committed can they be cleaned up and reused. So aborting is a noop but committing requires additional cleanup (which is put off until vacuum runs). Oracle does "optimistic MVCC" where it assumes most transactions will commit and most transactions will be reading mostly committed data. So it immediately does all the cleanup for the commit. It stores the old version in separate storage spaces called the rollback segment and redo logs. Committing is a noop (almost, there are some details, search for "delayed block cleanout") whereas rolling back requires copying back all that old data from the redo logs back to the table. Engineering is all about tradeoffs. -- greg