I'm posting this question with a Django tag, because I'm working in this environment, but it's actually more or a generic question: I'd like to show something like my timeline on Twitter, i.e. a list of posts of all the people I am following ordered chronologically.
My typical database structure looks like this:
Table Name: Users Columns: UserID PK EmailAddress Password TableName: Friends Columns: UserID PK FK FriendID PK FK TableName: Posts Columns: UserID PK FK Content
If I wanted to retrieve all posts of all the friends for a given user, it looks like this (this is not supposed to be valid SQL!):
SELECT * FROM Posts WHERE UserID IN (LIST OF "Given user friends' IDs")
This works - no problem! However, this does so not scale! Let's assume we have a busy website and our given user has 2,000 friends and there are some ten million posts in the database. In such a scenario, the database query would be highly inefficient and slow.
Can this be solved with a relational database like PostgreSQL or MySQL? If not, how does e.g. Twitter do it?