Designing Instagram: A Deep Dive into Photo Sharing at Scale

def get_feed(user_id, limit=30):
    # Check cache first
    cached_feed = redis.get(f"feed:{user_id}")
    if cached_feed:
        return cached_feed
    
    # Pull for celebrities + recent from follows
    posts = []
    
    # Get celebrity posts (pulled)
    celebrities = get_celebrity_follows(user_id)
    for celeb in celebrities:
        posts.extend(get_recent_posts(celeb, limit=10))
    
    # Get regular followee posts (should be cached/fan-outed)
    regular = get_cached_followee_posts(user_id)
    posts.extend(regular)
    
    # Sort and paginate
    posts.sort(key=lambda x: x.timestamp, reverse=True)
    return posts[:limit]

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(30) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile_photo_url VARCHAR(500),
    bio TEXT,
    follower_count INT DEFAULT 0,
    following_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_follower_count ON users(follower_count);

CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    image_url VARCHAR(500) NOT NULL,
    caption TEXT,
    like_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_user_id ON posts(user_id);
CREATE INDEX idx_created_at ON posts(created_at DESC);
CREATE INDEX idx_user_created ON posts(user_id, created_at DESC);

CREATE TABLE follows (
    follower_id BIGINT NOT NULL REFERENCES users(id),
    following_id BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id)
);

CREATE INDEX idx_following ON follows(following_id);

# Feed cache - user's personalized feed
feed:{user_id}  # List of post IDs

# Profile cache - user info
profile:{user_id}  # User object

# Post cache - individual post
post:{post_id}  # Post object

# Timeline cache - celebrity posts
timeline:{user_id}  # List of celebrity post IDs