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 [EMAIL PROTECTED]
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
