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