Course contentsShow
← System Design
Lesson 1661 of 1,91940. Real-World System: Social Media FeedFree lesson

Timeline Schema Design

Designing the timeline table schema with user_id, post_id, timestamp, and metadata for efficient querying and pagination.

Timeline Schema Design

You'll learn how to structure a database table schema that makes feed retrieval lightning-fast and supports seamless pagination.

The Core Challenge

When a user opens their social media feed, you need to quickly fetch their personalized timeline—a sorted list of posts. This timeline table acts as a pre-computed materialized view that stores which posts belong in each user's feed. Think of it as a personalized catalog index rather than computing "what should I show?" every time someone opens the app.

Essential Schema Components

Your timeline table needs these core fields:

  • user_id – Identifies whose feed this belongs to (partition key)
  • post_id – References the actual post content
  • timestamp – When the post was created or added to feed (sort key)
  • metadata – JSON or additional columns for ranking signals (likes, source, etc.)
CREATE TABLE user_timeline (
    user_id BIGINT,
    post_id BIGINT,
    created_at TIMESTAMP,
    added_at TIMESTAMP,
    metadata JSONB,
    PRIMARY KEY (user_id, created_at DESC, post_id)
);

CREATE INDEX idx_user_pagination 
ON user_timeline (user_id, created_at DESC);

Why This Design Works

The compound primary key (user_id, created_at, post_id) enables:

  • Fast single-user queries: All posts for one user are co-located
  • Natural pagination: "Give me 20 posts before timestamp X"
  • Time-ordered results: Newest posts first without expensive sorting

For pagination, you simply query: "WHERE user_id = ? AND created_at < ? LIMIT 20" using the last seen timestamp as the cursor.

Key Takeaway: A well-designed timeline schema with user_id partitioning and timestamp-based ordering transforms expensive join queries into simple, indexed lookups that scale to billions of feed entries.