Re: Multi-Versions and Vacuum -- cf Oracle & Vacuum alt

A combined response to several posts.  I am not advocating Oracle in
general, just noting differences and places where Postgres might benefit.

ORACLE MVC

Oracle definitely has MVC.  By default it is in Read Committed mode.  But
you can still get record locks in both Postgresql and Oracle if you Select
FOR UPDATE, which you must do in Read Committed mode to produce correct
transactions.

ORACLE LOCKING

My note about Oracle is that in its Read Committed mode transactions are
safer without being Serializable.  See http://www.SimpleORM.org/DBNotes.html
for details.  The difference is subtle but it is is important because
otherwise report may not be consistent.  And I do not think that there is
any down side.  So I commend the approach to the Postgres community.

TRANSACTION LOG ALTERNATIVE TO VACUUM AND ROLLBACK SEGMENTS

Oracle's use of Rollback segments to implement MVC has several issues.  But
it does stop the main database blocks becoming cluttered with old data.

But rather than try to make vacuum faster, incremental etc.  Why not just
use the transaction log to implement MVC and avoid the whole problem?

The idea is that you only store the current row's value in the main
database.  You are already keeping track of the updated row values in the
transaction log, and presumably at least one copy of those logs will be on
disk, ie. allow random access.

So if you find that you need to get an old version of a row, just look up
the log files.  And that can be very efficient because the new row can keep
a pointer to the log block containing the next older version.  And as that
block is likely to have been recently written to the disk it is likely to be
in the cache anyway.

In this way you only pay for MVC when you actually need to read behind a new
row.  The approaches used by Postgres and Oracle mean that you pay for the
MVC every time you update a row.  You also pay for them every time you
retrieve a row in Postgres because the blocks contain extra data and so the
cache will not be as effective.

In practice, you will only need the MVC read behinds if you are running
updating transactions AND reports on the same database AT THE SAME TIME.  If
you are doing a lot of that, build a wharehouse.

Anthony