Re: Scaleable DB structure for counters...

From: Christian Kratzer <ck-lists(at)cksoft(dot)de>
To: Eci Souji <eci(dot)souji(at)gmail(dot)com>
Cc: Harald Armin Massa <haraldarminmassa(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Scaleable DB structure for counters...
Date: 2006-07-16 11:38:56
Message-ID: 20060716132428.R63173@vesihiisi.cksoft.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)cksoft(dot)de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2006-07-16 12:25:23 Re: Browse database , schema
Previous Message hubert depesz lubaczewski 2006-07-16 09:23:03 Re: Scaleable DB structure for counters...