X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 30D4F5E40BA for ; Thu, 26 Aug 2004 15:18:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60497-07 for ; Thu, 26 Aug 2004 18:18:55 +0000 (GMT) Received: from bayswater1.ymogen.net (host-154-240-27-217.pobox.net.uk [217.27.240.154]) by svr1.postgresql.org (Postfix) with ESMTP id BB3E45E40AC for ; Thu, 26 Aug 2004 15:18:54 -0300 (ADT) Received: from solent (82-68-95-1.dsl.in-addr.zen.co.uk [82.68.95.1]) by bayswater1.ymogen.net (Postfix) with ESMTP id DCF3BA6547; Thu, 26 Aug 2004 19:18:52 +0100 (BST) Reply-To: From: "Matt Clark" To: "'Neil Cooper'" , Subject: Re: Disabling transaction/outdated-tuple behaviour Date: Thu, 26 Aug 2004 19:18:52 +0100 Organization: Ymogen Ltd Message-ID: <015f01c48b99$24739bf0$8300a8c0@solent> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: <1093543374.16123.38.camel@NCooper> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/366 X-Sequence-Number: 8002 > Immediately after performing a vaccum, updates take upto 50=20 > milliseconds to occur, however the update performance=20 > degrades over time, such that after a few hours of continuous=20 > updates, each update takes about half a second. Regular=20 > vacuuming improves the performance temporarily, but during=20 > the vacuum operation (which takes upto 2 minutes),=20 > performance of concurrent updates falls below an acceptable=20 > level (sometimes > 2 seconds per update). You must be doing an enormous number of updates! You can vacuum as often as you like, and should usually do so at least as often as the time it takes for 'all' tuples to be updated. So, in your case, every 10 updates. OK, that seems unnecessary, how about every 100 updates?=20 > According to the documentation, PostgreSQL keeps the old=20 > versions of the tuples in case of use by other transactions=20 > (i.e. each update is actually extending the table). I believe=20 > this behaviour is what is causing my performance problem. Yes, it probably is. > Is there a way to disable this behaviour such that an update=20 > operation would overwrite the current record and does not=20 > generate an outdated tuple each time? (My application does=20 > not need transactional support). No, I don't believe there is. If you really don't need transaction support then you might want to reconsider whether postgres is really the right tool. M