DesignAdvanced
Design Instagram-Like Storage
Users, posts, follows, likes, comments and a home feed at social-network scale. The interesting decisions are all about read amplification: how to serve a feed and a like count without recomputing them on every view.
Requirements
- Users follow other users (many-to-many, self-referencing).
- A user posts; a post has a like count and a comment count shown everywhere.
- The home feed shows posts from everyone a user follows, newest first.
- Comments can be threaded (a reply to a comment).
- Follower counts, like counts and comment counts are read constantly and must be instant.
Access patterns
These drive the whole design.
| Pattern | Frequency | Note |
|---|---|---|
| Home feed: posts from everyone I follow, newest first | constant, the core read | The read-amplification problem; the whole design turns on it. |
| A user’s profile: post count, follower count, following count | constant | Denormalized counters; recomputing from follows/likes is too slow. |
| Does A follow B? | constant | Composite unique index on follows(follower_id, followee_id) — index-only. |
| A post’s comments, threaded | per post view | Index comments(post_id); recursive CTE or materialised path for threads. |
| Like / unlike a post | very high write | Insert/delete a like row; update the cached count — hot rows. |
Sketch your entities, keys and indexes from the access patterns above, then reveal the reference design.