Design Reddit (posts, comments, votes)

Reddit's open-sourced 'hot' formula is roughly: score = log10(max(|ups - downs|, 1)) × sign + (created_at_seconds - epoch) / 45000. The log means the first 10 votes count as much as the next 90. The time term adds about 1 point every 12.5 hours, so a new post needs ~10x more net votes to beat one 12.5 hours older. A useful property: the hot score of a post only changes when it gets votes, not as time passes, because age is built into the score as a constant. So sorted sets in Redis stay valid without periodic re-scoring of every post. 'Top' is just net score within a time window (hour, day, week…), kept as separate sorted sets that are trimmed by time. For comments, 'best' uses the lower bound of the Wilson score confidence interval of the upvote ratio: a comment with 5 up and 0 down ranks below one with 100 up and 5 down, because we are less sure about the first. 'Controversial' favours many votes with a near-even split. Mentioning the Wilson score and the time-constant property of hot is a strong signal.

A viral post can get thousands of votes per second. If every vote does UPDATE posts SET ups = ups + 1 WHERE id = X, all those transactions lock the same row and the database stalls. Split the problem. First, the source of truth for 'who voted what' is the votes table keyed by (user_id, item_id); an upsert makes votes idempotent (double-tap sends +1 twice, the second changes nothing) and lets us compute the delta when someone flips their vote. Second, scores are derived: vote deltas go to Kafka partitioned by item_id, and an aggregator sums them per item over short windows and applies one update per item per window. Thousands of votes become one write per second. Because the aggregate can drift if events are lost, a nightly job recomputes scores from the votes table and fixes differences. The UI shows the user's own vote immediately from the client, while public counts are eventually consistent. Reddit also fuzzes displayed counts slightly, which makes it harder for spammers to see whether their bot votes counted.

Comments form a tree of unknown depth. Options: adjacency list (parent_id only) is simple to write but loading a tree needs recursive queries; nested sets make reads fast but inserts rewrite many rows; materialised path (store '0001.0042.0007') supports subtree queries with a prefix match and ordering by path. A practical design: store each comment with post_id, parent_id and depth, partitioned by post_id so the whole thread is one partition read, and assemble the tree in the Comment Service. A thread with 30k comments is ~12 MB of rows, too much to send to a phone, so the service sorts siblings and returns only the top part: top-level comments in sort order with a few levels of replies, cutting off branches with 'load more' stubs. For very hot threads (match threads, AMAs), keep the pre-sorted tree in cache and update it incrementally on new comments and score changes, refreshing every few seconds instead of per request. comment_count on the post is a denormalised counter updated asynchronously.

Each subreddit has precomputed sorted sets in Redis for hot, new, rising and top-by-window, each capped at ~1,000 posts (nobody pages beyond that). The Score Aggregator updates positions as votes arrive; new posts are added to 'new' and 'hot' on submit. A subreddit page is: ZREVRANGE on the right set, then hydrate posts from Memcached, then filter removed/NSFW per user settings. The home feed for a logged-in user merges the hot lists of their subscribed subreddits (most users follow < 100). A k-way merge of the top ~50 from each list by hot score is cheap, and the result is cached per user for a minute or two. Because hot is comparable across subreddits, the merge is fair-ish, but large subreddits dominate; a per-subreddit normalisation or cap keeps small communities visible. Logged-out traffic (a large share from search engines) gets fully rendered pages cached at the CDN for 30-60 s, which removes most load.

Updating scores synchronously keeps counts exact but creates hot-row contention and slows votes. Async aggregation via Kafka gives a few seconds of lag and needs reconciliation, but scales to viral posts. Users tolerate lag on public counts as long as their own vote shows instantly.

SQL ORDER BY hot_score with an index works for small scale, but every vote moves rows in the index and hot pages repeat the same query thousands of times. Redis sorted sets are fast and cheap to update, at the cost of a cache rebuild path if Redis is lost.

Adjacency list is simplest and fine when whole threads are loaded and assembled in memory. Materialised paths help subtree queries ('load more' for a deep branch). Nested sets are rarely worth it for write-heavy threads.

One item's Kafka partition gets thousands of events/s. The aggregator batches per item, so writes stay at ~1/s per item. If one partition is overloaded, pre-aggregate in the Vote Service memory for 1 s before publishing.

A cricket match thread gets hundreds of comments per minute and thousands of readers refreshing. Serve a cached tree refreshed every few seconds and default 'new' sort for live threads; push new comment counts over a WebSocket instead of full reloads.

Rebuild from Postgres: for each active subreddit, select recent posts and compute hot scores; serve stale CDN pages meanwhile. Rebuild most-active subreddits first.

Votes are already stored, so nothing is lost. Scores lag; when the consumer restarts it resumes from committed offsets. Aggregated updates must be idempotent per window, or use the nightly recompute to correct double-applied windows.

Abuse detection marks clustered votes (same IP ranges, new accounts, timing patterns) and sets their weight to zero, then recompute affected scores from the votes table.