Designing a Social Graph Database

Social graphs model relationships between users — follows, friendships, and connections.

Types of Social Graphs

Directed (Twitter/X):
  Alice follows Bob
  Bob does NOT follow Alice

Undirected (Facebook):
  Alice and Bob are friends (mutual)

Storage Options

Relational (Adjacency List)

CREATE TABLE follows (
  follower_id BIGINT,
  followee_id BIGINT,
  created_at  TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id)
);

INDEX ON (followee_id);  -- For "who follows X"

Graph Database (Neo4j)

// Create relationship
MATCH (a:User {id: 1}), (b:User {id: 2})
CREATE (a)-[:FOLLOWS]->(b)

// Find mutual friends
MATCH (me:User {id: 1})-[:FRIENDS]-(mutual)-[:FRIENDS]-(you:User {id: 2})
RETURN mutual.name

Graph Traversal

BFS (Shortest Path / Degrees of Separation)

6 degrees of separation:
Me → Friend → FoF → FoFoF → ...

BFS explores neighbors level by level
Kafka + BFS = scalable social graph traversal

Common Operations

Follow user:       INSERT into follows
Unfollow:          DELETE from follows
Get followers:     SELECT WHERE followee_id = ?
Get following:     SELECT WHERE follower_id = ?
Mutual friends:    Intersection of two follow sets
Friend suggestions: Friends-of-friends NOT already followed

Scaling Reads

For user with 50M followers:
follow_count cached in Redis (INCR/DECR)
follower list sharded (user_id % N shards)

Friend Suggestions (People You May Know)

Algorithm:
1. Find friends-of-friends
2. Rank by mutual friend count
3. Filter already-connected users
4. Cache top-20 suggestions in Redis

Conclusion

For simple social graphs, relational adjacency lists scale well. For complex traversals (shortest path, recommendations), graph databases like Neo4j excel.