3

I have two different implementations for retrieving posts from the users someone is following and I want to know which one you would recommend using. First I'l show you the tables, and then the two implementations.

post table

id (PK)| poster_id (FK) | value | created_on

repost table - necessary for both implementations

reposter_id (FK) | post_id (FK) | created_on

following table

follower_id (FK) | followee_id (FK)

user_feed table - necessary only for second implementation

user_id (FK) | post_id (FK) | created_on

First implementation: Requires the repost table, but no user_feed table. Whenever a user posts something I don't have to add a row for each of their followers in the user_feed table, but the query seems much more computationally expensive.

SELECT * FROM (
    SELECT p.id, p.poster_id, p.value, p.created_at
    FROM post AS p
    WHERE p.'user_id'=23 OR (p.'user_id' IN SELECT 'followee' FROM following WHERE 'follower'=23)
    UNION
    SELECT p.id, p.poster_id, p.value, rp.reposter_id, rp.created_at
    FROM post AS p
    INNER JOIN repost AS rp
    ON rp.post_id = p.id
    WHERE p.'user_id'=23 OR (p.'user_id' IN SELECT 'followee' FROM following WHERE 'follower'=23)))
ORDER BY created_at DESC
LIMIT 80;

Second implementation: Requires the user_feed table and the repost table. I add a record to the user_feed table every time someone posts/reposts something. The record is added for each one of the poster's followers. I only keep, say, 120 records for any particular user in the user_feed table. If a post is made and a user already has 120 records in the user_feed table, the oldest record for that user is deleted and the new one takes its place. If a user requests more records than there are present in the user_feed table for them, then the first implementation is used to retrieve them.

SELECT * FROM (
    SELECT p.id, p.poster, p.value, p.created_at
    FROM post As p
    WHERE p.'id' IN (SELECT 'post_id' FROM user_feed WHERE 'user_id'=23)
ORDER BY created_at DESC
LIMIT 80;

1 Answer 1

1

An interesting question.

Lets leave aside the question of how to represent reposts. Presumably you will have many types of 'post' some of will be sharing some sort of meta data.

The main thrust of your problem is whether to store to data of the post multiple times, once per follower, or have a single copy for the poster and dymamically generate 'feeds' when requested.

Although the single copy in the past would be considered by far the best way. Matching the concept of a normalised relational database which you query. These days, especialy for massivley scaled social network type apps, you would go with the multiple copy, No-SQL style approach.

Although you are using a relational db having a feed per user is a non-relational approach.

The benefit is faster access for you users to thier feed and the ability to put that data on a server by itself, without having to also include all the followed users feeds.

The downside is that it uses more diskspace and there will be a delay between a user posting and thier followers seeing that post.

The critical downside to the relational approach is that your single database becomes too large for any single computer to store in a quickly accessible fashion.

4
  • There won't be a delay for all users though, right? The first few thousand or more users whose feeds are written to first will be able to see it nearly instantly, right?
    – khakis
    Apr 17, 2016 at 21:39
  • Depends on your methodology, you might run out of time to do updates and skip some
    – Ewan
    Apr 17, 2016 at 21:46
  • I'm new to dbms's, so I'm not sure what you mean by "run out of time." If the program writing to the users' feeds is designed to update the feeds of all the users who follow the poster, won't it do just that? And, won't the users whose feeds that are written to first be able to see the updates nearly instantly?
    – khakis
    Apr 18, 2016 at 6:26
  • These are problems of massive scale. The question you have to answer is : given I don't have enough cpu power to do everything, what do i not do? how do I catch up later?
    – Ewan
    Apr 18, 2016 at 8:48

Your Answer

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

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