I'm facing a challenge and I need your opinion, let me explain:
I have a database of around 300 000 users, which all have a profile page, and I would like to store the amounts of visitors that visit their profile on a weekly ( or daily?) basis for reporting purpose (graph would be available on their admin page).
I'm thinking about doing so in a dedicated table (let's call it "stat") organised as follows:
I'm thinking about an AJAX request on the profile page that would filter only non-robot user, check if the ip exist in the ´current_ip´ table (with a LIKE request) and if it doesn't exist I would unserialize the ´current_ip´, push the ip of the new visitor, serialize the ip and UPDATE the table.
At the end of each period (so every week or every day) I'm thinking about a cron task counting the number of ip un the 'current_ip', push that number (with the date) in the 'statistic' value (using the same method than previously explained), and then delete the ´curent_ip´ value so it´s empty for the next period.
Btw I'm using php5 and PostgreSQL (9.1) with an i5 (4 x 3.2 Ghz) in an ubuntu 12.04LTS dedicated server with SSD and 16g RAM.
Is that the best, easiest or fastest way of doing it? Am I all wrong?! Should I use 1 line per period instead of using a serialized array to store historical values?!
Any suggestion is welcome =)
Cheers
Geoffrey
Use HBase counters instead of postgres. It's much more eficient for that purpose.