Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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!

share|improve this question
    
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? –  dezso Apr 12 '13 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 '13 at 14:51
    
Sounds like that. But what is the problem with it? –  dezso Apr 12 '13 at 14:59
    
Isn't it too slow with many concurrent users? –  Luccas Apr 12 '13 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. –  dezso Apr 12 '13 at 15:25

2 Answers 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.

share|improve this answer
    
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? –  dezso Apr 13 '13 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 ... –  Erwin Brandstetter Apr 13 '13 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 :) –  dezso Apr 13 '13 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 '13 at 20:04
1  
@Luccas: Note the fix to my query. –  Erwin Brandstetter Apr 13 '13 at 20:24

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

share|improve this answer
    
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. –  Erwin Brandstetter Apr 13 '13 at 19:58
    
@ErwinBrandstetter Thanks, edited. –  dezso Apr 13 '13 at 20:03
    
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. –  dezso Apr 13 '13 at 20:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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