From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: INSERT waiting under heavy load
Date: Sun, 8 Jan 2006 13:21:14 -0500
Message-ID: <20060108182114.GA31582@phlogiston.dyndns.org> (raw)
In-Reply-To: <23072.1136584149@sss.pgh.pa.us>
References: <20060106184227.GA1890@s2.yuriev.com>
<22727.1136580728@sss.pgh.pa.us>
<20060106201657.GB1890@s2.yuriev.com>
<23072.1136584149@sss.pgh.pa.us>
On Fri, Jan 06, 2006 at 04:49:09PM -0500, Tom Lane wrote:
> performance risk in the sort of scenario you are describing.
> (I'm not sure why it would manifest as transactions showing "INSERT
> waiting" state though.)
It's because of I/O. When you have a large number of updates, the
planner always assumes an indexscan (correctly), but you end up
scanning megabytes of dead tuples. With a large number of open
transactions, most of the time VACUUM can't recover the space. I
agree with what Tom said earlier in this thread: the design is
guaranteed to lose.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
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-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: INSERT waiting under heavy load
In-Reply-To: <20060108182114.GA31582@phlogiston.dyndns.org>
* 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