Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1FEEC650EA6 for ; Thu, 26 Jun 2008 10:53:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 26030-05 for ; Thu, 26 Jun 2008 10:53:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id 25F61650EC8 for ; Thu, 26 Jun 2008 10:53:49 -0300 (ADT) Received: from commandprompt.com (227-54-222-209.mycybernet.net [209.222.54.227]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m5QDtcJI000445 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 26 Jun 2008 06:55:42 -0700 Date: Thu, 26 Jun 2008 09:53:41 -0400 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: ??: Postgresql update op is very very slow Message-ID: <20080626135340.GE6918@commandprompt.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <473587.97248.qm@web32701.mail.mud.yahoo.com> <004b01c8d671$3dec1630$0544000a@hz.ali.com> <486219B6.7050003@enterprisedb.com> <005101c8d773$ff81b2c0$0644000a@hz.ali.com> <48638939.5080500@enterprisedb.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.17 (2007-11-01) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Thu, 26 Jun 2008 06:55:43 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/169 X-Sequence-Number: 30519 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/