Re: Scaleable DB structure for counters...

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.

Thanks for your reply,

- E


Harald Armin Massa wrote:
> Eci,
>
> the usual way is:
>
> create table books (id_book serial, author text, title text ...)
> create table access (id_access serial, id_book int4, timeofaccess
> timestamp,...)
>
> then for every access you write 1 record to access.
>
> A rough estimate: a book may be lent out every hour once, so that is
> 8544 records per year and book;
>
> IF you expect that table gets "to big", you still can move over to
> inheritance:
>
> create table access2006 inherits access
> create table access2007 inherits access
>
> and put rules on them to make sure the data goes into the correct table
> when you access only the access table. Google up "constraint exclusion"
> within the 8.1 release notes / the postgresql documentation.
>
> Harald
>
>
> On 7/16/06, *Eci Souji* <eci.souji@gmail.com
> <mailto:eci.souji@gmail.com>> wrote:
>
>     So we've got a table called "books" and we want to build records of how
>     often each book is accessed and when.  How would you store such
>     information so that it wouldn't become a huge unmanageable table?
>     Before I go out trying to plan something like this I figured I'd ask and
>     see if anyone had any experience with such a beast.
>
>     One idea I had was to create a separate DB for these counters and create
>     a schema for each year.  Within each year schema I would create month
>     tables.  Then I'd write a function to hit whatever schema existed like,
>     ala...
>
>     SELECT * FROM public.get_counters(date, hour, book_id);
>
>     get_day_counters would break up the date and based on the year do a
>     select counters from "2006".may WHERE day=12 and book_id=37.  If hour
>     had a value it could do select counters from "2006".may where day=12 and
>     book_id=37 and hour=18.
>
>     Offline scripts would take care of generating and populating these
>     tables, as they'd be historical and never real-time.
>
>     Thoughts?  I'm hoping someone has done something similar and can point
>     me in the right direction.
>
>
>     - E
>
>
>
>
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 6: explain analyze is your friend
>
>
>
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Reinsburgstraße 202b
> 70197 Stuttgart
> 0173/9409607
> -
> on different matter:
> EuroPython 2006 is over. It was a GREAT conference. If you missed it,
> now you can prepare budget for visiting EuroPython 2007.