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

Composite Keys for Timeline Access

Using composite keys like (user_id, timestamp) to enable efficient range queries and chronological ordering of posts.

Composite Keys for Timeline Access

What You'll Learn

You'll learn how using composite primary keys like (user_id, timestamp) enables lightning-fast range queries to retrieve a user's timeline in chronological order.

Why Simple Keys Fall Short

In a denormalized timeline table where each user has thousands of posts stored locally, a single primary key (like post_id) forces the database to scan the entire table and filter by user_id, then sort by time—an expensive operation repeated millions of times per second.

The Composite Key Solution

A composite key combines multiple columns as the primary key. For timelines, (user_id, timestamp) is ideal:

PRIMARY KEY (user_id, timestamp DESC)

This structure physically clusters all posts for the same user together, sorted by timestamp in descending order (newest first). When you query:

SELECT * FROM user_timeline 
WHERE user_id = 12345 
ORDER BY timestamp DESC 
LIMIT 20;

The database performs a single seek to the partition for user_id = 12345, then reads sequentially through already-sorted rows. No filtering across users. No sorting step. Just raw sequential I/O.

The Filing Cabinet Analogy

Imagine a filing cabinet where each drawer is labeled with a user ID, and inside each drawer, documents are pre-sorted by date, newest on top. Finding the latest 20 documents for a user means: open their drawer, grab the top 20 sheets. Done.

Without the composite key, you'd have one giant mixed pile requiring you to search through every document, pull out ones matching the user, then sort them by date.

Key Takeaway

Composite keys like (user_id, timestamp) transform timeline queries from full-table scans into efficient range scans, enabling sub-millisecond retrieval at social media scale by co-locating and pre-sorting data.