From: Jim C. Nasby <jnasby@pervasive.com>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Gavin Sherry <swm@alcove.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow concurrent update of same row in a given table
Date: Tue, 4 Oct 2005 13:55:13 -0500
Message-ID: <20051004185513.GS40138@pervasive.com> (raw)
In-Reply-To: <a97c77030509281929ca316a1@mail.gmail.com>
References: <a97c770305092805271ff5e83f@mail.gmail.com>
<Pine.LNX.4.58.0509282245040.19538@linuxworld.com.au>
<a97c7703050928104421786201@mail.gmail.com>
<Pine.LNX.4.58.0509290757220.22312@linuxworld.com.au>
<a97c77030509281929ca316a1@mail.gmail.com>
On Thu, Sep 29, 2005 at 07:59:34AM +0530, Rajesh Kumar Mallah wrote:
> > I see. These problems regularly come up in database design. The best thing
> > you can do is modify your database design/application such that instead of
> > incrementing a count in a single row, you insert a row into a table,
> > recording the 'dispatch_id'. Counting the number of rows for a given
> > dispatch id will give you your count.
> >
>
> sorry i will be accumulating huge amount of rows in seperate table
> with no extra info when i really want just the count. Do you have
> a better database design in mind?
>
> Also i encounter same problem in implementing read count of
> articles in sites and in counting banner impressions where same
> row get updated by multiple processes frequently.
Databases like to work on *sets* of data, not individual rows. Something
like this would probably perform much better than what you've got now,
and would prevent having a huge table laying around:
INSERT INTO holding_table ... -- Done for every incomming
connection/what-have-you
CREATE OR REPLACE FUNCTION summarize() RETURNS void AS $$
DECLARE
v_rows int;
BEGIN
DELETE FROM holding_table;
GET DIAGNOSTICS v_rows = ROW_COUNT;
UPDATE count_table
SET count = count + v_rows
;
END;
$$ LANGUAGE plpgsql;
Periodically (say, once a minute):
SELECT summarize()
VACUUM holding_table;
VACUUM count_table;
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, mallah.rajesh@gmail.com, swm@alcove.com.au
Subject: Re: Slow concurrent update of same row in a given table
In-Reply-To: <20051004185513.GS40138@pervasive.com>
* 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