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
JOIN follows f2 ON f1.followee_id = f2.follower_id
WHERE f1.follower_id = :user_id
  AND f2.followee_id != :user_id;

This query is ok if the fan-out is low. It feels like it's no longer fine when a few accounts have a few hundred thousand joins, because join now needs to walk a correspondingly large intermediate result set per request. The typical solution isn't a better query, it's a better index and a cap.
composite indexes on (follower_id, followee_id) and (followee_id, follower_id) so both directions of the join hit an index-only scan, plus a LIMIT applied early so the planner doesn't materialize more rows than the response will ever use.

The fan-out feed problem

The “social systems” hard problem is the timeline: display to me my feed of what people I follow posted recently, in order. There are two ways to construct it and they are both right - anything else and outages happen.

Fan-out on write means that when a user posts, you insert a row into every follower's feed table immediately. Reads are then a trivial SELECT * FROM feed WHERE user_id = :id ORDER BY created_at DESC LIMIT 50, fast and simple, because the expensive work happened once at write time. This collapses the moment an account with a very large follower count posts, because that single write turns into millions of feed-table inserts.

Fan-out on read flips it: the feed table doesn't exist, and instead a read does the equivalent of the mutual-join query above merged against a posts table, unioned across everyone you follow, sorted, and limited. This is cheap for the poster and expensive for the reader, and it gets more expensive as the number of accounts someone follows grows, because you're merging N sorted streams per page load.

Real systems use both: fan-out on write for the common case, with a fallback to fan-out on read specifically for high-follower accounts, merged at serve time. The SQL pattern worth internalizing here is the k-way merge via UNION ALL plus a bounded sort, which is exactly what a database's merge-join does internally, you're just doing it explicitly at the point where fan-out-on-write would be too expensive.

Mutual connections as a bitset problem

“People you may know” and “mutual friends” are graph problems disguised as SQL statements, and the naive self-join above doesn't work well, because SQL doesn't provide a native way to define a set intersection of rows. The pattern that actually scales better is to, instead, represent each user's follow list in a compact format (a sorted array or a bitset), and then intersect the lists outside the join machinery, either in application code or with a database extension designed for this use (Postgres's "roaring bitmap"-style extensions, or a materialized intarray intersection). The lesson generalizes: If a relational query's cost is mostly for set intersection instead of row filtering, then typically the solution is to change the way that the data is represented, not to add another index to the same schema.

Recursive CTEs and the two-hop wall

SQL does have a native tool for graph traversal, the recursive common table expression, and it's worth knowing exactly where it's appropriate and where it isn't:

WITH RECURSIVE network AS (
  SELECT followee_id AS user_id, 1 AS depth
  FROM follows WHERE follower_id = :user_id
  UNION
  SELECT f.followee_id, n.depth + 1
  FROM follows f
  JOIN network n ON f.follower_id = n.user_id
  WHERE n.depth < 3
)
SELECT DISTINCT user_id, MIN(depth) FROM network GROUP BY user_id;

This returns everyone within 3 hops of a user, and is clean to traverse a moderately sized graph for a limited depth. It fails for the same reason that all recursive CTEs fail to return results on a dense graph: If you're on the 2nd or 3rd level in a social network then you might end up getting close to all the nodes in the table, and now you're getting close to the size of the table in the recursive step that is scanning and deduplicating your nodes. That's when the right answer is not “a better query,” but rather “a different storage engine”, the dedicated graph database or the precomputed reachability index that is refreshed asynchronously, because the relational engine's per-row execution model just doesn't make for the right cost model for dense multi-hop traversal.

The general pattern

All of these problems have the same pattern: a correct and efficient query at low numbers of rows becomes an inefficient query at a certain graph density, which is fixed not by the "add an index and move on" solution, but by changing either the write path (fan-out), the data representation (bitsets instead of joined rows), or the engine (graph store instead of recursive CTE). Most of the real knowledge and expertise of building a social feature on top of a relational database is in knowing which wall you're approaching before you hit it in production.

StackFeed

SQL Patterns Hidden Inside Social Networks