Re: Scaleable DB structure for counters...

Hi,

On Sun, 16 Jul 2006, Eci Souji wrote:

> What if instead of book checkouts we were looking at how often a book was
> referenced?  In which case we're talking multiple times an hour, and we could
> easily have each book requiring hundreds of thousands of rows.  Multiply that
> by hundreds of thousands of books and a the table seems to become huge quite
> quick.  Would breaking up the table by year still make sense?  I'm just not
> familiar with having to deal with a table that could easily hit millions of
> records.

you might want to keep a separate table with counters per book
and per year or month which you regularly compute from your yearly
or month totals.

something like following untested code:

   INSERT INTO access_count
   SELECT id_book, date_trunc('day',timeofaccess) AS dayofaccess,count(id_book)
   FROM access
   WHERE date_trunc('day',timeofaccess) = date_trunc('day',now())
   GROUP BY id_book, dayofaccess

That way you do not need to count all the access records.
You just sum up the pre computed counts for each period.

   SELECT sum(count) FROM access_count WHERE id_book=?

You also have the option of throwing away the raw access data
for a certain day or month once that period of time is over.

This is more efficient than calling a trigger on each access and
also more scalable as there is no contention over a per book count
record.

Keeping the raw data in per month or year partitions is also propably
a good idea as it allows you to easily drop specific partitions.

Greetings
Christian

--
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136