Getting posts from users that are being followed: two different implementations

Question

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;

Answer 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.