Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5FA95650E98 for ; Thu, 26 Jun 2008 19:15:30 -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 44810-04 for ; Thu, 26 Jun 2008 19:15:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 3E1C7650E82 for ; Thu, 26 Jun 2008 19:15:16 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m5QMFFMi003414; Thu, 26 Jun 2008 18:15:15 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m5QMFEP9003408; Thu, 26 Jun 2008 18:15:15 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Thu, 26 Jun 2008 18:15:14 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: Holger Hoffstaette cc: pgsql-performance@postgresql.org Subject: Re: ??: Postgresql update op is very very slow In-Reply-To: Message-ID: 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; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/187 X-Sequence-Number: 30537 On Thu, 26 Jun 2008, Holger Hoffstaette wrote: > How do large databases treat mass updates? AFAIK both DB2 and Oracle use > MVCC (maybe a different kind?) as well An intro to the other approaches used by Oracle and DB2 (not MVCC) is at http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007#Transaction_Locking_and_Scalability (a URL which I really need to shorten one day). > Are there no options (algorithms) for adaptively choosing different > update strategies that do not incur the full MVCC overhead? If you stare at the big picture of PostgreSQL's design, you might notice that it usually aims to do things one way and get that implementation right for the database's intended audience. That intended audience cares about data integrity and correctness and is willing to suffer the overhead that goes along with operating that way. There's few "I don't care about reliability here so long as it's fast" switches you can flip, and not having duplicate code paths to support them helps keep the code simpler and therefore more reliable. This whole area is one of those good/fast/cheap trios. If you want good transaction guarantees on updates, you either get the hardware and settings right to handle that (!cheap), or it's slow. The idea of providing a !good/fast/cheap option for updates might have some theoretical value, but I think you'd find it hard to get enough support for that idea to get work done on it compared to the other things developer time is being spent on right now. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD