Four SQL Patterns That Power Social Network Features at Scale

Building social features like follows, feeds, and mutual connections may appear simple but requires well-known SQL query patterns to function reliably at scale. A follow relationship is typically stored in a basic adjacency table, but self-joins on that table become costly when high-follower accounts generate hundreds of thousands of intermediate rows. Feed timelines are handled via two strategies: fan-out on write pre-inserts posts into each follower's feed for fast reads, while fan-out on read computes the feed dynamically at query time, with production systems combining both approaches depending on follower count. Mutual-friend lookups are graph problems that strain standard SQL joins, and better-scaling solutions involve representing follow lists as sorted arrays or bitsets and intersecting them outside the join layer. Proper indexing in both directions, early use of LIMIT clauses, and hybrid fan-out strategies are the key engineering levers for keeping these queries performant.