From: Andrew Sullivan <ajs@commandprompt.com>
To: pgsql-performance@postgresql.org
Subject: Re: ??: Postgresql update op is very very slow
Date: Thu, 26 Jun 2008 09:53:41 -0400
Message-ID: <20080626135340.GE6918@commandprompt.com> (raw)
In-Reply-To: <pan.2008.06.26.12.40.59.151500@wizards.de>
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
<op.uc83c7m2cigqcu@apollo13.peufeu.com>
<004b01c8d671$3dec1630$0544000a@hz.ali.com>
<486219B6.7050003@enterprisedb.com>
<005101c8d773$ff81b2c0$0644000a@hz.ali.com>
<48638939.5080500@enterprisedb.com>
<pan.2008.06.26.12.40.59.151500@wizards.de>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ajs@commandprompt.com
Subject: Re: ??: Postgresql update op is very very slow
In-Reply-To: <20080626135340.GE6918@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox