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 64D145E46F4 for ; Thu, 26 Aug 2004 15:03:09 -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 55428-04 for ; Thu, 26 Aug 2004 18:03:06 +0000 (GMT) Received: from ussmtp02.scigames.com (ussmtp02.scientificgames.com [65.5.3.6]) by svr1.postgresql.org (Postfix) with ESMTP id 2F3FE5E40AC for ; Thu, 26 Aug 2004 15:03:02 -0300 (ADT) Received: by ussmtp02.scigames.com with XWall v3.30 ; Thu, 26 Aug 2004 14:09:44 -0400 From: Neil Cooper To: "pgsql-performance@postgresql.org" Subject: Disabling transaction/outdated-tuple behaviour Date: Thu, 26 Aug 2004 14:02:55 -0400 X-Assembled-By: XWall v3.30 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Message-ID: <1093543374.16123.38.camel@NCooper> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200408/364 X-Sequence-Number: 8000 I am using a simple PostgreSQL 7.3 database in a soft-realtime application. I have a problem where an update on a record within a (fully indexed) table containing less than ten records needs to occur as fast as possible. Immediately after performing a vaccum, updates take upto 50 milliseconds to occur, however the update performance degrades over time, such that after a few hours of continuous updates, each update takes about half a second. Regular vacuuming improves the performance temporarily, but during the vacuum operation (which takes upto 2 minutes), performance of concurrent updates falls below an acceptable level (sometimes > 2 seconds per update). According to the documentation, PostgreSQL keeps the old versions of the tuples in case of use by other transactions (i.e. each update is actually extending the table). I believe this behaviour is what is causing my performance problem. Is there a way to disable this behaviour such that an update operation would overwrite the current record and does not generate an outdated tuple each time? (My application does not need transactional support). =20 I believe this would give me the performance gain I need, and would eliminate the need for regular vacuuming too. =20 Thanks in advance, Neil Cooper. This communication (including any attachments) is intended for the use of t= he intended recipient(s) only and may contain information that is confident= ial, privileged or legally protected. Any unauthorized use or dissemination= of this communication is strictly prohibited. If you have received this co= mmunication in error, please immediately notify the sender by return e-mail= message and delete all copies of the original communication. Thank you for= your cooperation.