面向高效查询的NoSQL数据库设计：未查看好友帖子检索方案

Hey there! Let's tackle this problem of efficiently retrieving unviewed posts from friends in your NoSQL setup. First, let's recap the core requirements to make sure we're aligned: each user has posts, follows other users, and should only see friends' posts they haven't viewed yet—once viewed, those posts shouldn't show up again.

Your initial thought of a NonViewed subcollection under User makes sense but can get inefficient, especially as a user follows more people and accumulates lots of unviewed posts. Let's go through a few optimized approaches that scale better:

Instead of tracking unviewed posts (which grows endlessly as friends post more), track viewed posts per user. This is usually more efficient because most users will view a majority of their friends' posts over time, and the number of viewed posts grows more predictably than unviewed ones.

• 数据结构设计：
  • Add a viewedPosts field to your User document—this can be an array or hash set (depending on your NoSQL database's support, like MongoDB arrays or Firebase Maps) that stores IDs of posts the user has viewed.
  • Keep Post documents lean with core fields: authorId (linked to the user), content, createdAt, etc.
• 查询逻辑：
  1. Fetch the current user's list of friend IDs (stored in a following array in their User document, for example).
  2. Query all Post documents where authorId is in the friend list, and postId is not in the user's viewedPosts array.
  3. Sort results by createdAt in descending order to show the newest unviewed posts first.
• 优缺点：
  • ✅ Pros: Marking a post as viewed is a simple, efficient operation (just append the post ID to viewedPosts). Queries can be optimized with compound indexes on Post.authorId and Post.createdAt.
  • ❌ Cons: If a user views thousands of posts, the viewedPosts array can get large. Most NoSQL databases handle array filtering efficiently (like MongoDB's $nin with indexes or Firebase's whereNotIn), but if you hit limits, you can split viewed posts into time-based subcollections (e.g., viewedPosts_202409 for September 2024).

Create a dedicated PostView collection to track which user viewed which post. This avoids bloating User documents and works well if you need to track extra details like view time or device.

• 数据结构设计：
  • PostView document example:

    {
      userId: "user_123",
      postId: "post_456",
      viewedAt: Timestamp()
    }
    

  • Add compound indexes: { userId: 1, postId: 1 } (to enforce uniqueness—no duplicate view records for the same user-post pair) and { userId: 1, viewedAt: 1 } for time-based queries.
• 查询逻辑：
  1. Fetch the current user's friend IDs.
  2. Query all Post documents where authorId is in the friend list.
  3. Perform a left join with the PostView collection to filter out posts that have a matching userId-postId record.
  4. Sort by createdAt descending.
• 优缺点：
  • ✅ Pros: User documents stay small and scalable. Easy to add metadata like view duration or device later.
  • ❌ Cons: Requires a join operation (like MongoDB's $lookup or two separate queries in Firebase: first fetch friend posts, then fetch viewed post IDs, then filter). With proper indexing, this is still performant for most use cases.

If users mostly care about the newest unviewed posts, combine a "last feed view time" with basic viewed post tracking to avoid filtering hundreds of post IDs every time.

• 数据结构设计：
  • Add a lastFeedViewedAt field to the User document, storing the timestamp when the user last checked their friend feed.
  • Keep Post documents with createdAt and authorId.
• 查询逻辑：
  1. Fetch the user's friend IDs and lastFeedViewedAt timestamp.
  2. Query all Post documents where authorId is in the friend list and createdAt > lastFeedViewedAt.
  3. When the user finishes viewing these posts, update lastFeedViewedAt to the current time.
• 补充优化：To handle posts the user skipped (e.g., scrolled past without clicking), combine this with the viewedPosts array from方案1—use the timestamp to quickly fetch recent posts, then exclude any that are already in viewedPosts.
• 优缺点：
  • ✅ Pros: Blazing fast queries, since you're only filtering by time range instead of comparing dozens/hundreds of post IDs. Perfect for users who only care about the latest updates.
  • ❌ Cons: Doesn't track individual skipped posts unless paired with方案1.

• If your users primarily consume new content and you don't need 100% precise tracking of every skipped post: Combine方案3 + 方案1 for the best balance of speed and accuracy.
• If you need precise tracking of all viewed posts and expect large volumes of view records: 方案2 is the most scalable, as it keeps user documents lean.
• If your NoSQL database handles array filtering well and your users don't view an extreme number of posts: 方案1 is the simplest and most straightforward to implement.

Whichever approach you pick, don't forget to add a compound index on Post.authorId and Post.createdAt—this is critical for fast friend-post queries!

内容的提问来源于stack exchange，提问作者Brejuro