How to implement posts with “seen by” like facebook?

Question

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.

And if I see a specific post inside a group thought a url (facebook.com/groups/123/posts/123), it mark as seen this single post.

My question is about what is the intuitive way and right to model this and performance. I want to know what I am missing or wrong about about to get it right.

What happens on facebook: "When a new member join the group, all the old posts are marked as seen by him". But is it per post or global?

Scenarios:

1. Mark as seen all individual posts when click group.

Pros:

• Easy development
• Easy and fast queries. Could implement a int counter column on each post and a has_many table users_id x post_id
• Getting and individual post I can tell how many users saw without context

Cons:

• How to deal with a new member join a group with > 100 posts? Batch writes?

2.Mark as seen the group when click group.

Pros:

• One single write when seen a group

Any tips and advices are welcome! Thanks!

Answer 1

I think the main thing to consider here is how you store who have seen what. The most convenient way is to store the post-user pairs. Any solution with less granularity will have some cases hard to handle - your second suggestion, if I understood it correctly, would store a group-user pair, plus a flag that all posts are already seen (I imagine that this would be rather the last post ID at the moment of joining the group). This would only work if you can't miss posts, so that the unseen ones form a contiguous group, all having a bigger ID than all already-seen ones.

Since the user-post pairing consists exactly these two fields, which can be, expecting extremely high user and post numbers, 2×8 bytes per row, I wouldn't expect performance problems with 10,000 users and 100,000 posts (provided there are really groups, I mean, not all users belong to all groups and be able to read all posts - in this case, the table would be approximately 30 GB in size (plus the same size for the index necessary for the primary key). This size can only be handled by appropriate hardware and some magic, like archiving old posts as those are queried rarely and so on. If your system exceeds that size, you probably make nice money of it, being able to beef your system up :)

Answer 2

1. Is primitive and rather fast, but produces a lot of rows, which makes it slow after all.

2. Doesn't work. A simple tag seen per (user, group) cannot hold the necessary information which posts have been seen by the user and which haven't.

   I would consider:

3. Save a timestamp (timestamp with timezone or timestamptz for short, to accommodate international clientèle) when it was posted with every post (posted) and one for every combination of user and group when the user last visited (last_visited). This way you only need a single timestamp per user and group.

   This is based on: "visiting the group" = "seeing all existing posts at the time". I think that's how facebook handles this (at least in the one group of mine I am seeing this feature). If you want more granularity, you'll have to go with model 1. and add a flag per (post, user).

The query to get a number for seen for a single post could look something like:

SELECT count(*) AS seen_by
FROM   grp_usr  gu
JOIN   grp_post gp USING (grp_id)
WHERE  gp.post_id = $post_id
AND    gu.last_visited > gp.posted;

To get the numbers for the last n posts in the group:

SELECT post_id, count(*) AS seen_by
FROM   grp_usr  gu
JOIN  (
   SELECT *
   FROM   grp_post 
   WHERE  grp_id = $grp_id
   ORDER  BY posted DESC
   LIMIT  $n
   ) gp USING (grp_id)
WHERE  gu.last_visited > gp.posted
GROUP  BY p.posted DESC;

Write load would be at a minimum.
For extremely high read load I would consider materialized views with specialized indexes, updated at times / events that best fit your load. Do not add more indexes than necessary to grp_usr in this model, where I'd expect the most write load.