From the outside, social features seem like something straightforward: follow a user, like a post, see a feed, but when you attempt to implement them at any real scale you find that every single one is something you've got to be aware of, a pattern, a well-documented query pattern with all its failure modes. Here's a step-by-step look at four of them: adjacency list, fan-out feed, mutual-friends join, and some graph traversal that SQL wasn't really designed for.
The adjacency list and why "who do I follow" isn't free
A follow relationship is usually modeled as a plain adjacency table: follows(follower_id, followee_id, created_at). That's the whole schema, and it's deceptively adequate for a long time. The first place it breaks is when you need "who do I follow that also follows them", mutual connections, because that's a self-join against the same table:
SELECT f2.followee_id
FROM follows f1
