Re: recovery after long delete

Поиск
Список
Период
Сортировка
От Greg Stark
Тема Re: recovery after long delete
Дата
Msg-id 877jj5grek.fsf@stark.xeocode.com
обсуждение исходный текст
Ответ на recovery after long delete  (Markus Bertheau <twanger@bluetwanger.de>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: How to improve db performance with $7K?
Следующее
От: "Dave Held"
Дата:
Сообщение: Re: How to improve db performance with $7K?