Re: Speedier count(*)

From: PFC
Subject: Re: Speedier count(*)
Date: ,
Msg-id: op.svceokgbth1vuj@localhost
(view: Whole thread, Raw)
In response to: Re: Speedier count(*)  (Tino Wildenhain)
Responses: Re: Speedier count(*)  (Tino Wildenhain)
List: pgsql-performance

Tree view

Speedier count(*)  (Dan Harris, )
 Re: Speedier count(*)  ("Joshua D. Drake", )
 Re: Speedier count(*)  (Michael Fuhr, )
 Re: Speedier count(*)  (John A Meinel, )
 Re: Speedier count(*)  (Gavin Sherry, )
  Re: Speedier count(*)  (Mark Cotner, )
   Re: Speedier count(*)  (Tino Wildenhain, )
    Re: Speedier count(*)  (Gavin Sherry, )
     Re: Speedier count(*)  (Tino Wildenhain, )
      Re: Speedier count(*)  (PFC, )
       Re: Speedier count(*)  (Tino Wildenhain, )
        Re: Speedier count(*)  (Dan Harris, )

> 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.


    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

    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.

pgsql-performance by date:

From: "Jeffrey W. Baker"
Subject: Re: [SPAM?] Re: PG8 Tuning
From: Tom Arthurs
Subject: Re: [SPAM?] Re: PG8 Tuning