Comment Threads in a Viral Social Feed
A social platform stores user posts and comments in a single posts collection. Each post document currently embeds a comments array. The platform has 12 million monthly users and creates about 3 million posts per day. Average posts receive 8 comments, but viral posts can receive more than 20,000. The team sees three symptoms: some post documents approach the 16 MB BSON limit; concurrent commenters on viral posts cause write conflicts and retry storms; and the main feed, which displays only the three most recent comments per post, reads entire comment arrays. Product requirements are: the feed shows post plus three most recent comments; the post detail page paginates comments newest-first in pages of 25; moderators must list all comments by a given author across posts; comments can be edited, deleted, and reported. The team wants a document model that keeps feed reads fast and avoids unbounded arrays.
1. Diagnose the modelling problems in the current embedded-comments design and propose a revised document structure for posts and comments. State which relationship style you would use and why.
Read the worked answer
The embedded array is unbounded, so it fails in three ways. First, a viral post can exceed 16 MB. Second, every comment write updates the same post document, creating hot-document contention and retry storms. Third, the feed reads a huge array to show three comments, wasting I/O and memory.
Use a hybrid one-to-many model: comments become their own collection with a postId reference, while each post keeps a small denormalized recentComments array capped at three for feed reads. The comments collection is the source of truth for pagination, moderation, edits, and deletions. The post document keeps commentCount and a cached subset.
Example post:
{ _id, authorId, body, createdAt, commentCount,
recentComments: [ { commentId, authorId, body, createdAt } ] }
Example comment:
{ _id, postId, authorId, body, createdAt, status, editedAt }
This removes the 16 MB risk because comment documents grow individually. Feed reads need only the post document. Detail pages and moderation query the comments collection by indexed fields. The recentComments array is explicitly a cache, not an authority, so it can be repaired or rebuilt.
2. Give the indexes and query shapes for (a) feed reads, (b) paginated post detail, and (c) moderator lookup of all comments by an author.
Read the worked answer
(a) Feed reads should be satisfied by the posts collection. If the feed is posts from followed authors, index { authorId: 1, createdAt: -1 }. The recentComments array is embedded, so the feed does not query comments at all. This is the main read-amplification fix.
(b) Post detail: query the comments collection by postId and sort newest-first. Create { postId: 1, createdAt: -1 } and query:
db.comments.find({ postId: p }).sort({ createdAt: -1 }).limit(25)
For subsequent pages, use a range predicate on createdAt plus _id rather than skip, for example createdAt < lastSeenDate, so the index is used efficiently and pagination stays stable.
(c) Moderator lookup by author across posts:
db.comments.find({ authorId: a }).sort({ createdAt: -1 })
with index { authorId: 1, createdAt: -1 }. If moderators usually review only reported comments, add a partial index on { status: 1, createdAt: -1 } with partialFilterExpression { status: 'reported' }; it stays small because only reported comments are indexed. Verify with explain that the detail and moderation queries avoid a blocking SORT stage.
3. What consistency and hot-document risks remain with the subset pattern, and how would you mitigate them without wrapping every comment insert in a multi-document transaction?
Read the worked answer
The recentComments array can lag or become stale, and commentCount can drift. More importantly, updating the same post document on every comment still creates a hot document for viral posts, even though the update is small.
Mitigations: write the comment document first, then update the post with a single atomic update using $push with $each, $sort, $slice: -3 and $inc on commentCount. Retry that update idempotently. If the update fails, a repair job or change stream can reconstruct the subset from the comments collection. Use multi-document transactions only for operations that truly need them, such as deleting a comment and adjusting counters together; otherwise accept eventual consistency for the cached subset.
For extremely hot posts, stop maintaining recentComments above a threshold, for example when commentCount exceeds 1,000. The feed can then read the top three comments with a cached query on { postId: 1, createdAt: -1 } limit 3, or from a short-lived cache. This trades a little feed latency for removing thousands of writes to one post document. Also reconcile commentCount periodically, because $inc is atomic but can drift if a comment insert succeeds and the counter update fails.