Design nested comments with voting

I would store comments in one table with a materialized path. Each row holds its post, parent, depth and a path made of fixed-width blocks, so a branch is a prefix range on an index and sorting by path gives depth-first order. To open a thread, the API checks a cache that holds each page for 10 seconds. On a miss, one query returns the top 5 replies under each parent down to depth 6, and the service builds the tree.

With a 90% hit rate, the database sees about 700 reads a second at peak. Votes go to a store with one row per user and comment. The upsert returns the old value, so the change is the new value minus the old one, and a double click is a zero. Changes flow through a queue to workers that sum them for 5 seconds per comment and write one update, turning 4,600 writes a second into about 600.

The hot score is a signed log2 of the score plus a time bonus fixed at creation, so it changes only on votes. Deletes are soft, so replies stay. The trade-off is that public counts lag by up to about 16 seconds: 5 in the batch, 1 to write and 10 in the cache.

These numbers are assumptions for the exercise, not facts about a real system:

• The site gets 2 million new comments, 40 million votes and 60 million thread views a day, and peak is 10 times the average.
• A thread view returns up to 200 comments. A comment row takes about 500 bytes with its path and indexes, and a vote row takes 16 bytes.
• 90% of views land on a few thousand hot threads, so a cache that holds a page for 10 seconds answers 90% of views.
• In a 5-second window at peak, the votes touch about 3,000 different comments.

The arithmetic:

Thread views per second

60M a day ÷ 86,400 s ≈ 694

≈ 700/s on average, 7,000/s at a 10x peak

Database reads with the cache

7,000/s × 10% misses

≈ 700/s, easy for one primary and a replica

Score writes with batching

Peak 4,600 votes/s × 5 s = 23,000 votes on ≈ 3,000 comments; 3,000 ÷ 5 s

≈ 600 updates/s, against 4,600/s unbatched

Comment storage

2M × 365 = 730M comments × 500 bytes

≈ 365 GB a year

Vote storage

40M × 16 bytes = 640 MB a day × 365

≈ 234 GB a year

There are three common ways to store a tree in a table.

• Adjacency list. Each row stores its parent id. Writes are trivial. Reading a branch needs a recursive query, or one query per level, and cost grows with depth.
• Materialized path. Each row stores the path of its ancestors, such as the ids of every ancestor joined together. A branch is a prefix range on an index, and sorting by path gives depth-first order for free.
• Closure table. A second table holds one row for every ancestor and descendant pair. Branch reads are simple joins, but a comment at depth 4 adds 5 rows. At 2 million comments a day, that is about 10 million extra rows a day.

Comments never move to another parent, which is the case where paths hurt most. So I would choose the materialized path. Give each comment a number within its post, taken from a counter on the post row. Write the number as a fixed-width block of 4 base-36 characters, which allows over 1.6 million comments in one post. A reply's path is its parent's path plus its own block. Fixed width matters: it makes plain string comparison put a parent before its children and siblings in creation order. A depth limit of 20 caps a path at 80 characters. A reply to a comment at the limit attaches to its parent instead, so a chain cannot grow forever.

Index on the post and the path together. Loading oldest-first is then a range read with a cursor on the last path seen. Best and hot orders are not path order, so use one query with a window function. It numbers the children under each parent by score and keeps the top 5, down to depth 6. The service drops any row whose parent did not come back, orders siblings by score, and walks the tree depth-first.

Each row also stores its parent, depth and a direct reply count. The count is what a 'load 12 more replies' link shows, and clicking it reads that branch by path prefix.

One vote per user. The Vote store has a unique key on the comment and the user, and holds a value of minus 1, 0 or plus 1. A vote is an upsert that returns the old value in the same statement. The change is the new value minus the old one. A double click or a retry finds that the value is already set, computes a change of zero, and stops. The same rule handles flipping from up to down, which is a change of minus 2.

Batching the counter. Updating the comment's score on every vote means one hot comment takes hundreds of writes a second on one row. Instead, the API sends each nonzero change to a queue partitioned by comment id. One worker owns each comment, sums its changes for about 5 seconds, and writes a single update. In our estimate that turns 4,600 updates a second into about 600. A comment with 1,000 votes a second becomes one write every 5 seconds. A queue may deliver a message twice, so the score can drift slightly. A job that recounts recently active comments from the vote rows corrects it.

Hot score. Sorting by raw score would keep old comments on top forever. Add a time bonus that is fixed when the comment is created:

• hot = sign(score) × log2(max(|score|, 1)) + seconds since a fixed start date ÷ 43,200

The sign keeps a heavily downvoted comment below a neutral one: minus 8 scores minus 3, while scores of minus 1, 0 and 1 all tie at 0. Every 12 hours of age costs one doubling. A comment posted 12 hours later with half the score ties with the older one: 8 votes is 3, and 4 votes is 2, plus 1 for the newer 12 hours. Because the time part is a constant for a comment, the hot score changes only when its votes change. No job has to re-score every comment each minute. Store it in a column and index it by post and hot score.

For the best order, use a score that distrusts small samples, such as the lower bound of a confidence interval on the share of up votes. A comment with 1 up vote and no down votes should not beat one with 90 up and 10 down.

Finally, rate-limit votes per account, and give less weight to votes from brand-new accounts. Cheap vote farms are the main way this system is attacked.

Cache the page. The key is the post, the sort order and the page number. The value is the built tree. A TTL of 10 seconds is enough. It adds at most 10 seconds to the 5-second batch and the write, which fits the 20-second target. With 90% of views on hot threads, the database sees about 700 reads a second at peak. Per-reader details are kept out of the cached page: it holds no votes of the viewer. The API reads that viewer's votes for just the 200 comment ids on screen, in one indexed lookup, and merges them in.

When a hot page expires, thousands of requests may miss at the same moment. Let one request rebuild it while the others wait or are given the slightly old copy. This is often called single flight, or serving stale while revalidating.

Collapse by default. The first page shows the top 5 replies under each parent, down to depth 6. Anything cut off is a stub with the number of replies it hides. Loading it is a range read on that branch's path prefix, with a cursor for the next page. A thread with 100,000 comments never has to travel in one response.

Soft deletes. Hard-deleting a comment would orphan its replies or cascade a delete through the branch. Instead, set a deleted flag, blank the text and author in the response, and keep the row, its path and its score. Readers see a placeholder, and the replies stay in place. For privacy, also erase the text and author from the stored row itself, so only the empty shell remains.

A deleted comment with no live replies is clutter. A background job can mark those as pruned and hide them. Doing that check on each read would be too costly. Moderator removals work the same way, with a reason stored for the audit log.