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

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@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/