Re: Scaleable DB structure for counters...
От | Ron Johnson |
---|---|
Тема | Re: Scaleable DB structure for counters... |
Дата | |
Msg-id | 44BB194C.5060604@cox.net обсуждение исходный текст |
Ответ на | Re: Scaleable DB structure for counters... (Eci Souji <eci.souji@gmail.com>) |
Список | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 IOW, files. No problem. The # of files is known. That's a start. Is there any existing metric as to how often they are accessed? That's what you need to know before deciding on a design. This simple design might be perfectly feasible: CREATE TABLE T_USAGE_TXN ( BOOK_ID INTEGER, USER_ID INTEGER, REFERENCED_DT DATE, REFERENCED_TM TIME ) *All* the rows (in field order) would be the PK, and I'd then add secondary indexes on USER_ID/BOOK_ID REFERENCED_DT/BOOK_ID REFERENCED_DT/USER_ID Lastly, create and algorithmically *pre-populate* this table : T_CALENDAR ( DATE_ANSI DATE, YEARNUM SMALLINT, MONTH_NUM SMALLINT, DAY_OF_MONTH SMALLINT, DAY_OF_WEEK SMALLINT, JULIAN_DAY SMALLINT) So, if you want a list and count of all books that were referenced on Sundays in 2006: SELECT UT.BOOK_ID, COUNT(*) FROM T_USAGE_COUNT UT, T_CALENDAR C WHERE C.YEARNUM = 2006 AND C.DAY_OF_WEEK = 0 AND C.DATE_ANSI = UT.REFERENCED_DT; Eci Souji wrote: > I think "books" may have thrown everyone for a loop. These are > not physical books, but rather complete scanned collections that > would be available for search and reference online. One of the > most important features required would be keeping track of how > often each book was referenced and when. Time of day, days of > week, etc. This is why I was looking into how to construct some > form of counter system that would allow us to keep track of > accesses. > > Although I would love to see a robot librarian at work. :-) > > - E > > Ron Johnson wrote: 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. > > > Are all 200000 books accessed every hour? What kind of library > is this? Do you have robot librarians moving at hyperspeed? > Wouldn't a more reasonable value be 5000 books per *day*? > > It's easy to know when a book is checked out. How do you know > when a book is referenced? Are all books only accessed by the > librarians? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.3 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEuxlMS9HxQb37XmcRAmXCAJ42IBwCvaDMlfMsiJoPsELxL0e1QQCfUBWH 6M7o4n9q2CEKbYn/xgh6OnY= =iQF3 -----END PGP SIGNATURE-----
В списке pgsql-general по дате отправления:
Следующее
От: "Jaime Casanova"Дата:
Сообщение: Re: Log actual params for prepared queries: TO-DO item?