Re: MVCC cons

On 8/14/07, Kenneth Downs <ken(at)secdat(dot)com> wrote:
> RPK wrote:
> > I want to know whether MVCC has cons also. Is it heavy on resources? How
> > PGSQL MVCC relates with SQL Server 2005 new Snapshot Isolation.
> >
>
> Speaking as an end-user, I can give only one I've ever seen, which is
> performance. Because of MVCC, Postgres's write performance (insert and
> update) appears on my systems to be almost exactly linear to row size.
> Inserting 1000 rows into a table with row size 100 characters takes
> twice as long as inserting 1000 rows into a table with row size 50
> characters.

You were half right. Inserts in PostgreSQL perform similar to other
databases (or at least, use similar mechanisms). It's the updates
that suffer, because this translates to delete + insert essentially.
Databases that use simple locking strategies can simply update the
record in place.

PostgreSQL wins in terms of better concurrency (especially in long
transactions or transactions that touch a lot of records), cheap
rollbacks, and all the advantages of a sophisticated locking engine
(transactional ddl for example).

merlin