From: PFC <lists@boutiquenumerique.com>
To: Tino Wildenhain <tino@wildenhain.de>
To: Gavin Sherry <swm@alcove.com.au>
Cc: Mark Cotner <mcotner@yahoo.com>
Cc: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speedier count(*)
Date: Thu, 11 Aug 2005 14:08:34 +0200
Message-ID: <op.svceokgbth1vuj@localhost> (raw)
In-Reply-To: <1123757536.27613.10.camel@Andrea.peacock.de>
References: <BF204EF7.D9F6%mcotner@yahoo.com>
<1123745049.27613.1.camel@Andrea.peacock.de>
<Pine.LNX.4.58.0508112035580.1719@linuxworld.com.au>
<1123757536.27613.10.camel@Andrea.peacock.de>
> You could lock the count table to prevent the problem
> where 2 competing transactions do an insert, read the
> start value and add 1 to it and then write the result
> - which is n+1 rather then n+2 - so you are off by one.
> Think of the same when one transaction inserts 100
> and the other 120. Then you could even be off by 100.
Niet.
If your trigger does UPDATE counts_cache SET cached_count =
cached_count+N WHERE ...
Then all locking is taken care of by Postgres.
Of course if you use 2 queries then you have locking issues.
However the UPDATE counts_cache has a problem, ie. it locks this row FOR
UPDATE for the whole transaction, and all transactions which want to
update the same row must wait to see if the update commits or rollbacks,
so if you have one count cache row for the whole table you get MySQL style
scalability...
To preserve scalability you could, instead of UPDATE, INSERT the delta of
rows inserted/deleted in a table (which has no concurrencies issues) and
compute the current count with the sum() of the deltas, then with a cron,
consolidate the deltas and update the count_cache table so that the deltas
table stays very small.
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: lists@boutiquenumerique.com, tino@wildenhain.de, swm@alcove.com.au, mcotner@yahoo.com, fbsd@drivefaster.net
Subject: Re: Speedier count(*)
In-Reply-To: <op.svceokgbth1vuj@localhost>
* 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