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 contenttimestamp– 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.