Re: PostgreSQL vs. MySQL: fight - Mailing list pgsql-advocacy

"Jonah H. Harris" <jonah.harris@gmail.com> writes:

> On 8/13/07, Lukas Kahwe Smith <smith@pooteeweet.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.

Note that in the case of Oracle another interesting difference is that it's
storing the old version *of the block* not the record. At least when I last
was using it. That has advantages and disadvantages of course.

>> 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.

DB2 stores it in the log, but Oracle stores it in a separate area called the
rollback segments. Management of this space was Oracle's biggest bugaboo in
the past but from what I understand they have it under control now. The
problems Oracle had with rollback segments were exactly analogous to the
problems we have with vacuum, though the actual failure modes are different.

>> 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 don't like "optimized for commit" as a shorthand. In both architectures a
commit is essentially a noop.

The interesting differences are what happens when you do when you look at a
record which has been updated. In Postgres you ignore it, in Oracle you have
to do a random access I/O to fetch it from the rollback segment. Analyzing how
expensive that is is complex as it depends heavily on how much cache you have
and the mix of transactions.

Also, with rollback segments you have to do twice as much I/O at update time.
Again you hope the rollback segments are cached though, so it's unclear how
much that costs. And with DB2's approach of using the logs as rollback you
don't really have to do any I/O you didn't already have to do anyways.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com