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.

enter image description here

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?


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


  • 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


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

2.Mark as seen the group when click group.


  • One single write when seen a group

Any tips and advices are welcome! Thanks!

  • Regarding the con of (1), why an INSERT INTO user_posts SELECT post_id, new_user_id FROM posts p WHERE p.group = 'newly joined group'? wouldn't suffice? Apr 12, 2013 at 14:35
  • But if a group has > 1000 posts, I will create one row for each user_seen_post join table, for example?
    – Luccas
    Apr 12, 2013 at 14:51
  • Sounds like that. But what is the problem with it? Apr 12, 2013 at 14:59
  • Isn't it too slow with many concurrent users?
    – Luccas
    Apr 12, 2013 at 15:04
  • Well, this would be a very narrow table (possibly with many rows), well indexable and everything. I wouldn't expect performance problems with it. Apr 12, 2013 at 15:25

2 Answers 2


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 :)

  • BTW, disk space for such a row would be 24 bytes (heap tuple header + padding) + 2 x 4 bytes (for two integer columns) + 4 byte (item pointer) = 32 bytes (minimum). Double that to provide for the primary key index. Consider this related answer with more details on SO. Apr 13, 2013 at 19:58
  • Let me add here that the size calculation is for the worst-case scenario, in a real life example it would be much lower. I don't say you wouldn't have to handle the data volume somehow, though. Apr 13, 2013 at 20:09
  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
   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.

  • Now what if (we don't know if this is the case, I see) there may be posts unseen from before some already seen ones? That is, visiting the group does not mean that every post was seen? Apr 13, 2013 at 19:34
  • @dezso: My first query was no good, I fixed it. And yes, my simple model is built on "visiting the group" = "seeing all existing posts at the time". I think that's how facebook handles it (at least in the one facebook group I am member of that uses such a feature). But now that you mention it, the OP seems to have in mind to store it for each post ... Apr 13, 2013 at 19:40
  • I was thinking about something similar, with some last seen IDs instead of timestamps. Compared to that, your solution is clearly better :) Apr 13, 2013 at 19:48
  • Thanks for the detailed answers, solutions and advices @ErwinBrandstetter and @dezso! I will give a try about this solution!
    – Luccas
    Apr 13, 2013 at 20:04
  • 1
    @Luccas: Note the fix to my query. Apr 13, 2013 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.