Skip site navigation (1) Skip section navigation (2)

Re: ??: Postgresql update op is very very slow

From: Andrew Sullivan <ajs(at)commandprompt(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: ??: Postgresql update op is very very slow
Date: 2008-06-26 13:53:41
Message-ID: 20080626135340.GE6918@commandprompt.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Jun 26, 2008 at 02:40:59PM +0200, Holger Hoffstaette wrote:

> large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
> (maybe a different kind?) as well, but I cannot believe that large updates
> still pose such big problems.

DB2 does not use MVCC.  This is why lock escalation is such a big
problem for them.

Oracle uses a kind of MVCC based on rollback segments: your work goes
into the rollback segment, so that it can be undone, and the update
happens in place.  This causes a different kind of pain: you can run
out of rollback segments (part way through a long-running transaction,
even) and then have to undo everything in order to do any work at
all.  Every system involves trade-offs, and different systems make
different ones.  The bulk update problem is PostgreSQL's weak spot,
and for that cost one gets huge other benefits.  

> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?

How would you pick?  But one thing you could do is create the table
with a non-standard fill factor, which might allow HOT to work its magic.

A

-- 
Andrew Sullivan
ajs(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/

In response to

pgsql-performance by date

Next:From: Andrew SullivanDate: 2008-06-26 13:55:01
Subject: Re: ??: Postgresql update op is very very slow
Previous:From: Peter T. BreuerDate: 2008-06-26 13:49:44
Subject: Re: Hardware vs Software RAID

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group