Effectively record and store pageviews in a database?

Question

Sites like StackOverflow store and display pageviews. How do you do it effectively? Let’s take the number of views for StackOverflow questions as an example. I see the following options.

Option 1 .. When the application receives a request for a question, increase the score in the question table. It is very inefficient! The vast majority of requests are read-only, but you use an update for each of them.

Option 2. Maintain a cache that displays new view values ​​in questionIds. When the application receives a request for a request, increase the number of cached views for the question ID. You cache the maximum increase in views. So far, so good. Now you need to periodically clear the counters in the cache. This is the second part of the problem. You can use a second thread or some kind of planning component. This is a really separate issue and partly depends on your server platform (I use Java). Or instead of using a separate stream after a certain number of samples stored in the cache, you can perform an update on the request stream that has reached the threshold. The update functionality can be encapsulated in the cache, giving the cache some IQ points.

I like the idea of ​​a cache that flushes when a threshold is reached. I am curious to know what others have done, and if there is a better way.

Answer 1

I agree that writing each pageload to the database is inefficient. My approach is to cache requests and then commit them once per hour. Each time I update the cache, I compare the current time with LastWriteTime, and if more than an hour has passed, I write. This is an ASP.NET application, so I have a final commit in the application termination method. The latter is not guaranteed, but is considered an acceptable loss.