Database structure and queries for a Twitter-like posts timeline
|Added at||2017-01-03 23:01|
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:
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!):
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?