pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Clark <matt@ymogen.net>
To: 'Neil Cooper' <Neil.Cooper@scigames.com>
To: pgsql-performance@postgresql.org
Subject: Re: Disabling transaction/outdated-tuple behaviour
Date: Thu, 26 Aug 2004 19:18:52 +0100
Message-ID: <015f01c48b99$24739bf0$8300a8c0@solent> (raw)
In-Reply-To: <1093543374.16123.38.camel@NCooper>

> 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).

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? 

> 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.

Yes, it probably is.

> 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).

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




view thread (3+ messages)  latest in thread

Message-ID: <015f01c48b99$24739bf0$8300a8c0@solent>
Permalink:  ../015f01c48b99$24739bf0$8300a8c0@solent/
Also on:    postgresql.org/message-id/015f01c48b99$24739bf0$8300a8c0@solent

 · 

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: matt@ymogen.net, Neil.Cooper@scigames.com
  Subject: Re: Disabling transaction/outdated-tuple behaviour
  In-Reply-To: <015f01c48b99$24739bf0$8300a8c0@solent>

* 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