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.