What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

As far as I understand SO keeps votes in a separate table and calculates the post votes based on that data.

So DB query can look like this assuming votes can be 1/-1.

SELECT SUM(value) FROM votes
WHERE postId = [some post id]

In addition to vote count when a user opens a post it also show id the current user has voted up/down for that post, which can mean another DB query.

I would like to know the inner implementation details, since with such huge data it can be really slow to calculate votes all the time for each post. Is Stack Overflow caching this information, recalculating on background, etc.? If it is caching, then what? If it is calculating in background then how?

share|improve this question
4  
I'll defer answering this to someone who actually knows what they're talking about, but the short answer is: post scores are denormalized for speed, updated when votes are submitted, and corrected (when necessary) by background tasks. –  Shogging through the snow Oct 11 '13 at 4:58
1  
@Shog9 One of the cases where votes should be recalculated but aren't are user removed events. That's where I see it get out of sync the most often. But I can't say whether or not it's an expensive query to run upon deleting a user. –  Mysticial Oct 11 '13 at 5:05
    
@Shog9 what you mean denormalized ? does it mean that posts table contains the vote count/vote details information inn Posts table ? –  Tamerlane Oct 11 '13 at 5:09
3  
No, it means the bit that's displayed - the post's score - is in the posts table, and therefore doesn't need to be recalculated each time the post is displayed. See also: codinghorror.com/blog/2008/07/… –  Shogging through the snow Oct 11 '13 at 5:13
    
@Shog9 thanks for the clarification. Now it is interesting what is happening when a user up votes. Obviously it will not update the post and insert a row into votes table right away, but at the same time I haven't seen any info that SO is using any MQ system. I hope someone will have these details in the answer. –  Tamerlane Oct 11 '13 at 5:21
2  
@Mysticial Also serial voting reversals don't update the vote counts for posts. Makes them look better/worse than they actually are until someone comes along and votes or loads the separated counts to update it. –  animuson Oct 11 '13 at 5:23
    
@animuson Yeah; my over-developed sense of 'justice' makes me expand the vote counts on many of such persons' recent posts to fix that immediately. –  Andrew Barber Oct 11 '13 at 17:04

You must log in to answer this question.

Browse other questions tagged .