Re: recovery after long delete

Markus Bertheau <twanger(at)bluetwanger(dot)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