You may find this to be a useful resource:
Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems
It discusses at a high-level how twitter dealt with a similar problem.
It is a good read. In the mean time, I hope the below can help you on your journey:
I see this is tagged with OOP. This is more of a database technology/design question. A well-tuned database RDBMS can probably get you far, but you'd want to consider a blend of batch-processing, and real-time db updates. These will require trade offs that are unique to your application: Can a friends post be delayed by seconds, minutes or hours? keep the data users find of high value data flowing quickly, and slow down the lower value data.
What I did, is just to build relationships users-posts, and then we
can look up those relationships and see if that content is or not
This is needs to evolve as your user-base & usage grows.
Some things to consider:
You have de-normalized your Post-users relationship. this is bound to cause scale problems. Even users that signup, but do not view content are costing you space, perf and $$. as user relationships between groups, & friends change, the need to modify data in thins table could get tricky.
Consider storing the data in a way that represents the way that you described your domain model:
- Public-Posts (post-id - post-date)
- User-posts (user-id post-id, post-date)
- Friends-posts (user-id post-id, post-date)
- Group-posts (group-id post-id, post-date)
This way a user's relationship to friends, and groups will impact what they see. (which is probably what users expect).
Also, consider what a Public-Posts table would mean in reducing the data, and need for writes and writes to the database.
Make caching a 1st class concern.
Have you exhausted caching opportunities on the server, on the client and across application tiers. If you have, exhaust them even further. One efficient way to reduce the DB-as-bottleneck problem is to lay off the DB as much as possible.
- Can you cache recent "public" posts in memory?
- Can you cache recent & popular "groups" posts in memory?
- Can you identify common groups that have high read rates?
If your system can take data from a blend of cached data and SQL results, you'll be in much better shape. Even better if you are blending that data with cached data in the browser.
Consider ways that your system can gather cached data quickly, and augment, client side with slower db queried.
- Load common posts (synchronous operation) from cache.
- Start to collect personalized posts (groups/freinds),
- renders the UI
- UI receives the personalized posts and updates the UI.
- UI may cache personalized posts for future requests.
It can help increase the feeling of a fast load time, and reduce the need to retrieve the dame data from the database across requests,
be sure to know the usage patterns of your users. Some of these suggestions will not make sense when you consider how often posts are published.
Consider storing what read and write operations you need to perform, to look for common patterns.
Review how Friendships are being formed
If most people have 10 friends, and you design and test for that, but outliers have 100,000 bad things could happen.
It can also impact how you cache, and rules that keeps outiers from messing up the cache for everyone else. Or, maybe catering to those outliers
Lastly, i would recommend gathering real-world performance data.
When a large system that has specific performance needs, only make changes that you can prove have demonstrable impact. It will make your code more complicated. Ensure you are introducing complexity (and cost of maintenance) for benefits for your users. Throwing complexity at a problem that may be helping users is a sure way to make a system you'd rather not maintain.