Re: PostgreSQL vs. MySQL: fight

Jeff Davis wrote:
> On Sat, 2007-08-11 at 00:06 +0200, Lukas Kahwe Smith wrote:
>>> Is there a document explaining more of the differences between the
>>> postgresql MVCC model and something closer to InnoDB or Oracle, where it
>>> has rollback segments? I'm interested in the design tradeoffs between
>>> the two ideas.
>> I cannot give you an exact comparison. But the PostgreSQL docs are 
>> pretty good on how things work there and the following article explains 
>> how things are in Oracle and the rest:
>> http://www.ibphoenix.com/main.nfs?page=ibp_mvcc_roman
>>
> 
> Thanks for the link. 
> 
> 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.

> I wonder how they are able to update records when the new version takes
> up more space than the old version? Also, how do they update indexes
> that point to a value that has changed? And how do they reclaim storage
> for deletes?

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

> It seems like the approach of interbase, etc, has some advantages by
> keeping better cluster order and reducing the need for VACUUM, but seems
> like it might introduce other problems (although they don't explain what
> those other problems are). Hopefully HOT is the best of all worlds.

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

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.

regards,
Lukas