From: Tino Wildenhain <tino@wildenhain.de>
To: PFC <lists@boutiquenumerique.com>
Cc: 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:34:00 +0200
Message-ID: <1123763640.27613.18.camel@Andrea.peacock.de> (raw)
In-Reply-To: <op.svceokgbth1vuj@localhost>
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>
<op.svceokgbth1vuj@localhost>
Am Donnerstag, den 11.08.2005, 14:08 +0200 schrieb PFC:
>
> > 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.
Yes, in the case you use just the UPDATE statement you are right. This
does the locking I was talking about.
In either case I'd use an after trigger and not before to minimize
the impact.
> 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.
Yes, this is in fact a better approach to this problem.
(All this provided you want an unqualified count() - as the
original poster)
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: tino@wildenhain.de, lists@boutiquenumerique.com, swm@alcove.com.au, mcotner@yahoo.com, fbsd@drivefaster.net
Subject: Re: Speedier count(*)
In-Reply-To: <1123763640.27613.18.camel@Andrea.peacock.de>
* 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