TLDR;
If your users are confused that certain old unread topics are missing from suggested, consult
the suggested topics unread max days old
site setting.
The problem
For quite a few years I have been struggling with a very specific pathological performance problem.
As a forum grows certain users are mega active and collect a large amount of read state. This means Discourse could be tracking 10s of thousands of topics for a single user.
When you visit any topic, the first query to figure out suggested topics is:
Tell me what topics does current user have that are “unread”.
Unfortunately, in the case of mega active users this can mean you need to join a table with 10s of thousands of rows with another table of 100s of thousands of rows just to figure out the answer. We also need to do this every single time we show suggested topics to the user.
This is costly on server resources and caused regular performance issues for the most active users.
The solution/s
It has taken quite a while to solve this but now we keep track of another date per user
- Updated date of oldest unread topic
If I know that the oldest unread topic I have is 2 days ago, when figuring out what unread topics I have I only need to look at a handful of topics, so it is very fast.
Getting this new date to be correct was a challenge, I had to account for deletions / whispers and other weird edge cases. If for any reason a topic “changes” in such a way that unread should be updated for a user we will update the updated_at
column on the topics table. There is a regular 2 times a day job (ensure consistency) that will make sure the date on the user will always match reality.
This solution seems to have worked great cause we have gotten 0 complaints about missing unreads in a week.
This optimisation applies in 3 spots:
- First load of Discourse
- Visiting the unread tab
- Rendering suggested topics
The remaining problem
That said, some of our users are “unread hoarders”, they carry unread topics from 5 years ago. In pathological cases like this where people never clear the unread list the entire optimisation above makes very little difference.
So, these users still got regular large delays rendering suggested topics, which is a very common use case.
Additionally, after discussing this with users who like to hoard unread topics, they were not really interested in seeing ancient unread topics in suggested.
To address this problem we added the site setting:
suggested topics unread max days old
Feel free to adjust the default of 90 days, however I feel it is quite a good default.