Markus Bertheau <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match