pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Cooper <Neil.Cooper@scigames.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Disabling transaction/outdated-tuple behaviour
Date: Thu, 26 Aug 2004 14:02:55 -0400
Message-ID: <1093543374.16123.38.camel@NCooper> (raw)

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).
 
I believe this would give me the performance gain I need, and would
eliminate the need for regular vacuuming too.
 
Thanks in advance,

Neil Cooper.

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication 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.



view thread (3+ messages)  latest in thread

Message-ID: <1093543374.16123.38.camel@NCooper>
Permalink:  ../1093543374.16123.38.camel@NCooper/
Also on:    postgresql.org/message-id/1093543374.16123.38.camel@NCooper

 · 

reply

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: Neil.Cooper@scigames.com
  Subject: Re: Disabling transaction/outdated-tuple behaviour
  In-Reply-To: <1093543374.16123.38.camel@NCooper>

* 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