Database design for holding read/unread content state

In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: member_id and post_id. Each time a post is seen (read) by a member a record is inserted.

I am going to need to fetch/calculate/perform:

• Number of unread posts for each member

• Get all unread posts for each member

• Given list of posts, indicate which ones are unread by particular

member

• Mark all posts as read for each member separately

And I am going to need to do this on every request, since 1) and 2) is displayed in sidebar widget present on all pages and 3) is going to be needed when browsing through posts (also very common).

The model works in theory (and in practice under not much load), but sure it does not seem like a scalable solution. I am going to get ~1,000 posts per day viewed by ~500 members which gives 500,000 records a day in junction table...

I am a programmer not DB expert, thus I am asking for some ideas on how to optimize this situation.

It may be better to mark unread entries - read entries will accumulate over time. Unread entries you could kill after let"s say 90 days (show all stuff older than 90 days as read to avoid buildup.

You may also want to just have a top marker per forum - i.e. you can not have a specific post unread. This will seriously cut down on the number of database tnreis.

StackExchange Database Administrators Q#52355, answer score: 2