Re: MVCC cons

On 8/15/07, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Wed, 2007-08-15 at 07:06 +0530, Merlin Moncure wrote:
> > 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.
>
> I think in some databases that use locking, an INSERT can actually block
> a SELECT, and vice-versa. So wouldn't that mean PostgreSQL MVCC is
> better for INSERT performance?

For certain values of better, yes.

For inserting 10,000,000 rows, it's probably not as fast as some other
databases. But, you can insert those 10,000,000 rows while 100 users
run select statements and none of them will block. So, even if it
takes twice as long as a table locking db, that's still probably ok,
because the readers can keep right on reading.