pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Markus Bertheau <twanger@bluetwanger.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: recovery after long delete
Date: 14 Apr 2005 14:11:15 -0400
Message-ID: <877jj5grek.fsf@stark.xeocode.com> (raw)
In-Reply-To: <1113493643.7249.35.camel@localhost.localdomain>
References: <1113493643.7249.35.camel@localhost.localdomain>


Markus Bertheau <twanger@bluetwanger.de> 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




view thread (3+ messages)

Message-ID: <877jj5grek.fsf@stark.xeocode.com>
Permalink:  ../877jj5grek.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/877jj5grek.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, twanger@bluetwanger.de
  Subject: Re: recovery after long delete
  In-Reply-To: <877jj5grek.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox