Re: big transaction slows down over time - but disk seems

From: Richard Huxton <dev(at)archonet(dot)com>
To: Ben <bench(at)silentmedia(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: big transaction slows down over time - but disk seems
Date: 2006-11-01 17:15:16
Message-ID: 4548D624.1070208@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Ben wrote:
>
>
> On Wed, 1 Nov 2006, Richard Huxton wrote:
>
>> 1. Avoid updating the same timestamp more than once (if that's happening)
>
> Each row is updated at most once, and not all rows are updated.
>
>> 2. Update timestamps in one go at the end of the transaction (perhaps
>> by loading updates into a temp table).
>
> Hey, that's not a bad idea. I'll give that a shot. Thanks!
>
>> 3. Split the transaction in smaller chunks of activity.
>
> I'd be happy to do this too, except that I need a simple way to rollback
> everything, and I don't see how I can get that with this.

Well, you could with a temp-table, but it probably won't be necessary if
you have one. You might wan to issue a vacuum on the updated table after
the transaction completes.

Note that this idea is built on a set of assumptions that might not be
true, so do test.

Oh - if you're processing rows one at a time with your stored procedure,
see if there's not a way to process the whole set. That can make a huge
difference.

--
Richard Huxton
Archonet Ltd

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Steven Flatt 2006-11-01 19:15:29 Database-wide vacuum can take a long time, during which tables are not being analyzed
Previous Message Ben 2006-11-01 16:51:46 Re: big transaction slows down over time - but disk seems