Modeling Social Data at Scale
Social networks deal with highly interconnected data: users, posts, comments, likes, and follows. Choosing the right database model is critical for read efficiency, as social feeds are extremely read-heavy.
Relational Models
A normalized SQL schema with appropriate indexes can serve users, posts, comments, and reactions well. A home feed may become expensive when it must repeatedly merge large author sets under a strict latency target; measurements can then justify a materialized feed or query-specific projection. The problem is the access pattern and fanout, not the mere presence of a JOIN.
NoSQL & Wide-Column Stores
Databases like Cassandra (used by Instagram, and by Discord until its 2022 migration to ScyllaDB) or ScyllaDB are preferred for massive social scale. They use a denormalized, query-driven modeling approach. Instead of joining tables, you design tables to answer specific queries.
For example, to load a user's timeline, you don't join Users and Posts. You have a specific table user_timeline partitioned by user_id and clustered by timestamp (descending). When a user posts, it is fanned out and written directly to the timelines of their followers. Reads become a bounded single-partition range query whose cost still depends on page size, filtering, storage latency, and hot-partition load.
Graph Databases
For deep relationship queries (e.g., "Find friends of friends who like this band"), general-purpose multi-hop traversal engines like Neo4j or Amazon Neptune excel. However, such engines are usually not used for rendering high-throughput home feeds. This is about traversal engines, not graph data models: Facebook's TAO is a graph-shaped store of objects and associations that does serve the hot path. They are typically used asynchronously for recommendation engines and anti-fraud detection, rather than the hot path of feed generation.
Worked example: read versus write volume
Assume 500 million daily active users who each open their feed 10 times a day: 5 billion feed reads per day, about 58,000 per second on average and perhaps 175,000 per second at peak (3x). If they create 100 million posts per day, that is about 1,160 posts per second. Reads outnumber writes by roughly 50 to 1, which is why precomputing read-shaped data pays off.
Query-driven tables (Cassandra CQL style)
CREATE TABLE user_timeline (user_id bigint, bucket int, post_id timeuuid, author_id bigint, PRIMARY KEY ((user_id, bucket), post_id)) WITH CLUSTERING ORDER BY (post_id DESC);
CREATE TABLE posts_by_author (author_id bigint, post_id timeuuid, body text, PRIMARY KEY ((author_id), post_id)) WITH CLUSTERING ORDER BY (post_id DESC);
Reading the first page of a feed is one partition read of the newest 50 rows. The bucket (for example a month) prevents a hyperactive account from creating a multi-gigabyte partition. Deleting a post means deleting from posts_by_author and either fanning out tombstones or filtering deleted IDs at read time, which is why many systems store only post IDs in timelines and hydrate bodies from a cache.
Which store for which access pattern
| Access pattern | Good fit | Why | Watch out for |
|---|---|---|---|
| Profile, settings, post by id | Sharded relational (MySQL, Postgres) | Transactions, constraints, mature tooling | Cross-shard queries |
| Home timeline page | Wide-column (Cassandra, ScyllaDB) or Redis lists | Single-partition range read | Write amplification from fan-out, tombstones |
| Follow edges, one hop | Sharded adjacency lists, TAO-style store | Simple point and range lookups at huge QPS | Keeping forward and reverse edges consistent |
| Friends of friends, fraud rings | Graph database or offline graph processing | Multi-hop traversal is native | Hot path latency and sharding a graph |
| Counters (likes, followers) | Separate counter store or aggregated stream | Avoid hot-row contention | Drift from source; needs reconciliation |
How real systems do it
- Facebook serves the social graph through TAO, a read-optimized cache and data model of objects and associations backed by sharded MySQL.
- Instagram began on sharded PostgreSQL and moved high-volume feed and inbox style data to Cassandra.
- Twitter kept precomputed home timelines of roughly 800 tweet IDs per user in Redis, hydrating tweet bodies separately.
- Discord stores messages partitioned by channel plus a time bucket, first on Cassandra and since 2022 on ScyllaDB.