Re: PostgreSQL vs. MySQL: fight

On 8/13/07, Lukas Kahwe Smith <smith(at)pooteeweet(dot)org> wrote:
> > If I understand correctly, the idea is that non-postgres mvcc systems
> > (interbase, etc) write the new version in the old location, and copy the
> > old tuple version to a special undo log area. Is that a reasonable
> > summary?
>
> Correct.

Well, not in all cases. For Oracle and InnoDB, this is correct.
Ingres has update-in-place as-well-as a similar method to HOT.
Firebird/Interbase stores a delta row-version in the main table which
is cleaned up automagically on later queries.

> I do not know the perfect answers to all of these, but here is my attempt:
> 1) they need to shift around pages, which I presume they will try work
> around as much as possible

Depending on the system, their either move the whole row, or a piece
of it to another block.

> 2) Not really sure, I presume they maintain some kind of flag to tell
> transactions that want the old version to traverse some kind of list in
> the undo log

There is generally a table which keeps track of row versions such that
the engine knows where to find the old version. Oracle, for example,
has an ITL (interested transaction list) which points to a location of
the rollback segment which contains the original (UNDO) data needed to
rebuild the correct block/row version.

> 3) Well they do it immediately and just keep a copy in the undo log
> around until all transactions that started before the delete have ended.
> This is their primary advantage over the PostgreSQL style. There is no
> need for vaccum.

When storing the old version in the log, there is no reclamation
needed. In the case of Ingres/Interbase/Firebird, it is generally
reclaimed automagically later by the system.

> Well due to 1) and similar effects I would assume that a rollback is
> more expensive for them.

Yes, rollback is more expensive in *most* of the other systems.
However, 97% of all transactions commit (statistic, but changes
depending on the application)... so those systems have been designed
as, "optimized for commit".

> I guess PostgreSQL trades some disk space and the need for a clean up
> task like vacuum for working around the issues in the Oracle style MVCC,
> which relies on a rollback log that needs to be allocated independently,
> that needs to be undone on a rollback etc.

Basically. Reading the aforementioned books are a good place to start
in regards to differing MVCC models.

--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/